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.
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.