■ はじめに
現実を前にして正規化などといった理想を捨て去ったとある人は、
とある日とあるスキーマに全知全能の神を降臨させようと画策した。
すなわち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
に設定する
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
に設定して再度確認する
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
を除外する
- 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。
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
した結果をブラウザ上に表示できないかもしれない。
おわり!