Bug #78203 Inconsistent behaviors when auto_increment_offset is ignored(>auto..._increment)
Submitted: 25 Aug 2015 9:16 Modified: 26 Aug 2015 15:33
Reporter: Su Dylan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.22 OS:Any
Assigned to: CPU Architecture:Any

[25 Aug 2015 9:16] Su Dylan
Description:

Scenario #1 SQLs:
=================
drop table if exists t1; create table t1(a int primary key auto_increment);
SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 30;
insert into t1 values(0),(0);
select * from t1;

Scenario #1 output:
===================
mysql> drop table if exists t1; create table t1(a int primary key 
Query OK, 0 rows affected (0.01 sec)

insert into t1 values(0),(0);
Query OK, 0 rows affected (0.01 sec)

mysql> SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(0),(0);
selectQuery OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+---+
| a |
+---+
| 4 |
| 9 |
+---+
2 rows in set (0.00 sec)

Scenario #2 SQLs:
================
drop table if exists t1; create table t1(a int primary key auto_increment);
SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 988;
insert into t1 values(0),(0);
select * from t1;

Scenario #2 output:
===================
mysql> drop table if exists t1; create table t1(a int primary key auto_increment);
O_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 988;
iQuery OK, 0 rows affected (0.01 sec)

nsert into t1 values(0),(0);
Query OK, 0 rows affected (0.01 sec)

mysql> SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 988;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(0),(0);
elect * fromQuery OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+---+
| a |
+---+
| 2 |
| 7 |
+---+
2 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

Problems:
=========
As documented, when auto_increment_increment < auto_increment_offset, auto_increment_offset is ignored.
In the above two scenarios, since they have the same auto_increment_increment values and both the auto_increment_offset should be ignored, the same data are expected to be inserted.
However, they have different behaviors and different data rows are inserted.

How to repeat:

Scenario #1 SQLs:
=================
drop table if exists t1; create table t1(a int primary key auto_increment);
SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 30;
insert into t1 values(0),(0);
select * from t1;

Scenario #2 SQLs:
================
drop table if exists t1; create table t1(a int primary key auto_increment);
SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 988;
insert into t1 values(0),(0);
select * from t1;

Suggested fix:
The two scenarios have the same inserted data rows.
[26 Aug 2015 15:33] MySQL Verification Team
Hi!

As you yourself said, in cases , like the ones that you describe, the values that you have set for auto_increment_offset are ignored. This is well documented. 
However, that does not mean that calculations for the next auto-increment number are that simple. In the calculus of the first value of the auto_increment, auto_increment_offset is still used, but the final value is corrected if the case from the title is present. That means that auto_increment_offset is not set to zero.

This is a small detail that we did not consider important to document as such cases are very, very rare.