All Products
Search

This Product

    ApsaraDB RDS:What do I do if the data replication on an ApsaraDB RDS for MySQL instance is interrupted due to the setting of the unique_checks parameter?

    Document Center

    ApsaraDB RDS:What do I do if the data replication on an ApsaraDB RDS for MySQL instance is interrupted due to the setting of the unique_checks parameter?

    Last Updated:Nov 24, 2023

    Problem description

    When I replicate data from a primary ApsaraDB RDS for MySQL instance to its secondary or read-only RDS instance, the replication is interrupted and the following error message is 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

    Causes

    The unique_checks parameter is set to 0.

    Valid values of the unique_checks parameter:

    • 1: ApsaraDB RDS for MySQL checks the uniqueness of the unique key in the InnoDB table.

    • 0: If the data manged by the DML statement is not stored in the buffer pool, ApsaraDB RDS for MySQL does not read the primary key or unique key of the disk to check the uniqueness of secondary indexes in the InnoDB table. In this case, duplicate unique keys can be inserted.

    If the unique_checks parameter of the primary RDS instance is set to 0 and the data managed by the DML statement is not stored in the buffer pool, ApsaraDB RDS for MySQL does not read the primary key or unique key of the disk to check the uniqueness of secondary indexes in the InnoDB table. In this case, duplicate unique keys can be inserted into the primary RDS instance. If the unique_checks parameter of the secondary RDS instance is set to 0 and the data managed by the DML statement is stored in the buffer pool, ApsaraDB RDS for MySQL checks the uniqueness of the secondary index on which the DML statement is executed. In this case, duplicate unique keys cannot be inserted. As a result, the replication is interrupted. For more information, see MySQL bug #106121.

    Solutions

    • If you confirm that the inserted data does not cause unique key conflicts, you can set the unique_checks parameter to 0 to speed up the data import.

    • If the inserted data may cause unique key conflicts, we recommend that you set the unique_checks parameter to 1.