📋

Description

Error 1724 occurs when an `INSERT... ON DUPLICATE KEY UPDATE` statement is executed on a table that has more than one unique key or index (including the primary key). MariaDB flags this operation as unsafe for statement-based binary logging because it can lead to data inconsistencies across replicas due to non-deterministic key selection.

💬

Error Message

INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe

🔍

Known Causes

3 known causes

⚠️

Multiple Unique Keys Defined

The target table for the `INSERT... ON DUPLICATE KEY UPDATE` statement has more than one unique key or index (including the primary key) defined.

⚠️

Statement-Based Binary Logging

The database server is configured to use statement-based binary logging, which is sensitive to operations that might lead to inconsistencies across replicas.

⚠️

Ambiguous Key Resolution

With multiple unique keys, MariaDB cannot deterministically choose which unique key to use for the `ON DUPLICATE KEY` check, making the operation potentially non-deterministic across replicas.

🛠️

Solutions

3 solutions available

1. Specify the Target UNIQUE KEY for UPDATE easy

Explicitly tell MariaDB which UNIQUE KEY to check for duplicates.

1

When using `INSERT ... ON DUPLICATE KEY UPDATE`, you need to specify which `UNIQUE KEY` should be used to detect duplicates. MariaDB's `INSERT ... ON DUPLICATE KEY UPDATE` statement by default checks all `UNIQUE KEY` and `PRIMARY KEY` constraints. If there are multiple, it doesn't know which one to prioritize for the `UPDATE` part. To resolve this, you can use the `FOR UPDATE` clause with the specific `UNIQUE KEY` name. This tells MariaDB to only consider duplicates on that particular key for the update operation. You'll need to know the name of your `UNIQUE KEY`.

ALTER TABLE your_table_name ADD CONSTRAINT uk_specific_key UNIQUE KEY uk_specific_key_name (column1, column2);

2

Then, modify your `INSERT ... ON DUPLICATE KEY UPDATE` statement to include the `FOR UPDATE` clause, referencing the name of the unique key you want to target.

INSERT INTO your_table_name (id, column1, column2, column3) VALUES (1, 'value1', 'value2', 'new_value3') ON DUPLICATE KEY UPDATE column3 = VALUES(column3) FOR UPDATE uk_specific_key_name;

2. Consolidate UNIQUE Keys medium

If possible, combine multiple UNIQUE KEYs into a single one.

1

Analyze your table's `UNIQUE KEY` constraints. If there are multiple `UNIQUE KEY`s that are often checked together or if their uniqueness is logically dependent on each other, consider combining them into a single `UNIQUE KEY` or a composite `PRIMARY KEY`. This simplifies the uniqueness enforcement and resolves the ambiguity for `ON DUPLICATE KEY UPDATE`.

SHOW CREATE TABLE your_table_name;

2

If you have separate `UNIQUE KEY`s on `col_a` and `col_b`, and you want to enforce uniqueness on the combination of `col_a` and `col_b`, you can drop the individual `UNIQUE KEY`s and create a composite one.

ALTER TABLE your_table_name DROP INDEX uk_col_a;
ALTER TABLE your_table_name DROP INDEX uk_col_b;
ALTER TABLE your_table_name ADD UNIQUE KEY uk_composite (col_a, col_b);

3

After consolidating, your `INSERT ... ON DUPLICATE KEY UPDATE` statement will work without issues, as there's only one key to consider for duplicates.

INSERT INTO your_table_name (col_a, col_b, other_column) VALUES ('a_val', 'b_val', 'new_val') ON DUPLICATE KEY UPDATE other_column = VALUES(other_column);

3. Remove Redundant UNIQUE Keys medium

Eliminate any `UNIQUE KEY` constraints that are not strictly necessary.

1

Review all `UNIQUE KEY` constraints on your table. Sometimes, multiple `UNIQUE KEY`s might be defined, but one or more might be redundant or not essential for data integrity. For example, if you have a `UNIQUE KEY` on `email` and another on `username`, and your application logic primarily relies on `email` for uniqueness, you might consider removing the `username` `UNIQUE KEY` if it's not critical.

SHOW CREATE TABLE your_table_name;

2

If you identify a `UNIQUE KEY` that is not required, you can drop it.

ALTER TABLE your_table_name DROP INDEX index_name_to_remove;

3

Once redundant `UNIQUE KEY`s are removed, the `INSERT ... ON DUPLICATE KEY UPDATE` statement will have fewer (ideally one) `UNIQUE KEY` to consider, resolving the error.

INSERT INTO your_table_name (id, col1, col2) VALUES (1, 'val1', 'val2') ON DUPLICATE KEY UPDATE col2 = VALUES(col2);

🔗

Related Errors

5 related errors

MariaDB Error 1724: Unsafe INSERT ON DUPLICATE KEY