Error
Error Code:
1714
MariaDB Error 1714: Unsafe INSERT IGNORE SELECT
Description
This error indicates that an `INSERT IGNORE... SELECT` statement is considered unsafe for statement-based replication. The non-deterministic order of rows returned by the `SELECT` query can cause different results on the master and slave, leading to data inconsistencies and breaking replication.
Error Message
INSERT IGNORE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave.
Known Causes
3 known causesNon-Deterministic SELECT Order
The `SELECT` part of the `INSERT IGNORE` statement lacks an `ORDER BY` clause, allowing the database to return rows in an arbitrary, unpredictable order.
Statement-Based Replication (SBR)
The server is configured to use statement-based replication, which executes SQL statements directly on the slave, making it sensitive to non-deterministic operations.
Potential Replication Inconsistency
The non-deterministic nature can lead to different rows being ignored on the master and slave, causing data divergence and replication errors.
Solutions
3 solutions available1. Explicitly Order SELECT Results easy
Add an ORDER BY clause to the SELECT statement to ensure predictable row processing.
1
Identify the `INSERT IGNORE ... SELECT` statement causing the error.
2
Modify the `SELECT` part of the statement by adding an `ORDER BY` clause. This guarantees a consistent order for row processing, making the `IGNORE` clause predictable.
INSERT IGNORE INTO target_table (col1, col2) SELECT col1, col2 FROM source_table WHERE condition ORDER BY unique_column;3
Replace `unique_column` with a column or set of columns that uniquely identifies rows in `source_table` and ensures a stable, deterministic order. If no such column exists, consider adding one or using a combination of columns that achieves this.
2. Use a Temporary Table for Staging medium
Insert data into a temporary table first, then perform the INSERT IGNORE from the temporary table.
1
Create a temporary table that mirrors the structure of your `source_table` or the relevant columns.
CREATE TEMPORARY TABLE temp_source_data LIKE source_table;2
Insert the data from your original `source_table` into the temporary table. You can add an `ORDER BY` clause here to ensure a predictable order if needed, although it's less critical at this stage as the next step will handle the `IGNORE` logic.
INSERT INTO temp_source_data SELECT * FROM source_table WHERE condition;3
Perform the `INSERT IGNORE` operation from the temporary table into your `target_table`. Since the temporary table has a fixed set of data, the `INSERT IGNORE` behavior will be consistent.
INSERT IGNORE INTO target_table (col1, col2) SELECT col1, col2 FROM temp_source_data;4
The temporary table will be automatically dropped when the session ends.
3. Replicate with Consistent Ordering advanced
Ensure replication settings enforce consistent ordering for safer replication of INSERT IGNORE SELECT.
1
On your master server, modify the `my.cnf` or `my.ini` configuration file.
2
Add or ensure the following setting is present in the `[mysqld]` section to enable statement-based replication (SBR) with a focus on ordered statements.
binlog_format = MIXED3
Restart the MariaDB server on the master for the changes to take effect.
sudo systemctl restart mariadb4
On the slave server, ensure that `slave_order` is not set in a way that would cause divergence. Typically, `slave_order` is not explicitly set for SBR to work correctly. If it is set, consider removing or commenting it out.
5
Verify replication status and ensure that the `INSERT IGNORE SELECT` statements are being replicated without errors. This approach relies on the assumption that statement-based replication, when properly configured, will execute statements in the same order on master and slave.
SHOW SLAVE STATUS;