Description:
When you set value of identity variable and then switch again and again, you can see that it looses its value after some switching of connections.
How to repeat:
CREATE TABLE t1
(
id INT NOT NULL auto_increment,
PRIMARY KEY (id),
name VARCHAR(30)
) ENGINE = INNODB;
CREATE TABLE t2
(
id INT NOT NULL auto_increment,
PRIMARY KEY (id),
name VARCHAR(30)
) ENGINE = INNODB;
## Creating and connecting with new connection test_con1 ##
connect (test_con1, localhost, root,,);
connection test_con1;
SET @@autocommit = 0;
## Inserting rows in table t1 ##
INSERT into t1(name) values('Record_1');
INSERT into t1(name) values('Record_2');
INSERT into t1(name) values('Record_3');
## Verifying total values in t1 ##
SELECT @@identity from t1;
@@identity
3
3
3
## Now inserting some data in table t2 ##
INSERT into t2(name) values('Record_1');
## Verifying total values in t2 ##
SELECT @@identity from t2;
@@identity
1
## Creating and connecting with new connection test_con2 ##
connect (test_con2, localhost, root,,);
connection test_con2;
SELECT * from t1;
id name
## Verifying total values in t1 ##
SELECT @@identity from t1;
@@identity
## Verifying total values in t2 ##
SELECT @@identity from t2;
@@identity
## Inserting some more records in table t1 ##
INSERT into t1(name) values('Record_1_1');
INSERT into t1(name) values('Record_1_2');
## Verifying total values in t1 ##
SELECT @@identity from t1;
@@identity
5
5
## Inserting row in table t2 ##
INSERT into t2(name) values('Record_1_3');
## Verifying total values in t2 ##
SELECT @@identity from t2;
@@identity
2
## Switching to connection test_con1 ##
connection test_con1;
## Commiting rows added in test_con1 ##
COMMIT;
## Verifying records in both tables ##
SELECT * from t1;
id name
1 Record_1
2 Record_2
3 Record_3
4 Record_1_1
5 Record_1_2
SELECT * from t2;
id name
1 Record_1
2 Record_1_3
## Verifying total values in t1 after commiting data ##
SELECT @@identity from t1;
@@identity
1
1
1
1
1
## Verifying total values in t2 after commiting data ##
SELECT @@identity from t2;
@@identity
1
1
'Bug: Identity variable lost its value on switching connection';
Suggested fix:
On committing values this variable resets its value to 1.
Description: When you set value of identity variable and then switch again and again, you can see that it looses its value after some switching of connections. How to repeat: CREATE TABLE t1 ( id INT NOT NULL auto_increment, PRIMARY KEY (id), name VARCHAR(30) ) ENGINE = INNODB; CREATE TABLE t2 ( id INT NOT NULL auto_increment, PRIMARY KEY (id), name VARCHAR(30) ) ENGINE = INNODB; ## Creating and connecting with new connection test_con1 ## connect (test_con1, localhost, root,,); connection test_con1; SET @@autocommit = 0; ## Inserting rows in table t1 ## INSERT into t1(name) values('Record_1'); INSERT into t1(name) values('Record_2'); INSERT into t1(name) values('Record_3'); ## Verifying total values in t1 ## SELECT @@identity from t1; @@identity 3 3 3 ## Now inserting some data in table t2 ## INSERT into t2(name) values('Record_1'); ## Verifying total values in t2 ## SELECT @@identity from t2; @@identity 1 ## Creating and connecting with new connection test_con2 ## connect (test_con2, localhost, root,,); connection test_con2; SELECT * from t1; id name ## Verifying total values in t1 ## SELECT @@identity from t1; @@identity ## Verifying total values in t2 ## SELECT @@identity from t2; @@identity ## Inserting some more records in table t1 ## INSERT into t1(name) values('Record_1_1'); INSERT into t1(name) values('Record_1_2'); ## Verifying total values in t1 ## SELECT @@identity from t1; @@identity 5 5 ## Inserting row in table t2 ## INSERT into t2(name) values('Record_1_3'); ## Verifying total values in t2 ## SELECT @@identity from t2; @@identity 2 ## Switching to connection test_con1 ## connection test_con1; ## Commiting rows added in test_con1 ## COMMIT; ## Verifying records in both tables ## SELECT * from t1; id name 1 Record_1 2 Record_2 3 Record_3 4 Record_1_1 5 Record_1_2 SELECT * from t2; id name 1 Record_1 2 Record_1_3 ## Verifying total values in t1 after commiting data ## SELECT @@identity from t1; @@identity 1 1 1 1 1 ## Verifying total values in t2 after commiting data ## SELECT @@identity from t2; @@identity 1 1 'Bug: Identity variable lost its value on switching connection'; Suggested fix: On committing values this variable resets its value to 1.