<p>The statement tries to write to an auto-increment column while selecting from another table, making the binary log event unsafe for replication.</p>
<p>MySQL Error 1722: ER_BINLOG_UNSAFE_WRITE_AUTOINC_SELECT occurs when a statement writes to a table that has an AUTO_INCREMENT column while simultaneously selecting from another table. The non-deterministic row order makes the event unsafe for row-based replication. Rewrite the query to separate SELECT and INSERT, or use ORDER BY with INSERT … SELECT to guarantee deterministic ordering.</p>
Statements writing to a table with an auto-increment
Error 1722 is raised when a statement attempts to insert into or update a table that contains an AUTO_INCREMENT column while also selecting rows from another table in the same statement. The replication engine flags this pattern as unsafe because the order in which rows are read from the source table is not guaranteed, so the resulting AUTO_INCREMENT values could diverge on replicas.
The error appears only when binary logging is enabled and the server is in STATEMENT or MIXED mode. Row-based logging is deterministic, so the engine blocks unsafe statement-based events to keep replicas consistent.
If the statement were allowed, primary and replica servers might generate different AUTO_INCREMENT values, leading to data drift, foreign-key failures, or unique-key collisions. Fixing the statement before execution protects data integrity across the cluster.
Using INSERT … SELECT to copy data from one table into another table that has an AUTO_INCREMENT primary key.
Running UPDATE t1, t2 SET t1.id = NULL WHERE … when t1.id is AUTO_INCREMENT.
Employing CREATE TABLE t3 AS SELECT * FROM t2 and later altering t3 to add AUTO_INCREMENT, then running INSERT … SELECT INTO t3 again.
Replication mode set to STATEMENT or MIXED, so MySQL checks for unsafe constructs.
ER_BINLOG_UNSAFE_INSERT_SELECT - Insert from and into the same table with AUTO_INCREMENT.
ER_BINLOG_UNSAFE_AUTOINC_COLUMNS - Updating AUTO_INCREMENT columns non-deterministically.
ER_BINLOG_UNSAFE_STATEMENT - Generic warning for other unsafe constructs under statement logging.
ER_BINLOG_ROW_INJECTION_AND_STMT_ENGINE - Mixing row and statement formats in the same transaction.
Row-based logging is safe, but the server blocks the statement before deciding the format, so you still need a fix or a temporary SET SESSION binlog_format='ROW'.
ORDER BY works only when the ordering column is deterministic and unique, preventing ambiguous insert sequences.
Yes, but you lose replication and point-in-time recovery capabilities. Prefer rewriting the query.
Galaxy's AI copilot can refactor unsafe INSERT … SELECT patterns into two safe statements and surface warnings before you run the query.
.avif)
.avif){kind=logo}
