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;

Other common scenarios of auto-increment value changes

Besides the query mismatch between the primary and secondary instances caused by adding an auto-increment primary key to a table without a primary key, the following scenarios can also cause unexpected auto-increment value behavior. Check these scenarios when you troubleshoot auto-increment issues.

• REPLACE INTO causes fluctuating auto-increment values: A REPLACE INTO statement deletes the conflicting row and inserts a new one (DELETE followed by INSERT). Each insert allocates a new auto-increment value instead of reusing the original one. Frequent use of REPLACE INTO keeps increasing the auto-increment value, which appears to jump up and down and can grow far beyond the actual row count of the table.

• Duplicate auto-increment values after a primary/secondary switchover resets the increment parameters: After a primary/secondary switchover, the auto_increment_increment (increment step) and auto_increment_offset (starting offset) parameters of the instance may be reset to their default values. The newly generated auto-increment values may be discontinuous with, or conflict with, the sequence allocated before the switchover, which causes DUP KEY errors on the application side. After a switchover, verify that these two parameters still match your business requirements to avoid auto-increment conflicts during concurrent writes from multiple instances.

• The auto-increment value set through ALTER TABLE is lost after 24 hours: If you manually set the starting auto-increment value of a table by running ALTER TABLE ... AUTO_INCREMENT = N and a primary/secondary switchover occurs on the instance later, the auto-increment parameters are reset and your manual change is lost. To persist the change across switchovers, use an online DDL tool such as pt-online-schema-change (pt-osc) instead of a direct ALTER TABLE statement. Rebuilding the table in this way persists the auto-increment change together with the table structure.

• Duplicate auto-increment IDs when importing data with import table: When you use import table to load data that already contains values for the auto-increment column, those values may not be contiguous with the current auto-increment counter of the target table. Subsequent inserts can then generate auto-increment IDs that duplicate values already present in the imported data. Before the import, check the current auto-increment value of the target table. After the import completes, correct the auto-increment counter based on the actual maximum value to avoid conflicts on subsequent writes.