MySQL Bugs: #77542: Autoincrement variables Have different Behavior than the document

Bug #77542	Autoincrement variables Have different Behavior than the document
Submitted:	29 Jun 2015 13:41	Modified:	10 Jul 2015 12:49
Reporter:	Justin Biebber	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment_increment, auto_increment_offset

Description:
The auto_increment_increment and auto_increment_offset parameter has different behavior than the document description. In http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html, it says:
--------------------------------------
If either of these variables is changed, and then new rows inserted into a table containing an AUTO_INCREMENT column, the results may seem counterintuitive because 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

where N is a positive integer value in the series [1, 2, 3, ...]. For example:

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
+-----+
4 rows in set (0.00 sec)

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

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
|  35 |
|  45 |
|  55 |
|  65 |
+-----+
8 rows in set (0.00 sec)
The values shown for auto_increment_increment and auto_increment_offset generate the series 5 + N × 10, that is, [5, 15, 25, 35, 45, ...]. The highest value present in the col column prior to the INSERT is 31, and the next available value in the AUTO_INCREMENT series is 35, so the inserted values for col begin at that point and the results are as shown for the SELECT query.
----------------------------------------------------

But the actual testing result is the:
mysql> select * from t1;
+----+
| a  |
+----+
|  1 |
| 11 |
| 21 |
| 31 |
| 45 |
| 55 |
| 65 |
| 75 |
+----+
8 rows in set (0.00 sec)

Note that the least value that is greater than 31 is not 35, but it is 45. Why?

How to repeat:
mysql> create table t1 (a int not null auto_increment primary key);
Query OK, 0 rows affected (0.68 sec)

mysql> set auto_increment_increment = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (null), (null), (null), (null);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+
| a  |
+----+
|  1 |
| 11 |
| 21 |
| 31 |
+----+
4 rows in set (0.00 sec)

mysql> set auto_increment_offset = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (null), (null), (null), (null);
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+
| a  |
+----+
|  1 |
| 11 |
| 21 |
| 31 |
| 45 |
| 55 |
| 65 |
| 75 |
+----+
8 rows in set (0.00 sec)

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.00 sec)

Suggested fix:
Fixed the document by giving the correct logic to calculate the next value of autoincrement column

I think that our documentation covers the effects of the change in those values good enough.

Relevant chapter in our manual is the 5.1.4 (Server-wise system variables), where you can find sufficient description, among others:

"
Should one or both of these variables be changed and then new rows inserted into a table containing
an AUTO_INCREMENT column, the results may seem counterintuitive because 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. In other words, the series is calculated like so:
"

I can't agree with you. Just look at the words here: 

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(5) + N × auto_increment_increment(10)
According to the equation: The calculated numbers are : 5, 15, 25, 35, 45, 55.
The existing numbers are: 1, 11, 21, 31.
Now, with your normal logic, just tell me, which is the LEAST number that is greater than 31? 35 or 45? According to the equation, it should be 35. But unfortunately, my test result is 45. That's why I ask you to fix the document.
It's really wrong!

You are reporting that values that come, AFTER one of those variables are changed, are not logical.

The text from our manual that I quoted is on the point. There is no logic in the next value after one of those variables are changed.