Bug #119381 Contribution by Tencent: Duplicate auto-increment values happened with partitioned tables and uk conflict

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;
+    }
   }

Thank you for the contribution!
Bug is verified as described.