MySQL MIXED binlog replication produced inconsistent AUTO_INCREMENT IDs on replica after ALTER TABLE ADD COLUMN

The incident began when an AWS RDS MySQL instance received an end-of-life notification and the operator upgraded a green replica before failover. A prior migration had added an id column via ALTER TABLE X ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY and backfilled six referencing tables with UPDATE JOIN statements. Post-switch, table X showed reordered IDs while five dependent tables matched the new values and one retained source-generated IDs.

MySQL 8.0 reference manual section 17.5.1.4 states that adding an AUTO_INCREMENT column with ALTER TABLE is not safe for statement-based replication; the storage engine and row processing order determine assignment. The source ran binlog_format=MIXED, so five UPDATE statements replicated as STATEMENT and re-executed correctly on the replica, while the sixth triggered ROW logging because of the AUTO_INCREMENT column, copying source IDs verbatim.

The root cause is that MIXED mode switches to ROW for any statement containing AUTO_INCREMENT that the optimizer cannot guarantee deterministic ordering. This edge case was not caught by the operator’s replica validation because row counts matched and only one table exhibited the mismatch. Operational impact includes silent data corruption that appears hours after cutover and requires full re-migration or manual ID remapping.

Teams running MIXED binlog should either switch to ROW globally before any ALTER on replicated tables or verify AUTO_INCREMENT column values with checksum queries immediately after replica promotion. No automated RDS upgrade path currently flags this replication hazard.