A safe MySQL upgrade that wasn't so safe
A developer recounts a technical failure during a MySQL database upgrade where row IDs became inconsistent between the source and replica. The issue stemmed from how the AUTO_INCREMENT column was handled during replication, causing data corruption in specific tables.
Why it matters
This serves as a cautionary tale for database administrators regarding the complexities of schema migrations and replication consistency in cloud environments.
I get a notification that my database version has reached end of life, and it has to be upgraded. And the AWS extended support fees are a good motivator to upgrade as soon as possible.
I had a green replica up, so I upgrade that, check that everything works correctly, and then switch over.
I thought so. However, not everything was correct. An hour later, I get reports of a weird bug, so I inspect the database and find out that one specific table, let’s call it table X , has its IDs assigned in a different order. The row that had ID 1 in the previous database now has ID 26.
This table is also referenced in 6 other tables, 5 of which correctly reference these new IDs, but one table is somehow using the IDs of the previous database, which now refer to completely different rows.
Get smarter about the news
Sign up free for a feed built around what you actually care about, Dive Deeper research on any story, and the full text of every article.
Create free accountAlready have an account? Sign in