Bug #23315 last_insert_id() does not work
Submitted: 16 Oct 2006 1:05 Modified: 4 Jan 2007 12:49
Reporter: Ghazi Sarhan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.2.4 beta OS:Microsoft Windows (WindowsXP)
Assigned to: Mike Lischke

[16 Oct 2006 1:05] Ghazi Sarhan
Description:
After inserting a record the last_insert_id() does not work in MySQL Query Browser, from MySQL server's command line it works.

How to repeat:
From MySQL Query Browser:
use test;

CREATE TABLE t1 (
  `id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
  PRIMARY KEY(`id`)
) ENGINE = InnoDB;

insert into t1 (id) values (null);

select last_insert_id();

Returned value will be ZERO.

From MySQL server's command line:

mysql> use test;
Database changed
mysql> CREATE TABLE t1 (
    ->   `id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY(`id`)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 (id) values (null);
Query OK, 1 row affected (0.03 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)
[16 Oct 2006 6:08] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 1.2.4 rc, and inform about the results.
[16 Oct 2006 8:44] Ghazi Sarhan
The latest version I found on the website was 1.2.4 beta, I tried it and I got the same problem. I found the following in the server log, this may explane this problem:

061016 11:34:02	     56 Connect     root@localhost on test
		     56 Query       SET SESSION interactive_timeout=1000000
		     56 Query       SELECT @@sql_mode
		     56 Query       SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
		     56 Query       SET NAMES utf8
		     56 Query       SELECT @@SQL_MODE
		     56 Query       insert into t1 values(null)
061016 11:34:03	     56 Quit       
		     57 Connect     root@localhost on test
		     57 Query       SET SESSION interactive_timeout=1000000
		     57 Query       SELECT @@sql_mode
		     57 Query       SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
		     57 Query       SET NAMES utf8
		     57 Query       SELECT @@SQL_MODE
		     57 Query       select last_insert_id()
		     57 Quit       

It seems that MySQL Query Browser creates a new connection for each query command then drops the connection, which will make the last_insert_id() the first query command in a connection, which will be zero.
[16 Oct 2006 8:59] Valeriy Kravchuk
Verified just as described.
[4 Jan 2007 12:49] Mike Lischke
Fixing this requires some larger changes in the code we can't do right now. We'll later come back to this issue.
[12 Dec 2009 16:08] Valeriy Kravchuk
Works as expected in MySQL Workbench 5.1.10. Please, check it.
[16 Feb 2012 19:08] Ralf Steffler
It works,
if you run it in a procedure !
Same applies to ODBC Applications.
[13 Mar 2014 13:35] Omer Barnir
This bug is not scheduled to be fixed at this time.