Bug #21726 Incorrect result with multiple invocations of LAST_INSERT_ID

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

Bug #21808 was marked as a duplicate of this one. 5.0.24, Windows.

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.

This bug may have/be a duplicate at Bug #21616.

There is duplicate bug #22327

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.

I'm the second reviewer here, after Rafal. Setting a ETA.

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

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

Pushed to 5.0.

Pushed to 5.0.26, that is.

For the record: the patch was approved over email, phone, irc. Me, Rafal and Guilhem all looked at the final patch.

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.

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.

Noted in 5.0.26.

The value of LAST_INSERT_ID() was not always updated correctly within
stored routines.

Setting back to 'In progress' for 4.1.

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.

Bug still present in 5.0.26.

Paul, could you please provide an example the reveals this bug in 5.0.26?

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;

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 */

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.

Peter, I have opened a new bug#23249 based on your input, thanks.

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.

Paul, that's great.  Thanks for letting us know.

Approved the version for 4.1

4.1 version queued to 4.1-runtime and null-merged to 5.0-runtime and 5.1-runtime.

Bug#23932 relates to this bug.

Noted in 4.1.22 changelog.

The value of LAST_INSERT_ID() was not always updated correctly within
stored routines.

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