MySQL Bugs: #110942: There is a discontinuity in the value of the AUTO

Bug #110942	There is a discontinuity in the value of the AUTO_INCREMENT field
Submitted:	6 May 2023 4:30	Modified:	8 May 2023 13:31
Reporter:	yong jing	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.26-log ,8.0.33	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There is a discontinuity in the value of the AUTO_INCREMENT field after execute insert into table select from table statement

How to repeat:
Here are the tests for 8.0.33:
[root@sjbf tmp]# mysql 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use jy
Database changed

Create table t1 that has an AUTO INCREMENT column c1
mysql> CREATE TABLE t1 
    -> (
    ->   c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   c2 CHAR(1)
    -> ) ENGINE = INNODB;
Query OK, 0 rows affected (0.75 sec)

insert 100 rows records to t1

mysql> insert into t1(c2) select 'x' from information_schema.tables limit 100;
Query OK, 100 rows affected (0.16 sec)
Records: 100  Duplicates: 0  Warnings: 0

delete 99 rows records from t1, records with c1=100 are kept
mysql> delete from t1 where c1<100;
Query OK, 99 rows affected (0.13 sec)

mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 | x    |
+-----+------+
1 row in set (0.00 sec)

execute show create table t1 command to view auto_increment's value is:128 not is:101 that auto_increment'value is discontinuity
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

execute insert into t1 values statement to insert 1 row record,t1.c1's value is:128 not is:101
mysql> INSERT INTO t1 (c1,c2) VALUES (NULL,'b');
Query OK, 1 row affected (0.07 sec)

mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 | x    |
| 128 | b    |
+-----+------+
2 rows in set (0.00 sec)

execute show create table t1 command to view auto_increment's value is:129 
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

execute truncate statement to clear t1's data
mysql> truncate table t1;
Query OK, 0 rows affected (0.63 sec)

execute show create table t1 command to view auto_increment's value
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

execute insert into t1 values(100,'x') statement to insert 1 row record that t1.c1=100
mysql> insert into t1 values(100,'x');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 | x    |
+-----+------+
1 row in set (0.00 sec)

execute show create table t1 command to view auto_increment's value is:101 
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

execute insert into t1 values(null,'b') statement to insert 1 row record 
mysql> INSERT INTO t1 (c1,c2) VALUES (NULL,'b');
Query OK, 1 row affected (0.11 sec)

view t1's records that c1's max value is 101
mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 | x    |
| 101 | b    |
+-----+------+
2 rows in set (0.00 sec)

execute show create table t1 command to view auto_increment's value is:102,that auto_increments's values is  continuity
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Here's the 5.7.26-log version with the same results as 8.0.33 version
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use jy
Database changed

mysql> CREATE TABLE t1 
    -> (
    ->   c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   c2 CHAR(1)
    -> ) ENGINE = INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(c2) select 'x' from information_schema.tables limit 100;
Query OK, 100 rows affected (0.01 sec)
Records: 100  Duplicates: 0  Warnings: 0

mysql> delete from t1 where c1<100;
Query OK, 99 rows affected (0.01 sec)

mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 | x    |
+-----+------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (c1,c2) VALUES (NULL,'b')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 | x    |
| 128 | b    |
+-----+------+
2 rows in set (0.00 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.63 sec)

mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(100,'x');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 | x    |
+-----+------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (c1,c2) VALUES (NULL,'b');
Query OK, 1 row affected (0.11 sec)

mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 | x    |
| 101 | b    |
+-----+------+
2 rows in set (0.00 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Hi Mr. jing,

Thank you for your bug report.

However, it is not a bug. That is how InnoDB is designed to work since 2002. That is also the manner in which it will continue to function in the future.

This is all explained in our Reference Manual, in chapters on the auto-inc columns and in the chapters on the InnoDB storage engine.

Not a bug.