Bug #1618 | SELECT LAST_INSERT_ID() improperly returns 0 | ||
---|---|---|---|
Submitted: | 21 Oct 2003 20:28 | Modified: | 27 Oct 2003 2:03 |
Reporter: | David Levine | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 3.23.58 | OS: | Linux (redhat linux 9) |
Assigned to: | CPU Architecture: | Any |
[21 Oct 2003 20:28]
David Levine
[22 Oct 2003 5:05]
Indrek Siitan
LAST_INSERT_ID() only returns a value if the last operation was an insert that auto-generated the ID for the row. For example, with your table: mysql> insert into rep_gen (gen) values (618897000000000497); Query OK, 1 row affected (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) 0 is returned, since we inserted a static value. mysql> insert into rep_gen (gen) values (null); Query OK, 1 row affected (0.00 sec) mysql> select last_insert_id(); +--------------------+ | last_insert_id() | +--------------------+ | 618897000000000498 | +--------------------+ 1 row in set (0.00 sec) 618897000000000498, the auto-generated ID, is returned.
[22 Oct 2003 8:42]
David Levine
The previous comment is in error; there are two ways to change the last_insert_id, one is through an autoupdate, the other by explicitly using the last_insert_id function. The php code in question (and my tests from the command line) explicitly sets the last_insert_id to be gen+1, and still select last_insert_id returns 0. I want to emphasize that this is code used by me in dozens of applications over the last 5 years, and until 3.23.58 it never malfunctioned. Since it still works in many places, I now suspect that the problem has to do with table locking. The code fails following a LOCK TABLES entries WRITE (where entries is a different table in the same database). Moreover, the select statement that correctly returns gen failed until I modified the LOCK TABLES statement to "LOCK TABLES entries, rep_gen WRITE, that is, it insisted that I lock the rep-gen table also. As I indicated this code worked without problem for many years; still works most of the time, and appears to fail only within the lock tables section. Since the code failed the first time it was used following the "security" upgrade to version 3.23.58 it seems highly likely that there is a bug in this particular upgrade.
[27 Oct 2003 2:03]
Sergei Golubchik
I failed to repeat it: mysql> lock table t1 write; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 (gen) values (618897000000000497); Query OK, 1 row affected (0.00 sec) mysql> UPDATE t1 set gen=last_insert_id(gen+1); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select last_insert_id(); +--------------------+ | last_insert_id() | +--------------------+ | 618897000000000498 | +--------------------+ 1 row in set (0.00 sec)