DATABASES Signal 157
MySQL upgrade's new AUTO_INCREMENT column yields mismatched IDs on replica, corrupting one table's references
↗
Illustration only Photo by Albert Stoynov on Unsplash
A MySQL upgrade that added an AUTO_INCREMENT primary key to a replicated table produced different row IDs on the replica, causing one of six related tables to reference wrong rows.
Engineers upgrading MySQL replicas need to know that adding an AUTO_INCREMENT column via ALTER TABLE can assign different IDs on source and replica, depending on storage engine and row processing order. Under MIXED binlog format, some updates replicate as statements and others as row changes, so a single table can end up with incorrect foreign key values. This bug is easy to miss and can corrupt data in production.
Written by elseif from the cluster below · every claim links back to a sourceThe three things worth knowing
Adding an AUTO_INCREMENT column to a replicated table can result in different row IDs on source and replica.
With MIXED binlog format, some UPDATE statements replicate in STATEMENT mode and others in ROW mode, causing inconsistent references.
The table with an AUTO_INCREMENT column was the one that got wrong IDs, leading to incorrect references in one related table.
THE CLUSTER