{"record":{"id":"03e2efb18efe6d5e","repo":"phalcon/cphalcon","slug":"on-conflict-upserts-are-not-supported-by-mysql-us","errorCode":null,"errorMessage":"ON CONFLICT upserts are not supported by MySQL; use INSERT ... ON DUPLICATE KEY UPDATE via raw SQL instead","messagePattern":"ON CONFLICT upserts are not supported by MySQL; use INSERT \\.\\.\\. ON DUPLICATE KEY UPDATE via raw SQL instead","errorType":"exception","errorClass":"MysqlOnConflictNotSupported","httpStatus":null,"severity":"error","filePath":"phalcon/Db/Dialect/Mysql.zep","lineNumber":936,"sourceCode":"\n            if afterPosition {\n                let sql .=  \" AFTER `\" . afterPosition . \"`\";\n            }\n        }\n\n        return sql;\n    }\n\n    /**\n     * MySQL does not support the SQL-standard `ON CONFLICT DO UPDATE`\n     * upsert syntax - it has its own `INSERT ... ON DUPLICATE KEY UPDATE`\n     * which requires PHQL grammar work (deferred). The base helper is\n     * overridden here to throw, preventing accidental emission of invalid\n     * SQL on MySQL connections.\n     */\n    public function onConflictUpdate( string sqlQuery,  array conflictColumns,  array updateColumns) -> string\n    {\n        throw new MysqlOnConflictNotSupported();\n    }\n\n    /**\n     * MySQL does not support the SQL-standard `ON CONFLICT (...) DO UPDATE`\n     * upsert clause; `onConflictUpdate()` throws.\n     */\n    public function supportsOnConflictUpdate() -> bool\n    {\n        return false;\n    }\n\n    /**\n     * Returns a SQL modified with a LOCK IN SHARE MODE clause. The `modifier`\n     * argument is accepted for signature parity with the contract but is\n     * silently ignored on MySQL - its legacy `LOCK IN SHARE MODE` syntax has\n     * no `NOWAIT` / `SKIP LOCKED` variant. Callers needing those modifiers\n     * should target PostgreSQL or stay on `forUpdate()`.\n     *","sourceCodeStart":918,"sourceCodeEnd":954,"githubUrl":"https://github.com/phalcon/cphalcon/blob/b7419de9cd0a8a3f48441ead84c9f8415d463e25/phalcon/Db/Dialect/Mysql.zep#L918-L954","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Feature-check first: if ($adapter->getDialect()->supportsOnConflictUpdate()) { ... } else { use the MySQL path }","On MySQL use INSERT ... ON DUPLICATE KEY UPDATE via raw SQL or the query builder's insert/update logic","Ensure the table has a PRIMARY KEY or UNIQUE key so ON DUPLICATE KEY UPDATE fires as intended"],"exampleFix":"// before\n$sql = $mysqlDialect->onConflictUpdate($insertSql, ['id'], ['counter']);\n\n// after\n$sql = 'INSERT INTO stats (id, counter) VALUES (?, ?) '\n     . 'ON DUPLICATE KEY UPDATE counter = counter + 1';","handlingStrategy":"type-guard","validationCode":"if (!$adapter->getDialect()->supportsOnConflictUpdate()) {\n    // MySQL path: INSERT ... ON DUPLICATE KEY UPDATE\n    $sql = 'INSERT INTO t (id, c) VALUES (:id, :c) ON DUPLICATE KEY UPDATE c = VALUES(c)';\n} else {\n    $sql = $dialect->onConflictUpdate($insertSql, $conflictColumns, $updateColumns);\n}","typeGuard":"function canUpsertWithOnConflict(\\Phalcon\\Db\\Adapter\\AdapterInterface $adapter): bool\n{\n    return $adapter->getDialect()->supportsOnConflictUpdate();\n}","tryCatchPattern":"try {\n    $sql = $dialect->onConflictUpdate($sql, ['id'], ['counter']);\n} catch (\\Phalcon\\Db\\Exceptions\\MysqlOnConflictNotSupported $e) {\n    $sql = 'INSERT INTO t (id, counter) VALUES (?, ?) ON DUPLICATE KEY UPDATE counter = counter + 1';\n}","preventionTips":["Always feature-check supportsOnConflictUpdate() before building upsert SQL","Maintain an explicit per-adapter upsert strategy in multi-backend apps","Ensure a PRIMARY KEY/UNIQUE index exists for MySQL upsert semantics"],"tags":["phalcon","mysql","upsert","on-conflict","sql-dialect"],"backgroundTag":"upsert-unsupported-dialect","analyzedSha":"b7419de9cd0a8a3f48441ead84c9f8415d463e25","analyzedAt":"2026-08-21T06:21:18.811Z","schemaVersion":2},"datasetVersion":"2026-08-21T11:28:35.574Z"}