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:
None 
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
Description:
last_insert_id() incorrectly returns 0 instead of a very large number

I discovered the error in php (see code below) and replicated it using the mysqlgui client

How to repeat:
table rep_gen has the following columns
gen bigint(20) primary key default 0
rep_time timestamp(14) allows nulls, default null
rep_code bigint(20) allows nulls, default null
rep_lock tinyint(4) allows nulls, default 0
rep_lock_time timestamp(14) allows nulls, default null
rep_lock_max bigint(20) allows nulls, default 3600

the table has one row and gen = 618897000000000497

        $sql = "UPDATE rep_gen SET gen=LAST_INSERT_ID(gen+1)";

	mysql_query($sql, $conn);

	$sql = "SELECT LAST_INSERT_ID()";
	//$sql = "SELECT gen FROM rep_gen";

	$RS = mysql_query($sql, $conn);

	$x = mysql_result($RS, 0, 0);
	mysql_free_result($RS);
        echo $x

will return 0 rather than 618897000000000498
however if the commented line is uncommented so that gen is selected directly rather than as the last_insert_id the correct number 618897000000000498 will be returned

Suggested fix:
probably some sort of overflow error since the integers involved are very large; perhaps the last_insert_id field is not wide enough?
[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)