<p>CREATE TABLE … SELECT on a table containing an AUTO_INCREMENT column is unsafe for statement-based replication, so MySQL raises error 1723.</p>
<p>MySQL Error 1723 ER_BINLOG_UNSAFE_CREATE_SELECT_AUTOINC occurs when a CREATE TABLE … SELECT inserts into an AUTO_INCREMENT column while binlog_format is STATEMENT or MIXED. Switch to ROW binlog format or remove AUTO_INCREMENT to resolve the issue.</p>
CREATE TABLE... SELECT... on a table with an
MySQL raises error 1723 when it must log a CREATE TABLE … SELECT that inserts into an AUTO_INCREMENT column while binlog_format is STATEMENT or MIXED. Because the SELECT retrieval order is not deterministic, replicas can assign different AUTO_INCREMENT values, risking data drift, so the server blocks or flags the statement as unsafe.
Statement-based replication relies on executing identical SQL on source and replica. If row order differs, AUTO_INCREMENT sequences diverge, damaging primary key integrity, foreign keys and application logic. Preventing unsafe statements preserves consistency across the cluster.
Switch the session or global binlog_format to ROW, or rewrite the operation as INSERT … SELECT after creating the table separately. Alternatively remove the AUTO_INCREMENT attribute or add ORDER BY to produce deterministic ordering then enable --binlog_rows_query_log_events.
From MySQL 5.7 onward, the server may downgrade to row-based logging automatically in MIXED mode, emitting a warning instead of an error. On older versions or strict environments the statement fails entirely. Behavior also depends on sql_log_bin and slave_preserve_commit_order settings.
Galaxy's SQL editor highlights replication mode and warns in real time when you compose CREATE TABLE … SELECT statements with AUTO_INCREMENT columns. The AI copilot suggests row-based alternatives and can generate deterministic INSERT scripts, helping users avoid error 1723 before running the query on production.
Use ROW binlog format for nondeterministic statements, avoid AUTO_INCREMENT in SELECT-based table creation, and control row order explicitly to maintain replication safety.
The server or session is using binlog_format=STATEMENT or MIXED, making nondeterministic statements unsafe.
The destination table created by the statement defines an AUTO_INCREMENT primary key, so row order directly affects generated values.
The SELECT pulls rows without explicit ordering, letting storage engines return rows in varying sequences across servers.
Older releases cannot automatically switch to ROW logging and instead raise an error for the unsafe statement.
Raised when an INSERT … SELECT targets an AUTO_INCREMENT column under statement-based logging.
Occurs when an UPDATE modifies an AUTO_INCREMENT column nondeterministically.
A generic warning that the preceding statement is unsafe for statement-based replication.
If binlog_format is already ROW globally, the statement is safe and the warning can be ignored.
An ORDER BY on a unique or primary key column produces deterministic row order, making the statement safe for statement-based logging.
Yes, but the statement will not be replicated, which may leave replicas inconsistent. Use only for one-off maintenance on the primary.
Row-based logging produces larger binary logs but avoids many unsafe statement problems. The trade-off is typically acceptable for safety.
.avif)
.avif){kind=logo}
