A database administrator received a notification indicating that their MySQL database version had reached end of life and required an upgrade. To avoid extended support fees from AWS, the administrator upgraded a green replica and verified its functionality before switching over to it. However, after the switch, reports of a bug emerged, revealing that one specific table, referred to as table X, had its IDs assigned in a different order than expected. For instance, the row that had ID 1 in the previous database now had ID 26. This table is referenced in six other tables, five of which correctly referenced the new IDs, while one table continued to use the IDs from the previous database, leading to discrepancies in row references.
Prior to the upgrade, a migration had been executed to add a new auto-incrementing primary key to table X, which also updated the six related tables to reference the new ID. However, the addition of an AUTO_INCREMENT column to a replicated table can cause the rows to receive different IDs on the source and replica. According to MySQL documentation, the order of ID assignment can vary based on the storage engine and the processing order of rows.
MySQL replication records changes made on the source in a binary log, which is then used by replicas to reproduce transactions. The recording and application of these changes depend on the binary log format, which can be configured in three ways. In this case, the source database had the binlog_format set to MIXED. Consequently, the five tables referenced the correct new IDs because their update statements were replicated using STATEMENT mode, which executed the exact UPDATE statement on the replica. However, for the remaining table, MySQL opted to use ROW mode, which directly applied the resulting row changes from the source to the replica. This led to the x_id values generated on the source being copied to the replica, resulting in incorrect row references due to differing IDs.
The only notable difference between the problematic table and the others was the presence of the AUTO_INCREMENT column. MySQL considers certain cases involving AUTO_INCREMENT unsafe for statement-based replication, leading to the use of ROW mode under MIXED. This situation highlights the potential risks associated with MySQL replicas, as unexpected issues can arise that may be difficult to detect but could result in significant problems in a production environment.