MySQL Bugs: #35116: LAST_INSERT_ID() give wrong value after REPLACE INTO query

Bug #35116	LAST_INSERT_ID() give wrong value after REPLACE INTO query
Submitted:	6 Mar 2008 15:35	Modified:	10 Mar 2008 11:57
Reporter:	Axel Axel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux (5.0.45-1ubuntu3.1 mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i486) using readline 5.2)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	INSERT_ID, LAST_INSERT_ID, REPLACE

Description:
LAST_INSERT_ID() function doesn't give the good value after an REPLACE INTO when this REPLACE INTO occurs as an DELETE/INSERT.

Shouldn't the last_insert_id() value be 2 and not 3 ?

LAST_INSERT_ID() returns the generated value from an auto_increment column, and in the case of a REPLACE INTO of an existing row, the resulting insert probably specifies the id, and so LAST_INSERT_ID() isn't changed.

Because of that, in every day use, REPLACE INTO isn't reliable. If this is a normal behaviour, I think the REPLACE INTO documentation should mention that LAST_INSERT_ID() can't be used, that this can be avoided by using INSERT ON DUPLICATE http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html instead of REPLACE INTO.

How to repeat:
create table t2(id serial, val TEXT);
replace into t2 values(null, 'ok');
replace into t2 values(null, 'ok');
replace into t2 values(null, 'ok');

select * from t2;
+----+------+
| id | val  |
+----+------+
|  1 | ok   | 
|  2 | ok   | 
|  3 | ok   | 
+----+------+
3 rows in set (0.01 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)

replace into t2 values(2, 'bad');
Query OK, 2 rows affected (0.01 sec)

2 rows affected : there is a delete and then an insert.

mysql> select * from t2;
+----+------+
| id | val  |
+----+------+
|  1 | ok   | 
|  2 | bad  | 
|  3 | ok   | 
+----+------+
3 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)

I think current behaviour is intended and should just be documented clearly.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

It is documented clearly:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
"
LAST_INSERT_ID() (with no argument) returns the first *automatically generated* value
<skip>
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).
"