Help us understand the problem. What is going on with this article?

MySQLで外部キー付きテーブルをSET FOREIGN_KEY_CHECKS=0;でリストアしているのに外部キー制約違反が出るとき

More than 1 year has passed since last update.

タイトルが長くてすみません。
かなりレアケースな話のため、タイトルをピンポイントにしないと…ということで。

MySQL(InnoDB)で、mysqldumpでダンプしたデータに外部キー制約付きテーブルが含まれる場合、「SET FOREIGN_KEY_CHECKS=0;」をしてからリストアする必要があります。

例えば、MySQLクライアントを起動し、

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SOURCE 【ダンプファイル名】;

でリストアしたりしますが、このようにしたとしても、場合により外部キー制約違反(例:エラーコード1005とメッセージ中のエラー150の組み合わせ)が出ることがあります。

14.19.5 InnoDB のエラーコード(MySQL 5.6 リファレンスマニュアル)

「SET FOREIGN_KEY_CHECKS=0;」しているのになぜ?と思ってしまいますが、よく見ると、他にも「MySQL server has gone away」(または「MySQL サーバーが存在しなくなりました」)というエラーメッセージが表示されていることがあります。

原因

パケットが大きすぎるクエリがクライアントからサーバに投げられる
      ↓
・サーバがクライアントとの接続(セッション)を切断する
      ↓
・クライアントがサーバに再接続する
      ↓
「SET FOREIGN_KEY_CHECKS=1;」の状態に戻る
      ↓
・この状態で外部キー制約に関わるクエリが投げられ、外部キー制約違反が検出されると、エラーが出る

という流れでエラーが出ている可能性があります。
※すべてがこのケースにあてはまるわけではありません。あくまでも一例です。なお、ここでいう「パケット」は「TCPのパケット」のことではなくて「MySQLのパケット」ですので、TCPのパケット長は関係ありません。

MySQL サーバーが存在しなくなりました(MySQL 5.6 リファレンスマニュアル)

対処

「max_allowed_packet」を十分大きな値に設定してからリストアします。

ダンプをリストアする場合、アプリケーションで通常投げられるよりも長いクエリがサーバに投げられることがあります。
そのため、アプリケーションで投げられるクエリだけを想定して「max_allowed_packet」を設定した場合、ダンプからリストアする際のクエリが受け付けられない可能性があります。
※例えば、複数のTEXT/BLOBカラムを持つテーブルがあって、レコードをINSERT/UPDATEする際にそれらのカラムを同時にINSERT/UPDATEすることがない(必ず個別にINSERT/UPDATEする)場合に、それに合わせて max_allowed_packet を設定しているケース。
ただ、どちらかというと新しく立てたサーバに「max_allowed_packetを設定し忘れた」とか「0を1個入れ忘れた」というケースのほうが多そうですが。

「パケットが大きすぎます」というエラーメッセージが出てくれれば原因に気づきやすいのですが、先に示したリファレンスマニュアルに記されている通り、「MySQL サーバーが存在しなくなりました」(英語表記では「MySQL server has gone away」)というエラーメッセージが出て接続(セッション)が切れてしまうことがあるので非常にわかりづらいです。

さらに、外部キー制約付きテーブルをリストアしている場合は、「SET FOREIGN_KEY_CHECKS=0;」を付けているにもかかわらず外部キー制約違反のエラーが出るため、ますます原因に辿り着きにくくなります。
※「MySQL server has gone away」が先に出てくれればまだわかりやすいのですが、場合によっては外部キー制約違反のエラーが先に出てから「MySQL server has gone away」が出ることがあります。こうなると、「外部キー制約違反が原因でサーバへの接続が切れた」と誤解してしまいます。

まとめ(?)

「外部キー制約付きテーブルをリストアするときは SET FOREIGN_KEY_CHECKS=0; で」
と、
「MySQL server has gone away が出る原因の1つに max_allowed_packet が小さいことが挙げられる」
は、それぞれ良く知られたことですが、組み合わさってトラブルが発生すると(エラーの出方が若干トリッキーなこともあって)原因の特定に手間取る…という話でした。

なお、厳密にはMySQLの話ではないですが…。
Amazon(AWS)のAuroraの設定に関して、「初期設定のままで使うのが良い」と言われることがよくありますが、これは設定全般のことを指して言っているのではないので、注意が必要です(あくまでも性能を出すために必要なグローバル/スレッドバッファサイズなどの適正値の話)。
例えば、以下のような設定は、使う環境に合わせて設定する必要があります。

  • innodb_file_per_table
  • innodb_file_format
  • innodb_large_prefix

max_allowed_packet も同じです。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away