INSERT into auto-increment column with SELECT is unsafe for statement-based replication
Production Risk
Medium — replication may produce inconsistent auto-increment values on replicas.
An INSERT ... SELECT statement that inserts into an auto-increment column is unsafe for statement-based replication because the generated values may differ between master and replica.
- 1INSERT ... SELECT into a table with an AUTO_INCREMENT column in STATEMENT binlog mode.
- 2The order in which rows are inserted may differ on the replica, resulting in different auto-increment values.
INSERT INTO dest_table (name) SELECT name FROM src_table;
expected output
Warning 1620: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column.
Fix 1
Switch to ROW or MIXED binlog format
SET GLOBAL binlog_format = 'ROW';
Why this works
ROW format records the exact row values (including generated auto-increment IDs) written on the master.
Fix 2
Add ORDER BY to the SELECT to enforce deterministic ordering
INSERT INTO dest_table (name) SELECT name FROM src_table ORDER BY id;
Why this works
A deterministic ORDER BY ensures rows are inserted in the same order on both master and replica.
✕
MySQL 8.0 — 1620 ER_BINLOG_UNSAFE_AUTOINC_COLUMNS
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev