Bug #20392 INSERT_ID session variable has weird value
Submitted: 12 Jun 2006 14:16 Modified: 23 Jul 2006 6:09
Reporter: Guilhem Bichot
Status: Closed
Category:Server: General Severity:S3 (Non-critical)
Version:5.0 OS:Linux (linux)
Assigned to: Alexander Barkov Target Version:

[12 Jun 2006 14:16] Guilhem Bichot 
Description:
didn't test with 5.0.
SELECT @@INSERT_ID gives value which has nothing to do with what was SET.

How to repeat:
After a fresh start of the MySQL server:

MASTER> set session insert_id=20;
Query OK, 0 rows affected (0.00 sec)

MASTER> select @@session.insert_id;
+---------------------+
| @@session.insert_id |
+---------------------+
|                   0 | !!!!!!!!!!!
+---------------------+
1 row in set (0.00 sec)

See: we set INSERT_ID and don' t see the set value in the SELECT (so what is the point of
having a selectabled @@INSERT_ID ?).

"For fun", if after that we SET LAST_INSERT_ID we observe this:

MASTER> set session last_insert_id=100;
Query OK, 0 rows affected (0.00 sec)

MASTER> select @@session.insert_id;
+---------------------+
| @@session.insert_id |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

MASTER> select @@session.last_insert_id;
+--------------------------+
| @@session.last_insert_id |
+--------------------------+
|                      100 | 
+--------------------------+
1 row in set (0.01 sec)

MASTER> select @@session.insert_id;
+---------------------+
| @@session.insert_id |
+---------------------+
|                 100 | !!!!!!!!!!!!!!!!!!!!!!
+---------------------+
1 row in set (0.00 sec)

Selecting @@LAST_INSERT_ID changed the value of @@INSERT_ID.
From looking at the code, this behaviour is understandable, but the code sounds wrong.
[22 Jun 2006 16:15] Alexander Barkov 
A patch has been commited:
http://lists.mysql.com/commits/8075
[22 Jun 2006 16:16] Alexander Barkov 
Thanks to Guilhem for suggesting a fix.
[22 Jun 2006 16:24] Guilhem Bichot 
comments sent by email
[22 Jun 2006 16:45] Alexander Barkov 
Additional fix:
http://lists.mysql.com/commits/8080
(better test coverage, as Guilhem suggested)
[23 Jul 2006 6:07] Paul DuBois 
Noted in 5.0.24 changelog.

SELECT @@INSERT_ID displayed a value unrelated to a preceding SET INSERT_ID.
(It was returning LAST_INSERT_ID instead.)