Bug #37185 last_insert_id() returns all rows (of one value) instead of one row of one value
Submitted: 4 Jun 2008 9:29 Modified: 4 Jun 2008 9:43
Reporter: Nigel Hill Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S5 (Performance)
Version:5 OS:Windows
Assigned to: CPU Architecture:Any
Tags: LAST_INSERT_ID

[4 Jun 2008 9:29] Nigel Hill 
Description:
While accessing a MySQL database from another server an issue with last_insert_id() was found; Instead of returning one row containing one value it returns all the rows with the same value, the number of rows returned being the count of rows. 
In this instance it created a cross-server download of 5Mb on every execution causing excessive bandwith usage which incremented due to each execution inserting a row.
This will not normally present a problem assuming most sites use a local database, however only one returned row is required in any instance.
The resolution was to select last_insert_id limiting results to 1 row. 

How to repeat:
SELECT LAST_INSERT_ID() FROM `table_name`

Suggested fix:
SELECT LAST_INSERT_ID() FROM `table_name` LIMIT 0,1
[4 Jun 2008 9:34] Domas Mituzas 
LAST_INSERT_ID() is not per-table aggregate function, but global-scope one.
[4 Jun 2008 9:37] Paul DuBois 
To amplify on the last comment:

SELECT LAST_INSERT_ID() FROM table_name will always return one row per table. This is not what you want. LAST_INSERT_ID() is not specific to a table, it's specific to the final AUTO_INCREMENT value, so you don't want your second query, either. Just use this:

SELECT LAST_INSERT_ID();
[4 Jun 2008 9:43] Nigel Hill 
That explains why the error has never appeared previously to me. This was code from another developer and you are right it is absolutely is an error in the request.