<p>MySQL raises error 1715 when an INSERT … SELECT … ON DUPLICATE KEY UPDATE statement is considered unsafe for the binary log because its outcome depends on an unpredictable row order.</p>
<p>MySQL Error 1715 (ER_BINLOG_UNSAFE_INSERT_SELECT_UPDATE) signals that INSERT … SELECT … ON DUPLICATE KEY UPDATE is non-deterministic for replication. Switch to row-based logging, add ORDER BY with LIMIT 1, or rewrite the logic to separate insert and update steps to resolve the problem.</p>
INSERT... SELECT... ON DUPLICATE KEY UPDATE is unsafe
MySQL error 1715 appears when you execute an INSERT … SELECT … ON DUPLICATE KEY UPDATE statement under statement-based or mixed binary logging. The server marks the query as unsafe because the order in which rows are read from the SELECT decides which existing row is updated, and that order may vary between primary and replica servers.
The error prevents the statement from being written to the binary log, protecting replicas from diverging. While the query may succeed locally, replication would break without this safeguard.
MySQL uses deterministic rules to ensure every statement produces identical results on all servers in a replication chain. If a query relies on non-guaranteed row order, it becomes non-deterministic. INSERT … SELECT … ON DUPLICATE KEY UPDATE is flagged because the first conflicting row encountered triggers the UPDATE clause, and that “first” row can change.
The risk is greatest when tables lack a unique ORDER BY, the SELECT spans multiple partitions, or the optimizer chooses different execution plans on different servers.
Convert to row-based replication by setting binlog_format=row; row events store the actual changes, eliminating order concerns. This is the simplest global fix if you control the server.
If you must stay on statement-based logging, rewrite the logic: first INSERT IGNORE the selected rows, then perform an UPDATE that matches the same key set. Each statement is deterministic on its own.
Bulk upsert from a staging table often triggers the error. Use INSERT INTO target SELECT * FROM staging for new rows, followed by UPDATE target JOIN staging ON key=key SET … for existing rows.
Self-referencing upserts also fail. Replace them with an UPDATE target SET col = (SELECT …) WHERE EXISTS(SELECT …) plus a separate INSERT for missing rows, or enable binlog_format=row for the session.
Prefer row-based replication for write-heavy systems using complex upserts. It avoids most unsafe-statement errors without query rewrites.
When you must upsert under statement-based logging, always supply a deterministic ORDER BY on the SELECT clause and ensure a LIMIT 1 in the UPDATE path so MySQL updates a predictable row.
Error 1592 (ER_BINLOG_UNSAFE_STATEMENT) flags other unsafe statements like LIMIT without ORDER BY. The fix pattern mirrors error 1715: enable row-based logging or rewrite queries.
Error 1665 (ER_BINLOG_UNSAFE_AUTOINC_SELECT) involves INSERT … SELECT with auto-increment columns. Again, switch to row-based format or separate INSERT and SELECT steps.
Without ORDER BY, MySQL may fetch rows in different orders on primary and replica, making the update target unpredictable.
Only statement events are affected. Row-based logging avoids the error entirely.
Statistics differences can change join order, shifting which row matches first on replicas.
Row retrieval order across partitions can vary, especially with parallel query execution.
Raised for statements that include non-deterministic constructs like LIMIT without ORDER BY under statement-based logging.
Indicates INSERT … SELECT involving an AUTO_INCREMENT column is unsafe. Row-based logging or query rewrite resolves it.
Occurs when a storage engine does not support logging. Switching engines or binlog format is required.
Yes, MySQL aborts the statement when binlog_format is STATEMENT or MIXED to prevent replication drift. Under ROW format, the query runs normally.
Error 1715 appears in MySQL 5.6 and later whenever statement-based logging encounters the unsafe pattern.
No. MySQL converts the warning to an error when log_bin = ON, so replication safety overrides application logic.
Galaxy’s AI copilot flags unsafe upserts as you type and suggests deterministic rewrites or session-level binlog_format changes, reducing replication issues before code reaches production.
.avif)
.avif){kind=logo}
