Bug #58864 Clarify LAST_INSERT_ID() behavior on update by INSERT ON DUPLICATE KEY UPDATE

• Submitted: 10 Dec 2010 12:13
• Modified: 19 Jul 2011 15:09
• Reporter: Terry Nycum
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S4 (Feature request)
• Version: 5.1
• OS: Any
• Assigned to: Paul DuBois
• CPU Architecture: Any
• Tags: insert on duplicate, insert on duplicate key update, LAST_INSERT_ID

[10 Dec 2010 12:13] Terry Nycum

Description:
There's some ambiguity left at the bottom of http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html:
"If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful prior to MySQL 5.1.12. However, you can work around this by..."

Beyond implying that it's meaningful, this doesn't really indicate what the behavior has been since 5.1.12. It could do the more useful thing that it now does, return the updated row's value in the AUTO_INCREMENT column, ... or ... it could simply now be meaningful in the same way as it has always been in the insertion case, giving the AUTO_INCREMENT value.

How to repeat:
n/a

Suggested fix:
Following the example of the work-around, clarify that since 5.1.2, LAST_INSERT_ID() has behaved like a LAST_INSERT/UPDATE_ID().

[19 Jul 2011 15:09] Paul DuBois

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Revised wording:

If a table contains an AUTO_INCREMENT column and INSERT ... ON
DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID()
function returns the AUTO_INCREMENT value. Exception: For updates,
LAST_INSERT_ID() is not meaningful prior to MySQL 5.1.12. However,
you can work around this by ...