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.