Bug #21726 Incorrect result with multiple invocations of LAST_INSERT_ID
Submitted: 18 Aug 2006 19:57 Modified: 13 Nov 2006 18:28
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.25-bk,4.1 OS:
Assigned to: Tomash Brechko CPU Architecture:Any

[18 Aug 2006 19:57] Dean Ellis
Description:
Multiple invocations of LAST_INSERT_ID() always return the initial result of the function within a stored procedure.

How to repeat:
CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY );
CREATE TABLE t2 ( a INT );
DELIMITER //
CREATE PROCEDURE p1 ()
BEGIN
 INSERT INTO t1 VALUES (NULL);
 INSERT INTO t2 VALUES (LAST_INSERT_ID());
 INSERT INTO t1 VALUES (NULL);
 INSERT INTO t2 VALUES (LAST_INSERT_ID());
 INSERT INTO t1 VALUES (NULL);
 INSERT INTO t2 VALUES (LAST_INSERT_ID());
END//
DELIMITER ;
CALL p1();
SELECT * FROM t1;
SELECT * FROM t2;

Currently produces output of:

mysql> SELECT * FROM t1;
+---+
| a |
+---+
| 1 | 
| 2 | 
| 3 | 
+---+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+
| a    |
+------+
|    1 | 
|    1 | 
|    1 | 
+------+
3 rows in set (0.00 sec)

Suggested fix:
n/a
[31 Aug 2006 5:32] Valeriy Kravchuk
Bug #21808 was marked as a duplicate of this one. 5.0.24, Windows.
[31 Aug 2006 8:01] Guilhem Bichot
It will be interesting to check if this bug also exists in 5.1: I changed some things in LAST_INSERT_ID() in 5.1 in July.
[5 Sep 2006 19:48] Vance Rodriguez
This bug may have/be a duplicate at Bug #21616.
[14 Sep 2006 19:44] Sveta Smirnova
There is duplicate bug #22327
[22 Sep 2006 12:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12394

ChangeSet@1.2264, 2006-09-22 16:33:20+04:00, kroki@moonlight.intranet +10 -0
  BUG#21726: Incorrect result with multiple invocations of LAST_INSERT_ID
  
  Non-upper-level INSERTs (the ones in the body of stored procedure,
  stored function, or trigger) into a table that have AUTO_INCREMENT
  column didn't affected the result of LAST_INSERT_ID() on this level.
  
  The problem was introduced with the fix of bug 6880, where current
  insert_id value that was remembered on the first call to LAST_INSERT_ID()
  was returned from that function until it was reset before the next
  _upper-level_ statement.  The very procedure of remembering current
  insert_id had another defects that made the fix for bug 6880 only a
  partial solution.
  
  The fix for bug#21726 employs the following protocol:
  
   - remember insert_id value after the statement execution (which is at
     that point equals to the first insert_id value generated by the
     statement or the result of its explicit setting).
  
   - on statements "LAST_INSERT_ID(expr)", "SET LAST_INSERT_ID= expr", and
     on binary log command "LAST_INSERT_ID= constant" update remembered
     insert_id value.
  
  Thus, the value returned by LAST_INSERT_ID() is not affected by values
  generated by current statement, but affected by its explicit update.
  
  This fix is for 5.0 only, as in 5.1 WL 3146 solves the same problems in
  a more generic way.  This patch tries to produce the same results as 5.1.
[27 Sep 2006 16:14] Konstantin Osipov
I'm the second reviewer here, after Rafal. Setting a ETA.
[28 Sep 2006 19:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12762

ChangeSet@1.2289, 2006-09-28 23:04:11+04:00, kroki@moonlight.intranet +13 -0
  BUG#21726: Incorrect result with multiple invocations of LAST_INSERT_ID
  
  Non-upper-level INSERTs (the ones in the body of stored procedure,
  stored function, or trigger) into a table that have AUTO_INCREMENT
  column didn't affected the result of LAST_INSERT_ID() on this level.
  
  The problem was introduced with the fix of bug 6880, which in turn was
  introduced with the fix of bug 3117, where current insert_id value was
  remembered on the first call to LAST_INSERT_ID() (bug 3117) and was
  returned from that function until it was reset before the next
  _upper-level_ statement (bug 6880).
  
  The fix for bug#21726 brings back the behaviour of version 4.0, and
  implements the following: remember insert_id value in
  Item_func_last_insert_id::fix_fields() (which at that point equals to
  the first insert_id value generated by the previous statement), and
  return that remembered value from THD::insert_id().
  
  Thus, the value returned by LAST_INSERT_ID() is not affected by values
  generated by current statement, nor by LAST_INSERT_ID(expr) calls in
  this statement.
  
  Version 5.1 does not have this bug (it was fixed by WL 3146).
[2 Oct 2006 10:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12930

ChangeSet@1.2293, 2006-10-02 14:28:23+04:00, kroki@moonlight.intranet +14 -0
  BUG#21726: Incorrect result with multiple invocations of LAST_INSERT_ID
  
  Non-upper-level INSERTs (the ones in the body of stored procedure,
  stored function, or trigger) into a table that have AUTO_INCREMENT
  column didn't affected the result of LAST_INSERT_ID() on this level.
  
  The problem was introduced with the fix of bug 6880, which in turn was
  introduced with the fix of bug 3117, where current insert_id value was
  remembered on the first call to LAST_INSERT_ID() (bug 3117) and was
  returned from that function until it was reset before the next
  _upper-level_ statement (bug 6880).
  
  The fix for bug#21726 brings back the behaviour of version 4.0, and
  implements the following: remember insert_id value at the beginning
  of the statement or expression (which at that point equals to
  the first insert_id value generated by the previous statement), and
  return that remembered value from LAST_INSERT_ID() or @@LAST_INSERT_ID.
  
  Thus, the value returned by LAST_INSERT_ID() is not affected by values
  generated by current statement, nor by LAST_INSERT_ID(expr) calls in
  this statement.
  
  Version 5.1 does not have this bug (it was fixed by WL 3146).
[2 Oct 2006 13:45] Tomash Brechko
Pushed to 5.0.
[2 Oct 2006 13:50] Tomash Brechko
Pushed to 5.0.26, that is.
[2 Oct 2006 15:04] Konstantin Osipov
For the record: the patch was approved over email, phone, irc. Me, Rafal and Guilhem all looked at the final patch.
[3 Oct 2006 9:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12990

ChangeSet@1.2294, 2006-10-03 13:38:16+04:00, kroki@moonlight.intranet +9 -0
  Fix for the patch for bug#21726: Incorrect result with multiple
  invocations of LAST_INSERT_ID.
  
  Reding of LAST_INSERT_ID inside stored function wasn't noted by caller,
  and no LAST_INSERT_ID_EVENT was issued for binary log.
  
  The solution is to add THD::last_insert_id_used_bin_log, which is much
  like THD::last_insert_id_used, but is reset only for upper-level
  statements.  This new variable is used to issue LAST_INSERT_ID_EVENT.
[3 Oct 2006 20:12] Russ Brown
Is there a workaround that I can use to get around this problem while I wait for an official release which contains the fix?

The only one I can think of is to wrap the inserts into individual functions, but that seems really messy and over the top...

Thanks.
[4 Oct 2006 19:12] Paul Dubois
Noted in 5.0.26.

The value of LAST_INSERT_ID() was not always updated correctly within
stored routines.
[5 Oct 2006 9:54] Tomash Brechko
Setting back to 'In progress' for 4.1.
[6 Oct 2006 9:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13222

ChangeSet@1.2554, 2006-10-06 13:34:07+04:00, kroki@moonlight.intranet +13 -0
  BUG#21726: Incorrect result with multiple invocations of LAST_INSERT_ID.
  
  Note: bug#21726 does not directly apply to 4.1, as it doesn't have stored
  procedures.  However, 4.1 had some bugs that were fixed in 5.0 by the
  patch for bug#21726, and this patch is a backport of those fixes.
  Namely, in 4.1 it fixes:
  
    - LAST_INSERT_ID(expr) didn't return value of expr (4.1 specific).
  
    - LAST_INSERT_ID() could return the value generated by current
      statement if the call happens after the generation, like in
  
        CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
        INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
  
    - Redundant binary log LAST_INSERT_ID_EVENTs could be generated.
[11 Oct 2006 12:20] Paul van Rossem
Bug still present in 5.0.26.
[11 Oct 2006 12:28] Tomash Brechko
Paul, could you please provide an example the reveals this bug in 5.0.26?
[12 Oct 2006 15:46] Peter Andrews
This bug is stil present in 5.1.11 beta windows.

Here is a sample that returns increasing values for last_insert_id() despite the table remaining with only one row:

DROP TABLE IF EXISTS `d`;

CREATE TABLE `d` (
 `k` int(11) NOT NULL auto_increment,
 `a` int(11) default NULL,
 `c` int(11) default NULL,
 PRIMARY KEY  (`k`),
 UNIQUE KEY `idx_1` (`a`)
) ENGINE=InnoDB;
insert into d values(null,4,2);
insert into d values(null,5,2),(null,4,2);
select last_insert_id();
select * from d;

insert into d values(null,5,2),(null,4,2);
select last_insert_id();
select * from d;

insert into d values(null,5,2),(null,4,2);
select last_insert_id();
select * from d;

insert into d values(null,5,2),(null,4,2);
select last_insert_id();
select * from d;

insert into d values(null,5,2),(null,4,2);
select last_insert_id();
select * from d;
[13 Oct 2006 7:22] Paul van Rossem
Example where this still fails in 5.0.26:

CREATE TABLE T3
  (T3ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE T1
  (T1ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   T3Ref INTEGER,
   FOREIGN KEY (T3Ref) REFERENCES T3(T3ID) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE T2
  (T2ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
   T1Ref INTEGER NOT NULL,
   FOREIGN KEY (T1Ref) REFERENCES T1(T1ID) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO T3(T3ID) VALUES (123);
INSERT INTO T1(T1ID, T3Ref) VALUES (33, 123);
INSERT INTO T2(T2ID, T1Ref) VALUES (222, 33);

CREATE PROCEDURE T1_COPY (srcT1 INTEGER)
BEGIN
  DECLARE dstT1 INTEGER
  DECLARE srcT3 INTEGER; DECLARE dstT3 INTEGER;
  DECLARE srcT2 INTEGER; DECLARE dstT2 INTEGER;
  DECLARE done SMALLINT DEFAULT 0;
  DECLARE t2Cursor CURSOR FOR SELECT T2ID FROM T2 WHERE T1Ref = srcT1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  SELECT T3Ref INTO srcT3 FROM T1 WHERE T1ID = srcT1;
  CALL T3_COPY(srcT3, dstT3);
  INSERT INTO T1(T3Ref) VALUES (dstT3);
  SET dstT1 = LAST_INSERT_ID();  /* this sets the value of the last T3 insert,              
                                     not of the T1 insert! */
  OPEN t2Cursor;
  REPEAT
    FETCH t2Cursor INTO srcT2;
    IF NOT done THEN 
       CALL T2_COPY(srcT2, dstT1, dstT2); /* fails because of invalid dstT1 */
    END IF;
  UNTIL done END REPEAT;
END $

CREATE PROCEDURE T2_COPY (srcT2 INTEGER, dstT1 INTEGER, OUT newT2 INTEGER)
BEGIN
  DECLARE srcT1 INTEGER;
  SELECT T1Ref INTO srcT1 FROM T2 WHERE T2ID = srcT2;
  INSERT INTO T2(T1Ref) VALUES (dstT1);
  SET newT2 = LAST_INSERT_ID();
END $

CREATE PROCEDURE T3_COPY (srcT3 INTEGER, OUT newT3 INTEGER) 
BEGIN
  INSERT INTO T3() VALUES ();
  SET newT3 = LAST_INSERT_ID();
END $

CALL T1_COPY(33, result);  /* fails for resons mentioned above */
[13 Oct 2006 7:59] Tomash Brechko
Paul, after running your test case I get the following correct results:

SELECT * FROM T1;
#+------+-------+
#| T1ID | T3Ref |
#+------+-------+
#|   33 |   123 |
#|   34 |   124 | <= '34' was generated by INSERT INTO T1(T3Ref) VALUES (dstT3);
#+------+-------+    and it was assigned to dstT1.

SELECT * FROM T2;
#+------+-------+
#| T2ID | T1Ref |
#+------+-------+
#|  222 |    33 |
#|  223 |    34 | <= '34' was inserted by INSERT INTO T2(T1Ref) VALUES (dstT1);
#+------+-------+    which is correct.

SELECT * FROM T3;
#+------+
#| T3ID |
#+------+
#|  123 |
#|  124 |
#+------+

If you results differ from the above, please paste them, and also the output of "SELECT VERSION();".  If you have the same results, then I guess everything is correct.
[13 Oct 2006 9:08] Tomash Brechko
Peter, I have opened a new bug#23249 based on your input, thanks.
[13 Oct 2006 11:24] Paul van Rossem
Tomash,
I re-downloaded and re-installed windows server 5.0.26, and now I get the correct results. So the problem must have been with my server installation. Owe you an apology. The problem appears solved with 5.0.26.
Regards, Paul.
[13 Oct 2006 11:32] Tomash Brechko
Paul, that's great.  Thanks for letting us know.
[16 Oct 2006 16:47] Konstantin Osipov
Approved the version for 4.1
[17 Oct 2006 10:01] Tomash Brechko
4.1 version queued to 4.1-runtime and null-merged to 5.0-runtime and 5.1-runtime.
[3 Nov 2006 10:54] Tomash Brechko
Bug#23932 relates to this bug.
[11 Nov 2006 3:27] Paul Dubois
Noted in 4.1.22 changelog.

The value of LAST_INSERT_ID() was not always updated correctly within
stored routines.
[13 Nov 2006 18:28] Paul Dubois
Corrected the 4.1.21 changelog entry:

Redundant binary log LAST_INSERT_ID events could be generated;
LAST_INSERT_ID(expr) didn't return value of expr; LAST_INSERT_ID()
could return the value generated by the current statement if the call
happens after value generation, as in:

CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());