Adding an auto-increment primary key causes inconsistent query results between the primary and secondary instances

Problem description

After you run ALTER TABLE to add an auto-increment primary key to a table that has no primary key, querying by the same auto-increment ID returns different rows on the primary and secondary RDS instances.

Cause

When MySQL assigns auto-increment values to an existing table, it numbers rows based on the physical order they are stored in the table. Without a primary key, that order is determined by the storage engine's internal row IDs. The same row can have different row IDs on the primary and secondary instances, so after the ALTER TABLE completes, the same row ends up with a different auto-increment value on each instance.

This is a known MySQL behavior. For details, see BUG#92949 and the MySQL replication documentation.

Solution

Rebuild the table on the primary instance by inserting rows in a consistent, deterministic order:

1. On the primary instance, create a new table (t2) with the same structure as the original table (t1), and add an auto-increment primary key to it.

2. Insert all rows from the original table into the new table, sorted by a stable set of fields.

3. Drop the original table and rename the new table to the original name.

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;