| Bug #6466 | @@LAST_INSERT_ID out-dated in stored procedure | ||
|---|---|---|---|
| Submitted: | 5 Nov 2004 15:52 | Modified: | 6 Nov 2004 15:52 |
| Reporter: | Michael Kofler | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.2 compiled Nov. 04 2004 | OS: | Linux (linux) |
| Assigned to: | CPU Architecture: | Any | |
[6 Nov 2004 15:52]
Alexander Keremidarski
5.0 compiled from ChangeSet@1.1631, 2004-11-05 22:49:15-08:00, brian@avenger.(none) always returns 3 equal values as expected
[6 Nov 2004 16:10]
Michael Kofler
CREATE PROCEDURE `test`.`test_last_insert_id`() BEGIN DROP TABLE IF EXISTS _test; CREATE TABLE _test (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ENGINE = MyISAM; INSERT INTO _test (b) VALUES (123); SELECT LAST_INSERT_ID(), @@last_insert_id, @@identity; INSERT INTO _test (b) VALUES (123); SELECT LAST_INSERT_ID(), @@last_insert_id, @@identity; END --- mysql> call test_last_insert_id(); +------------------+------------------+------------+ | LAST_INSERT_ID() | @@last_insert_id | @@identity | +------------------+------------------+------------+ | 1 | 1 | 1 | +------------------+------------------+------------+ 1 row in set (0,13 sec) +------------------+------------------+------------+ | LAST_INSERT_ID() | @@last_insert_id | @@identity | +------------------+------------------+------------+ | 2 | 1 | 1 | +------------------+------------------+------------+ 1 row in set (0,14 sec) Query OK, 0 rows affected (0,14 sec)
Description: To begin with: LAST_INSERT_ID() works for me. I read all those other last_insert_id-bugs ... However, @@LAST_INSERT_ID is wrong (contains previous instead of current LAST_INSERT_ID). The same applies also to @@IDENTITY. How to repeat: mytable is a table with an auto_increment id column within a SP: INSERT INTO mytable (data) VALUES ('test'); SELECT LAST_INSERT_ID(), @@LAST_INSERT_ID, @@IDENTITY; returns two different values (first one is correct, second and third are off by one in my tests, but perhaps are even more off with more connections open and simultanous inserts)