Row size too large (> 8126). が出たので、解決がてら周辺知識をまとめてみた

環境

mysql 5.6

現象

大量のTEXT型のカラムに対してデータをいれる際に下記のエラーがMysqlによって吐かれた。

mysql

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help

解決方法

エラー文にかかれているように
ROW_FORMAT=DYNAMICか ROW_FORMAT=COMPRESSEDをテーブルに設定してあげればOK!

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hoge1` TEXT NOT NULL AUTO_INCREMENT,
  `hoge2` TEXT NOT NULL AUTO_INCREMENT,
   :
   :
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

注意点

上記だけだと正しく反映されない場合がある。
それはSHOW TABLE STATUS LIKE [対象テーブル名]\Gで確認できる。
ROW_FORMATが指定したものになっていれば良いが出来ていない場合がある。
その際は下記の設定をmy.cnfにする必要がある。
my.cnfがどこ？という場合は右記で確認 mysql --help | grep my.cnf

my.cnf

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

再起動後に先ほどのcreate文を流せば反映されるはず。

そもそもの話

my.cnfに指定したBarracudaとは？

innodbにはAntelopeとBarracudaとあり、Barracudaのほうが新しいフォーマット
違いとしては
Antelope
可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の先頭768バイトを B-tree ノードのインデックスレコードに格納し、残りをオーバーフローページに格納する。

Barracuda
可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の全てを外部のオーバーフローページに格納し、クラスタインデックスレコードにそのページへのポインタ(20B)のみを格納する。

つまりBarracudaはポインタのみを格納するためより多くの文字列を格納できる！

ROW_FORMATにDynamicとかCompressedって何？

ROW_FORMATの種類は現状4種類!

ROW_FOMATの種類	何者？	対応innodb_file_format
Redundant	かつて使われていたもの	Antelope
Compact	myql5.0から追加されUTF8の最適化やRedundantよりもデータサイズが小さくなる。	Antelope
Dynamic	データの圧縮を行わない	Barracuda
Compressed	データを圧縮しデータサイズを縮小してI/O現象の効果	Barracuda

何も指定しないとCompactになります。
さきほどの注意事項でBarracudaを設定しないとDynamicとCompressedを設定できなかったのも
この対応表を見ればすっきり！

DynamicとCompressedどっちを選択？

Dynamicはデータをgzipで圧縮するようで、その分スループットが落ちるそうなので、
パフォーマンスを気にしないならCompressedかな？

おまけ

RailsのMigrationでROW_FORMATを指定

create_table :table_name, options: 'ROW_FORMAT=DYNAMIC' do |t|
## カラム
end