📋

Description

This error indicates that a SQL statement intended for replication (binlog) involves a trigger or stored function that attempts to insert values into an AUTO_INCREMENT column. MariaDB flags this as unsafe because the generated AUTO_INCREMENT values cannot be reliably logged for statement-based replication, potentially leading to data inconsistencies across replicas.

💬

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

⚠️

Statement-Based Replication (SBR)

The database is configured for statement-based replication, which struggles to correctly log non-deterministic AUTO_INCREMENT assignments made within triggers or functions.

⚠️

Trigger/Function AUTO_INCREMENT Usage

A database trigger or stored function executes logic that implicitly or explicitly inserts new rows into a table's AUTO_INCREMENT column during the execution of a statement.

⚠️

Non-Deterministic Value Generation

The exact AUTO_INCREMENT value assigned by the database can be non-deterministic (e.g., influenced by `LAST_INSERT_ID()`), making it difficult to guarantee consistent replication using SBR.

🛠️

Solutions

3 solutions available

1. Temporarily Disable Binary Logging for the Session easy

Disable binlog for the current session to allow unsafe inserts.

1

Before executing the statement that causes the error, set the `log_bin` variable to `OFF` for the current session.

SET SESSION log_bin = 0;

2

Execute your original SQL statement that was causing the error.

-- Your original SQL statement here that inserts into an AUTO_INCREMENT column via trigger/function

3

After the statement is executed, you can optionally re-enable binary logging for the session if needed, though it will typically reset upon session termination.

SET SESSION log_bin = ON;

2. Modify Trigger/Stored Function Logic medium

Adjust the trigger or stored function to avoid direct AUTO_INCREMENT inserts.

1

Identify the trigger or stored function that is performing the unsafe insert.

SHOW TRIGGERS LIKE 'your_table_name%';
SHOW FUNCTION STATUS WHERE Db = 'your_database_name' AND Name = 'your_function_name';

2

Review the logic within the trigger or function. The goal is to remove or redesign the part that explicitly inserts a value into an `AUTO_INCREMENT` column.

-- Example: If your trigger does something like:
-- INSERT INTO another_table (id, data) VALUES (NEW.id, 'some_data');
-- You might need to change it to rely on the AUTO_INCREMENT generating the ID.

3

If the trigger/function needs to generate a unique identifier, consider using `LAST_INSERT_ID()` after the initial insert, or a different mechanism that doesn't directly write to an `AUTO_INCREMENT` column.

-- If you need to reference the generated ID within the same transaction for other operations:
-- INSERT INTO main_table (data) VALUES ('some_data');
-- SET @generated_id = LAST_INSERT_ID();
-- INSERT INTO another_table (id, data) VALUES (@generated_id, 'related_data');

4

Apply the modified trigger or stored function code. For triggers, use `ALTER TRIGGER` or `DROP TRIGGER` and `CREATE TRIGGER`.

ALTER TRIGGER your_trigger_name
-- new trigger body here

3. Review and Adjust Application Code medium

Ensure your application code doesn't bypass AUTO_INCREMENT by explicitly providing values.

1

Examine the application code that interacts with the database, particularly the parts that perform inserts.

-- Example in a hypothetical application code (Python):
# Instead of:
# cursor.execute("INSERT INTO my_table (id, name) VALUES (%s, %s)", (123, 'Test Name'))
# Use:
# cursor.execute("INSERT INTO my_table (name) VALUES (%s)", ('Test Name',))

2

Look for any instances where the application code is explicitly providing a value for an `AUTO_INCREMENT` column. This is often a sign of misunderstanding how `AUTO_INCREMENT` works.

SELECT COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name' AND EXTRA LIKE '%auto_increment%';

3

Modify the application code to omit the `AUTO_INCREMENT` column from the `INSERT` statement's column list and value list. Let the database handle the generation of the ID.

-- Example SQL statement to be generated by application:
INSERT INTO your_table_name (column1, column2) VALUES ('value1', 'value2');

🔗

Related Errors

5 related errors

MariaDB Error 1671: Unsafe AUTO_INCREMENT Inserts