This document introduces the possible causes and solutions for adding an auto-increment primary key that causes inconsistent query data in primary and secondary instances.
Issue Description
Querying with the same auto-increment primary key value (auto-increment ID) on both the primary and secondary instances results in inconsistent data in the query results.
Possible Causes
When an auto-increment primary key is added to a table without a primary key, the auto-increment primary key values are assigned based on the physical storage order of the data in the table. Because a table without a primary key lacks an explicit primary key constraint, the order of its row data is determined by the RowID within the storage engine, and the RowID for the same data may differ between the primary and secondary instances, resulting in inconsistent physical arrangement order of the data in the primary and secondary instances, thus causing the auto-increment primary key values assigned to the same data to differ across different instances. Therefore, when the primary and secondary instances are queried separately using the same auto-increment primary key value, the results obtained may differ. For details, see BUG#92949 and MySQL official documentation. Solution
1. Create a new table identical to the original table without a primary key on the primary instance, and add an auto-increment primary key.
2. Sort the data by all fields and then insert it into the new table.
3. Delete the original table without a primary key, and rename the new table to the name of the original table without a primary key.
Example
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;