技術 約2分で読めます

PHPでmysqldumpのSQLを流し込んだらFOREIGN_KEY_CHECKSが効かなかった話

PHP MySQL PDO マイグレーション トラブルシューティング

事故の概要

PHPで作ったWebインストーラーで、mysqldumpで出力したSQLファイルを流し込むマイグレーション処理を実装していた。

ある日、複数に分かれていたマイグレーションファイルを1つに統合したところ、突然エラーが発生するようになった。

SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'admin_users'

外部キー制約のエラー。テーブル作成順序の問題だ。

原因調査

mysqldumpで出力したSQLファイルの冒頭には、こういう行がある。

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

これはMySQLの「条件付きコメント」（Conditional Comments）という機能で、MySQLバージョン4.0.14以上なら実行される特殊なコメントだ。FOREIGN_KEY_CHECKS=0 を設定することで、テーブル作成順序を気にせずにCREATE TABLEを実行できるようになる。

当然これが効いていると思っていたが、同一セッションで確認してみると…

$pdo->exec("/*!40014 SET FOREIGN_KEY_CHECKS=0 */;");
$result = $pdo->query("SELECT @@FOREIGN_KEY_CHECKS")->fetchColumn();
echo $result; // 1 ← 効いてない！

1のまま。条件付きコメントが効いていなかった。

解決策

条件付きコメントに頼らず、明示的にSET文を実行するようにした。

public function runMigrations(PDO $pdo): array
{
    // mysqldumpの条件付きコメントに頼らず、明示的に設定
    $pdo->exec('SET FOREIGN_KEY_CHECKS=0');
    $pdo->exec('SET UNIQUE_CHECKS=0');

    // マイグレーション実行
    $sql = file_get_contents($migrationFile);
    $statements = $this->splitStatements($sql);

    foreach ($statements as $stmt) {
        $pdo->exec($stmt);
    }

    // 元に戻す
    $pdo->exec('SET FOREIGN_KEY_CHECKS=1');
    $pdo->exec('SET UNIQUE_CHECKS=1');

    return $results;
}

これで問題なく動くようになった。

追加検証

後日、Docker環境（PHP 8.0/8.2 + MySQL 8.0）で同じ条件を再現しようとしたところ、条件付きコメントは正常に動作した。

$pdo->exec("/*!40014 SET FOREIGN_KEY_CHECKS=0 */;");
$result = $pdo->query("SELECT @@FOREIGN_KEY_CHECKS")->fetchColumn();
echo $result; // 0 ← 効いてる！

事故が起きた環境はさくらインターネット（PHP 8.3.8）だったが、完全な原因特定には至らなかった。環境依存の何かがあるのかもしれない。

教訓

  1. mysqldumpのSQLをそのまま流し込むな - 条件付きコメントが効かない環境がある
  2. 明示的にSET FOREIGN_KEY_CHECKS=0を実行する - 確実に動く方法を選ぶ
  3. 「動くはず」ではなく「確実に動く」コードを書く - 特に本番環境では

mysqldumpの出力はMySQLコマンドラインツールで流し込むことを想定しているので、PHPのPDOで使う場合は注意が必要だ。

しかし下手に動いちゃうとマジでわからないな……。

参考