MySQL Bugs: #23249: LAST_INSERT_ID() is being changed when no insert occurred

Bug #23249	LAST_INSERT_ID() is being changed when no insert occurred
Submitted:	13 Oct 2006 9:07	Modified:	26 Oct 2006 3:23
Reporter:	Tomash Brechko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.27-BK, 5.0.26, 5.1.11	OS:	Linux (Linux)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
This bug was originally mentioned in a comment for bug#21726 by Peter Andrews.  For multi-insert, if first rows could be inserted, but then we have a row that violates unique constraint, the whole statement is rolled back, but insert id is nevertheless increased as if new generated values were inserted.

MyISAM does not have this bug, so I suspect this is InnoDB-specific.

How to repeat:
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);
#ERROR 1062 (23000): Duplicate entry '4' for key 2
select last_insert_id();
#+------------------+
#| last_insert_id() |
#+------------------+
#|                2 |
#+------------------+
select * from d;
#+---+------+------+
#| k | a    | c    |
#+---+------+------+
#| 1 |    4 |    2 |
#+---+------+------+

# If you repeat this block several times, you'll see that insert id is
# increased, while no rows inserted.
insert into d values(null,5,2),(null,4,2);
#ERROR 1062 (23000): Duplicate entry '4' for key 2
select last_insert_id();
#+------------------+
#| last_insert_id() |
#+------------------+
#|                3 |
#+------------------+
select * from d;
#+---+------+------+
#| k | a    | c    |
#+---+------+------+
#| 1 |    4 |    2 |
#+---+------+------+

insert into d values(null,5,2),(null,4,2);
#ERROR 1062 (23000): Duplicate entry '4' for key 2
insert into d values(null,5,2),(null,4,2);
#ERROR 1062 (23000): Duplicate entry '4' for key 2

# When we finally insert something successfully, we see the gap in a
# sequence.
insert into d values(null,100,100);
select * from d;
#+---+------+------+
#| k | a    | c    |
#+---+------+------+
#| 1 |    4 |    2 |
#| 6 |  100 |  100 |
#+---+------+------+

Thank you for a bug report. Verified just as described with 5.0.27-BK on Linux.

The reason for this behavior is that the rollback code is not aware of what auto-increment counters have been incremented in the statement.

I do not understand what does it mean that MyISAM does not have this 'bug', as MyISAM never does a roolback.

In the future, if we relax the bottleneck in the AUTO-INC locking of a table, then it is also theoretically impossible to reset the auto-inc counter to the original value since the value may have been incremented meanwhile by another connection. That is why I am putting this bug report to the 'Won't fix' state.

This bug is probably misnamed. I understand Heikki's point and, as she understood the bug, it is appropriate to not fix.

It seems to me the bigger issue is that LAST_INSERT_ID is being changed when no insert occurred. As far as the future issue of possible insertions on another connection, this should not be relevant to LAST_INSERT_ID since that is a session level variable.

Peter,

it is Mr. Heikki Tuuri :).

The problem with LAST_INSERT_ID maybe could be fixed by the MySQL interpreter resetting it if MySQL rolls back the SQL statement.

Regards,

Heikki

It is incorrect grammatically but I now see why people use the plural 'they' instead of the singular 'he/she'. Sorry, Heikki.

I am not sure where your comment leaves us -- this bug is now marked 'Won't Fix' but the changing of LAST_INSERT_ID() after a non-insertion is a real bug. Should I create a new bug with a more appropriate title and description or will you change the status of this one?

There appear to have been many bugs in and around this area (I noticed <a href=http://bugs.mysql.com/bug.php?id=21726">21726</a> and all of its apparent duplicates) so I don't want to create a new bug incorrectly.

In 5.1 from the bitkeeper tree (that is, not released yet), I expect the LAST_INSERT_ID problem to be gone (it should stay at the id successfully inserted).

Please ignore my comment. I misread the bug's scenario and mixed it with another one! Awfully sorry...

I am changing the synopsis of this bug report and classifying it as an open MySQL server bug.

It is a verified bug (or a request for proper documentation of InnoDB behaviour vs. MyISAM one).

Peter, since your INSERT statement generates an error, you should not relay on the value returned by LAST_INSERT_ID(), i.e. its value is undefined in this case.  This is missing currently from LAST_INSERT_ID() docs, but is said in the docs for mysql_insert_id():

"If the previous statement returned an error, the value of mysql_insert_id() is undefined."

However, I guess explicit ROLLBACK will give the same result.  I'll try to look if that can be fixed outside of InnoDB as Heikki suggests (and yes, comparison with MyISAM was wrong :-)).

Oops, concurrent editing is not quite supported.

Fixing fields erroneously changed during cocnurrent editing.

If this is classified as an InnoDB bug, then this is 'Won't fix'.

If this is classified as an InnoDB bug, then this is 'Won't fix'.

Ok, it is a Server bug :D.

Doc team,

After discussion with Konstantin we decided to not fix this bug.  Please add to documentation of LAST_INSERT_ID() the line similar to that from documentation of mysql_insert_id():

"If the previous statement returned an error, the value of LAST_INSERT_ID() is
undefined."

Also please note that that is also the case for transactional tables: if the statement is rolled back due to an error, value of LAST_INSERT_ID() is left undefined.  Furthermore, even for manual ROLLBACK value of LAST_INSERT_ID() is not restored to that before the transaction, it remains as it was at the point of ROLLBACK.

Thanks.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.