DR Error: A dml without pk write target db fail

Scenarios

During a DR task with MySQL serving as the source, an error is reported, and the log information is as follows: A dml without pk write target db fail

Possible Causes

• If a table does not have a primary key to uniquely identify every row and the network connection is unstable, data written to the table without a primary key may be inconsistent with that in the source database.
• The source is RDS for MySQL of an earlier version (5-5.7.23). Tables that have no primary key contain hidden primary keys in the source database. As a result, the DRS task reports an error indicating that the update or delete operation is not hit.

Solution

• If the table does not have a primary key, create a primary key for the table and create a DRS DR task again.
• If the source is RDS for MySQL of an earlier version (5-5.7.23) and there are hidden primary keys in the tables having no primary key, perform the following steps:
  1. Use an account with the process permission to run the following SQL statement at the source end to query table information. In the statement, database/table indicates the database name and table name of a table without a primary key. If the table is a partition table, use the like statement.

     select * from information_schema.INNODB_SYS_TABLES  where name = 'database/table'; 

     

  2. Run the following SQL statement to query the column information of the table without a primary key based on TABLE_ID obtained in 1:

     select * from information_schema.INNODB_SYS_COLUMNS where TABLE_ID = 44;

     

  3. According to the query result, the third column whose POS is 2 is the hidden auto-increment primary key column. If the hidden primary key column in binlog is not the last column, DRS synchronization will fail.
  4. Log in to the RDS console and upgrade the minor kernel version or contact RDS customer service to upgrade the version.
  5. Create a DRS task again.