MySQL Bugs: #21522: LAST_INSERT_ID() returns invalid value

Bug #21522	LAST_INSERT_ID() returns invalid value
Submitted:	8 Aug 2006 20:10	Modified:	17 Aug 2006 8:39
Reporter:	Georg Richter	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	4.1/5.0/5.1BK	OS:	Linux (Linux)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
when inserting values via prepared statement LAST_INSERT_ID() does not work correctly.

How to repeat:
CREATE TABLE t1 (a int not null auto_increment primary key);

PREPARE mystmt FROM "INSERT INTO t1 VALUES (LAST_INSERT_ID() + 1)";

EXECUTE mystmt;

# this returns 0 instead of 1 
SELECT LAST_INSERT_ID();

Thank you for the bug report. Verfied as described.

miguel@hegel:~/dbs/4.1> bin/mysql -uroot dbh
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t1 (a int not null auto_increment primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> PREPARE mystmt FROM "INSERT INTO t1 VALUES (LAST_INSERT_ID() + 1)";
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> 
mysql> EXECUTE mystmt;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.01 sec)

mysql>

This is not a bug.

According to the MySQL Reference Manual section 12.9.3. Information Functions:

"LAST_INSERT_ID(), LAST_INSERT_ID(expr)

Returns the first automatically generated value that was set for an AUTO_INCREMENT column by the most recent INSERT or UPDATE statement to affect such a column.

...

The value of LAST_INSERT_ID() is not changed if you set the AUTO_INCREMENT  column of a row to a non-"magic" value (that is, a value that is not NULL and not 0)."

In the statement "INSERT INTO t1 VALUES (LAST_INSERT_ID() + 1)" AUTO_INCREMENT column is set explicitly to a value other than NULL or 0, hence last insert id is not affected in any way (this is so even when this statement is used by itself, not as a prepared statement).

Instead please consider the following:

CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t1 VALUES (NULL);
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
PREPARE mystmt FROM "INSERT INTO t1 VALUES (NULL)";
EXECUTE mystmt;
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 | <= Correct
+------------------+

Then:

PREPARE mystmt FROM "INSERT INTO t1 VALUES (LAST_INSERT_ID() + 1)";
EXECUTE mystmt;
SELECT * FROM t1;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 | <= Correct
+---+

We can see that LAST_INSERT_ID() works from prepared statement.