Bug #20392 INSERT_ID session variable has weird value
Submitted: 12 Jun 2006 12:16 Modified: 23 Jul 2006 4:09
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0 OS:Linux (linux)
Assigned to: Alexander Barkov CPU Architecture:Any

[12 Jun 2006 12: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 14:15] Alexander Barkov
A patch has been commited:
http://lists.mysql.com/commits/8075
[22 Jun 2006 14:16] Alexander Barkov
Thanks to Guilhem for suggesting a fix.
[22 Jun 2006 14:24] Guilhem Bichot
comments sent by email
[22 Jun 2006 14:45] Alexander Barkov
Additional fix:
http://lists.mysql.com/commits/8080
(better test coverage, as Guilhem suggested)
[23 Jul 2006 4: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.)