Bug #47888 LAST_INSERT_ID() not working properly within a stored procedure

• Submitted: 7 Oct 2009 9:28
• Modified: 7 Oct 2009 9:52
• Reporter: Tom L
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.0.24
• OS: Windows (Server 2003)
• CPU Architecture: Any
• Tags: last_insert_id(), stored procedure

[7 Oct 2009 9:28] Tom L

Description:
When using last_insert_id() within a stored procedure only returns the id of the first record inserted in that procedure. Calling it between successful inserts does not change the return value.

How to repeat:
Create a stored procedure like this:

begin 

declare id1 int(11); 
declare id2 int(11); 
declare id3 int(11); 

DROP TABLE IF EXISTS `example`;

CREATE TEMPORARY TABLE IF NOT EXISTS `example` (`MainId` int(11) NOT NULL auto_increment, `Text` text NOT NULL,  PRIMARY KEY  (`MainID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO example (`Text`) VALUES ("Example 1"); 

set id1 = last_insert_id(); 

INSERT INTO example (`Text`) VALUES ("Example 2"); 

set id2 = last_insert_id(); 

INSERT INTO example (`Text`) VALUES ("Example 3"); 

set id3 = last_insert_id(); 

select id1;
select id2;
select id3;

end 

id1, id2 and id3 all return the same value which is 1. They should return 1, 2 and 3.

[7 Oct 2009 9:38] Sveta Smirnova

Thank you for the report.

I can not repeat described behavior. Please upgrade to current version 5.0.86.

[7 Oct 2009 9:52] Tom L

I have tested on our production database which is version 5.0.50 and the issue is not there. The bug is pre 5.0.50 it seems.