<p>MySQL flags REPLACE ... SELECT as unsafe under STATEMENT or MIXED binary logging because row order is nondeterministic and may break replication.</p>
<p>MySQL Error 1716 ER_BINLOG_UNSAFE_REPLACE_SELECT happens when you run a REPLACE ... SELECT while binlog_format is STATEMENT or MIXED, making the statement unsafe for replication. Change binlog_format to ROW or rewrite the query to INSERT ... ON DUPLICATE KEY UPDATE to resolve the issue.</p>
REPLACE... SELECT is unsafe because the order in which
MySQL raises error 1716 when you issue a REPLACE ... SELECT while the server binary log format is STATEMENT or MIXED and the statement is flagged unsafe for replication. The nondeterministic order in which the SELECT retrieves rows means the REPLACE may overwrite different rows on replicas.
Because statement based replication replays SQL text rather than row images, the replication outcome must be deterministic. When MySQL cannot guarantee identical results on master and slave, it stops the statement to protect data consistency and shows ER_BINLOG_UNSAFE_REPLACE_SELECT.
The primary trigger is the combination of STATEMENT or MIXED binlog_format with a REPLACE ... SELECT that lacks an ORDER BY clause and relies on the natural row order. The server cannot predict which rows will be replaced because the SELECT may return rows in a different order on replica nodes.
The error can also occur when the target table and source table are the same or share indexes that change during execution, causing further nondeterminism.
The fastest fix is to change binary logging to ROW, which logs the actual row images and removes order dependence. Alternatively rewrite the statement to INSERT ... ON DUPLICATE KEY UPDATE or insert into a temporary table before replacing.
If you must retain STATEMENT logging, add an ORDER BY clause with a deterministic primary key to the SELECT so every server processes rows identically.
During bulk upserts from a staging table engineers often use REPLACE ... SELECT for convenience and hit this error on production replicas. Switching to ROW format or using INSERT ... ON DUPLICATE KEY UPDATE resolves the pipeline without impacting replication safety.
Automated schema migration tools may default to STATEMENT format. Set binlog_format=ROW at session level inside migration scripts to avoid interruptions.
Use ROW binary logging in modern MySQL clusters to eliminate most unsafe statement errors. When designing ETL queries avoid relying on implicit row order and always include explicit ORDER BY keys.
For idempotent upsert logic prefer INSERT ... ON DUPLICATE KEY UPDATE which is row deterministic and widely supported.
Other unsafe statement errors such as ER_BINLOG_UNSAFE_INSERT_SELECT, ER_BINLOG_UNSAFE_UPDATE_IGNORE and ER_BINLOG_UNSAFE_AUTOINC_COLUMNS appear under similar conditions. The corrective actions are identical: use ROW logging or rewrite the SQL to deterministic forms.
binlog_format is set to STATEMENT or MIXED so MySQL relies on deterministic SQL text replication.
The SELECT part of the statement lacks ORDER BY therefore row replacement order is unpredictable.
The REPLACE reads from and writes to the same table or overlapping indexes causing changing datasets mid query.
Different engines may return rows in different orders, increasing nondeterminism between master and slave.
Occurs when INSERT ... SELECT is unsafe under STATEMENT logging.
Raised for UPDATE with IGNORE clause that may skip rows differently on replicas.
Triggered when ORDER BY is absent in INSERT involving auto increment columns while using statement logging.
No. ROW logging records actual changes so MySQL does not need to evaluate statement safety and the error disappears.
In recent versions the server throws an error not warning. You should not bypass it because replicas might diverge.
Performance is similar and often better because REPLACE deletes then inserts while INSERT updates in place.
Galaxy's SQL editor highlights replication unsafe patterns and its AI copilot suggests deterministic rewrites, reducing the chance of deploying unsafe REPLACE ... SELECT statements.
.avif)
.avif){kind=logo}
