Bug #8988 mysql_insert_id() returns wrong value for multiple inserts
Submitted: 6 Mar 2005 11:41 Modified: 7 Mar 2005 7:56
Reporter: Michal Čihař Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[6 Mar 2005 11:41] Michal Čihař
Description:
mysql_insert_id() returns id of first inserted row instead of last one that would be expected.

Originating report:
https://sourceforge.net/tracker/index.php?func=detail&aid=1156963&group_id=23067&atid=3774...

How to repeat:
INSERT INTO `dep` ( `dep_id` , `txt` )
VALUES (
NULL, 'item1'
), (
NULL, 'item2'
);

mysql_insert_id() now returns 9, but table contains:

dep_id	txt
9  	item1
10 	item2
[7 Mar 2005 2:16] Jorge del Conde
verified with 4.1.11 from bk

mysql> create table t11(a int not null auto_increment primary key, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t11 values (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4); 
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select last_insert_id() from t11;
+------------------+
| last_insert_id() |
+------------------+
|                1 |
|                1 |
|                1 |
|                1 |
+------------------+
4 rows in set (0.00 sec)

mysql>
[7 Mar 2005 2:32] Paul DuBois
For multiple-row inserts, mysql_insert_id() does
indeed return the first ID value.  This is documented
at:

http://dev.mysql.com/doc/mysql/en/mysql-insert-id.html

The SQL function LAST_INSERT_ID() behaves the same
way in this respect:

http://dev.mysql.com/doc/mysql/en/information-functions.html

So what you observe is the expected behavior.
[7 Mar 2005 7:56] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php