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

[13 Oct 2006 9:07] Tomash Brechko
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 |
#+---+------+------+
[13 Oct 2006 10:48] Valerii Kravchuk
Thank you for a bug report. Verified just as described with 5.0.27-BK on Linux.
[13 Oct 2006 13:54] Heikki Tuuri
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.
[13 Oct 2006 14:05] Peter Andrews
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.
[13 Oct 2006 14:10] Heikki Tuuri
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
[13 Oct 2006 14:29] Peter Andrews
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.
[13 Oct 2006 14:34] Guilhem Bichot
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).
[13 Oct 2006 14:50] Guilhem Bichot
Please ignore my comment. I misread the bug's scenario and mixed it with another one! Awfully sorry...
[13 Oct 2006 14:55] Heikki Tuuri
I am changing the synopsis of this bug report and classifying it as an open MySQL server bug.
[13 Oct 2006 15:00] Valerii Kravchuk
It is a verified bug (or a request for proper documentation of InnoDB behaviour vs. MyISAM one).
[13 Oct 2006 15:03] Tomash Brechko
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 :-)).
[13 Oct 2006 15:05] Tomash Brechko
Oops, concurrent editing is not quite supported.
[13 Oct 2006 15:07] Tomash Brechko
Fixing fields erroneously changed during cocnurrent editing.
[13 Oct 2006 15:08] Heikki Tuuri
If this is classified as an InnoDB bug, then this is 'Won't fix'.
[13 Oct 2006 15:08] Heikki Tuuri
If this is classified as an InnoDB bug, then this is 'Won't fix'.
[13 Oct 2006 15:08] Heikki Tuuri
Ok, it is a Server bug :D.
[17 Oct 2006 10:21] Tomash Brechko
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.
[26 Oct 2006 3:23] Paul Dubois
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.