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: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[27 May 2024 9:01]
Huaxiong Song
[27 May 2024 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 2024 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 2024 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 2024 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 2024 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 2024 10:36]
MySQL Verification Team
Thank you , Mr. Song, We shall file a Documentation bug for the missing info in the Manual.
[29 May 2024 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 { ... 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 ```
[29 May 2024 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.
[25 Sep 2024 11:50]
Huaxiong Song
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!!!
[25 Sep 2024 11:58]
MySQL Verification Team
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 ........
[25 Sep 2024 12:01]
Huaxiong Song
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; ```
[25 Sep 2024 12:03]
MySQL Verification Team
Thank you for your bug report. This is a verified bug now.
[26 Sep 2024 2:35]
Huaxiong Song
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.
[26 Sep 2024 8:33]
MySQL Verification Team
Hi Mr. Song, Thank you for your contribution.