// HACKER NEWS — CYBERSECURITY
A safe MySQL upgrade that wasn't so safe
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.
That is mind-boggling. How could things get so messed up?
Some time ago, before the upgrade, a migration was run to add a new auto-incrementing primary key to table X.
The migration also updated 6 related tables so that they referenced the new ID instead of the old one. For each table, the update looked roughly like this:
As it turns out, adding an AUTO_INCREMENT column to a replicated table can result in the rows getting different IDs on the source and replica.
According to the MySQL documentation on replication and AUTO_INCREMENT, adding an AUTO_INCREMENT column with ALTER TABLE might not produce the same row ordering on the source and replica. The order in which the IDs are assigned depends on the storage engine and the order in which the rows are processed.
But why would this new field be correctly referenced in 5 out of 6 tables and be completely messed up in one table?