Bug #20392 INSERT_ID session variable has weird value

• Submitted: 12 Jun 2006 12:16
• Modified: 23 Jul 2006 4:09
• Reporter: Guilhem Bichot
• Status: Closed
• 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.)