pingcap
Repeatedly ecounter `CDC:ErrMySQLDuplicateEntryCDC` Error in TiCDC.
What did you do?
Issue Description
When using TiCDC to replicate data to TiDB or MySQL, you may encounter the error if you execute SQL upstream in specific pattern:
CDC:ErrMySQLDuplicateEntryCDC
This error typically occurs due to how TiDB processes transactional changes. Specifically, TiDB converts DELETE + INSERT operations of a same row within the same transaction into UPDATE changes. TiCDC then replicates these as batch UPDATEs . Since TiDB or MySQL enforces unique key constraints row by row, an update operation that attempts to swap unique key values can lead to conflicts.
Example Scenario
Consider the following table:
CREATE TABLE data_table (
id BIGINT(20) NOT NULL PRIMARY KEY,
value BINARY(16) NOT NULL,
UNIQUE KEY value_index (value)
) CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
If an upstream transaction attempts to swap two value fields:
DELETE FROM data_table WHERE id = 1;
DELETE FROM data_table WHERE id = 2;
INSERT INTO data_table (id, value) VALUES (1, 'v3');
INSERT INTO data_table (id, value) VALUES (2, 'v1');
TiDB internally translates this into two UPDATE operations:
UPDATE data_table SET value = 'v3' WHERE id = 1;
UPDATE data_table SET value = 'v1' WHERE id = 2;
If v1 still exists in the table when the second update executes, TIDB or MySQL's unique key constraint will be violated, leading to ErrMySQLDuplicateEntry error.
Solution: Enable Safe Mode
To prevent this issue, you can enable safe mode in TiCDC by setting the safe-mode=true parameter in the sink-uri configuration. This forces TiCDC to split UPDATE operations into DELETE + INSERT, preserving the original logical changes and avoiding batch update conflicts.
Configuration Example
Modify your TiCDC sink URI as follows:
mysql://user:password@host:port/?safe-mode=true
Potential Side Effects
Increased replication latency due to additional DELETE + INSERT operations.
Conclusion
If you frequently encounter CDC:ErrMySQLDuplicateEntryCDC, enabling safe-mode=true in the sink-uri is an effective way to resolve the issue by preserving the original DELETE + INSERT logic, avoiding unique key conflicts in TiDB or MySQL.
What did you expect to see?
No response
What did you see instead?
as above.
Versions of the cluster
v7.5.5
We plan to fix it from v9.0. And since there is a workaround, we have no plan to pick it to previous versiosn.
