<p>The statement-based binary log rejects CREATE ... REPLACE SELECT because row order is nondeterministic and can break replication consistency.</p>
<p>MySQL Error 1718: ER_BINLOG_UNSAFE_CREATE_REPLACE_SELECT occurs when CREATE ... REPLACE SELECT is logged in STATEMENT mode. The nondeterministic row order can cause master and replica divergence. Switch to ROW binlog_format or rewrite the statement to remove REPLACE to resolve the issue.</p>
CREATE... REPLACE SELECT is unsafe because the order in
Error 1718 fires when MySQL tries to write a CREATE ... REPLACE SELECT statement to the binary log while binlog_format is STATEMENT or MIXED. The server marks the statement unsafe because REPLACE depends on row order, which may differ on replicas.
The error stops the statement to protect replication integrity. If the binary log is disabled or set to ROW, the same query runs without issue.
Statement-based replication requires deterministic results. With CREATE ... REPLACE SELECT, the first matching row encountered is replaced, so varying SELECT order yields different outcomes. MySQL cannot guarantee identical order across nodes, so it blocks the operation.
The problem surfaces most often on production clusters that still use STATEMENT logging for performance or backward compatibility.
The fastest remedy is to set binlog_format to ROW, either session-wide for the current connection or globally for the server. ROW logging records individual row changes and removes ordering ambiguity.
If changing the binary log format is impossible, rewrite the query: insert into a temporary table, index it, then apply REPLACE or INSERT ... ON DUPLICATE KEY UPDATE from that deterministic source.
During zero-downtime migrations, teams often clone tables with CREATE OR REPLACE SELECT in STATEMENT mode and hit Error 1718. Switching to ROW for the migration window eliminates the block.
Scheduled ETL jobs running on replicas may break because master accepted the query in ROW but the job forces STATEMENT. Align formats across environments to prevent mismatches.
Use ROW binlog_format as a default in modern clusters. Where performance is critical, MIXED mode with key-based ORDER BY clauses can be acceptable, but test thoroughly.
Build idempotent loads with INSERT ... ON DUPLICATE KEY UPDATE instead of REPLACE to reduce nondeterminism. Always add explicit ORDER BY on primary keys when bulk loading.
Errors 1592 (ER_BINLOG_UNSAFE_STATEMENT) and 1593 (ER_BINLOG_UNSAFE_AUTOINC_SELECT) also surface when nondeterministic statements are logged in STATEMENT mode. The fixes-switch to ROW or rewrite statements-mirror the solution for Error 1718.
binlog_format=STATEMENT records SQL text, so MySQL rejects nondeterministic queries.
Row replacement depends on retrieval order, which varies between master and replica.
In MIXED mode, MySQL falls back to STATEMENT when it believes a query is safe. Detection failure triggers Error 1718.
General flag for unsafe statements in STATEMENT binlog mode.
Triggered when an INSERT SELECT with auto-increment columns is nondeterministic.
Raised for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE when order is unpredictable.
Disabling the binary log avoids the error but breaks replication and point-in-time recovery. Use only on standalone hosts.
MIXED mode works if MySQL switches to ROW for unsafe queries. Error 1718 proves the statement stayed in STATEMENT, so it is still unsafe.
ORDER BY can help but does not guarantee identical execution plans across nodes. The safest fix remains ROW logging.
Galaxy surfaces replication-mode warnings in real time and suggests switching the session to ROW before execution, preventing runtime failures.
.avif)
.avif){kind=logo}
