📋

Description

MySQL Error 1715 indicates that an `INSERT... SELECT... ON DUPLICATE KEY UPDATE` statement is considered unsafe for statement-based replication. This occurs because the order of rows retrieved by the `SELECT` part is non-deterministic, which can lead to different results on a replica compared to the source server.

💬

Error Message

INSERT... SELECT... ON DUPLICATE KEY UPDATE is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are updated. This order cannot be predicted and may differ on source and the replica.

🔍

Known Causes

3 known causes

⚠️

Non-Deterministic SELECT Order

The `SELECT` query within the `INSERT... SELECT... ON DUPLICATE KEY UPDATE` statement lacks an `ORDER BY` clause, making the sequence of retrieved rows unpredictable.

⚠️

Statement-Based Replication Active

The MySQL server is configured to use statement-based binary logging, which cannot reliably replicate operations where the outcome depends on the order of rows processed.

⚠️

Complex SELECT Operations

The `SELECT` statement involves joins, subqueries, or other complex operations that inherently produce an undefined row order without an explicit `ORDER BY` clause.

🛠️

Solutions

3 solutions available

1. Ensure Deterministic Row Order with a Stable ORDER BY Clause easy

Modify the SELECT statement to include a stable ORDER BY clause based on a unique key or a combination of columns that guarantees consistent row retrieval order.

1

Identify the `INSERT...SELECT...ON DUPLICATE KEY UPDATE` statement that is causing the error.

2

Examine the `SELECT` part of the statement. If it lacks an `ORDER BY` clause, or if the `ORDER BY` clause is not guaranteed to produce a consistent order across replication (e.g., relying on implicit order or non-unique columns), modify it.

For example, if your `SELECT` is `SELECT col1, col2 FROM source_table`, and `source_table` has a unique primary key `id`, change it to:

3

Add or modify the `ORDER BY` clause in the `SELECT` statement to use a unique identifier or a combination of columns that ensures a deterministic order.

INSERT INTO target_table (col1, col2) SELECT col1, col2 FROM source_table ORDER BY id, another_unique_column;

4

Apply this change to the statement on your source MySQL server.

2. Rewrite as Separate INSERT and UPDATE Statements medium

Deconstruct the `INSERT...SELECT...ON DUPLICATE KEY UPDATE` into two distinct statements: an `INSERT IGNORE` to insert new rows and a subsequent `UPDATE` to modify existing ones.

1

Locate the problematic `INSERT...SELECT...ON DUPLICATE KEY UPDATE` statement.

2

Create a first statement to insert only the rows that do not already exist in the target table. Use `INSERT IGNORE` to suppress errors for duplicate keys.

INSERT IGNORE INTO target_table (col1, col2) SELECT col1, col2 FROM source_table WHERE NOT EXISTS (SELECT 1 FROM target_table WHERE target_table.unique_key = source_table.unique_key);

3

Create a second statement to update the existing rows in the target table with the values from the source table. This update should target rows that already exist and match the criteria.

UPDATE target_table tt JOIN source_table st ON tt.unique_key = st.unique_key SET tt.col1 = st.col1, tt.col2 = st.col2 WHERE tt.unique_key IN (SELECT unique_key FROM source_table);

4

Execute these two statements sequentially on your source MySQL server.

3. Utilize Row-Based Replication (RBR) with a Deterministic SELECT advanced

Configure your replication to use row-based replication (RBR) and ensure the `SELECT` statement within the `INSERT...SELECT...ON DUPLICATE KEY UPDATE` is deterministic.

1

Verify that your MySQL replication is configured to use row-based replication. This is the default for newer MySQL versions, but it's good to confirm.

Check `@@GLOBAL.binlog_format` on your source server. It should be set to `ROW`.

2

If `binlog_format` is not `ROW`, change it. This requires a server restart.

On the source server:
1. Edit `my.cnf` or `my.ini` to include `binlog_format = ROW` under the `[mysqld]` section.
2. Restart the MySQL server.

3

Even with RBR, the `SELECT` part of the `INSERT...SELECT...ON DUPLICATE KEY UPDATE` must be deterministic to avoid replication issues. Apply the solution from 'Ensure Deterministic Row Order with a Stable ORDER BY Clause' to make the `SELECT` deterministic.

See Solution 1 for modifying the `SELECT` statement with a stable `ORDER BY` clause.

4

Test thoroughly to ensure that changes are replicated correctly.

🔗

Related Errors

5 related errors

MySQL Error 1715: Unsafe INSERT...SELECT for Replication