Description:
When a unique key conflict occurs during an INSERT SELECT operation on a
partitioned table with a unique key, it results in duplicate auto-increment
values.
How to repeat:
The method is being worked on version 8.0.43.
Apply the following patch to add debug_sync point:
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
--- a/sql/sql_union.cc (revision ee079e5ca930ce5ed0643326abadeed8cd41fffc)
+++ b/sql/sql_union.cc (revision c9fe4970b092d3a404d59e8e9777d94af47e89ab)
@@ -1797,6 +1797,7 @@
DBUG_EXECUTE_IF("simulate_partial_result_set_scenario",
my_error(ER_UNKNOWN_ERROR, MYF(0)););
+ DEBUG_SYNC_C("insert_select_after_insert_once");
}
Do as follow:
```
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`c` int NOT NULL,
d int NOT NULL,
PRIMARY KEY (`id`,`c`),
UNIQUE KEY `uk_d` (d, c)
)
PARTITION BY RANGE (`c`)
(PARTITION p0 VALUES LESS THAN (1000000000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (9000000000) ENGINE = InnoDB);
session1:
INSERT INTO t1(c,d) VALUES(1,21), (2,22), (3,23);
SET DEBUG_SYNC = 'insert_select_after_insert_once wait_for continue';
INSERT INTO t1(c,d) select tmp.* from (select 4, 24 from dual union select 3, 23 from dual) tmp;
session2:
INSERT INTO t1(c,d) VALUES(5,25), (6,26), (7,27);
SET DEBUG_SYNC = 'now signal continue';
sesssion1:
INSERT INTO t1(c,d) VALUES(8, 28), (9, 29), (10, 210);
select id, count(*) as c from t1 group by id having c >= 2 limit 10;
+----+---+
| id | c |
+----+---+
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
+----+---+
```
It can be observed that duplicate auto-increment keys have occurred.
Suggested fix:
Analyze:
========
MySQL's AUTO_INCREMENT mechanism has a rollback feature. For AUTO_INCREMENT values that have been reserved but not yet fully used, they are returned to the pool. During this process, Partition_share::next_auto_inc_valis assigned the value of handler::next_insert_id.
However, when an insertion error occurs (such as a unique key conflict), there is a logic to restore next_insert_id, where handler::next_insert_idis reassigned to prev_insert_id(which is the value of handler::next_insert_idsaved before the insertion). The issue arises because this restoration process occurs before the return of the unused AUTO_INCREMENT values.
This sequence causes some AUTO_INCREMENT values that have already been allocated to other threads to be incorrectly returned to the pool, ultimately leading to duplicate AUTO_INCREMENT values in subsequent insertions.
Based on the reproduction process described above, let's analyze step by step starting from the completion of the insertion of (1,21), (2,22), and (3,23):
1、initialization：Partition_share::next_auto_inc_val = 4
2、session1 insert (4,4,24)：
（1）set Partition_share::next_auto_inc_val：4 => 5
（2）set handler::next_insert_id：0 => 5
3、session2 insert (5,5,25), (6,6,26), (7,7,27)：
（1）set Partition_share::next_auto_inc_val：5 => 8
（2）set handler::next_insert_id：0 => 6 => 7 => 8
4、session1 insert (8,3,23)，unique key conflict happened
（1）set prev_insert_id = handler::next_insert_id（5）
（2）set Partition_share::next_auto_inc_val：8 => 10
（3）set handler::next_insert_id：5 => 9
（4）restore handler::next_insert_id = prev_insert_id：9 => 5
（5）release Partition_share::next_auto_inc_val = handler::next_insert_id：10 => 5
This resulted in the return of the already allocated auto-increment values [5,7], which led to duplicate auto-increment values when they were reused later.
Solution:
========
During restore_auto_increment, it is unsafe to rollback to prev_insert_id because other concurrent operations may occur between the two insertions. The only safe approach is to rollback to insert_id_for_cur_row.
Since this rollback strategy is more conservative, it will not affect the correctness of other processes.
Patch as follow:
diff --git a/sql/handler.h b/sql/handler.h
--- a/sql/handler.h (revision c9fe4970b092d3a404d59e8e9777d94af47e89ab)
+++ b/sql/handler.h (revision 16317388105354d5e59b106135f8db7b1d6dc31a)
@@ -5932,19 +5932,15 @@
DBUG_PRINT("info", ("auto_increment: next value %lu", (ulong)id));
next_insert_id = id;
}
- void restore_auto_increment(ulonglong prev_insert_id) {
+ void restore_auto_increment(ulonglong prev_insert_id [[maybe_unused]]) {
/*
Insertion of a row failed, re-use the lastly generated auto_increment
id, for the next row. This is achieved by resetting next_insert_id to
- what it was before the failed insertion (that old value is provided by
- the caller). If that value was 0, it was the first row of the INSERT;
- then if insert_id_for_cur_row contains 0 it means no id was generated
- for this first row, so no id was generated since the INSERT started, so
- we should set next_insert_id to 0; if insert_id_for_cur_row is not 0, it
- is the generated id of the first and failed row, so we use it.
+ insert_id_for_cur_row.
*/
- next_insert_id =
- (prev_insert_id > 0) ? prev_insert_id : insert_id_for_cur_row;
+ if (insert_id_for_cur_row) {
+ next_insert_id = insert_id_for_cur_row;
+ }
}
© 2025, Oracle Corporation and/or its affiliates
Description: When a unique key conflict occurs during an INSERT SELECT operation on a partitioned table with a unique key, it results in duplicate auto-increment values. How to repeat: The method is being worked on version 8.0.43. Apply the following patch to add debug_sync point: diff --git a/sql/sql_union.cc b/sql/sql_union.cc --- a/sql/sql_union.cc (revision ee079e5ca930ce5ed0643326abadeed8cd41fffc) +++ b/sql/sql_union.cc (revision c9fe4970b092d3a404d59e8e9777d94af47e89ab) @@ -1797,6 +1797,7 @@ DBUG_EXECUTE_IF("simulate_partial_result_set_scenario", my_error(ER_UNKNOWN_ERROR, MYF(0));); + DEBUG_SYNC_C("insert_select_after_insert_once"); } Do as follow: ``` CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `c` int NOT NULL, d int NOT NULL, PRIMARY KEY (`id`,`c`), UNIQUE KEY `uk_d` (d, c) ) PARTITION BY RANGE (`c`) (PARTITION p0 VALUES LESS THAN (1000000000) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (9000000000) ENGINE = InnoDB); session1: INSERT INTO t1(c,d) VALUES(1,21), (2,22), (3,23); SET DEBUG_SYNC = 'insert_select_after_insert_once wait_for continue'; INSERT INTO t1(c,d) select tmp.* from (select 4, 24 from dual union select 3, 23 from dual) tmp; session2: INSERT INTO t1(c,d) VALUES(5,25), (6,26), (7,27); SET DEBUG_SYNC = 'now signal continue'; sesssion1: INSERT INTO t1(c,d) VALUES(8, 28), (9, 29), (10, 210); select id, count(*) as c from t1 group by id having c >= 2 limit 10; +----+---+ | id | c | +----+---+ | 5 | 2 | | 6 | 2 | | 7 | 2 | +----+---+ ``` It can be observed that duplicate auto-increment keys have occurred. Suggested fix: Analyze: ======== MySQL's AUTO_INCREMENT mechanism has a rollback feature. For AUTO_INCREMENT values that have been reserved but not yet fully used, they are returned to the pool. During this process, Partition_share::next_auto_inc_valis assigned the value of handler::next_insert_id. However, when an insertion error occurs (such as a unique key conflict), there is a logic to restore next_insert_id, where handler::next_insert_idis reassigned to prev_insert_id(which is the value of handler::next_insert_idsaved before the insertion). The issue arises because this restoration process occurs before the return of the unused AUTO_INCREMENT values. This sequence causes some AUTO_INCREMENT values that have already been allocated to other threads to be incorrectly returned to the pool, ultimately leading to duplicate AUTO_INCREMENT values in subsequent insertions. Based on the reproduction process described above, let's analyze step by step starting from the completion of the insertion of (1,21), (2,22), and (3,23): 1、initialization：Partition_share::next_auto_inc_val = 4 2、session1 insert (4,4,24)： （1）set Partition_share::next_auto_inc_val：4 => 5 （2）set handler::next_insert_id：0 => 5 3、session2 insert (5,5,25), (6,6,26), (7,7,27)： （1）set Partition_share::next_auto_inc_val：5 => 8 （2）set handler::next_insert_id：0 => 6 => 7 => 8 4、session1 insert (8,3,23)，unique key conflict happened （1）set prev_insert_id = handler::next_insert_id（5） （2）set Partition_share::next_auto_inc_val：8 => 10 （3）set handler::next_insert_id：5 => 9 （4）restore handler::next_insert_id = prev_insert_id：9 => 5 （5）release Partition_share::next_auto_inc_val = handler::next_insert_id：10 => 5 This resulted in the return of the already allocated auto-increment values [5,7], which led to duplicate auto-increment values when they were reused later. Solution: ======== During restore_auto_increment, it is unsafe to rollback to prev_insert_id because other concurrent operations may occur between the two insertions. The only safe approach is to rollback to insert_id_for_cur_row. Since this rollback strategy is more conservative, it will not affect the correctness of other processes. Patch as follow: diff --git a/sql/handler.h b/sql/handler.h --- a/sql/handler.h (revision c9fe4970b092d3a404d59e8e9777d94af47e89ab) +++ b/sql/handler.h (revision 16317388105354d5e59b106135f8db7b1d6dc31a) @@ -5932,19 +5932,15 @@ DBUG_PRINT("info", ("auto_increment: next value %lu", (ulong)id)); next_insert_id = id; } - void restore_auto_increment(ulonglong prev_insert_id) { + void restore_auto_increment(ulonglong prev_insert_id [[maybe_unused]]) { /* Insertion of a row failed, re-use the lastly generated auto_increment id, for the next row. This is achieved by resetting next_insert_id to - what it was before the failed insertion (that old value is provided by - the caller). If that value was 0, it was the first row of the INSERT; - then if insert_id_for_cur_row contains 0 it means no id was generated - for this first row, so no id was generated since the INSERT started, so - we should set next_insert_id to 0; if insert_id_for_cur_row is not 0, it - is the generated id of the first and failed row, so we use it. + insert_id_for_cur_row. */ - next_insert_id = - (prev_insert_id > 0) ? prev_insert_id : insert_id_for_cur_row; + if (insert_id_for_cur_row) { + next_insert_id = insert_id_for_cur_row; + } }