ApsaraDB RDS:After I add an auto-increment primary key to a table in my ApsaraDB RDS instance, the query result from the primary RDS instance is inconsistent with the query result from the secondary RDS instance. What do I do?

Problem description

When I use the same auto-increment primary key value (auto-increment ID) to query data from the primary and secondary RDS instances, the query results are inconsistent.

Causes

When you add an auto-increment primary key to a table without a primary key, the values for the auto-increment primary key are assigned based on the order in which data records are sorted in the table. If a table does not have a primary key, data records in the table are sorted by row ID of the storage engine. A data record in the table may have different row IDs on the primary and secondary RDS instances, and the data record is sorted in different orders on the primary and secondary RDS instances. As a result, the same data record corresponds to different values for the auto-increment primary key on the primary and secondary RDS instances. For more information, see BUG#92949 and MySQL documentation.

Solutions

You can perform the following steps to resolve the issue:

1. On the primary RDS instance, create a table that is the same as the original table without a primary key and add an auto-increment primary key to the new table.

2. Sort data records in the original table by field and insert the sorted data records into the new table.

3. Delete the original table and change the name of the new table to the name of the original table.

Sample code:

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
DROP TABLE t1;
RENAME TABLE t2 TO t1;