📋

Description

MySQL Error 1716 occurs when a `REPLACE INTO ... SELECT ...` statement is executed, particularly in a replication environment. The error indicates that the order in which rows are retrieved by the `SELECT` query is not predictable, which can lead to inconsistent results between the source and its replicas, causing data divergence.

💬

Error Message

REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on source and the replica.

🔍

Known Causes

3 known causes

⚠️

Non-Deterministic SELECT Order

The `SELECT` clause within the `REPLACE...SELECT` statement lacks an `ORDER BY` clause, meaning the order of rows returned is not guaranteed and can vary.

⚠️

Statement-Based Replication (SBR)

The MySQL server is configured for Statement-Based Replication (SBR), which logs SQL statements. Non-deterministic statements like this can cause replication issues.

⚠️

Potential Replication Divergence

The core problem is the risk that the `REPLACE` operation might behave differently on the source and replica due to the unpredictable `SELECT` order, leading to data inconsistencies.

🛠️

Solutions

3 solutions available

1. Eliminate REPLACE...SELECT by Using Separate INSERT and DELETE Statements medium

Break down the REPLACE...SELECT into an INSERT followed by a DELETE for deterministic behavior.

1

Identify the `REPLACE...SELECT` statement causing the replication error.

Example: 
REPLACE INTO target_table (col1, col2, col3)
SELECT colA, colB, colC FROM source_table WHERE condition;

2

Replace the `REPLACE...SELECT` with two separate statements: an `INSERT` and a `DELETE`.

BEGIN;

-- Insert new or updated rows
INSERT INTO target_table (col1, col2, col3)
SELECT colA, colB, colC FROM source_table WHERE condition
ON DUPLICATE KEY UPDATE col2 = VALUES(col2), col3 = VALUES(col3);

-- Delete rows that are no longer present in the source (if applicable)
-- This part requires careful consideration of your logic and might need adjustment.
-- A common scenario is to delete rows from target_table not found in source_table's selected set.
-- You might need a temporary table or a more complex JOIN for this.
DELETE FROM target_table
WHERE (col1, col2) NOT IN (SELECT colA, colB FROM source_table WHERE condition);

COMMIT;

3

Ensure that the `ON DUPLICATE KEY UPDATE` clause correctly handles updates for existing rows, matching the behavior of `REPLACE` which deletes and inserts.

ON DUPLICATE KEY UPDATE col2 = VALUES(col2), col3 = VALUES(col3);

4

Test the new `INSERT` and `DELETE` logic thoroughly on a staging environment to confirm it produces the same logical outcome as the original `REPLACE...SELECT`.

2. Add a Deterministic ORDER BY Clause to the SELECT Statement easy

Introduce a stable ordering to the SELECT statement to make the REPLACE...SELECT operation predictable.

1

Locate the `REPLACE...SELECT` statement that is causing the error.

Example: 
REPLACE INTO target_table (col1, col2, col3)
SELECT colA, colB, colC FROM source_table WHERE condition;

2

Add a deterministic `ORDER BY` clause to the `SELECT` statement. This clause should reference columns that guarantee a unique and consistent order of rows.

REPLACE INTO target_table (col1, col2, col3)
SELECT colA, colB, colC FROM source_table WHERE condition
ORDER BY colA ASC; -- or any other column(s) that provide a stable order

3

Ensure the `ORDER BY` columns are part of the `source_table` and that their values are sufficient to establish a unique order. If not, consider including a primary key or a combination of columns.

4

Deploy the modified statement to the source MySQL server. The replication will then attempt to replicate this ordered `REPLACE...SELECT`.

3. Disable Replication for the Specific Statement medium

Prevent the problematic REPLACE...SELECT statement from being replicated by using the `replicate-ignore-table` or `replicate-do-table` options, or by using `SQL_NO_CACHE`.

1

Determine the `source_table` and `target_table` involved in the `REPLACE...SELECT` statement.

Example: 
REPLACE INTO target_table (col1, col2, col3)
SELECT colA, colB, colC FROM source_table WHERE condition;

2

Option A: Ignore replication for the target table (if the `REPLACE...SELECT` is the only operation on this table that needs to be excluded).

In my.cnf or my.ini on the replica server:

[mysqld]
replicate-ignore-table=your_database_name.target_table

3

Option B: Only replicate specific tables (if you want to be very precise and exclude all other tables by default). This is generally less practical for a single statement issue.

In my.cnf or my.ini on the replica server:

[mysqld]
replicate-do-table=your_database_name.another_table
replicate-do-table=your_database_name.yet_another_table

4

Option C: Use `SQL_NO_CACHE` within the `SELECT` statement on the source (this is a less common workaround and might not always be sufficient depending on replication configuration).

REPLACE INTO target_table (col1, col2, col3)
SELECT SQL_NO_CACHE colA, colB, colC FROM source_table WHERE condition;

5

Restart the MySQL replica server after modifying the configuration file.

6

Manually apply the `REPLACE...SELECT` statement on the replica server to ensure data consistency. This requires careful coordination.

🔗

Related Errors

5 related errors

MySQL Error 1716: Unsafe REPLACE...SELECT Replication