<p>MySQL flags INSERT ... ON DUPLICATE KEY UPDATE as unsafe for statement-based binary logging when the target table has more than one UNIQUE key.</p>
<p>MySQL Error 1724 ER_BINLOG_UNSAFE_INSERT_TWO_KEYS appears when you use INSERT ... ON DUPLICATE KEY UPDATE on a table with multiple UNIQUE keys while running statement based logging. Switch to row based logging or redesign the table to clear the error.</p>
INSERT... ON DUPLICATE KEY UPDATE on a table with more
MySQL raises Error 1724 with SQLSTATE HY000 when a statement uses INSERT ... ON DUPLICATE KEY UPDATE on a table that contains more than one UNIQUE key and the server is writing the statement to the binary log in STATEMENT format.
The server labels the statement unsafe because the outcome can differ on replicas if the duplicate check chooses a different key, breaking deterministic replication. MySQL therefore blocks the write to protect data consistency.
The error appears during execution, not at parse time. It affects masters or primaries that have binlog_format set to STATEMENT or MIXED and at least one replica reading the binary log. Developers typically see it after enabling replication or upgrading versions that tighten safety checks.
If binlog_format is already ROW, the statement logs safely and the error never surfaces. The issue is purely about statement determinism in multi key scenarios.
Using INSERT ... ON DUPLICATE KEY UPDATE on a table with two or more UNIQUE indexes triggers nondeterministic duplicate detection. MySQL cannot guarantee which key fires first, so STATEMENT logging becomes unsafe.
Another trigger is a target table that has both PRIMARY KEY and additional UNIQUE constraints. Even if no duplicate is expected, MySQL still performs the safety check before logging.
The fastest fix is changing binlog_format to ROW, which records row images instead of SQL text, eliminating nondeterminism. This is safe in production and recommended by MySQL engineers.
Alternatively, simplify the schema by keeping only one UNIQUE key involved in the duplicate check, or rewrite the operation into separate INSERT and UPDATE statements guarded by unique lookups.
High-throughput ingestion tables often have both an auto-increment primary key and a business-key UNIQUE index. Switching to row-based logging resolves the problem without changing the schema.
ETL jobs that upsert into dimension tables can instead use INSERT IGNORE followed by UPDATE when rows are missing, making each step deterministic and safe for statement logging.
Always use ROW binlog format in environments with complex upserts. This is the default in many managed MySQL services because it prevents an entire class of unsafe-statement errors.
If you must stay on STATEMENT logging for size reasons, keep only one UNIQUE constraint on tables that rely on INSERT ... ON DUPLICATE KEY UPDATE, or switch to REPLACE INTO.
Error 1592 (ER_BINLOG_UNSAFE_STATEMENT) flags other unsafe constructs such as LIMIT without ORDER BY under statement logging. The same switch to ROW format resolves it.
Error 1665 (ER_BINLOG_UNSAFE_AUTOINC_COLUMNS) appears when an UPDATE touches an auto-increment column. Again, ROW format or rewriting queries eliminates the risk.
The table has both a primary key and one or more secondary UNIQUE keys, creating ambiguity in duplicate detection.
The server or session is set to binlog_format=STATEMENT or MIXED, causing MySQL to evaluate statement safety rules.
This specific SQL syntax is flagged because it depends on which key triggers the duplicate, making results nondeterministic.
Occurs when a statement with nondeterministic behavior is logged in STATEMENT mode.
Raised when an UPDATE touches an auto increment column under STATEMENT logging.
Flags unsafe statements when binlog_format is MIXED and switches could be ambiguous.
Yes. The check is only to protect replication. If binary logging is disabled or the server is standalone, the statement executes normally.
Row images increase binlog size but usually improve replica apply speed because they skip duplicate checks. Disk and network overhead is the main tradeoff.
Yes. REPLACE INTO first deletes matching rows then inserts the new row, making the action deterministic even with multiple UNIQUE keys.
Galaxy warns editors when they run INSERT ... ON DUPLICATE KEY UPDATE on a multi key table and suggests enabling ROW logging, reducing production surprises.
.avif)
.avif){kind=logo}
