📋

Description

This error occurs when an SQL statement invokes a trigger or a stored function that subsequently attempts to insert data into an AUTO_INCREMENT column. MySQL considers this operation "unsafe" for statement-based binary logging (SBL) because the generated AUTO_INCREMENT values cannot be reliably recorded for replication or point-in-time recovery.

💬

Error Message

Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.

🔍

Known Causes

3 known causes

⚠️

Trigger Inserts into AUTO_INCREMENT

A BEFORE/AFTER INSERT/UPDATE trigger on one table executes an INSERT statement into another table that contains an AUTO_INCREMENT primary or unique key.

⚠️

Stored Function Modifies AUTO_INCREMENT

An SQL statement calls a stored function which, as part of its execution, performs an INSERT operation into a table with an AUTO_INCREMENT column.

⚠️

Statement-Based Binary Logging (SBL)

The MySQL server is configured to use statement-based binary logging (BINLOG_FORMAT = STATEMENT), which struggles to deterministically log AUTO_INCREMENT values generated indirectly.

🛠️

Solutions

3 solutions available

1. Modify Trigger/Function to Avoid AUTO_INCREMENT Inserts medium

Adjust the trigger or stored function to not directly insert values into an AUTO_INCREMENT column.

1

Identify the trigger or stored function that is causing the error. This is usually the one that performs an `INSERT` statement targeting an `AUTO_INCREMENT` column.

2

Examine the `INSERT` statement within the trigger or function. If it explicitly specifies a value for the `AUTO_INCREMENT` column, remove that column and its value from the `INSERT` statement. Let MySQL handle the auto-generation.

Example of problematic `INSERT`:
sql
INSERT INTO your_table (auto_id, other_column) VALUES (123, 'some_value');


Example of corrected `INSERT` (if `auto_id` is the AUTO_INCREMENT column):
sql
INSERT INTO your_table (other_column) VALUES ('some_value');

3

If the trigger/function needs to *reference* the newly generated `AUTO_INCREMENT` ID, use `LAST_INSERT_ID()` *after* the initial insert that generated the ID. This is typically done in the calling statement or another trigger/function that executes *after* the primary insert.

Example within a stored procedure (after an initial insert):
sql
INSERT INTO your_table (other_column) VALUES ('some_value');
SET @new_id = LAST_INSERT_ID();
-- Now use @new_id in subsequent operations or inserts into other tables
INSERT INTO another_table (fk_id, data) VALUES (@new_id, 'related_data');

4

Recreate the modified trigger or stored function with the corrected `INSERT` statement.

Example of recreating a trigger:
sql
DELIMITER $$
CREATE TRIGGER your_trigger_name
AFTER INSERT ON some_other_table
FOR EACH ROW
BEGIN
    -- Corrected INSERT statement here
    INSERT INTO your_table (other_column) VALUES (NEW.some_value);
END$$
DELIMITER ;

2. Disable Binary Logging for the Statement easy

Temporarily disable binary logging for the specific statement that triggers the error.

1

Locate the SQL statement that is causing the error. This is the statement that *invokes* the trigger or function, not the trigger/function itself.

2

Prefix the problematic SQL statement with `SET sql_log_bin = 0;` and suffix it with `SET sql_log_bin = 1;` to temporarily disable binary logging for that specific execution.

sql
SET sql_log_bin = 0;
-- Your problematic SQL statement here, which calls the trigger/function
INSERT INTO some_table (column1) VALUES ('value');
SET sql_log_bin = 1;

3

Execute the modified statement. This will prevent the statement and its effects (including trigger actions) from being logged to the binary log, thus bypassing the error.

3. Review and Refactor Trigger Logic for Replication Compatibility advanced

Ensure triggers are written with replication in mind, avoiding direct AUTO_INCREMENT manipulation.

1

Understand your replication setup. If you are using statement-based replication, this error is more likely to occur because the replica might not be able to correctly re-execute the same trigger logic that generated the AUTO_INCREMENT value on the master.

2

Audit all triggers and stored functions that interact with `AUTO_INCREMENT` columns. The goal is to ensure that any `INSERT` into an `AUTO_INCREMENT` column is handled by MySQL's auto-generation mechanism, not by explicitly providing a value.

3

For triggers that need to insert related data into other tables using the newly generated `AUTO_INCREMENT` ID, use `LAST_INSERT_ID()` within the same session or transaction. This function reliably returns the last generated `AUTO_INCREMENT` value for the current connection.

Example in a stored procedure:
sql
-- First, insert into the table with AUTO_INCREMENT
INSERT INTO parent_table (data) VALUES ('parent data');

-- Get the generated ID
SET @parent_id = LAST_INSERT_ID();

-- Now use the ID to insert into a child table
INSERT INTO child_table (parent_id, child_data) VALUES (@parent_id, 'child data');

4

Consider using row-based replication if feasible, as it logs the actual row changes rather than the SQL statements, which can sometimes mitigate issues with triggers and `AUTO_INCREMENT` values. However, this is a significant architectural change and may not be a quick fix.

5

Test thoroughly after making any changes to triggers or replication settings in a staging environment before applying them to production.

🔗

Related Errors

5 related errors

MySQL Error 1671: Unsafe AUTO_INCREMENT Trigger/Function