Translated from the original Japanese article
Tech 3 min read

FOREIGN_KEY_CHECKS Didn't Work When Running mysqldump SQL via PHP PDO

PHP MySQL PDO Migration Troubleshooting Experiment

What Happened

I had implemented a migration process in a PHP-based web installer that piped SQL files generated by mysqldump directly into the database.

One day, after consolidating several separate migration files into one, errors started appearing out of nowhere.

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

A foreign key constraint error. A table creation ordering problem.

Root Cause Investigation

At the top of SQL files generated by mysqldump, you’ll find a line like this:

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

This is MySQL’s “Conditional Comments” feature — special comments that execute on MySQL version 4.0.14 and above. Setting FOREIGN_KEY_CHECKS=0 lets you run CREATE TABLE statements without worrying about the order of table creation.

Naturally I assumed this was taking effect, but checking within the same session revealed otherwise:

$pdo->exec("/*!40014 SET FOREIGN_KEY_CHECKS=0 */;");
$result = $pdo->query("SELECT @@FOREIGN_KEY_CHECKS")->fetchColumn();
echo $result; // 1 ← Not working!

Still 1. The conditional comment wasn’t being applied.

Fix

Instead of relying on the conditional comment, I started executing SET statements explicitly.

public function runMigrations(PDO $pdo): array
{
    // Don't rely on mysqldump's conditional comments — set explicitly
    $pdo->exec('SET FOREIGN_KEY_CHECKS=0');
    $pdo->exec('SET UNIQUE_CHECKS=0');

    // Run migration
    $sql = file_get_contents($migrationFile);
    $statements = $this->splitStatements($sql);

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

    // Restore
    $pdo->exec('SET FOREIGN_KEY_CHECKS=1');
    $pdo->exec('SET UNIQUE_CHECKS=1');

    return $results;
}

This resolved the issue.

Additional Testing

Later, when I tried to reproduce the same conditions in a Docker environment (PHP 8.0/8.2 + MySQL 8.0), the conditional comments worked fine.

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

The environment where the failure occurred was Sakura Internet (PHP 8.3.8), but I couldn’t pinpoint the exact cause. There may be some environment-specific factor at play.

Lessons Learned

  1. Don’t pipe mysqldump SQL directly — conditional comments may not work in all environments.
  2. Explicitly run SET FOREIGN_KEY_CHECKS=0 — choose the method that reliably works.
  3. Write code that definitely works, not code that “should” work — especially in production.

mysqldump output is designed to be piped through the MySQL command-line tool, so be careful when using it with PHP PDO.

Though if it happens to work silently, you’d never know what hit you…

Reference