Inconsistent Data Obtained on the Primary and Standby Nodes When a Query Is Performed Using an Auto-Increment Primary Key Value

Scenario

When an auto-increment primary key value is used to query data on the primary and standby nodes, data inconsistency occurs in the query results.

Possible Causes

For a table without a primary key, the order of data in the table is determined by the ROWID of the storage engine. The ROWIDs may be different on the primary and standby nodes, so the orders of data on the primary and standby nodes may be different. When an auto-increment primary key is added to the table, the values of the auto-increment primary key are initialized based on the data order in the table. As a result, the auto-increment primary key values are different for the same data, that is, the data queried on the primary and standby nodes by using the same auto-increment primary key value is different. For details, see Replication and AUTO_INCREMENT.

Solution

To add an auto-increment column to a table containing data, create a new table with the same table structure, add the auto-increment column to the new table, and then import data from the original table to the new table. (When importing data, ensure that no write operation is being performed on the original table, to prevent data inconsistency between the two tables.)

The detailed procedure is as follows:

1. On the primary node, create a new table named t2 that is the same as the table without a primary key (original table t1), and add an auto-increment primary key to the new table.
   Example:

   CREATE TABLE t2 LIKE t1;
   ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;

2. Insert all data of the original table t1 to the new table t2.

   Example:

   INSERT INTO t2(col1, col2) SELECT col1, col2 FROM t1 ORDER BY col1, col2;

   

   To ensure that the orders of data in the corresponding tables on the primary and standby nodes are the same, the ORDER BY clause must contain all columns of t1.

3. Delete t1 and rename t2 as t1.

   Example:

   DROP TABLE t1;
   RENAME TABLE t2 TO t1;