質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.78%

古いmysqldumpでダンプしたSQLファイルをMySQL5.7.18でimportしたい

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 6,450

score 27

わからない事、知りたいこと

古い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        

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

CREATE TABLE `bk_table` (
  `a_bk_id` int(11) NOT NULL AUTO_INCREMENT,
  `a_id` int(11) NOT NULL,
  `a_id2` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id3` int(11) NOT NULL,
  `a_id4` int(11) NOT NULL DEFAULT '1',
  `a_id5` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
  `a_id6` int(11) NOT NULL,
  `a_id7` tinyint(4) NOT NULL DEFAULT '1',
  `a_id8` tinyint(4) NOT NULL DEFAULT '0',
  `a_id9` tinyint(4) NOT NULL DEFAULT '0',
  `a_id10` int(11) NOT NULL DEFAULT '0',
  `a_id11` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id12` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id13` tinyint(4) NOT NULL DEFAULT '0',
  `a_id14` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id15` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
  `a_id16` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id17` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id18` int(11) NOT NULL DEFAULT '0',
  `a_id19` tinyint(4) NOT NULL,
  `a_id20` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id21` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `a_id22` int(11) NOT NULL DEFAULT '17',
  `a_id23` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id24` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id25` int(11) NOT NULL,
  `a_id26` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `a_id27` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `a_id28` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id29` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id30` int(11) NOT NULL DEFAULT '0',
  `a_id31` int(11) NOT NULL,
  `a_id32` int(11) NOT NULL,
  `a_id33` int(11) NOT NULL,
  `a_id34` datetime NOT NULL,
  `a_id35` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id36` int(11) NOT NULL,
  `a_id37` int(11) NOT NULL,
  `a_id38` tinyint(4) NOT NULL DEFAULT '1',
  `a_id39` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id40` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id41` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id42` int(11) NOT NULL DEFAULT '0',
  `a_id43` int(11) NOT NULL DEFAULT '0',
  `a_id44` int(11) NOT NULL DEFAULT '0',
  `a_id45` int(11) NOT NULL DEFAULT '0',
  `a_id46` int(11) NOT NULL DEFAULT '0',
  `a_id47` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id48` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id49` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id50` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id51` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id52` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id53` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id54` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id55` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id56` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id57` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id58` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id59` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id60` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id61` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id62` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id63` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id64` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id65` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id66` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id67` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id68` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id69` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id70` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id71` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id72` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `a_id73` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `a_id74` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `a_img_1` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_2` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_3` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_4` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_5` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_6` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_7` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_8` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_9` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_10` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_11` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_12` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_13` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_14` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_15` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_16` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_17` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_18` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_19` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_20` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_21` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_22` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_23` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_24` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_25` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_26` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_27` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_28` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_29` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_30` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_31` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_32` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_33` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_34` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_35` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_36` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_37` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_38` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_39` text COLLATE utf8_unicode_ci NOT NULL,
  `a_img_40` text COLLATE utf8_unicode_ci NOT NULL,
-- もっともっと続く 文字数制限で全部掲載できない
  `a_id94` datetime NOT NULL,
  `a_id95` datetime NOT NULL,
  `a_id96` datetime NOT NULL,
  `a_id97` tinyint(4) NOT NULL,
  `a_id98` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id99` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id100` int(11) NOT NULL,
  `a_id101` text COLLATE utf8_unicode_ci,
  `a_id102` text COLLATE utf8_unicode_ci,
  `a_extra_option_1` text COLLATE utf8_unicode_ci NOT NULL,
  `a_extra_option_2` text COLLATE utf8_unicode_ci NOT NULL,
  `a_extra_option_3` text COLLATE utf8_unicode_ci NOT NULL,
  `a_extra_option_4` text COLLATE utf8_unicode_ci NOT NULL,
  `a_extra_option_5` text COLLATE utf8_unicode_ci NOT NULL,
  `a_id103` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `a_id104` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `a_id105` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `a_id106` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `a_check_flag` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a_bk_id`),
  KEY `a_id3` (`a_id3`)
) ENGINE=InnoDB AUTO_INCREMENT=1852156 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • miyahan

    2017/07/03 20:32

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

    キャンセル

  • miyahan

    2017/07/06 01:25

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

    キャンセル

  • thisprogrammer

    2017/07/06 10:46

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

    キャンセル

回答 4

checkベストアンサー

+3

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

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 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/21 13:28

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

    キャンセル

0

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/06 13:24

    構文としては合っているので、本件とは関係ないですね

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

-1

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/06/27 09:12

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

    キャンセル

  • 2017/07/06 13:23

    全く関係ないですね

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.78%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る