【MySQL5.6->5.7】 とてもたくさんの列をもったテーブルをつくりたい／Row size too large (> 8126)についてかんがえる

■ はじめに

現実を前にして正規化などといった理想を捨て去ったとある人は、
とある日とあるスキーマに全知全能の神を降臨させようと画策した。
すなわちTEXT型の列を大量に備えたテーブルを用意して、
あらゆる情報と状況を何とかしてもらおうと企図したのだった……！！

■ やってみる

MySQL5.6を実行できる環境を用意して、
とりあえず以下のようなCREATE TABLE文を叩いてみる。

mysql> CREATE TABLE zeus (
    ->   col1 TEXT
    -> , col2 TEXT
    -> , col3 TEXT
-- 中略
    -> , col195 TEXT
    -> , col196 TEXT
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ERROR 1118 (42000): 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.

一行あたりのデータ長が8126バイトを超えているから、
どれか列をTEXT型かBLOB型に変えるなり、ROW_FORMAT=DYNAMICかROW_FORMAT=COMPRESSED にしてね、と言われる。
もう既に全ての列はTEXT型にしてあるため前者は問題ない。
（以下『TEXT型』とあるとき、『TEXT型ないしBLOB型』と読み替えても差し支えない）

▼ 試してみたこと1: innodb_strict_mode=0にする

なんかエラーメッセージで提案されていたけれど、こっそり許してもらえないかお願いしてみる。

● 1. innodb_strict_mode=0に設定する

my.cnf

innodb_strict_mode=0

● 2. CREATE TABLEする

mysql> CREATE TABLE zeus (
    ->   col1 TEXT
    -> , col2 TEXT
    -> , col3 TEXT
-- 中略
    -> , col195 TEXT
    -> , col196 TEXT
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 139
Message: 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 row in set (0.00 sec)

メッセージの内容は同じだけれど、ERRORではなくWarningとなりCREATE TABLEすることはできた。
※MySQL5.6でのinnodb_strict_modeのデフォルト値は無効なので、特に変更していなければ実ははじめからWarningで処理が通る。

● 3. INSERT INTOしてみる

mysql> INSERT INTO zeus VALUES (
    ->   'sugoi'
    -> , 'sugoi'
    -> , 'sugoi'
-- 中略
    -> , 'sugoi'
    -> , 'sugoi'
    -> );
Query OK, 1 row affected (0.00 sec)

なんだよゆーじゃん！

● 4. もっとINSERT INTOしてみる

mysql> INSERT INTO zeus VALUES (
    ->   'sugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoi'
    -> , 'sugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoi'
    -> , 'sugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoi'
-- 中略
    -> , 'sugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoi'
    -> , 'sugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoisugoi'
    -> );
ERROR 1118 (42000): 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.

んあーっ！？
こんなふうに時間差で問題が発覚するのはつらい。。。

▼ 試してみたこと2:　ROW_FORMAT=DYNAMICかROW_FORMAT=COMPRESSED にする

こっそり許してもらえても本質的には問題解決しなかったため、
エラーメッセージで提案されていたことを試してみる。

● 1. ALTER TABLEでROW_FORMAT=DYNAMICにしてみる

mysql> ALTER TABLE zeus ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 3 warnings (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 3

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1478
Message: InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
*************************** 2. row ***************************
  Level: Warning
   Code: 1478
Message: InnoDB: assuming ROW_FORMAT=COMPACT.
*************************** 3. row ***************************
  Level: Warning
   Code: 139
Message: 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.
3 rows in set (0.01 sec)

ROW_FORMAT=DYNAMICにするためには、innodb_file_formatが関係するとのこと。
メッセージにassuming ROW_FORMAT=COMPACTとあるように、
ROW_FORMATはMySQL5.6のデフォルトであるCompactから結局変わっていない。

mysql> SHOW TABLE STATUS LIKE 'zeus';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| zeus | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2017-06-02 14:53:08 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

● 2. innodb_file_formatを確認する

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file_format';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| innodb_file_format | Antelope |
+--------------------+----------+
1 row in set (0.00 sec)

MySQL5.6はデフォルトだとinnodb_file_format=Antelopeとなっている。
ROW_FORMAT=DYNAMICを設定するためにはinnodb_file_format=Barracudaとしなければならない。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-compression-syntax-warnings.html

Antelope (すなわちROW_FORMAT=COMPACTまたはROW_FORMAT=REDUNDANT) は、
TEXT型だと各列先頭768バイトまでがメッセージの言う『Row size』に含まれる。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-row-format-antelope.html
よってTEXT型の列しか存在しないテーブルをCREATE TABLEしたときには『Row size』の上限に達していなくても、
INSERT INTOしたときは各列の最大768バイトまで『Row size』に含まれるため、上のようにエラーとなる場合がある。

そういうわけで単純計算すると、TEXT型の列を11持つテーブルについて、
それぞれに768バイト以上のデータを格納しようとすると上限に達してエラーになる。
（なおinnodb_strict_mode=1であろうと、TEXT型の列を11持つテーブルをCREATE TABLEしてもエラーにはならない）

※このあたりの話題はたくさんある『Row size too large (> 8126)』に関する記事で
大抵詳しく語られていることなので、更なる詳細はここでは割愛します。

● 3. innodb_file_format=Barracudaに設定して再度確認する

my.cnf

innodb_file_per_table
innodb_file_format=Barracuda
innodb_file_format_max=Barracuda

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        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

ROW_FORMAT=DYNAMICを設定するためにはinnodb_file_per_tableも設定しないとだめ。

● 4. もういちどALTER TABLEでROW_FORMAT=DYNAMICにしてみる

mysql> ALTER TABLE zeus ROW_FORMAT=DYNAMIC;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

どぼぢで？

● 5. sql_modeからNO_ENGINE_SUBSTITUTIONを除外する

my.cnf

- sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
+ sql_mode=STRICT_TRANS_TABLES

これにきづけなくてほんとうにつらかった。かなしかった。

● 6. みたびALTER TABLEでROW_FORMAT=DYNAMICにしてみる

mysql> ALTER TABLE zeus ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS LIKE 'zeus';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| zeus | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-06-02 18:51:11 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

ようやくROW_FORMAT=DYNAMICにできた。
さてBarracudaはTEXT型のみだと、はたして最大でどれだけの列を持つことが可能なんだろうか。

● 7. ためしにALTER TABLEでADD COLUMNしてみる

mysql> ALTER TABLE zeus ADD COLUMN `col197` TEXT AFTER `col196`;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

そーなのかー

そういうわけで、TEXT型の列は196までしかつくることができないっぽい。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-row-format-dynamic.html
https://bugs.mysql.com/bug.php?id=69336

格納されるデータが41バイト以上であるとき、データはすべてオフページに格納され、
インラインに格納されるのは20バイトのポインタだけであるが、
格納されるデータが40バイト以下であるとき、データをすべてインラインに格納するために、
予め41バイトを『Row size』として予約しているという解釈でいいのだろうか。

日本語で書かれたいくつかの記事や書籍も見てみたけれど、
TEXT型が予約するのは20バイトのポインタだけであるという旨が書いてあるように思われる。
であるならば列は400くらい持てる計算になるけれど、実際はその半分も持つことができないみたい。
41バイトであるならば41*196=8036となり、8126にかなり近い値になるように思えるのだけれど、
まず間違いなくきっと自分が何かを知らなくて、自分が誤っている。ですので詳しい方ご教授ください。。。。

いずれにせよBarracudaだと、CREATE TABLEしたときには『Row size』の上限に達していなければ、
少なくともTEXT型の列にデータを格納したとしても『Row size』の上限に達することはない。はず。

● 8. 全知全能の神が持てる列数の最大が196でいいの？

駄目です

そもそも『Row size』の8126とはどこからきているのかというと、
デフォルト値で設定されたinnodb_page_sizeの16Kの半分からきている。

ではそのinnodb_page_sizeを引き上げればいいのではないかというと、
『16K バイトを超えるページサイズでは通常な動作が保証されません』とあり、引き上げられない。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-restrictions.html

冒頭のとある人はトランザクションまで捨ててエンジンをMyISAMに変更するしかないのか。。。

▼ 試してみたこと3: MySQLのバージョンを5.7にする

MySQL5.7だとinnodb_page_sizeは64Kまで対応している！！
https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

みんな！MySQL5.7にしよう！
XAMPPのバンドルがMySQL5.6.3以降MariaDBに変わっているからできないって？ 僕もそう思う。

● 1. innodb_page_size=64Kに設定する

MySQL5.7を実行できる環境を用意する。ちなみにこの例のOSはCentOS7.2。

my.cnf

innodb_page_size=64K

# systemctl restart mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
# cat /var/log/mysqld.log | grep ERROR
2017-06-02T22:09:33.436269Z 0 [ERROR] InnoDB: Data file './ibdata1' uses page size 16384, but the innodb_page_size start-up parameter is 65536
2017-06-02T22:09:33.436314Z 0 [ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile './ibdata1' could not be found in the doublewrite buffer.
2017-06-02T22:09:33.436326Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-06-02T22:09:34.037317Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-06-02T22:09:34.037352Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-06-02T22:09:34.037358Z 0 [ERROR] Failed to initialize plugins.
2017-06-02T22:09:34.037362Z 0 [ERROR] Aborting

あっ、そっかぁ……
ibdataのpage sizeは16Kなのにinnodb_page_size=64Kなのはだめであると。

ではibdataのpage sizeを64Kに引き上げればいいのではないかというと、それはできないらしい。
ibdataのpage sizeは後から変更できない。つまりmysql_upgradeみたいなサムシングで何とかできない。

MySQLのインスタンスが作成されるときのみ、ibdataのpage sizeは設定できる。
そういうわけで、新規インストールしてdumpしたおいたデータを突っ込むしかない。

● 2. innodb_page_size=64Kに設定されたインスタンスを作成する

# mysqld --initialize --innodb_page_size=64K --user=mysql
# cat /var/log/mysqld.log
2017-06-02T22:56:12.057688Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-06-02T22:56:12.061962Z 0 [Warning] InnoDB: innodb-page-size has been changed from the default value 16384 to 65536.
2017-06-02T22:56:14.584273Z 0 [Warning] InnoDB: New log files created, LSN=130886
2017-06-02T22:56:15.011321Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-06-02T22:56:15.164809Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ********-****-****-****-************.
2017-06-02T22:56:15.165817Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-06-02T22:56:15.166586Z 1 [Note] A temporary password is generated for root@localhost: ********
# sed -i -e '$ a innodb_page_size=64K' /etc/my.cnf
# systemctl start mysqld.service
# mysql -uroot -p

mysql> SET password FOR root@localhost=password('********');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE SCHEMA sandbox;
Query OK, 1 row affected (0.00 sec)

mysql> USE sandbox;
Database changed
mysql> CREATE TABLE zeus (
    ->   col1 TEXT
    -> , col2 TEXT
    -> , col3 TEXT
-- 中略
    -> , col396 TEXT
    -> , col397 TEXT
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE zeus ADD COLUMN `col398` TEXT AFTER `col397`;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 16383. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> SHOW TABLE STATUS LIKE 'zeus';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| zeus | InnoDB |      10 | Dynamic    |    0 |              0 |       65536 |               0 |            0 |         0 |           NULL | 2017-06-03 08:13:50 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.01 sec)

innodb_page_size=64Kにはなったけれど『Row size』は16Kまでにしかならなかった。そういう仕様らしい。

ちなみにSHOW TABLE STATUSからもわかるように、このまわりのシステム変数のデフォルト値に変更がある。
*がついているシステム変数はDeprecatedであるため、バージョンアップ時には記述を無効にすることが望ましい。

システム変数名	変更前デフォルト値	変更後デフォルト値	変更が行われたバージョン
innodb_strict_mode	OFF	ON	5.7.7
innodb_file_per_table	OFF	ON	5.6.6
*innodb_file_format	Antelope	Barracuda	5.7.7
*innodb_file_format_max	Antelope	Barracuda	5.7.7
*innodb_large_prefix	OFF	ON	5.7.7
innodb_default_row_format	--	Dynamic	5.7.9 (新設)

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
またsql_modeの変更にも注意。https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

列数もinnoDBの最大値が1017であるのに対して、TEXT型のみだと397までしか持てなかった。
MySQLのスキーマに全知全能の神は実在しない。いいね？

※innodb_page_sizeを変更するメリットデメリットも割愛します。理想を捨ててテーブルを設計するのもそうだけれど。
ただ強いて言うならば、とてもたくさんの列をもったテーブルは、MySQLWorkbenchみたいなのでSELECTすると
表示ですごくモッサリする。PHPMyAdminみたいなのだとSELECTした結果をブラウザ上に表示できないかもしれない。

おわり！