1722
MySQLWARNINGNotableReplicationHIGH confidence
Unsafe statement: INSERT ... SELECT on a table with AUTO_INCREMENT
Production Risk
Medium — replicas may have different AUTO_INCREMENT values, causing data divergence.
What this means
An INSERT ... SELECT on a table with an AUTO_INCREMENT column is unsafe for statement-based replication because AUTO_INCREMENT values may differ between source and replica.
Why it happens
- 1Using INSERT INTO t1 SELECT ... FROM t2 where t1 has an AUTO_INCREMENT column in statement-based replication.
How to reproduce
trigger — this will error
trigger — this will error
INSERT INTO orders SELECT NULL, product_id, qty FROM cart;
expected output
Warning (Code 1722): Unsafe statement written to binary log using statement format. Statement is unsafe because it accesses an AUTO_INCREMENT column in an INSERT...SELECT.
Fix
Switch to ROW-based replication
Switch to ROW-based replication
SET GLOBAL binlog_format = 'ROW';
Why this works
ROW format records actual row values including the assigned AUTO_INCREMENT, ensuring replica consistency.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 1722 ER_BINLOG_UNSAFE_WRITE_AUTOINC_SELECT
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev