Bug #89098 Adding an auto_increment column to existing table creates gaps
Submitted: 3 Jan 15:07 Modified: 4 Jan 14:13
Reporter: Riccardo Pizzi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[3 Jan 15:07] Riccardo Pizzi
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....
[3 Jan 15:45] Riccardo Pizzi
forgot to mention, we use default innodb_autoinc_lock_mode (1)
[4 Jan 14:13] Miguel Solorzano
Thank you for the bug report. Verified as described.