phalcon/cphalcon · error · MissingForeignKeyChecks

DATABASE PARAMETER 'FOREIGN_KEY_CHECKS' HAS TO BE 1

Error message

DATABASE PARAMETER 'FOREIGN_KEY_CHECKS' HAS TO BE 1

What it means

Thrown by Phalcon\Db\Adapter\Pdo\Mysql::addForeignKey(). Before emitting the ALTER TABLE ... ADD FOREIGN KEY statement, the adapter prepares and executes the dialect probe `SELECT @@foreign_key_checks` and requires the server to confirm FOREIGN_KEY_CHECKS is on (1). When that check does not succeed, the constraint is not added: MySQL accepts FK DDL while referential checking is suspended, which can silently produce an inconsistent schema. This is a connection/server state error, not a problem with the Reference definition itself.

Source

Thrown at phalcon/Db/Adapter/Pdo/Mysql.zep:68

     */
    protected type = "mysql";

    /**
     * Adds a foreign key to a table
     */
    public function addForeignKey(
        string tableName, 
        string schemaName, 
        <ReferenceInterface> reference
    ) -> bool {
        var foreignKeyCheck;

        let foreignKeyCheck = this->{"prepare"}(
            this->dialect->getForeignKeyChecks()
        );

        if unlikely !foreignKeyCheck->execute() {
            throw new MissingForeignKeyChecks();
        }

        return this->{"execute"}(
            this->dialect->addForeignKey(
                tableName,
                schemaName,
                reference
            )
        );
    }

    /**
     * Returns an array of Phalcon\Db\Column objects describing a table
     *
     * ```php
     * print_r(
     *     $connection->describeColumns("posts")
     * );

View on GitHub (pinned to b7419de9cd)

Solutions

1. Re-enable checks on the same connection, then retry: $connection->execute("SET FOREIGN_KEY_CHECKS = 1");
2. Audit migrations/seeders for SET FOREIGN_KEY_CHECKS=0 and wrap the disable/enable pair in try/finally so the flag is always restored.
3. Verify state manually: SELECT @@foreign_key_checks; must return 1. If 0, also check SELECT @@GLOBAL.foreign_key_checks and server init settings.
4. If the probe statement itself fails, check connection health and that the session can read system variables.

Example fix

// before
$connection->execute("SET FOREIGN_KEY_CHECKS = 0");
// ... truncate/drop tables ...
$connection->addForeignKey('orders', 'app', $reference); // throws MissingForeignKeyChecks

// after
$connection->execute("SET FOREIGN_KEY_CHECKS = 0");
try {
    // ... truncate/drop tables ...
} finally {
    $connection->execute("SET FOREIGN_KEY_CHECKS = 1");
}
$connection->addForeignKey('orders', 'app', $reference); // ok

// Restore session state before DDL
$ok = (int) $connection->query("SELECT @@foreign_key_checks")->fetchColumn();
if ($ok !== 1) {
    $connection->execute("SET FOREIGN_KEY_CHECKS = 1");
}
$connection->addForeignKey('orders', 'app', $reference);

use Phalcon\Db\Exceptions\MissingForeignKeyChecks;

try {
    $connection->addForeignKey('orders', 'app', $reference);
} catch (MissingForeignKeyChecks $e) {
    // Session had FK checks off - restore and retry once
    $connection->execute("SET FOREIGN_KEY_CHECKS = 1");
    $connection->addForeignKey('orders', 'app', $reference);
}

When it happens

Trigger: Calling $connection->addForeignKey($table, $schema, $reference) on a Pdo\Mysql connection in the same session after `SET FOREIGN_KEY_CHECKS = 0` was issued (typical in migration/seed scripts that disable checks to drop or truncate tables); a server started with foreign_key_checks disabled globally; or the prepared probe failing to execute because the connection dropped or lost privileges.

Common situations: Migration scripts that toggle FOREIGN_KEY_CHECKS=0 for table teardown and never restore it; fixture loaders / DB testers disabling checks between tests; long-lived pooled connections reused after a dump import that began with SET FOREIGN_KEY_CHECKS=0; MySQL configured with foreign-key-checks=0 in my.cnf.

Related errors

• The index 'columns' is required in the definition array
• The index 'sql' is required in the definition array
• Unrecognized MySQL data type at column {}
• The index 'columns' is required in the definition array
• The index 'sql' is required in the definition array

AI-assisted analysis of phalcon/cphalcon@b7419de9cd (2026-08-21). Data as JSON: /api/errors/ae4b6e9da94312c6. Report an issue: GitHub.