MySQLで SQLSTATE[42000] Row size too large(> 8126)のエラーが出たときの対処方法

2020/12/12

「Row size too large(> 8126)」のエラーが出たときの対処方法

「Row size too large(> 8126)」のエラーが出る状況とは

　
開発中のシステムのデバッグをしているときに、各項目に入力できる最大文字数を入力するチェックを行いましたが、そのときに下記のエラーが発生しました。
　

2020-12-01 00:00:00 Error: [SamplesTable.php@saveColumn] >>> SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

2020-12-01 00:00:00 Error: [SamplesTable.php@saveColumn] >>> SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

　
エラーの内容としては、「入力した文字数が多いので処理できません」と言うものです。
　
入力文字数が少ないときは出ていなかったエラーなのですが、入力する文字数が多いと発生するエラーとなります。
　
1レコードあたりの文字数が多い場合に発生しますので、テーブルに含まれるカラム数が多い場合や、varcharの指定文字数が大きいカラムが複数ある場合などに発生する可能性が高まるエラーです。
　
　
最大文字数を入力してのデバッグが十分出ない場合は、本番稼働を始めてからエラーが発生して慌てる原因にもなりますね。
　
　

「Row size too large(> 8126)」エラーの対処方法の概要

　
1レコードあたりの文字数が多い場合に発生するエラーですので、対処方法としては、文字数が増えないようにテーブルを分割する方法もあります。
ですが、テーブルを分割するとシステム全体の見直しも必要になりますので、そのような対応をしないでいい方法を紹介します。
　
　

　
この記事では、上記のように長いテキストを別テーブルに持つことで 1レコードとして持つ文字数を減らす設定をする方法の解説します。（上記はかなりザックリとしたイメージです。）
　
　
なお、今回の解説する方法は「innodb_file_format」の設定が「Barracuda」である必要があります。
　
　

「innodb_file_format」の設定が「Barracuda」であることの確認

　
「innodb_file_format」の設定内容の確認は下記の SQLを実行し、表示される値を確認します。
　

SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';

1	SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';

mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';

+--------------------------+-----------+

| Variable_name | Value |

+--------------------------+-----------+

| innodb_file_format | Barracuda |

| innodb_file_format_check | ON |

| innodb_file_format_max | Barracuda |

| innodb_file_per_table | ON |

+--------------------------+-----------+

　
上記の通り、「innodb_file_format」の設定が「Barracuda」であれば対応することができます。
　
　
「innodb_file_format」の設定が「Barracuda」ではない場合は、「Barracuda」に設定変更を行うことで対応することができます。
　
設定変更方法については、この記事の後半の『「innodb_file_format」の設定を「Barracuda」に変更する』に記載していますので、そちらを確認してください。
　
　
「innodb_file_format」の設定変更は、MySQLの設定ファイル「/etc/my.cnf」を変更しますが、レンタルサーバなどで「innodb_file_format」が「Barracuda」がなっておらず、かつ、「innodb_file_format」の設定を変更することが出来ない場合は、この記事の対応方法を採用することは出来ません。
（私が借りているサーバの一つである Xサーバーは「Barracuda」でした。）
　
　

現在のテーブルの「ROW_FORMAT」を確認する

　
環境を確認して「innodb_file_format」の設定が「Barracuda」であれば、「ROW_FORMAT=DYNAMIC」「ROW_FORMAT=COMPRESSED」の設定を行います。
　
「ROW_FORMAT=DYNAMIC」「ROW_FORMAT=COMPRESSED」の設定は、テーブル単位で行いますが、設定変更を行う前に現在の設定を確認しておきます。
　
確認には下記の SQLを実行します。
　

SHOW TABLE STATUS;

1	SHOW TABLE STATUS;

　
上記の SQL文を実行すると、各テーブルのステータスが表示されます。
　

mysql> SHOW TABLE STATUS;
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment          |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------+
| samples | InnoDB |      10 | Compressed |    5 |           1850 |       16384 |               0 |        16384 |         0 |             21 | 2020-01-01 00:00:00 | NULL        | NULL       | utf8_general_ci |     NULL |                | サンプルテーブル |
| users   | InnoDB |      10 | Compact    |   15 |            782 |       16384 |               0 |        16384 |         0 |           NULL | 2020-01-01 00:00:00 | NULL        | NULL       | utf8_general_ci |     NULL |                | ユーザーテーブル |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------+
2 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS;

+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------+

| samples | InnoDB | 10 | Compressed | 5 | 1850 | 16384 | 0 | 16384 | 0 | 21 | 2020-01-01 00:00:00 | NULL | NULL | utf8_general_ci | NULL | | サンプルテーブル |

| users | InnoDB | 10 | Compact | 15 | 782 | 16384 | 0 | 16384 | 0 | NULL | 2020-01-01 00:00:00 | NULL | NULL | utf8_general_ci | NULL | | ユーザーテーブル |

2 rows in set (0.00 sec)

　
上記の「Row_format」欄が「Compact」になっていることが多いと思いますが、これを「DYNAMIC」「COMPRESSED」に変更します。（上記はすでに変更した後ですので、「samples」の「Row_format」は「Compressed」となっています。）
　
　

「SHOW TABLE STATUS;」文の使い方

　
上記の通り「SHOW TABLE STATUS;」と記述するとデータベースに登録されているすべてのテーブルの情報が表示されますので、テーブルを指定したい場合は、下記の SQLのようにしてテーブルを指定します。
　

SHOW TABLE STATUS where name="samples"
SHOW TABLE STATUS like "%samples%"

1 2	SHOW TABLE STATUS where name="samples" SHOW TABLE STATUS like "%samples%"

　
　
また、下記の SQL文でも似たようなテーブルの設定情報を表示することが出来ます。
　

select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='sample' and TABLE_NAME='samples';

1	select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='sample' and TABLE_NAME='samples';

「ALTER TABLE samples ROW_FORMAT=COMPRESSED;」で設定を変更する

　
「ROW_FORMAT」の設定変更は簡単で、下記の Alter Tableの SQL文を実行するだけです。
　

ALTER TABLE samples ROW_FORMAT=COMPRESSED;

1	ALTER TABLE samples ROW_FORMAT=COMPRESSED;

　
上記の「samples」のところを変更したいテーブル名に変更します。
　
これで設定が変更され、「Row size too large(> 8126)」のエラーは出なくなります。
　
　

「ROW_FORMAT」の「DYNAMIC」と「COMPRESSED」の違い

　
エラーメッセージには「ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED」と書かれていますが、「DYNAMIC」と「COMPRESSED」の違いを確認しておきます。
　
https://dev.mysql.com/doc/refman/5.6/ja/innodb-row-format-dynamic.html
https://dev.mysql.com/doc/refman/5.6/ja/innodb-compression.html
　
「ROW_FORMAT」の「DYNAMIC」と「COMPRESSED」の説明は、上記の MySQLのオフィシャルの資料に記載があります。
「COMPRESSED」の場合は、データを圧縮して取り扱うため、I/Oやメモリ使用量の軽減などで処理を高速化される可能性があるようです。
　
実験などを行って確かめたわけではないため確実なことは言えませんが、「COMPRESSED」の方を第一選択肢として設定しておく方が良さそうです。
　
　

「ROW_FORMAT=DYNAMIC」「ROW_FORMAT=COMPRESSED」は create文に記述する

　
先の「Alter table」文を使って「ROW_FORMAT」を設定する方法は既存のテーブルの設定変更のときに使用する方法です。
ですが、新たに作成するテーブルの「ROW_FORMAT」をあらかじめ指定する場合は下記のように Create文に記述します。
　

CREATE TABLE `samples` (
  `id`          int(11)     NOT NULL AUTO_INCREMENT            COMMENT 'サンプルid',
  `sample_name` varchar(50) DEFAULT NULL                       COMMENT 'サンプル名',
  `sample_type` varchar(50) DEFAULT NULL                       COMMENT 'サンプルタイプ',
  `created`     datetime    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
  `modified`    datetime    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新日時',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `sample_idx` (`sample_name`),
  KEY `search_idx` (`sample_name`,`sample_type`)
) ROW_FORMAT=COMPRESSED ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='サンプルテーブル';

CREATE TABLE `samples` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'サンプルid',

`sample_name` varchar(50) DEFAULT NULL COMMENT 'サンプル名',

`sample_type` varchar(50) DEFAULT NULL COMMENT 'サンプルタイプ',

`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',

`modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新日時',

PRIMARY KEY (`id`),

UNIQUE KEY `id_UNIQUE` (`id`),

KEY `sample_idx` (`sample_name`),

KEY `search_idx` (`sample_name`,`sample_type`)

) ROW_FORMAT=COMPRESSED ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='サンプルテーブル';

　
上記のように Create文のテーブルの設定である「ENGINE」や「CHARSET」を記述するところに並行して「ROW_FORMAT」の設定を記述します。
　
　
　

「innodb_file_format」の設定を「Barracuda」に変更する

　
今回の「ROW_FORMAT」を「DYNAMIC」「COMPRESSED」に変更する方法は、「innodb_file_format」が「Barracuda」である必要があります。
ですが、「Barracuda」になっていない場合は、それを変更する必要があります。
　
変更する対象ファイルは、MySQLの設定ファイル「/etc/my.cnf」です。
　

# -------------------------------------------
# InnoDB behavior
# -------------------------------------------
# [Global]
innodb_file_format         = Barracuda
innodb_write_io_threads    = 8
innodb_read_io_threads     = 8
innodb_stats_on_metadata   = 0
innodb_max_dirty_pages_pct = 90
innodb_adaptive_hash_index = 1
innodb_adaptive_flushing   = 1
innodb_strict_mode         = 1
innodb_io_capacity         = 3000
innodb_autoinc_lock_mode   = 1
innodb_change_buffering    = inserts
innodb_old_blocks_time     = 500

# -------------------------------------------

# InnoDB behavior

# -------------------------------------------

# [Global]

innodb_file_format = Barracuda

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_stats_on_metadata = 0

innodb_max_dirty_pages_pct = 90

innodb_adaptive_hash_index = 1

innodb_adaptive_flushing = 1

innodb_strict_mode = 1

innodb_io_capacity = 3000

innodb_autoinc_lock_mode = 1

innodb_change_buffering = inserts

innodb_old_blocks_time = 500

　
「/etc/my.cnf」を開き、上記のように「innodb_file_format」を「Barracuda」に変更します。
　
サーバによってはそもそも「innodb_file_format」の設定の記述がない場合もあります。
その場合は、上記の設定をコピペするといいかと思います。
　
ただし、「my.cnf」は処理の順番がありますので、「my.cnf」ファイルの一番最後に追記しても動かない場合があるようです。
　
「innodb_file_format」を追記しても設定が反映されない場合は、「my.cnf」にある「innodb_*****」の記述を探して、それらの近くに追記して試して見てください。
　
　
また、変更した「my.cnf」の内容を反映させるには MySQLを再起動する必要があります。
　

systemctl restart mysql
service mysql restart

1 2	systemctl restart mysql service mysql restart

　
MySQLを再起動する場合は、上記のコマンドなどを実行します。
（「mysql」は「mysqld」でも問題ありません。）
　
　
その他の起動方法、起動ステータスの確認などについては「Apacheの起動しているかの確認方法と起動、再起動、終了のコマンド」に記事を書いていますので参考にしてください。
　
　

XAMPPの場合の「my.cnf」設定変更方法

　
XAMPPの場合は「my.cnf」がありません。
代わりに「c:\xampp3\mysql\bin\my.ini」がありますので、こちらを修正します。
　
また、私の XAMPP環境では「innodb_file_format」がありませんでしたので、上記に記載した他のサーバの設定内容をそのままコピペしました。
　
　
先にも書きましたが、「innodb_file_format」の設定は「my.ini」のどこに記述してもいいと言うものではなさそうで、「my.ini」の中段辺りにある「innodb_data_home_dir」の近くに記述することで処理が反映されるようになりました。
　
また、更新した「my.ini」の内容を反映させるには MySQLの再起動が必要です。
XAMPPの場合はコントロールパネルから「Stop」「Start」ボタンを押せば OKです。
　
　

「innodb_file_format」の「Antelope」「Barracuda」の違い

　
「innodb_file_format」の「Antelope」「Barracuda」の違いは、「Antelope」が古い形式で、「Barracuda」は問題点を改善した新しい形式、と言えます。
　
　

　
最初にも記載した上記のように、MySQLでは varchar型や text型などの可変長のカラムがあった場合、すべての文字列を一つのテーブルに保存するのではなく、可変長のカラムのテキストを保存する別のテーブルに保存するようになっています。
　
ですが、
「Antelope」では、先頭の 768byteはテーブルに保存し、残りを可変長用のテーブルに保存しています。
そのため、可変長のカラムが多いテーブルの場合は入力する文字列によっては 1レコードの文字数制限に達してしまう問題がありました。
　
それを改善したのが「Barracuda」です。
「Barracuda」では、すべての文字列を可変長用のテーブルに保存し、元のテーブルには 20byteのポインタのみを格納するようになっています。
　
そのため、200近い数の varcharカラムを持つようなテーブルを作成しなければ 1レコードの文字数制限には達しないようです。
　
　
https://qiita.com/rhap/items/298cbd4b8e15a212df98
上記の記事を読むとカラム数の限界を理解することが出来るかと思います。
　
　
ちなみに、MySQL5.7.7以上だと「innodb_file_format」のデフォルトの設定が「Barracuda」になるようですので、せっかく書いたこの記事は読まれることが徐々に減っていくのだろうと思います．．．．

- MySQL

最後までお読みいただきましてありがとうございます。
この記事が参考になったと思いましたらソーシャルメディアで共有していただけると嬉しいです！

Message コメントをキャンセル

※入力いただいたコメントは管理者の承認後に掲載されます。

: MySQL、CakePHP 2.3で「tinyint(1)」の Boolean型の動作を再確認

MySQL＋CakePHPの環境で「tinyint(1)」を利用する際の動作を検証。「tinyint(1)」の Boolean型について CakePHPでは自動処理が実施されていることを確認しました。

: MySQLの「tinyint(1)」の悲劇　Boolean型になるとは．．．

MySQLで tinyint(1)を指定すると Boolean型を指定したことになります。意外な誤解があります。

: 別サーバの MySQLにアクセスする設定・ファイアウォール設定、ポート設定、MySQL設定、テーブルへ権限付与の方法

外部サーバからアクセス方法解説。GRANTで権限付与。my.cnfのbind-addressで制限解除。3306ポートの開放、ファイアウォールの制限解除。確認方法と設定方法を詳細解説。

: Insert On Duplicate Key Update構文の使い方

レコードの有無で Insert、Updateを切り替える On Duplicate Key構文の使い方の説明です。

: MySQLの数値型（int、tinyint、bigint、decimal、number、float）の解説

MySQLの数値型についてテーブル設計のたびに調べているような気がしたので、調べてまとめてみた。数値型には整数型、固定小数点型、浮動小数点型がある。

: MySQLのタイムゾーン（mysql_tzinfo_to_sql）の設定方法・XAMPP環境の解説も

MySQLのタイムゾーンテーブル（mysql.time_zone）の中身を確認し、からの場合は追加する方法を解説。LinuxとWindows・XAMPPとで異なる対応方法についてそれぞれ解説。