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
unique_checks is a MySQL parameter that controls whether InnoDB performs uniqueness checks when inserting data. It is a dynamic variable and can be modified online without a restart.
1. Connect to the target TencentDB for MySQL instance through a MySQL client.
2. Refer to the following command to query the current parameter value.
SELECT @@unique_checks;
SHOW VARIABLES LIKE 'unique_checks';
3. Modify the parameter value as needed. Refer to the following command for modification:
SET unique_checks = 0;
SET unique_checks = 1;
Equivalent syntax:
SET SESSION unique_checks = 0;
SET @@session.unique_checks = 0;