Bug #64089 | wrong last_insert_id() after setting id in trigger / recursive triggers | ||
---|---|---|---|
Submitted: | 20 Jan 2012 23:49 | Modified: | 7 Jul 2012 11:19 |
Reporter: | Thomas Mayer | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S4 (Feature request) |
Version: | 5.1.54 | OS: | Linux (5.1.54-1ubuntu4) |
Assigned to: | CPU Architecture: | Any | |
Tags: | last_insert_id trigger |
[20 Jan 2012 23:49]
Thomas Mayer
[24 Jan 2012 14:46]
Valeriy Kravchuk
IMHO this is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id: "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)." Your trigger does exactly this, sets value to 123.
[25 Feb 2012 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[25 Feb 2012 14:20]
Thomas Mayer
As written, I know about this documented behaviour of MySql. I have read this part of the documentation. However, I consider it as a limitation of MySQL: Either - Setting the last_insert_id should be possible by the trigger or - The work of the trigger should be transparent to the sql statement triggering the trigger. Not allowing things makes triggers not applicable in some cases. Additionally, the recursiveness of triggers (action of trigger triggers the same trigger again) is pointless when MySQL throws errors because of detected recursions. I see applications for recursive triggers (e.g. the task "duplicate every insert 10 times"), but I oftenly could not use them because of that limitation. It should be possible to enable/disable recursiveness as well as recursion detection in any way. Most of the time I would like to disable recursiveness. Please consider this report as a feature request if you want.
[7 Jul 2012 11:19]
Valeriy Kravchuk
I agree that this: "The work of the trigger should be transparent to the sql statement triggering the trigger." is a reasonable feature request. It would be useful to see last_insert_id() returning the ID "generated", even it was explicitly set in trigger.
[12 Sep 2013 19:46]
Jon Miller
I just ran into this issue myself. I would like to see this change made as well. I found that the LAST_INSERT_ID() value that is in the trigger is basically it's own variable. So, for example, say you do an INSERT and LAST_INSERT_ID() is 5. Then, you do another INSERT that causes a trigger to generate a new id, say that one is 6 (inside the trigger). Then, you call LAST_INSERT_ID() from normal user code outside the trigger. It will say 5, not 6 like you would expect it to. It's almost as if the trigger is executing in another transaction or connection or something. This functionality would be very useful to me for working with a legacy database that has a less than ideal method of generating id values.
[18 Oct 2013 1:03]
Emmanuel Merali
+1 I too agree that, although the current behaviour is documented, it seems wrong. I believe that the last_insert_id() should be left alone after changing it in a trigger. Currently, there is no simple way to get the id of a newly inserted record if that id has been calculated in an insert before trigger.
[12 Mar 2014 2:45]
Jon Miller
Any word on whether this will ever be fixed? I could really use this on a project that I'm working on.