phalcon/cphalcon · error · MysqlOnConflictNotSupported

ON CONFLICT upserts are not supported by MySQL; use INSERT .

Error message

ON CONFLICT upserts are not supported by MySQL; use INSERT ... ON DUPLICATE KEY UPDATE via raw SQL instead

What it means

The MySQL dialect deliberately overrides onConflictUpdate() to throw MysqlOnConflictNotSupported. MySQL does not implement the SQL-standard ON CONFLICT DO UPDATE upsert clause (it uses INSERT ... ON DUPLICATE KEY UPDATE), and teaching PHQL to emit the MySQL form is deferred, so the dialect throws rather than silently generating invalid SQL. supportsOnConflictUpdate() returns false on MySQL so callers can feature-check before attempting the upsert.

Source

Thrown at phalcon/Db/Dialect/Mysql.zep:936

            if afterPosition {
                let sql .=  " AFTER `" . afterPosition . "`";
            }
        }

        return sql;
    }

    /**
     * MySQL does not support the SQL-standard `ON CONFLICT DO UPDATE`
     * upsert syntax - it has its own `INSERT ... ON DUPLICATE KEY UPDATE`
     * which requires PHQL grammar work (deferred). The base helper is
     * overridden here to throw, preventing accidental emission of invalid
     * SQL on MySQL connections.
     */
    public function onConflictUpdate( string sqlQuery,  array conflictColumns,  array updateColumns) -> string
    {
        throw new MysqlOnConflictNotSupported();
    }

    /**
     * MySQL does not support the SQL-standard `ON CONFLICT (...) DO UPDATE`
     * upsert clause; `onConflictUpdate()` throws.
     */
    public function supportsOnConflictUpdate() -> bool
    {
        return false;
    }

    /**
     * Returns a SQL modified with a LOCK IN SHARE MODE clause. The `modifier`
     * argument is accepted for signature parity with the contract but is
     * silently ignored on MySQL - its legacy `LOCK IN SHARE MODE` syntax has
     * no `NOWAIT` / `SKIP LOCKED` variant. Callers needing those modifiers
     * should target PostgreSQL or stay on `forUpdate()`.
     *

View on GitHub (pinned to b7419de9cd)

Solutions

1. Feature-check first: if ($adapter->getDialect()->supportsOnConflictUpdate()) { ... } else { use the MySQL path }
2. On MySQL use INSERT ... ON DUPLICATE KEY UPDATE via raw SQL or the query builder's insert/update logic
3. Ensure the table has a PRIMARY KEY or UNIQUE key so ON DUPLICATE KEY UPDATE fires as intended

Example fix

// before
$sql = $mysqlDialect->onConflictUpdate($insertSql, ['id'], ['counter']);

// after
$sql = 'INSERT INTO stats (id, counter) VALUES (?, ?) '
     . 'ON DUPLICATE KEY UPDATE counter = counter + 1';

if (!$adapter->getDialect()->supportsOnConflictUpdate()) {
    // MySQL path: INSERT ... ON DUPLICATE KEY UPDATE
    $sql = 'INSERT INTO t (id, c) VALUES (:id, :c) ON DUPLICATE KEY UPDATE c = VALUES(c)';
} else {
    $sql = $dialect->onConflictUpdate($insertSql, $conflictColumns, $updateColumns);
}

function canUpsertWithOnConflict(\Phalcon\Db\Adapter\AdapterInterface $adapter): bool
{
    return $adapter->getDialect()->supportsOnConflictUpdate();
}

try {
    $sql = $dialect->onConflictUpdate($sql, ['id'], ['counter']);
} catch (\Phalcon\Db\Exceptions\MysqlOnConflictNotSupported $e) {
    $sql = 'INSERT INTO t (id, counter) VALUES (?, ?) ON DUPLICATE KEY UPDATE counter = counter + 1';
}

When it happens

Trigger: Calling $mysqlAdapter->getDialect()->onConflictUpdate($sql, $conflictCols, $updateCols) or upsert/query code paths that invoke it on a MySQL connection; running the same upsert code that works on PostgreSQL against MySQL.

Common situations: Multi-backend applications sharing one upsert helper; upgrading to a Phalcon version where the base dialect gained ON CONFLICT support but the MySQL override intentionally still throws; assuming dialect parity between Postgres and MySQL features.

Related errors

• ON CONFLICT requires at least one conflict-target column
• ON CONFLICT DO UPDATE requires at least one update column
• Operator '{}' is not supported by this SQL dialect
• 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/03e2efb18efe6d5e. Report an issue: GitHub.