Bug #114299 Unexpected behavior of auto_increment_offset
Submitted: 11 Mar 13:09 Modified: 11 Mar 14:34
Reporter: Jingqi Tian 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

[11 Mar 13:09] Jingqi Tian
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.
[11 Mar 14:11] MySQL Verification Team
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.
[11 Mar 14:23] MySQL Verification Team
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 ......
[11 Mar 14:24] Jingqi Tian
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
[11 Mar 14:27] MySQL Verification Team
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.
[11 Mar 14:34] Jingqi Tian
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.