Description:
Description of autoincrement behavior in regard to @@auto_increment_increment and @@auto_increment_offset (http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html#sysvar_auto_increme...) says:
"auto_increment_offset determines the starting point for the AUTO_INCREMENT column value."
"When the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored."
"...the series of AUTO_INCREMENT values is calculated without regard to any values already present in the column, and the next value inserted is the least value in the series that is greater than the maximum existing value in the AUTO_INCREMENT column. The series is calculated like this:
auto_increment_offset + N × auto_increment_increment"
The second quote, however, does not specify what exactly it means, the value is ignored. It cannot be ignored completely, since it determines the starting point of a sequence, and participates in the sequence formula.
Is it considered equal to 0? Or equal to 1? Or equal to the previous value? I can't find a confirmation to any of these theories, the behavior changes depending on factors unknown to me. See examples below.
In the example 1, the last insert apparently uses the offset value 600, even though it should be ignored, according to the manual.
In the example 2, I have no idea where the values come from.
# Example 1
# (works differently with MyISAM, but not well either)
#
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t1 (a int auto_increment primary key) engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)
mysql> set auto_increment_offset = 200, auto_increment_increment = 300;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (null),(null);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+-----+
| a |
+-----+
| 200 |
| 500 |
+-----+
2 rows in set (0.00 sec)
mysql> set auto_increment_offset = 600, auto_increment_increment = 60;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (null),(null);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
# If offset = 1, sequence is 1, 61, ... 541, 601, ...
# If offset = 0, sequence is 0, 60, ... 540, 600, ...
# If offset = 200, sequence is 200, 260, ... 520, 580, ...
# If offset = 600, sequence is 600, 660, ...
mysql> select * from t1;
+-----+
| a |
+-----+
| 200 |
| 500 |
| 600 |
| 660 |
+-----+
4 rows in set (0.00 sec)
# Example 2
# (works the same way with MyISAM)
#
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (a int auto_increment primary key) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> set auto_increment_offset = 600, auto_increment_increment = 50;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (null),(null);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+
| a |
+----+
| 34 |
| 84 |
+----+
2 rows in set (0.00 sec)
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.21 |
+-----------+
1 row in set (0.00 sec)
I'll leave it to you to decide whether code changes are required or not, since changing the behavior could break the whole universe of applications, but at the very list it should be clearly explained in the manual, to have a respectable reference when the question arises.
How to repeat:
# Example 1
drop table if exists t1;
create table t1 (a int auto_increment primary key) engine=InnoDB;
set auto_increment_offset = 200, auto_increment_increment = 300;
insert into t1 values (null),(null);
select * from t1;
set auto_increment_offset = 600, auto_increment_increment = 60;
insert into t1 values (null),(null);
select * from t1;
# Example 2
drop table if exists t1;
create table t1 (a int auto_increment primary key) engine=InnoDB;
set auto_increment_offset = 600, auto_increment_increment = 50;
insert into t1 values (null),(null);
select * from t1;
drop table t1;
