MySQL Bugs: #115136: Skip value when add an auto

Bug #115136	Skip value when add an auto_increment column
Submitted:	27 May 2024 9:01	Modified:	25 Sep 2024 12:03
Reporter:	Huaxiong Song (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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)
     BEGIN
     declare x INT;
     set x = 1;
     while x < num do
        INSERT INTO `a` (`a`, `b`) VALUES (x, x);
        set x = x + 1;
     end  while;
end$$
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.

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.

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)

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.

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.

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].

Thank you , Mr. Song,

We shall file a Documentation bug for the missing info in the Manual.

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 {
  ...

  mutex_enter(&m_autoinc_mutex);

  auto value = m_sequence++;

  mutex_exit(&m_autoinc_mutex);
  ...
}
```

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);
b
7150
14300
21450
28600
35750
42900
50050
57200
64350
71500
78650
85800
92950
```

Hi Mr. Song,

A documentation bug has been filed.

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

I have further analyzed and tested this issue and I am convinced that this is a bug. This question has nothing to do with 'this is how InnoDB works with auto-increment keys'.

The following is a supplement to the original test case:
After increasing the size of dd_buffer, we can find that the skip value disappears.
# test case

set @ori_val = @@innodb_ddl_buffer_size;

create table a(a int, b int, primary key(a));

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

call insert_into_tables(10000);

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

# Max(id) is bigger than max(a) and max(b)
select max(a), max(b), max(id) from a;

# Mofidy ddl buffer size
set session innodb_ddl_buffer_size = 104857600;

alter table a drop column id, drop index id;

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

# Max(id) is same with max(a) and max(b)
select max(a), max(b), max(id) from a;

drop table a;
drop PROCEDURE insert_into_tables;
set session innodb_ddl_buffer_size = @ori_val;

For the first select, the result is:
select max(a), max(b), max(id) from a;
max(a)  max(b)  max(id)
9999    9999    10000

For the second select, the result is:
select max(a), max(b), max(id) from a;
max(a)  max(b)  max(id)
9999    9999    9999

========================================
Okay, the above is the recurrence process, and we can know that this has nothing to do with the so-called innodb processing of auto-increment.
So what exactly causes this? Here is my analysis：

From the code of parallel DDL build, we can see:
1. Build row first: call ddl::Row::build
dberr_t Row::build(ddl::Context &ctx, dict_index_t *index, mem_heap_t *heap,
                   size_t type) noexcept {
  ...

  auto &fts = ctx.m_fts;

  if (fts.m_doc_id != nullptr && fts.m_doc_id->is_generated()) {
    fts.m_doc_id->increment();
  }

  if (ctx.m_add_autoinc != ULINT_UNDEFINED) {
    auto err = ctx.handle_autoinc(m_ptr);

    if (err != DB_SUCCESS) {
      return err;
    }
  }

  return DB_SUCCESS;
}

Here, auto_inc will be handled.

2. After that, we will do `bulk_inserter`, the stack is:
bulk_inserter
-->add_row
---->bulk_add_row
`
3. In the function of `bulk_add_row`, if the key_buffer is full, `ddl::Cursor::copy_row` will be called, and `ddl::Row::build` will be called again, so autoinc is skipped!!!

Hi Mr. Song,

Can you please make a full and a complete test case, so that we can test it properly ????

Simply, make a full test case with all new additions .........

Thanks in advance ........

Please, I have provided a complete test case in my reply just now. . .

```
set @ori_val = select @@innodb_ddl_buffer_size;
create table a(a int, b int, primary key(a));

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

call insert_into_tables(10000);

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

# Max(id) is bigger than max(a) and max(b)
select max(a), max(b), max(id) from a;

# Mofidy ddl buffer size
set session innodb_ddl_buffer_size = 104857600;

alter table a drop column id, drop index id;

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

# Max(id) is same with max(a) and max(b)
select max(a), max(b), max(id) from a;

drop table a;
drop PROCEDURE insert_into_tables;
set session innodb_ddl_buffer_size = @ori_val;
```

Thank you for your bug report.

This is a verified bug now.

Tiny fix of bug 115136 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug115136.patch (application/octet-stream, text), 3.66 KiB.

Hi Mr. Song,

Thank you for your contribution.