EC
1723
MySQLWARNINGNotableReplicationHIGH confidence

Unsafe statement: CREATE TABLE ... SELECT on a table with AUTO_INCREMENT

Production Risk

Medium — AUTO_INCREMENT values may diverge between source and replica.

What this means

CREATE TABLE ... SELECT where the created table has an AUTO_INCREMENT column is unsafe for statement-based replication.

Why it happens
  1. 1Using CREATE TABLE ... SELECT when the new table implicitly or explicitly gains an AUTO_INCREMENT column in STATEMENT mode.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY) SELECT name FROM t1;

expected output

Warning (Code 1723): Unsafe statement written to binary log using statement format.

Fix

Separate CREATE TABLE from INSERT ... SELECT

Separate CREATE TABLE from INSERT ... SELECT
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
INSERT INTO t2 (name) SELECT name FROM t1;

Why this works

Separating DDL and DML ensures each is logged as a distinct, deterministic event.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1723 ER_BINLOG_UNSAFE_CREATE_SELECT_AUTOINC

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors