A MySQL upgrade went wrong: replica IDs diverged and one table pointed to wrong rows
A safe MySQL upgrade that wasn't so safe
A developer upgraded a MySQL database replica and switched over, only to discover an hour later that one table's auto-increment IDs didn't match the source. Five related tables correctly referenced the new IDs, but a sixth used IDs from the old database, pointing to entirely different rows. The root cause: the source used MIXED binary logging. For most tables, the UPDATE statement was replicated in STATEMENT mode, but for the table with an AUTO_INCREMENT column, MySQL switched to ROW mode, copying the source's ID values directly to the replica, where they were wrong.
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.
- Insimwytim
That's kinda fun (not in a fun way).
Since the update is done semi-automatically by the means of AWS RDS, does AWS takes any responsibility for that? Unless this use case is warned against in migration notes, I would think they ought to.
- 190n
I don't understand why this issue only appeared after the upgrade, not immediately after the migration to add the column?
- krautburglar
Mixing binlog formats across replicas sounds like user error to me.