MySQL Bugs: #87926: Incorrect auto_increment ids issued when close to ULLONG

Bug #87926	Incorrect auto_increment ids issued when close to ULLONG_MAX
Submitted:	30 Sep 2017 23:02	Modified:	19 Mar 2022 12:54
Reporter:	Manuel Ung	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6/5.7/8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When auto_increment last inserted id is close to ULLONG_MAX and auto_increment_offset or auto_increment_increment is set, much lower ids are being inserted.

How to repeat:
SET auto_increment_increment = 300;
CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=innodb;
INSERT INTO t1 VALUES (18446744073709551613, 'a');
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (NULL, 'b');
SHOW CREATE TABLE t1;
SELECT * FROM t1;
1       b
18446744073709551613    a
DROP TABLE t1;

SET auto_increment_offset = 200;
CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=innodb;
INSERT INTO t1 VALUES (18446744073709551613, 'a');
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (NULL, 'b');
SHOW CREATE TABLE t1;
SELECT * FROM t1;
a       b
200     b
18446744073709551613    a

DROP TABLE t1;

Suggested fix:
The server should return an error in this case, instead of inserting at a lower value.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile  --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.21-log Source distribution 2017-SEP-06

Copyright (c) 2000, 2017, 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 5.7 > CREATE DATABASE p;
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > USE p
Database changed
mysql 5.7 > SET auto_increment_increment = 300;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 > INSERT INTO t1 VALUES (18446744073709551613, 'a');
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > SHOW CREATE TABLE t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `b` char(8) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql 5.7 > SHOW CREATE TABLE t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `b` char(8) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 > SELECT * FROM t1;
+----------------------+------+
| a                    | b    |
+----------------------+------+
|                    1 | b    |
| 18446744073709551613 | a    |
+----------------------+------+
2 rows in set (0.00 sec)

mysql 5.7 > DROP TABLE t1;
Query OK, 0 rows affected (0.02 sec)

mysql 5.7 > SET auto_increment_increment = 300;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql 5.7 > INSERT INTO t1 VALUES (18446744073709551613, 'a');
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > SHOW CREATE TABLE t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `b` char(8) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 > INSERT INTO t1 VALUES (NULL, 'b');
ERROR 1264 (22003): Out of range value for column 't1' at row 167
mysql 5.7 >

Probably a new document issue to be added related to Innodb table restrictions as:

https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

"When an AUTO_INCREMENT integer column runs out of values, a subsequent INSERT operation returns a duplicate-key error. This is general MySQL behavior."

Thank you for the bug report.

I don't think it's just a documentation issue. The bug report is that values are added, even though an error should be returned.

I don't think you used exactly my repro steps, the second set command is
SET auto_increment_offset = 200;

I've also included the result set from the two SELECTs in my repro steps, and if you look at them, you'll notice that (1, 'b') was inserted for the first table and (200, 'b') was inserted for the second. The insertions should not have happened.

Posted by developer:
 
Fixed as of the upcoming 5.7.38, 8.0.29 release,:

Incorrect AUTO_INCREMENT values were generated when the maximum integer
column value was exceeded. The error was due to the maximum column value
not being considered. The previous valid AUTO_INCREMENT value should have
been returned in this case, causing a duplicate key error.