<p>The statement is unsafe for statement-based replication because a trigger or stored function inserts into an AUTO_INCREMENT column, so the values cannot be logged correctly.</p>
<p>MySQL Error 1671 ER_BINLOG_UNSAFE_AUTOINC_COLUMNS arises when a statement that fires a trigger or stored function also writes to an AUTO_INCREMENT column, making it unsafe for statement-based replication. Switch to row-based logging or rewrite the trigger to fix the issue.</p>
Statement is unsafe because it invokes a trigger or a
Error 1671 appears when MySQL’s binary log is in STATEMENT or MIXED mode and an SQL statement invokes a trigger or stored function that performs an INSERT into a table with an AUTO_INCREMENT column.
Because AUTO_INCREMENT values are generated per row on the primary but only once per SQL statement in the log, the replica may assign different values, breaking data consistency. MySQL marks the statement as unsafe and refuses to run it to protect replication integrity.
The main cause is a trigger or function that runs additional INSERT commands on tables that use AUTO_INCREMENT columns while binary logging is set to STATEMENT or MIXED.
Other contributing factors include BEFORE INSERT triggers that copy rows to audit tables, AFTER INSERT triggers that propagate data to child tables, and stored functions that build denormalized records with their own AUTO_INCREMENT keys.
Use row-based replication (binlog_format = ROW) so that each row image, not the SQL text, is logged. This guarantees identical AUTO_INCREMENT values on replicas.
If you must keep statement logging, remove the AUTO_INCREMENT from the target table or refactor the trigger to accept the generated id from the base insert instead of generating a new one.
Audit triggers writing to log tables: convert the id column in the audit table to BIGINT without AUTO_INCREMENT and populate it with the NEW.id from the parent record.
Fan-out triggers cloning data to shard tables: perform the secondary inserts in application code or use INSERT ... SELECT to log deterministic values.
Default to row-based replication in modern MySQL versions because it avoids most unsafe-statement alerts.
Keep business logic in the application layer where AUTO_INCREMENT ids are known, rather than in triggers that run implicitly during replication.
Error ER_BINLOG_UNSAFE_INSERT_SELECT occurs when INSERT ... SELECT reads and writes the same table under statement logging. Switching to row format solves both errors.
A BEFORE or AFTER trigger performs its own INSERT that generates a new id, making the statement nondeterministic.
The function contains INSERT INTO tbl VALUES() where tbl.id is AUTO_INCREMENT, leading to divergent id assignment on replicas.
In MIXED mode, MySQL cannot automatically fall back to row logging when triggers write to AUTO_INCREMENT columns, so it stops with error 1671.
INSERT ... SELECT reads and writes the same table with statement logging, causing nondeterministic results.
Catch-all error for operations that mix nondeterministic functions or system variables under statement-based replication.
Using SKIP LOCKED with statement logging can produce inconsistent row ordering on replicas.
No. Row format logs each inserted row’s complete image, so AUTO_INCREMENT values remain identical on replicas.
You may disable binary logging for a session, but doing so breaks replication if the server is a primary. Use only on standalone servers.
MIXED tries row logging when needed, but triggers that write AUTO_INCREMENT values still raise error 1671. Prefer full ROW format.
Galaxy’s AI copilot flags unsafe trigger patterns in real time and suggests ROW logging or trigger rewrites, preventing replication outages.
.avif)
.avif){kind=logo}
