Bug #35362 Auto_Increment_Increment looses its value on switching connections & values
Submitted: 18 Mar 2008 6:08 Modified: 20 Mar 2008 21:10
Reporter: Salman Rawala Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.1.22, 5.0, 5.1, 6.0 BK OS:Any (MS Windows, Linux)
Assigned to: CPU Architecture:Any
Tags: auto_increment_increment

[18 Mar 2008 6:08] Salman Rawala
Description:
On changing value of auto_increment_increment in different connections and inserting records in table, the variable starts behaving abnormally and increments some random index numbers.

How to repeat:
CREATE TABLE t1
(
id INT NOT NULL auto_increment,
PRIMARY KEY (id),
name VARCHAR(30)
);

--echo ## Setting initial value of auto_increment_increment to 5 ##
SET @@auto_increment_increment = 5;

--echo ## Inserting first record in table to check behavior of the variable ##
INSERT into t1(name) values('Record_1');	
SELECT * from t1;

+----+----------+
| id | name     |
+----+----------+
|  1 | Record_1 |
+----+----------+

--echo ## Changing value of variable to 10 ##
SET @@global.auto_increment_increment = 10;

--echo ## Inserting record and verifying value of column id ##
INSERT into t1(name) values('Record_2');	
SELECT * from t1;

+----+----------+
| id | name     |
+----+----------+
|  1 | Record_1 |
|  6 | Record_2 |
+----+----------+

--echo ## Test behavior of variable after assigning some larger value to it ##
SET @@auto_increment_increment = 100;
INSERT into t1(name) values('Record_5');	
SELECT * from t1;

+-----+----------+
| id  | name     |
+-----+----------+
|   1 | Record_1 |
|   6 | Record_2 |
| 101 | Record_5 |
+-----+----------+

--echo ## Creating new connection test_con1 ##
CONNECT (test_con1,localhost,root,,);
CONNECTION test_con1;

--echo ## Setting global value of variable and inserting data in table ##
SET @@global.auto_increment_increment = 20;
INSERT into t1(name) values('Record_6');
SELECT * from t1;

+-----+----------+
| id  | name     |
+-----+----------+
|   1 | Record_1 |
|   6 | Record_2 |
| 101 | Record_5 |
| 111 | Record_6 |
+-----+----------+

--echo ## Setting session value of variable and inserting data in table ##
SET @@session.auto_increment_increment = 2;
INSERT into t1(name) values('Record_8');
INSERT into t1(name) values('Record_9');
SELECT * from t1;

+-----+----------+
| id  | name     |
+-----+----------+
|   1 | Record_1 |
|   6 | Record_2 |
| 101 | Record_5 |
| 111 | Record_6 |
| 113 | Record_8 |
| 115 | Record_9 |
+-----+----------+

--echo ## Creating another new connection test_con2 ##
CONNECT (test_con2,localhost,root,,);
connection test_con2;

--echo ## Setting value of session variable to 5 and verifying its behavior ##
+++ SET @@session.auto_increment_increment = 5; +++
+++ INSERT into t1(name) values('Record_10'); +++
+++ SELECT * from t1; +++

+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | Record_1  |
|   6 | Record_2  |
| 101 | Record_5  |
| 111 | Record_6  |
| 113 | Record_8  |
| 115 | Record_9  |
| 116 | Record_10 |
+-----+-----------+

--echo Bug : Here Record_10 id should be 120 instead of 115 because we have set the value of variable to 5

Suggested fix:
Record_10 id should be 120 instead of 115 because we have set the value of variable to 5.
[19 Mar 2008 9:25] Sveta Smirnova
Thank you for the report.

Verified as described.
[20 Mar 2008 21:10] Guilhem Bichot
Hello,
116 is expected, it's explained in much detail here:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#option_mysqld_auto-inc...
and especially starting from
"Should one or both of these variables be changed" etc.
You have auto_increment_increment=5 and auto_increment_offset=1, so the serie is 1 + 5 * N; the inserted value is the first member of this serie which is greater than the max value in the column (115): 116.