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

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


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

```sql:
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`する

```sql:
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)
```

```sql:
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`してみる

```sql:
mysql> INSERT INTO zeus VALUES (
    ->   'sugoi'
    -> , 'sugoi'
    -> , 'sugoi'
-- 中略
    -> , 'sugoi'
    -> , 'sugoi'
    -> );
Query OK, 1 row affected (0.00 sec)
```
なんだよゆーじゃん！
###● 4. もっと`INSERT INTO`してみる

```sql:
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`にしてみる

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

```sql:
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`から結局変わっていない。

```sql:
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`を確認する

```sql:
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
```

```sql:
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`にしてみる

```sql:
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`を除外する
```diff:my.cnf
- sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
+ sql_mode=STRICT_TRANS_TABLES
```
これにきづけなくてほんとうにつらかった。かなしかった。
###● 6. みたび`ALTER TABLE`で`ROW_FORMAT=DYNAMIC`にしてみる
```sql:
mysql> ALTER TABLE zeus ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

```sql:
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`してみる
```sql:
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
```

```sql
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`した結果をブラウザ上に表示できないかもしれない。

おわり！