| Bug #1714 | last_insert_id next value even upon failed insert | ||
|---|---|---|---|
| Submitted: | 30 Oct 2003 12:05 | Modified: | 3 Nov 2003 12:16 |
| Reporter: | Patrick Galbraith | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
| Version: | 4.0.15 | OS: | Linux (Linux, RH 9.0) |
| Assigned to: | CPU Architecture: | Any | |
[30 Oct 2003 12:14]
Dean Ellis
Behaviour occurs with both MyISAM and InnoDB with 4.0.16. LAST_INSERT_ID() reports an incremented value. The next successful insert uses the value the duplicate-key query would have used if successful, rather than last_insert_id() + 1. You should be testing for success of INSERT before testing the value of LAST_INSERT_ID(), but I agree, LAST_INSERT_ID() should not be returning the increment value. Thank you
[3 Nov 2003 12:16]
Brian Aker
Hi Pat! This is not a bug since you should not be using the state of the last insert id to determine whether or not the insert occured.
Description: When I insert a duplicate record into a table (a record that fails to insert because the unique constraint on three columns), the return value of last_insert_id() is still incremented, one higher than the last record, as if the record was inserted. This causes a lot of grief on my web application ;) How to repeat: The last record: 102 | 101 | Test | Customer5 | | | patg+8383838@patg.net | 28282 Main Ave. | | | | WA | 282828 | | | | | | | | 0000-00-00 00:00:00 | 20031029093557 | +-----+-----------+------------+-------------+----------------------------------+-----------+----------------------------------+-------------------------------+-------------------+-------------------+--------+-------------------+------------+---------+-----------+------------+---------+-----------+-----------+---------------+---------------------+----------------+ 91 rows in set (0.00 sec) The insert: mysql> insert into users (firstname, lastname, email) values ('Test', 'Customer5', 'patg+8383838@patg.net'); ERROR 1062: Duplicate entry 'patg+8383838@patg.net-Customer5-Test' for key 2 should be 102: mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 103 | Suggested fix: I'm guessing that behind every auto_increment, is a sequence of some sort? Perhaps the sequence gets updated, even if the target table insert fails? I have yet to try this with myISAM table type.