📋

Description

This error indicates that a `CREATE ... REPLACE SELECT` statement is considered unsafe for statement-based replication. It occurs because the order of rows retrieved by the `SELECT` query is non-deterministic, potentially causing data inconsistencies between a MySQL source and its replica.

💬

Error Message

CREATE... 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 `CREATE ... REPLACE SELECT` statement lacks an `ORDER BY` clause, making the row retrieval order unpredictable.

⚠️

Statement-Based Replication Context

The database is configured for statement-based replication, which is highly sensitive to operations that produce different results depending on execution order.

⚠️

Ambiguous Row Replacement Logic

The `REPLACE` operation relies on a consistent input order to determine which existing rows are updated or inserted, which becomes ambiguous without a defined `SELECT` order.

🛠️

Solutions

4 solutions available

1. Use a UNIQUE or PRIMARY KEY for Row Identification medium

Ensure rows can be uniquely identified for reliable replacement.

1

Identify a column or set of columns that uniquely identifies each row in your target table. This is often a primary key or a unique index.

2

Modify your `CREATE OR REPLACE SELECT` statement to explicitly reference this unique identifier in the `WHERE` clause of the `SELECT` part. This ensures that the replacement targets specific, predictable rows.

CREATE OR REPLACE TABLE target_table SELECT ... FROM source_table WHERE source_table.unique_id = target_table.unique_id;

3

If your target table doesn't have a unique identifier, add one. This is the most robust long-term solution.

ALTER TABLE target_table ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

2. Perform INSERT ON DUPLICATE KEY UPDATE medium

Handle potential duplicates by updating existing rows or inserting new ones.

1

Ensure your target table has a `PRIMARY KEY` or `UNIQUE INDEX` on the columns that define uniqueness. This is crucial for `INSERT ... ON DUPLICATE KEY UPDATE` to work.

ALTER TABLE target_table ADD UNIQUE INDEX unique_idx (column1, column2);

2

Replace the `CREATE OR REPLACE SELECT` statement with an `INSERT ... ON DUPLICATE KEY UPDATE` statement. This statement will attempt to insert rows from the `SELECT` query. If a duplicate key is found (based on your unique index), it will execute the `UPDATE` clause.

INSERT INTO target_table (col1, col2, col3) 
SELECT source_col1, source_col2, source_col3 
FROM source_table 
ON DUPLICATE KEY UPDATE 
  col1 = VALUES(col1), 
  col2 = VALUES(col2), 
  col3 = VALUES(col3);

3

The `VALUES(column_name)` syntax refers to the value that would have been inserted for that column.

3. Split into Separate CREATE and UPDATE/DELETE Operations medium

Break down the operation into distinct, predictable steps.

1

Create a temporary table to hold the new or updated data from your `SELECT` query.

CREATE TEMPORARY TABLE temp_source LIKE source_table; 
INSERT INTO temp_source SELECT ... FROM source_table;

2

Delete rows from the target table that should be replaced. This deletion should be based on a predictable condition, ideally using a unique identifier.

DELETE target_table 
FROM target_table 
JOIN temp_source ON target_table.unique_id = temp_source.unique_id;

3

Insert the data from the temporary table into the target table.

INSERT INTO target_table (col1, col2, ...) 
SELECT col1, col2, ... FROM temp_source;

4

Drop the temporary table.

DROP TEMPORARY TABLE temp_source;

4. Ensure Consistent Ordering with ORDER BY easy

Provide a deterministic order to the SELECT statement.

1

Add an `ORDER BY` clause to your `SELECT` statement within the `CREATE OR REPLACE SELECT` command. This clause must specify columns that guarantee a consistent and predictable row order.

CREATE OR REPLACE TABLE target_table 
SELECT ... FROM source_table 
ORDER BY column1, column2;

2

Crucially, ensure that the `ORDER BY` clause uses columns that are also present and consistently ordered in the replica. Using primary keys or unique indexes in the `ORDER BY` is highly recommended.

CREATE OR REPLACE TABLE target_table 
SELECT ... FROM source_table 
ORDER BY target_table.primary_key_column;

3

Be aware that this is still less robust than using explicit row identification for replacement, as row order can still be subject to subtle variations in very complex scenarios or under heavy load, though it significantly reduces the risk.

🔗

Related Errors

5 related errors

MySQL Error 1718: Unsafe CREATE REPLACE SELECT