#わからない事、知りたいこと
古いmysqldumpでダンプしたSQLファイルをMYSQL5.7.18でimportすると次のようなエラーになる。

ERROR 1118 (42000) at line 52: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

よって、インポートできない。このエラーを解決してSQLファイルを読み込み、データベースを復元したい。

やってみたこと

古いmysqldumpでダンプしたSQLファイルの先頭に次のように記述した。

SET GLOBAL innodb_default_row_format=DYNAMIC;

しかし、エラーメッセージは変わらなかった。

回答者の要望

`SHOW GLOBAL VARIABLES LIKE 'innodb_file_%';` の結果

古いサーバー

Variable_name	Value
innodb_file_format	Antelope
innodb_file_format_check	ON
innodb_file_format_max	Antelope
innodb_file_per_table	ON

新しいサーバー（MYSQL5.7.18）

Variable_name	Value
innodb_file_format	Barracuda
innodb_file_format_check	ON
innodb_file_format_max	Barracuda
innodb_file_per_table	ON

エラーの出るテーブル定義

sql
1CREATE TABLE `bk_table` (
2  `a_bk_id` int(11) NOT NULL AUTO_INCREMENT,
3  `a_id` int(11) NOT NULL,
4  `a_id2` text COLLATE utf8_unicode_ci NOT NULL,
5  `a_id3` int(11) NOT NULL,
6  `a_id4` int(11) NOT NULL DEFAULT '1',
7  `a_id5` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
8  `a_id6` int(11) NOT NULL,
9  `a_id7` tinyint(4) NOT NULL DEFAULT '1',
10  `a_id8` tinyint(4) NOT NULL DEFAULT '0',
11  `a_id9` tinyint(4) NOT NULL DEFAULT '0',
12  `a_id10` int(11) NOT NULL DEFAULT '0',
13  `a_id11` text COLLATE utf8_unicode_ci NOT NULL,
14  `a_id12` text COLLATE utf8_unicode_ci NOT NULL,
15  `a_id13` tinyint(4) NOT NULL DEFAULT '0',
16  `a_id14` text COLLATE utf8_unicode_ci NOT NULL,
17  `a_id15` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
18  `a_id16` text COLLATE utf8_unicode_ci NOT NULL,
19  `a_id17` text COLLATE utf8_unicode_ci NOT NULL,
20  `a_id18` int(11) NOT NULL DEFAULT '0',
21  `a_id19` tinyint(4) NOT NULL,
22  `a_id20` text COLLATE utf8_unicode_ci NOT NULL,
23  `a_id21` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
24  `a_id22` int(11) NOT NULL DEFAULT '17',
25  `a_id23` text COLLATE utf8_unicode_ci NOT NULL,
26  `a_id24` text COLLATE utf8_unicode_ci NOT NULL,
27  `a_id25` int(11) NOT NULL,
28  `a_id26` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
29  `a_id27` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
30  `a_id28` text COLLATE utf8_unicode_ci NOT NULL,
31  `a_id29` text COLLATE utf8_unicode_ci NOT NULL,
32  `a_id30` int(11) NOT NULL DEFAULT '0',
33  `a_id31` int(11) NOT NULL,
34  `a_id32` int(11) NOT NULL,
35  `a_id33` int(11) NOT NULL,
36  `a_id34` datetime NOT NULL,
37  `a_id35` text COLLATE utf8_unicode_ci NOT NULL,
38  `a_id36` int(11) NOT NULL,
39  `a_id37` int(11) NOT NULL,
40  `a_id38` tinyint(4) NOT NULL DEFAULT '1',
41  `a_id39` text COLLATE utf8_unicode_ci NOT NULL,
42  `a_id40` text COLLATE utf8_unicode_ci NOT NULL,
43  `a_id41` text COLLATE utf8_unicode_ci NOT NULL,
44  `a_id42` int(11) NOT NULL DEFAULT '0',
45  `a_id43` int(11) NOT NULL DEFAULT '0',
46  `a_id44` int(11) NOT NULL DEFAULT '0',
47  `a_id45` int(11) NOT NULL DEFAULT '0',
48  `a_id46` int(11) NOT NULL DEFAULT '0',
49  `a_id47` text COLLATE utf8_unicode_ci NOT NULL,
50  `a_id48` text COLLATE utf8_unicode_ci NOT NULL,
51  `a_id49` text COLLATE utf8_unicode_ci NOT NULL,
52  `a_id50` text COLLATE utf8_unicode_ci NOT NULL,
53  `a_id51` text COLLATE utf8_unicode_ci NOT NULL,
54  `a_id52` text COLLATE utf8_unicode_ci NOT NULL,
55  `a_id53` text COLLATE utf8_unicode_ci NOT NULL,
56  `a_id54` text COLLATE utf8_unicode_ci NOT NULL,
57  `a_id55` text COLLATE utf8_unicode_ci NOT NULL,
58  `a_id56` text COLLATE utf8_unicode_ci NOT NULL,
59  `a_id57` text COLLATE utf8_unicode_ci NOT NULL,
60  `a_id58` text COLLATE utf8_unicode_ci NOT NULL,
61  `a_id59` text COLLATE utf8_unicode_ci NOT NULL,
62  `a_id60` text COLLATE utf8_unicode_ci NOT NULL,
63  `a_id61` text COLLATE utf8_unicode_ci NOT NULL,
64  `a_id62` text COLLATE utf8_unicode_ci NOT NULL,
65  `a_id63` text COLLATE utf8_unicode_ci NOT NULL,
66  `a_id64` text COLLATE utf8_unicode_ci NOT NULL,
67  `a_id65` text COLLATE utf8_unicode_ci NOT NULL,
68  `a_id66` text COLLATE utf8_unicode_ci NOT NULL,
69  `a_id67` text COLLATE utf8_unicode_ci NOT NULL,
70  `a_id68` text COLLATE utf8_unicode_ci NOT NULL,
71  `a_id69` text COLLATE utf8_unicode_ci NOT NULL,
72  `a_id70` text COLLATE utf8_unicode_ci NOT NULL,
73  `a_id71` text COLLATE utf8_unicode_ci NOT NULL,
74  `a_id72` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
75  `a_id73` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
76  `a_id74` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
77  `a_img_1` text COLLATE utf8_unicode_ci NOT NULL,
78  `a_img_2` text COLLATE utf8_unicode_ci NOT NULL,
79  `a_img_3` text COLLATE utf8_unicode_ci NOT NULL,
80  `a_img_4` text COLLATE utf8_unicode_ci NOT NULL,
81  `a_img_5` text COLLATE utf8_unicode_ci NOT NULL,
82  `a_img_6` text COLLATE utf8_unicode_ci NOT NULL,
83  `a_img_7` text COLLATE utf8_unicode_ci NOT NULL,
84  `a_img_8` text COLLATE utf8_unicode_ci NOT NULL,
85  `a_img_9` text COLLATE utf8_unicode_ci NOT NULL,
86  `a_img_10` text COLLATE utf8_unicode_ci NOT NULL,
87  `a_img_11` text COLLATE utf8_unicode_ci NOT NULL,
88  `a_img_12` text COLLATE utf8_unicode_ci NOT NULL,
89  `a_img_13` text COLLATE utf8_unicode_ci NOT NULL,
90  `a_img_14` text COLLATE utf8_unicode_ci NOT NULL,
91  `a_img_15` text COLLATE utf8_unicode_ci NOT NULL,
92  `a_img_16` text COLLATE utf8_unicode_ci NOT NULL,
93  `a_img_17` text COLLATE utf8_unicode_ci NOT NULL,
94  `a_img_18` text COLLATE utf8_unicode_ci NOT NULL,
95  `a_img_19` text COLLATE utf8_unicode_ci NOT NULL,
96  `a_img_20` text COLLATE utf8_unicode_ci NOT NULL,
97  `a_img_21` text COLLATE utf8_unicode_ci NOT NULL,
98  `a_img_22` text COLLATE utf8_unicode_ci NOT NULL,
99  `a_img_23` text COLLATE utf8_unicode_ci NOT NULL,
100  `a_img_24` text COLLATE utf8_unicode_ci NOT NULL,
101  `a_img_25` text COLLATE utf8_unicode_ci NOT NULL,
102  `a_img_26` text COLLATE utf8_unicode_ci NOT NULL,
103  `a_img_27` text COLLATE utf8_unicode_ci NOT NULL,
104  `a_img_28` text COLLATE utf8_unicode_ci NOT NULL,
105  `a_img_29` text COLLATE utf8_unicode_ci NOT NULL,
106  `a_img_30` text COLLATE utf8_unicode_ci NOT NULL,
107  `a_img_31` text COLLATE utf8_unicode_ci NOT NULL,
108  `a_img_32` text COLLATE utf8_unicode_ci NOT NULL,
109  `a_img_33` text COLLATE utf8_unicode_ci NOT NULL,
110  `a_img_34` text COLLATE utf8_unicode_ci NOT NULL,
111  `a_img_35` text COLLATE utf8_unicode_ci NOT NULL,
112  `a_img_36` text COLLATE utf8_unicode_ci NOT NULL,
113  `a_img_37` text COLLATE utf8_unicode_ci NOT NULL,
114  `a_img_38` text COLLATE utf8_unicode_ci NOT NULL,
115  `a_img_39` text COLLATE utf8_unicode_ci NOT NULL,
116  `a_img_40` text COLLATE utf8_unicode_ci NOT NULL,
117-- もっともっと続く 文字数制限で全部掲載できない
118  `a_id94` datetime NOT NULL,
119  `a_id95` datetime NOT NULL,
120  `a_id96` datetime NOT NULL,
121  `a_id97` tinyint(4) NOT NULL,
122  `a_id98` text COLLATE utf8_unicode_ci NOT NULL,
123  `a_id99` text COLLATE utf8_unicode_ci NOT NULL,
124  `a_id100` int(11) NOT NULL,
125  `a_id101` text COLLATE utf8_unicode_ci,
126  `a_id102` text COLLATE utf8_unicode_ci,
127  `a_extra_option_1` text COLLATE utf8_unicode_ci NOT NULL,
128  `a_extra_option_2` text COLLATE utf8_unicode_ci NOT NULL,
129  `a_extra_option_3` text COLLATE utf8_unicode_ci NOT NULL,
130  `a_extra_option_4` text COLLATE utf8_unicode_ci NOT NULL,
131  `a_extra_option_5` text COLLATE utf8_unicode_ci NOT NULL,
132  `a_id103` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
133  `a_id104` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
134  `a_id105` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
135  `a_id106` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
136  `a_check_flag` int(11) NOT NULL DEFAULT '0',
137  PRIMARY KEY (`a_bk_id`),
138  KEY `a_id3` (`a_id3`)
139) ENGINE=InnoDB AUTO_INCREMENT=1852156 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

miyahan

2017/07/02 06:54

ダンプしたファイルの CREATE TABLE 文を張り付けてくれますか？

thisprogrammer

2017/07/03 11:22

承知しました。擬似コードを作成後、再度返信いたします。

miyahan

2017/07/03 11:32

あ、あと `SHOW GLOBAL VARIABLES LIKE 'innodb_file_%';` の実行結果もお願いします

miyahan

2017/07/05 16:25

追記ありがとうございます。該当のテーブルのカラム数はいくつでしょうか？

thisprogrammer

2017/07/06 01:46

ご連絡ありがとうございます。276カラムとなりました。

行動規範の内容に同意します

回答4件

ベストアンサー

結論から申し上げると、おそらくテーブルが巨大すぎるのが原因だと思います。

InnoDB の "Row size too large" エラーをGoogle等で検索すると「**ROW_FORMAT = DYNAMIC or COMPRESSED **にして可変長データをオフページに格納させてBツリーノードを軽くする」といった対処方法がよく出てきたと思います。最初私もそれを睨んで何度も質問させていただきましたが、不適切な設定はされておらずきちんとDYNAMICモードで作成されるようになっていました。しかし今回のテーブルはそのテクニックすら効かないほど大きすぎました。

デフォルトで InnoDB は1レコードあたり8KB(8192Byte)以下である必要があります。DYNAMIC/COMPRESSED 方式においてTEXT等の可変長カラムは20Byteのポインタ情報のみを格納するので、計算上は400カラムほど作成できるはずなのですが、実際に試したところ197カラムまでしか作ることができませんでした。メカニズムは分からないのですが、1カラムあたり41Byteほど確保するようです。(40Byte以下のデータはインライン(ノード内)に記録されるので、それの予約？？)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.9.3 DYNAMIC および COMPRESSED 行フォーマット

新しいサーバーでのみエラーになったのは、MySQL 5.7 で InnoDB のエラーチェックが厳密になったためだと思います。(innodb_strict_mode が ON)

場当たり的な対処としては、

エラーを無視する: SET innodb_strict_mode=0; してからテーブルを作成する → 無理矢理警告を無視して作成するため、あとで大きなデータをINSERTしたときにエラーになったりデータが破損する可能性があります。
InnoDBのページサイズを大きくする: my.cnf に innodb_page_size=64K の設定を追加し、MySQLインスタンスを再作成する(テーブルやDBではなく、MySQLをまるごと最初から再セットアップ) → MySQL 5.6以下や MariaDB との互換性が無くなる

などが考えられます。

重々わかっているとは思いますが、そもそもこれだけカラムが多いのはデータベース設計が誤っていると考えられます。きちんと正規化し、取り回しのしやすいテーブルにすべきです。

投稿2017/07/06 04:23

編集2017/07/06 04:41

miyahan

総合スコア3095

thisprogrammer

2017/07/21 04:28

返信遅れまして失礼いたしました。お忙しい中、これだけ詳しい回答ありがとうございました。ベストアンサーを送らせてください。場当たり的な対処、本当に助かりました。

行動規範の内容に同意します

my.ini の innodb_log_file_size を変更すればいいとおもいます。
MySQL 5.6 Release Notes

投稿2017/06/23 06:09

退会済みユーザー

総合スコア0

thisprogrammer

2017/06/27 00:12

試しにinnodb_log_file_size=4Gとしてみましたが、どうにもエラーはなおらずじまいでした。せっかくの投稿でしたが、申し訳ございません。

miyahan

2017/07/06 04:23

全く関係ないですね

行動規範の内容に同意します

5.7にバージョンアップするとき、(innodb_strict_mode が ON)の設定の影響を受ける部分がないかのチェックが大変そう。でも、テストして、より正しいSQLにしておかないといけないですね。

投稿2017/07/06 05:44

ochiaishiro

総合スコア28

MySQLのドキュメントに以下のような記述がありますので、
ダンプされたテーブルのCREATE文を確認されては？

11.4.3 BLOB 型と TEXT 型
ほとんどの点で、BLOB カラムを、任意の長さに設定できる VARBINARY カラムと見なすことができます。同様に、TEXT カラムを VARCHAR カラムと見なすことができます。BLOB と TEXT は、次の点で VARBINARY と VARCHAR とは異なっています。
BLOB と TEXT カラムのインデックスには、インデックスプリフィクス長を指定する必要があります。CHAR と VARCHAR では、プリフィクス長はオプションです。セクション8.3.4「カラムインデックス」を参照してください。
BLOB および TEXT カラムに DEFAULT 値を含めることはできません。
＝＝＝ここまで

投稿2017/07/03 23:59

ochiaishiro

総合スコア28