MySQL Bugs: #108229: Exists gaps when add a auto

Bug #108229	Exists gaps when add a auto_increment column
Submitted:	23 Aug 2022 3:41	Modified:	24 Aug 2022 1:45
Reporter:	Fengchun Hua	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.7.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have a table that contains less than 10K rows, I add a auto_increment column(bigint) as a primary key as the first column. I found there exists a gap in this column.

How do I find this? I have 4 standby mysqld server, and replicate stopped because of a dumplicate key(UK and PK mismatch), I found data inconsistent.

I tried to read rows and found pk=1200 are missing in master, and after this row, all rows between master and standby are differece of 1. PK=1200 is the gap that exists in master but not in all standbys.

I'm using RBR and innodb_autoinc_lock_mode=1.

Offical doc says that, there will no gaps in a single statement when innodb_autoinc_lock_mode=1.

How to repeat:
Have no idea.
Tried to repeat it for serval times.

Hi Mr. Hua,

Thank you for your bug report.

However, it is not a bug.

Let us quote our manual for 5.7:

"
 In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time.

"

Hence, if you load data with more then one statement in the dump file, then gaps are an expected behaviour.

Not a bug.

Thanks for reply. But I'm not loading data from a dump file, but altering table to add a aaotu_increment column. This alter table is a single statement, gaps should not exists. 

Will mysql splite `alter tabel add column` to multi-statement?

Hi Mr. Hua,

What you describe is quite possible. Particularly if the ALTER operation was set to INSTANT with no locks. As our Reference Manual clearly states, for auto-increment keys it is best to use COPY algorithm.