GoKawiilMySQL replica upgrade exposes AUTO_INCREMENT replication bug, corrupting foreign keys
An engineer upgrading a MySQL database via a replica discovered that a table's auto-incrementing primary key was assigned in a different row order on the replica than on the source. Because six related tables had been migrated to reference these new IDs, one of them ended up pointing to completely wrong rows after the switchover, while the other five happened to remain correct.
GoKawiil's interpretation of the reporting above, not reported fact.
This exposes a subtle but serious gap in MySQL's replication guarantees: adding an AUTO_INCREMENT column via ALTER TABLE does not guarantee identical row ordering between source and replica, especially depending on storage engine behavior. Teams relying on replicas for supposedly safe, zero-downtime upgrades could silently corrupt data integrity across foreign-key relationships without any error being thrown.
- Adding AUTO_INCREMENT columns via ALTER TABLE can produce different ID orderings on MySQL replicas versus the source.
- A routine replica-based database upgrade led to silent data corruption in one of six related tables.
- Migrations that depend on auto-generated IDs should be re-verified after failover, since replication order is not guaranteed to match the source.
High Performance MySQL, 4th Edition (O'Reilly) — If a routine version upgrade can silently reshuffle your auto-increment IDs and break foreign key references, it's worth having a deep reference on hand. High Performance MySQL covers schema design, replication, and upgrade behavior in depth — exactly the territory where this kind of surprise hides. A solid desk companion for anyone maintaining production MySQL.
See High Performance MySQL, 4th Edition (O'Reilly) on Amazon → Affiliate link — we may earn a commission on purchases, at no extra cost to you. Product picked by AI based on this article; it is not a tested recommendation.Source: blog.elis.cc, 2026-08-29
Published there as: “A safe MySQL upgrade that wasn't so safe”
Read the original report → The summary and analysis above are GoKawiil's own, written from reporting by the source above. Facts and quotes belong to the original publisher.