<p>MySQL throws ER_BINLOG_UNSAFE_UPDATE_IGNORE when UPDATE IGNORE is logged in STATEMENT mode, because non-deterministic row ordering can break replication consistency.</p>
<p>MySQL Error 1719: ER_BINLOG_UNSAFE_UPDATE_IGNORE appears when UPDATE IGNORE is written to the binary log in STATEMENT mode. Because the row update order is unpredictable, replicas may diverge. Switch to ROW binlog_format or remove the IGNORE modifier to resolve the issue.</p>
UPDATE IGNORE is unsafe because the order in which rows
Error 1719 (SQLSTATE HY000) signals ER_BINLOG_UNSAFE_UPDATE_IGNORE. MySQL halts execution because an UPDATE IGNORE statement is unsafe to record in statement-based binary logging. The server cannot guarantee that masters and replicas will apply the same rows in the same order, risking data drift.
The error emerges only when binlog_format=STATEMENT or MIXED is active. In those modes MySQL stores the exact SQL text in the binary log. If row ordering affects which rows are ignored, each replica could make different decisions, producing inconsistent results.
Ignoring the error blocks the write altogether, causing application failure. Bypassing it with log_slave_updates or super privileges forces execution, but that jeopardizes replication integrity. Resolving the root cause keeps production clusters consistent and avoids silent data corruption.
The primary cause is executing UPDATE IGNORE under statement-based logging. The IGNORE modifier tells MySQL to skip duplicate-key or constraint violations, but which row triggers a violation depends on update order. That order is nondeterministic without an ORDER BY clause.
The error can also arise in mixed logging when MySQL cannot automatically switch to row format, typically for tables using non-transactional storage engines like MyISAM.
The safest fix is to change the binary logging format to ROW. Row-based logging records the final images of each row, making the update order irrelevant.
If changing server-level settings is impossible, remove the IGNORE keyword and handle duplicate-key errors in application code, or rewrite the update as INSERT ... ON DUPLICATE KEY UPDATE, which is deemed safe.
Bulk maintenance jobs often run UPDATE IGNORE to patch data without aborting on the first error. Run such jobs on a single-instance maintenance replica configured with ROW logging, then replicate the sanitized data back.
Legacy applications using MyISAM trigger the error more frequently because the engine cannot leverage row-based fallback. Migrating to InnoDB or ROW logging eliminates the hazard.
Default new clusters to binlog_format=ROW. Combine that with primary keys on every table to keep row events compact.
When you must remain in STATEMENT mode, avoid UPDATE IGNORE entirely. Instead, select conflicting rows first or use INSERT ... ON DUPLICATE KEY UPDATE.
ER_BINLOG_UNSAFE_STATEMENT appears for other nondeterministic constructs like LIMIT without ORDER BY. The remedy is identical: switch to ROW logging or rewrite the query deterministically.
ER_BINLOG_UNSAFE_AUTOINC warns about INSERT ... SELECT on tables with AUTO_INCREMENT. Adding an ORDER BY primary_key or enabling row logging fixes the issue.
binlog_format=STATEMENT or MIXED prevents MySQL from logging row images, forcing safety checks.
The IGNORE clause lets MySQL skip errors, making the outcome order-dependent.
Without ORDER BY, MySQL chooses an execution plan that may vary across servers.
Tables using MyISAM or MEMORY block automatic ROW fallback in MIXED mode.
Raised for LIMIT without ORDER BY or nondeterministic functions in STATEMENT mode.
Triggered by INSERT ... SELECT on tables with AUTO_INCREMENT columns under statement logging.
Returned when INSERT IGNORE SELECT is unsafe due to similar ordering issues.
Avoid ignoring it. Forcing execution can cause master and replica data divergence.
ROW increases binlog size but often speeds up replica apply because fewer locks are needed.
MIXED tries to switch to ROW automatically but fails for certain engines and statements, so issues can still occur.
Galaxy highlights unsafe statements during query review and lets teams run them safely on maintenance replicas or rewrite them collaboratively.
.avif)
.avif){kind=logo}
