Description:
We have experienced a replication crash due to a bug in the way ALTER TABLE handles the addition of an auto_increment column to an existing table.
While in 5.6 ALTER TABLE... ADD COLUMN X INT NOT NULL AUTO_INCREMENT.... successfully enumerates all rows without gaps, in 5.7 many gaps are created, therefore such statement, issued on a 5.6 master, will break a 5.7 slave because the master will use auto_increment numbers that are already being used (incorrectly) on the slave.
Even without bringing replication in the mix, adding an auto_increment column to an existing table should not create any gaps, and does not in 5.6.
In order to trigger this behaviour, the row size needs to be somewhat large, 350 bytes or more - weird enough, it will not happen if the row size is small.
How to repeat:
Create the following table:
CREATE TABLE `ai_test_case` (
`a` bigint(20) unsigned NOT NULL,
`blobbo` char(255) DEFAULT '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012',
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Fill the table with 50,000 rows using a small script, here's the one that I used:
for i in $(seq 1 1 50000)
do
echo "INSERT INTO ai_test_case (a) VALUES($i);"
done | mysql -u rpizzi -ANr -p rick
Execute the following ALTER statement:
alter table ai_test_case drop primary key, add id int not null auto_increment primary key first;
verify with the following:
select max(id) from ai_test_case;
You will notice that at least a dozen gaps appear in the auto_increment enumeration.
Something odd that I noticed is, the gaps are all multiples of a value around 7,400, tested with different tables and always the same....
Description: We have experienced a replication crash due to a bug in the way ALTER TABLE handles the addition of an auto_increment column to an existing table. While in 5.6 ALTER TABLE... ADD COLUMN X INT NOT NULL AUTO_INCREMENT.... successfully enumerates all rows without gaps, in 5.7 many gaps are created, therefore such statement, issued on a 5.6 master, will break a 5.7 slave because the master will use auto_increment numbers that are already being used (incorrectly) on the slave. Even without bringing replication in the mix, adding an auto_increment column to an existing table should not create any gaps, and does not in 5.6. In order to trigger this behaviour, the row size needs to be somewhat large, 350 bytes or more - weird enough, it will not happen if the row size is small. How to repeat: Create the following table: CREATE TABLE `ai_test_case` ( `a` bigint(20) unsigned NOT NULL, `blobbo` char(255) DEFAULT '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012', PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Fill the table with 50,000 rows using a small script, here's the one that I used: for i in $(seq 1 1 50000) do echo "INSERT INTO ai_test_case (a) VALUES($i);" done | mysql -u rpizzi -ANr -p rick Execute the following ALTER statement: alter table ai_test_case drop primary key, add id int not null auto_increment primary key first; verify with the following: select max(id) from ai_test_case; You will notice that at least a dozen gaps appear in the auto_increment enumeration. Something odd that I noticed is, the gaps are all multiples of a value around 7,400, tested with different tables and always the same....