Bug #21808 LAST_INSERT_ID() returns wrong id
Submitted: 24 Aug 2006 11:46 Modified: 31 Aug 2006 5:31
Reporter: Andrey Kazachkov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.24 OS:Microsoft Windows (Windows)
Assigned to:

[24 Aug 2006 11:46] Andrey Kazachkov
Description:
After inserting a row into a table that references another table LAST_INSERT_ID() returns wrong id. 

How to repeat:
DELIMITER GO

use mysql
GO

DROP DATABASE IF EXISTS `my_test_db`
GO

CREATE DATABASE `my_test_db` /*!40100 DEFAULT CHARACTER SET ascii */
GO

USE my_test_db
GO

DROP TABLE IF EXISTS t_b
GO

DROP TABLE IF EXISTS t_a
GO

CREATE TABLE t_a(
	nId int PRIMARY KEY auto_increment,
	wstrName VARCHAR(256) UNICODE
)
GO

INSERT INTO t_a(wstrName) VALUES('str1')
GO

INSERT INTO t_a(wstrName) VALUES('str2')
GO

INSERT INTO t_a(wstrName) VALUES('str3')
GO

CREATE TABLE t_b(
	nId int  NOT NULL auto_increment,
	wstrName VARCHAR(256) UNICODE,
	nVal int,
  PRIMARY KEY  (`nId`),
  CONSTRAINT `tb_ta_nId` FOREIGN KEY (`nVal`) REFERENCES `t_a` (`nId`)
)
GO

DROP PROCEDURE IF EXISTS `xxx`
GO

CREATE PROCEDURE `xxx` ()
BEGIN
	DECLARE nA int;
	DECLARE nB int;
	INSERT INTO t_a(wstrName) VALUES('y');
	SET nA = LAST_INSERT_ID();
	INSERT INTO t_b(wstrName, nVal) VALUES('x', nA);
	SET nB = LAST_INSERT_ID();
	SELECT nA "Last inserted into t_a", nB "Last inserted into t_b";
	SELECT nId "t_b.nId", wstrName "t_b.wstrName" FROM t_b;
END
GO

CALL xxx()
GO

+------------------------+------------------------+
| Last inserted into t_a | Last inserted into t_b |
+------------------------+------------------------+
|                      4 |                      4 |
+------------------------+------------------------+
1 row in set (0.05 sec)

+---------+--------------+
| t_b.nId | t_b.wstrName |
+---------+--------------+
|       1 | x            |
+---------+--------------+
1 row in set (0.06 sec)

Query OK, 0 rows affected (0.06 sec)
[31 Aug 2006 5:31] Valerii Kravchuk
Duplicate of bug #21726, already verified S1 bug.