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;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:
UPDATE some_table
JOIN x
ON x.old_id = some_table.x_old_id
SET some_table.x_id = x.id;The AUTO_INCREMENT column
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.
OK, I wasn’t aware of that.
But why would this new field be correctly referenced in 5 out of 6 tables and be completely messed up in one table?
The binary log format
This is where it becomes more confusing.
MySQL replication uses the “binary log” to record changes made on the source. Those changes are then sent to the replicas, which use them to reproduce the same transactions.
What gets recorded, and how it gets applied on the replica, depends on the binary log “format”. MySQL supports 3 “formats”:
STATEMENT: The SQL statement itself is written to the binary log, and the replica executes that statement.ROW: The changes made to individual rows are written to the binary log, and those row changes are applied directly to the replica.MIXED: With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases.
Apparently, my source database had binlog_format configured as MIXED.
So the reason the 5 tables referenced the correct new IDs is because their update statements were replicated using STATEMENT mode. The exact UPDATE statement ran again on the replica. It looked up the replica’s version of x.id and wrote the correct local ID into each related table.
But for the remaining table, MySQL decided to use ROW mode instead… In that mode, the replica does not run the original UPDATE. It receives the resulting row changes from the source and applies them directly.
So the x_id values generated on the source were copied to the replica. But because table X had different IDs on the replica, those values now pointed to completely different rows.
You might ask, why did MySQL decide to use ROW mode just for one table?
The only visible difference I could find between this table and the other 5 was that this one had an AUTO_INCREMENT column.
MySQL does have specific cases involving AUTO_INCREMENT that it considers unsafe for statement-based replication and therefore logs using ROW under MIXED.
That’s ironic, isn’t it? It all seems to come down to the AUTO_INCREMENT feature in the end.
Conclusion
Be careful with MySQL replicas. The scary thing about this kind of issue is that it’s unexpected and easy to miss, but it can quickly turn into a disaster in production, and you’re left wondering how did things end up like that.