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.