Bug #35375 Identity Variable looses its value on switching connection again and again
Submitted: 18 Mar 2008 7:25 Modified: 19 Mar 2008 7:13
Reporter: Salman Rawala Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.22 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: IDENTITY

[18 Mar 2008 7:25] Salman Rawala
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.
[19 Mar 2008 7:13] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

IDENTITY is session variable. In connection test_con1 its value was 1 after insert into table t2, so behavior is correct.