MySQL upgrade causes data inconsistency due to AUTO_INCREMENT and replication issues

Unexpected Data Inconsistency Post-Upgrade

Following a MySQL database upgrade, a critical bug emerged where a specific table, referred to as table X, exhibited different ID assignments on the upgraded replica compared to the original source database. For example, a row previously identified as ID 1 was assigned ID 26 on the replica. This discrepancy led to incorrect data referencing in one of six related tables, while the other five correctly adapted to the new ID assignments.

The Role of AUTO_INCREMENT in Replication

The root cause was traced back to a prior migration that added an AUTO_INCREMENT primary key to table X using an ALTER TABLE statement. MySQL documentation indicates that adding an AUTO_INCREMENT column to a replicated table via ALTER TABLE might not produce identical row ordering on the source and replica. The order of ID assignment can vary based on the storage engine and how rows are processed during replication.

Impact on Foreign Key Relationships

While five out of six tables correctly updated their foreign key references to align with the new, reordered IDs in table X, one table continued to use the original ID assignments. This resulted in that table referencing completely different rows in table X than intended, highlighting a significant data integrity issue that was not immediately apparent after the upgrade.

Binary Log Format and Replication Behavior

The article further explores how MySQL's binary log format influences replication. MySQL replication uses a binary log to record changes, which are then applied to replicas. The format of this log, either STATEMENT (SQL statements are replicated) or ROW (individual row changes are replicated), dictates how these changes are reproduced on the replica. This distinction is crucial for understanding how data inconsistencies can arise when operations like adding AUTO_INCREMENT columns are replicated.