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.