Full or Incremental Phase Error: Duplicate entry '%s' for key 'PRIMARY'

Scenarios

During a full or increment migration or synchronization, an error is reported, and the log information is as follows: service CAPTURER failed, cause by: Duplicate entry '120' for key 'PRIMARY'.

Possible Causes

The possible causes are as follows:

• binlog_format in the source database is not set to ROW.
• The binlog_format setting of the source database does not take effect immediately.

Solution

1. Log in to the source database using the MySQL official client or other tools.
2. Run the following command for setting global parameters in the source database.

   set global binlog_format = ROW;

3. Run the following command on the source database and check whether the preceding operation is successful:

   select @@global.binlog_format;

4. You can use either of the following methods to ensure that the modified binlog format of the source database takes effect immediately:

   Method 1

   1. Select a non-service period to disconnect all service connections on the current database.
      1. Run the following command to query all service threads (excluding all binlog dump threads and current threads) in the current database:

         show processlist;

      2. Stop all the service threads queried in the previous step.
      

      Do not create or start a migration task before the preceding operations are complete. Otherwise, data may be inconsistent.

   2. To prevent the binlog format of the source database from becoming invalid due to database restart, add or modify the binlog_format parameter in the startup configuration file (my.ini or my.cnf) of the source database and save the modification.

      binlog_format=ROW

   Method 2

   1. To prevent the binlog format of the source database from becoming invalid due to database restart, add or modify the binlog_format parameter in the startup configuration file (my.ini or my.cnf) of the source database and save the modification.

      binlog_format=ROW

   2. Ensure that the binlog_format parameter is successfully added or modified. Then, restart the source database at a non-service period.