MySQL 'Mixed' Binlog Format Can Cause Silent Data Corruption During Upgrades
Non-deterministic AUTO_INCREMENT assignments during replica switchovers can lead to mismatched row IDs and corrupted data relationships.
A developer attempting a standard MySQL upgrade discovered that a common migration strategy can lead to silent data corruption. The issue emerged during a replica switchover, where a table's IDs were assigned in a different order on the replica than on the source, causing related tables to point to the wrong rows.
The corruption was triggered by adding an AUTO_INCREMENT column via an ALTER TABLE command on a replicated table. According to MySQL documentation and reports from blog.elis.cc, this operation can result in different row IDs between the source and replica because the assignment order depends on the storage engine and the specific processing order of the rows. While the tables appeared identical in structure, the underlying primary keys had diverged.
The Role of Mixed Binlog Formats
The failure was exacerbated by the source database being configured with `binlog_format = 'MIXED'`. In this mode, MySQL chooses between STATEMENT-based replication (which replays the SQL command) and ROW-based replication (which replays the actual data changes).
In this specific case, updates to five related tables were replicated using STATEMENT mode. This allowed the replica to resolve the correct local IDs based on its own internal row order. However, one related table was replicated using ROW mode, which forced the replica to copy the source's IDs exactly. Because the replica's IDs for the primary table differed from the source's, this ROW-based update created links to the wrong records, corrupting the data relationship.
Why It Matters
This incident highlights a critical edge case in MySQL replication where the 'MIXED' binary logging format can lead to undetected data loss or corruption. It demonstrates that standard upgrade paths—such as upgrading a green replica to avoid AWS extended support fees before performing a cutover—are not foolproof if the underlying data consistency has been compromised by non-deterministic operations.
As the author of blog.elis.cc noted, "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."
Prevention and Mitigation
Engineers performing similar migrations should be wary of adding AUTO_INCREMENT columns to existing replicated tables. To prevent such corruption, teams should verify ID consistency between source and replica using checksums. Alternatively, teams can avoid MIXED binlog formats in favor of full ROW-based replication, which ensures that the exact data state is mirrored regardless of local processing order, eliminating the risk of non-deterministic ID assignment during replication.