📋

Description

This error signifies that an `INSERT IGNORE... SELECT` statement has been executed, which is considered unsafe in replication environments. The core issue lies in the non-deterministic order of rows returned by the `SELECT` subquery, which can lead to data inconsistencies between the source and its replicas because the `IGNORE` clause's behavior depends on this unpredictable order.

💬

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 source and the replica.

🔍

Known Causes

3 known causes

⚠️

Non-Deterministic SELECT Query

The `SELECT` statement within `INSERT IGNORE` does not guarantee a consistent row retrieval order, especially when lacking an `ORDER BY` clause or using non-unique keys for sorting.

⚠️

Statement-Based Replication (SBR)

This error commonly arises in environments configured for statement-based replication, where the exact execution order of statements is critical but can diverge across servers.

⚠️

Unpredictable Duplicate Resolution

The `IGNORE` clause relies on the order of rows to resolve duplicate key conflicts, which becomes non-deterministic without a stable sort order, potentially leading to data inconsistencies.

🛠️

Solutions

3 solutions available

1. Ensure Consistent Row Ordering with ORDER BY easy

Add an ORDER BY clause to the SELECT statement to guarantee consistent row retrieval order for replication.

1

Identify the `INSERT IGNORE ... SELECT` statement causing the replication error.

2

Modify the `SELECT` part of the statement to include an `ORDER BY` clause. The `ORDER BY` clause must use columns that are unique or have a high degree of uniqueness to ensure a deterministic order. Ideally, use the primary key or a combination of columns that uniquely identifies each row.

INSERT IGNORE INTO target_table (col1, col2) 
SELECT col1, col2 
FROM source_table 
WHERE some_condition 
ORDER BY unique_id_column;

3

Apply this modified statement to both your source and replica servers. If the statement is part of an application's logic, update the application code.

2. Replace INSERT IGNORE SELECT with Transactional Operations medium

Break down the operation into individual INSERTs within a transaction, or use a staged approach for better control.

1

Understand the intent of the `INSERT IGNORE ... SELECT` statement. It's likely trying to insert rows from a source table into a target table, skipping duplicates based on unique constraints.

2

Option 1: Use `INSERT ... ON DUPLICATE KEY UPDATE` if you want to update existing rows instead of ignoring them. This is often a safer alternative for replication.

INSERT INTO target_table (col1, col2, unique_col) 
SELECT col1, col2, unique_col 
FROM source_table 
WHERE some_condition 
ON DUPLICATE KEY UPDATE 
col1 = VALUES(col1), col2 = VALUES(col2);

3

Option 2: Create a temporary table to stage the data, then perform a standard `INSERT` into the target table. This can help isolate the operation and make it more predictable.

CREATE TEMPORARY TABLE temp_staging_table AS 
SELECT col1, col2 
FROM source_table 
WHERE some_condition;

INSERT IGNORE INTO target_table (col1, col2) 
SELECT col1, col2 
FROM temp_staging_table 
WHERE NOT EXISTS (SELECT 1 FROM target_table WHERE target_table.unique_col = temp_staging_table.unique_col); -- Adjust WHERE clause based on your unique constraint

DROP TEMPORARY TABLE temp_staging_table;

4

Implement the chosen alternative in your application logic or as a stored procedure, ensuring it's applied consistently on both source and replica.

3. Disable Binary Logging for the Statement (Use with Caution) advanced

Temporarily disable binary logging for the specific `INSERT IGNORE ... SELECT` statement if other solutions are not feasible.

1

This is a **last resort** and should be used with extreme caution as it can lead to data divergence if not managed properly. It's generally not recommended for production environments.

2

To disable binary logging for a single statement, you can set the `sql_log_bin` session variable to `0` before executing the statement and then reset it to `1` afterwards. This needs to be done on the **source** server.

SET SESSION sql_log_bin = 0;

INSERT IGNORE INTO target_table (col1, col2) 
SELECT col1, col2 
FROM source_table 
WHERE some_condition;

SET SESSION sql_log_bin = 1;

3

After running this on the source, you will need to manually apply the same data insertion on the replica server, ensuring that the replica's binary log is enabled for that operation.

4

Alternatively, if you are using statement-based replication, you might consider changing the replication format to `ROW` or `MIXED` if it's not already, as row-based replication is generally more robust for such statements. However, this is a server-wide configuration change.

🔗

Related Errors

5 related errors

MySQL Error 1714: Unsafe INSERT IGNORE SELECT Replication