MySQL Bugs: #114299: Unexpected behavior of auto_increment

Bug #114299	Unexpected behavior of auto_increment_offset
Submitted:	11 Mar 2024 13:09	Modified:	11 Mar 2024 14:34
Reporter:	Jingqi Tian (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto increment

Description:
According to official documentation, when the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. But in fact, when the value of auto_increment_offset is greater than that of auto_increment_increment, the behavior is unpredictable.

How to repeat:
set auto_increment_increment = 5850;

set auto_increment_offset = 6000;

create table t1 (id int primary key auto_increment);

insert into t1 values (null);

select * from t1;

The result should be:
1

However, the result is:
134

Suggested fix:
In storage/innobase/handler/ha_innodb.cc:get_auto_increment() function:

ulonglong prev_auto_inc = autoinc;
autoinc = ((autoinc - 1) + increment - offset) / increment;
autoinc = autoinc * increment + offset;

The data type of increment and offset is ulonglong. When offset is greater than (autoinc - 1) + increment, the numeric underflow.

Hi Mr. Tian,

Thank you for your bug report.

However, we were not able to repeat your test case.

Here are the results that we got with our latest 8.0.36 and 8.3.0 binaries:

mysql> SET SESSION  auto_increment_offset=25;
Query OK, 0 rows affected (0.00 sec)

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

mysql>
mysql> CREATE TABLE autoinc2 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM autoinc2;
+-----+
| col |
+-----+
|   4 |
|   9 |
|  14 |
|  19 |
+-----+
4 rows in set (0.00 sec)

As you can see auto_increment_offset was ignored .......

Can't repeat.

Hi Mr. Tian,

With larger values, offset at 6000 and increment at 5000,  we managed to repeat the behaviour that you reported:

SELECT * FROM autoinc2;
+-------+
| col   |
+-------+
|  6000 |
| 11000 |
| 16000 |
| 21000 |
+-------+
4 rows in set (0.00 sec)

This is now a verified bug.

It would be a Documentation bug, but this inconsistency between small and large values make it a bug in the code.

Thank you ......

Please try to repeat it following my test case. This bug can only be reproduced under specific auto_increment_increment and auto_increment_offset values

Hi Mr. Tian,

You missed our last remark and the fact that we repeated the behaviour with larger values.

You also missed that this report is now a verified bug.

Thank you for your reply.

But I don't think this is a documentation error. When auto_increment_increment is greater than auto_increment_offset, the result is completely unpredictable. 

In addition, when I used gdb to debug, I found that a numerical underflow occurred when executing "autoinc = ((autoinc - 1) + increment - offset) / increment;", which should not be a behavior by design.