Bug #115136 Skip value when add an auto_increment column
Submitted: 27 May 9:01 Modified: 29 May 2:05
Reporter: Huaxiong Song (OCA) Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[27 May 9:01] Huaxiong Song
When add an auto_increment column, some records are skipped.

How to repeat:
Run mtr with the test-case:

# Test case:
create table a(a int, b int, primary key(a));

delimiter $$;
CREATE PROCEDURE insert_into_tables(IN num INTEGER)
     declare x INT;
     set x = 1;
     while x < num do
        INSERT INTO `a` (`a`, `b`) VALUES (x, x);
        set x = x + 1;
     end  while;
delimiter ;$$

call insert_into_tables(10000);

alter table a add column `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, ADD KEY (`id`);

select max(a), max(b), max(id) from a;

drop table a;
drop PROCEDURE insert_into_tables;

In my test, the result is:
select max(a), max(b), max(id) from a;
max(a)  max(b)  max(id)
9999    9999    10000

I think max(id) should be equal to 9999. And I try to run test case with different values of innodb_autoinc_lock_mode(0, 1, 2), there is no change occurred.
[27 May 10:07] MySQL Verification Team
HI Mr. Song,

Thank you for your bug report.

However, this is not a bug.

Autoincrement keys start from 1, so with ten thousand tuples, its largest value is 10.000.

If you take a look at your table and read our Reference Manual, you will see that a result is corret.

Not a bug.
[27 May 10:34] Huaxiong Song
Please look at the definition of the stored procedure. I only have 9999 pieces of data, but the IDs from 1 to 10000 are 10000 pieces of data, right?
In fact, we can simply find that a certain piece of data has been skipped. For example, in the test on my server, id = 9533 was skipped. Through this SQL, we can see the following results:
MySQL [test]> select * from a;
| a    | b    | id    |
| 9532 | 9532 | 9532  |
| 9533 | 9533 | 9534  |
| 9534 | 9534 | 9535  |
| 9535 | 9535 | 9536  |
| 9999 | 9999 | 10000 |
9999 rows in set (0.01 sec)
[27 May 10:59] MySQL Verification Team
Hi Mr. Song,

You are quite right.

However, this is how InnoDB works with auto-increment keys.

It reserves a number of values in advance, before insertion starts.

During insertion, it can skip certain values. 

This is described in our Reference Manual.
[27 May 11:06] Huaxiong Song
Hi, this is what I see:

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement are consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.
[28 May 2:31] Huaxiong Song
Sorry.... which Reference Manual should I refer to?
There seems to be no mention in the Reference Manual that data skips will occur when innodb_autoinc_lock_mode=0. 

Or can you tell me where to introduce it? From my current reading, data reservation and gap should only occur when innodb_autoinc_lock_mode = [1|2].
[28 May 10:36] MySQL Verification Team
Thank you , Mr. Song,

We shall file a Documentation bug for the missing info in the Manual.
[29 May 2:05] Huaxiong Song
Hi, I think your answer can be further discussed...

First, I want to know if gaps are still possible under innodb_autoinc_lock_mode = 0 (just DDL as described in the test case, no deletion, no update, no rollback), yes or no?

Second, from the code of parallel DDL, the multi-threaded processing of auto-increment values ​​is protected by mutex, and they can only increase sequentially (but gaps actually occur).
dberr_t Context::handle_autoinc(const dtuple_t *dtuple) noexcept {


  auto value = m_sequence++;


Finally, let's increase the amount of data and execute the test case. It can be found that the intervals of the gap values ​​are fixed. I think these gap values ​​should have increased sequentially. In other words, they are not skipped randomly.
call insert_into_tables(100000);
select b from a where b not in (select id from a);
[29 May 10:24] MySQL Verification Team
Hi Mr. Song,

A documentation bug has been filed.

We have added your last comments to it, so we shall see what will happen.