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)
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)