<p>The statement INSERT IGNORE ... SELECT is marked unsafe for binary logging because row order is nondeterministic, risking data drift between master and replica.</p>
<p>MySQL Error 1714: ER_BINLOG_UNSAFE_INSERT_IGNORE_SELECT appears when INSERT IGNORE ... SELECT is logged in ROW or STATEMENT mode. MySQL blocks or warns because nondeterministic row order can desync replicas. Rewrite the query to deterministic ordering or switch to row-based replication to resolve the issue.</p>
INSERT IGNORE... SELECT is unsafe because the order in
Error 1714 fires when MySQL evaluates an INSERT IGNORE ... SELECT statement while binary logging is enabled. The server flags the statement as unsafe because the implicit row order of the SELECT determines which duplicate keys are ignored. Different execution paths on master and replica could choose different rows, breaking replication.
The message typically appears as a warning in MIXED mode or as a hard error in STATEMENT mode. In fully row-based logging, the statement is allowed but marked so that replicas apply row images deterministically.
The root cause is nondeterministic ordering in a SELECT without ORDER BY combined with INSERT IGNORE. When duplicate keys arise, the first encountered row wins. Without an explicit order, MySQL may scan tables differently on master and replica, yielding divergent results.
This situation only becomes critical when binary logging or replication is active. Local, non-logged servers will execute the statement without complaint.
Add a deterministic ORDER BY clause to the SELECT so both master and replica see rows in the same sequence. Alternatively, remove the IGNORE modifier and handle duplicates with ON DUPLICATE KEY UPDATE or a staging table.
If code changes are impossible, switch the server and replicas to ROW binlog_format, which records each inserted row explicitly, eliminating ambiguity.
Bulk backfill jobs often copy data from one table into another with INSERT IGNORE. Adding ORDER BY primary_key resolves the issue quickly.
ETL pipelines that deduplicate on insert can instead deduplicate beforehand or use INSERT ... ON DUPLICATE KEY UPDATE to maintain determinism.
Always include ORDER BY on SELECT parts of INSERT IGNORE in replicated environments. Adopt row-based logging for workloads that cannot guarantee deterministic statements.
Review application ORMs that generate INSERT IGNORE patterns and configure them to emit safe SQL when replication is enabled.
Errors 1592 and 1729 flag other unsafe constructs such as UPDATE ... LIMIT without ORDER BY. The fixes follow the same theme: make the statement deterministic or move to row-based logging.
Monitoring SHOW SLAVE STATUS regularly helps detect divergence early, even after unsafe statements slip through.
The SELECT clause scans source tables in an undefined order, letting storage engine heuristics decide row retrieval sequence.
INSERT IGNORE suppresses errors on key conflicts, relying on first-seen rows. Different orders choose different survivors.
Only these formats rely on deterministic statements. Row-based format records each row, avoiding the hazard.
UPDATE or DELETE statements that use LIMIT without ORDER BY create nondeterministic row choice similar to error 1714.
Creating a table from a nondeterministic SELECT is flagged for the same replication safety reasons.
Using nondeterministic functions inside logged statements can diverge replicas unless row-based logging is used.
No. The check only runs when binary logging is on. Stand-alone servers without binlog enabled will execute the statement normally.
Row format writes more data to the binlog, increasing I/O, but avoids many unsafe statement checks. Evaluate impact by benchmarking.
Ignoring the warning risks silent data drift. Always make the statement deterministic or move to row-based logging.
Galaxy's AI copilot detects INSERT IGNORE ... SELECT without ORDER BY and proposes safe rewrites before code reaches production.
.avif)
.avif){kind=logo}
