MySQL Version Upgrade Triggers Silent Data Corruption via Mixed Binary Logging

A database infrastructure upgrade intended to eliminate Amazon Web Services extended support fees led to hidden data misalignments during a MySQL cutover, demonstrating the complex risks associated with database replication mechanisms. As first reported by Hacker News, a technical post-mortem detailed how a routine database migration resulted in secondary tables referencing invalid record keys following a traffic switch to an upgraded replica environment. The incident highlights potential pitfalls when managing primary key updates across replicated open-source database clusters.

The upgrade process was originally prompted by notifications that an existing MySQL instance on Amazon Web Services had reached end-of-life status. Faced with impending AWS extended support surcharges designed to incentivize upgrades off legacy database versions, engineers executed a standard blue-green deployment strategy. The team provisioned a green replica instance, applied the required database version upgrade to that copy, conducted verification checks to ensure operational readiness, and subsequently directed application traffic to the newly upgraded node.

Despite initial validation tests passing successfully, system monitoring flagged unusual application bugs roughly one hour after the cutover was completed. A detailed inspection of the underlying data revealed that a key table, referenced in the technical analysis as table X, had assigned primary key values in a completely different order on the replica compared to the original source database. For example, a data row assigned primary key ID 1 in the original primary database was assigned ID 26 on the upgraded replica instance.

The scope of the corruption expanded due to dependencies across the application schema, where table X was referenced by six related database tables. In a migration executed prior to the database version upgrade, engineers had added a new auto-increment primary key to table X while updating all six dependent tables to reference the newly created identifier instead of the legacy structure. Following the version upgrade cutover, five of the dependent tables accurately reflected the updated primary key mappings on the replica, but one table retained reference values corresponding to the old database state, causing its foreign keys to point toward entirely wrong data rows.

The root cause of the primary key mismatch involved core behavioral characteristics of MySQL replication and table schema modifications. According to official MySQL technical documentation regarding replication and auto-increment properties, adding an AUTO_INCREMENT column via an ALTER TABLE command does not guarantee identical row sequence processing between a source database and its replicas. The specific order in which primary key IDs are generated on each node depends on the underlying storage engine architecture and the exact physical sequence in which individual data rows are processed during schema execution.

The selective corruption across the related tables was directly connected to MySQL's binary logging configuration. MySQL utilizes a binary log to record state changes on a primary source database, which are then transmitted to replica nodes to replicate incoming transactions. The source database in this scenario operated with its binlog_format parameter set to MIXED. This setting permits MySQL to dynamically alternate between STATEMENT mode—where original SQL commands are transmitted and executed on the replica—and ROW mode, where precise row-level modifications are sent directly to replica nodes.

For five of the related tables, MySQL recorded changes using statement-based replication. When those UPDATE queries were executed on the replica node, the database dynamically evaluated its own local version of table X primary keys, thereby writing accurate local identifiers into each dependent table. However, for the sixth dependent table, MySQL automatically switched to row-based replication. Under row-based logging, the replica bypasses query execution and directly applies the literal row state received from the primary source. As a result, primary key references generated on the source node were copied directly onto the replica, where they pointed to incorrect entries due to the divergent key ordering on table X.

Investigators determined that MySQL triggered the row-based logging fallback for the sixth table because that specific table contained its own AUTO_INCREMENT column. Under MySQL's replication rules, specific operations involving auto-increment fields are classified as unsafe for statement-based logging when running in mixed mode, causing the system to automatically fall back to row-based changes. The technical breakdown emphasizes the danger of combining schema alterations with mixed-mode binary logging, as silent primary key drift between replicas can bypass basic operational checks and cause critical enterprise data misalignments in production environments.