MySQL Bugs: #21812: LAST_INSERT_ID not updated after insert inside a stored function

Bug #21812	LAST_INSERT_ID not updated after insert inside a stored function
Submitted:	24 Aug 2006 14:17	Modified:	28 Sep 2006 16:56
Reporter:	Guido Pott	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.26-BK, 5.0.24-community-nt	OS:	Linux (Linux, XP)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	LAST_INSERT_ID, stored function

Description:
Select LAST_INSERT_ID() does not work if called after a Stored Function which was used to insert a record. On the other hand it works if you use a Store Procedure for inserting the records.

How to repeat:
CREATE DATABASE IF NOT EXISTS TEST;
USE TEST;
#create a table for inserts
DROP TABLE IF EXISTS `TestTable`;
CREATE TABLE `TestTable` (               
       `ID` int(11) NOT NULL auto_increment,  
       `Name` varchar(15) default NULL,       
        PRIMARY KEY  (`ID`)                    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;   

DROP FUNCTION IF EXISTS `insertTestFunc`;
DELIMITER //
CREATE FUNCTION `insertTestFunc`() returns int(11)
   BEGIN
        INSERT INTO `TestTable` (Name) values ('Foo1');        
        return LAST_INSERT_ID();
   END;
//
DELIMITER ; 

DROP PROCEDURE IF EXISTS `insertTestProc`;
DELIMITER //
CREATE PROCEDURE `insertTestProc`()
   BEGIN
        INSERT INTO `TestTable` (Name) values ('Foo2');        
   END;
//
DELIMITER ;   

#Lets test
# with Function...
SELECT insertTestFunc();   # OK
SELECT LAST_INSERT_ID();   # NOT OK !!!

# with Procedure...
CALL insertTestProc();
SELECT LAST_INSERT_ID(); #OK

# with INSERT Statement...
INSERT INTO `TestTable` (Name) values ('Foo3');
SELECT LAST_INSERT_ID(); #OK

SELECT * FROM TestTable;
SELECT Version();

Suggested fix:
Server side

Bug #22335 was marked as a duplicate of this one.

Verified just as described with 5.0.26-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.26-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `TestTable`;
CRQuery OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `TestTable` (
    ->        `ID` int(11) NOT NULL auto_increment,
    ->        `Name` varchar(15) default NULL,
    ->         PRIMARY KEY  (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.15 sec)

mysql> DROP FUNCTION IF EXISTS `insertTestFunc`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //
mysql> CREATE FUNCTION `insertTestFunc`() returns int(11)
    ->    BEGIN
    ->         INSERT INTO `TestTable` (Name) values ('Foo1');
    ->         return LAST_INSERT_ID();
    ->    END;
    -> //
Query OK, 0 rows affected (0.04 sec)

mysql> DROP PROCEDURE IF EXISTS `insertTestProc`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `insertTestProc`()
    ->    BEGIN
    ->         INSERT INTO `TestTable` (Name) values ('Foo2');
    ->    END;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> SELECT insertTestFunc();
+------------------+
| insertTestFunc() |
+------------------+
|                1 |
+------------------+
1 row in set (0.03 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> CALL insertTestProc();
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO `TestTable` (Name) values ('Foo3');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.01 sec)

mysql> select * from TestTable;
+----+------+
| ID | Name |
+----+------+
|  1 | Foo1 |
|  2 | Foo2 |
|  3 | Foo3 |
+----+------+
3 rows in set (0.01 sec)

This works as designed and needs to be documented:

From: Konstantin Osipov 
To: docs
Subject: Re: 12.9.3. Information Functions: LAST_INSERT_ID() and BUG#15728

* Jon Stephens [06/04/30 02:08]:
> > Sorry for misleading comment.  Indeed, context is saved for functions
> > and triggers, but not for procedures.  This is my fault, I have known
> > that at some moment, but eventually forgot, and was too lazy to
> > recheck.
> If I had a dollar for every time I've done something like that, I'd be
> able to retire. :)

> > I'm not ready to tell you why procedures are treated differently in
> > this regard.  I will discuss it with someone who knows---perhaps he
> > will be able to provide a "why".  I will let you know of the outcome,
> > but this probably won't happen before May, 2nd.
>
> That's fine, just let me know what you find out, and we'll go from there.

For stored functions and triggers we push the current execution
environment, such as last_insert_id and row_count and execute a
trigger/function in a new environment. After execution, the old
environment is restored.
This is done to ensure that in an insert or an update with a
stored function or trigger the returned values of affected rows or
last_insert_id are from the statement itself, not from the last
executed trigger or function within that statement.

The change was coded by Monty in August 2005.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

See description of LAST_INSERT_ID() at:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html