Scenario Description
When TencentDB for MySQL is used, replication interruption may occur on standby or read-only instances, with the following error message displayed:
[ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'XXX' at master log mysql-bin.XXX, end_log_pos XXX; Could not execute Write_rows_v1 event on table XXX.XXX; Duplicate entry 'XXX' for key 'xxx.xxx', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.XXX, end_log_pos XXX, Error_code: MY-001062
Cause Analysis
Setting unique_checks to 0 may cause the above error.
The following describes the values of the unique_checks parameter:
Value 1: TencentDB for MySQL checks the uniqueness of unique keys in InnoDB tables.
Value 0: If the data operated by a DML statement is not in the buffer pool, TencentDB for MySQL does not check the uniqueness of secondary indexes in InnoDB tables by reading primary keys and unique keys from the disk. In this case, duplicate unique keys can be inserted.
On the primary instance, when unique_checks is set to 0, if the data operated by a DML statement is not in the buffer pool, TencentDB for MySQL does not check the uniqueness of secondary indexes in InnoDB tables by reading primary keys and unique keys from the disk. In this case, the primary instance successfully inserts duplicate unique keys. On the standby instance, when unique_checks is set to 0, if the data operated by a DML statement is in the buffer pool, TencentDB for MySQL checks the uniqueness of the secondary indexes operated by the DML statement. In this case, duplicate unique keys are not allowed to be inserted, resulting in replication interruption. For details, see MySQL official Bug#106121. Problem-solving Ideas
First check the unique_checks parameter value of the instance, and then modify the parameter value based on the actual situation.
If you are certain that the inserted data will not cause unique key conflicts, you can set unique_checks to 0 to speed up data import.
If you are not sure whether the inserted data will cause unique key conflicts, set unique_checks to 1.
Operation Steps
2. Choose Database Management > Parameter Settings and click Bulk Parameter Modification.
3. Find the unique_checks parameter and modify its runtime value.
4. If you cannot find this parameter in the instance, submit a ticket to contact the technical team for assistance.