A routine MySQL upgrade on AWS RDS triggered a silent data corruption issue when promoting a green replica. After upgrading the replica and verifying functionality, the author switched traffic over. An hour later, a critical bug emerged: one table (table X) had its auto-increment IDs assigned in a different order on the replica versus the source. The row previously holding ID 1 now held ID 26.

The root cause traced back to an earlier migration that added an AUTO_INCREMENT primary key to table X via `ALTER TABLE X ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY`. Six related tables were updated to reference this new ID using JOIN-based UPDATE statements. However, MySQL documentation warns that adding AUTO_INCREMENT columns to replicated tables may produce different row ordering on source and replica depending on storage engine and processing order.

The discrepancy manifested due to the source database's `binlog_format=MIXED`. Five tables replicated their UPDATE statements in STATEMENT mode, causing the replica to re-execute the JOIN against its local (different) ID values — correctly mapping references. But the sixth table, which also had an AUTO_INCREMENT column, was deemed unsafe for statement-based replication and logged in ROW mode. The replica applied raw row changes from the source, copying source-generated `x_id` values that now pointed to wrong rows on the replica.

This subtle interaction between AUTO_INCREMENT assignment order, MIXED binary logging, and unsafe statement detection caused silent referential integrity failure. The author warns that such issues are easy to miss during upgrades but can rapidly corrupt data.