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.

Quick and easy, right?

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?

The migration

Some time ago, before the upgrade, a migration was run to add a new auto-incrementing primary key to table X.

ALTER TABLE X ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ;

... continue reading