Bug #5017 LAST_INSERT_ID called from within Stored Procedures

• Submitted: 12 Aug 2004 12:51
• Modified: 12 Oct 2004 15:57
• Reporter: Naresh Sadhnani
• Status: Not a Bug
• Category: MySQL Server: User-defined functions ( UDF )
• Severity: S3 (Non-critical)
• Version: 5.0.0-alpha
• OS: Linux (Linux / FreeBSD)
• Assigned to: Per-Erik Martin
• CPU Architecture: Any

[12 Aug 2004 12:51] Naresh Sadhnani

Description:
The LAST_INSERT_ID does not work correctly when called from within a stored procedure.

How to repeat:
CREATE TABLE `temp` 
(
  `ID` int(11) NOT NULL auto_increment,
  `Name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`ID`)
);

CREATE PROCEDURE `test`(strName varchar(50),out intID int)
begin
insert into temp (Name) values (strName);
set @intID = LAST_INSERT_ID();
end

call test('Badger', @A)

select @A

-> @A is not the expected value

[12 Aug 2004 14:39] Naresh Sadhnani

This is found on the FreeBSD as well not only on RedHat 9.0

[13 Aug 2004 23:13] Matthew Lord

I verified this using 5.0.1-alpha on x86 linux (2.4.21 #12 SMP).

@A is NULL rather than 1.

CREATE TABLE `temp`
(
  `ID` int(11) NOT NULL auto_increment,
  `Name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`ID`)
);

\d |

CREATE PROCEDURE `test`(strName varchar(50),out intID int)
begin
insert into temp (Name) values (strName);
set @intID = LAST_INSERT_ID();
end

\d ;

call test('Badger', @A);

select @A;

[12 Oct 2004 14:02] [ name withheld ]

Even
SELECT @test = LAST_INSERT_ID();
fails.

However
SELECT @test := LAST_INSERT_ID();
works.

Spot the difference. 

Also
SET @test = 1;
works.

I wonder if this is the intended syntax or a bug.

[12 Oct 2004 15:57] Per-Erik Martin

There's an error in the test case.

set @intID = LAST_INSERT_ID();

sets the (global) user variable intID, not the local parameter. It should be:

set intID = LAST_INSERT_ID();

[25 Nov 2004 3:25] Gregg H

I am running under win2000... and seem to have the following problem related to the one described here....

C:\MYSQL\BIN>mysql p2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 5.0.0-alpha-max-debug

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

mysql> delimiter //
mysql>
mysql> cREATE TABLE `temp` ( `ID` int(11) NOT NULL auto_increment, `Name` varch
r(50) NOT NULL default '', PRIMARY KEY (`ID`) )//
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> drop procedure insertproc//
ERROR 1289 (42000): PROCEDURE insertproc does not exist
mysql> create procedure insertproc(in s char (20), out param1 int)
    -> begin
    -> insert into temp (name) values (s);
    -> set param1 :=   last_insert_id() ;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call insertproc("xx",@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 0    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql>
mysql> call insertproc("xx1",@b);
Query OK, 0 rows affected (0.00 sec)

mysql> select @b;
+------+
| @b   |
+------+
| 0    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql>