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:
None 
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
Description:
When inserting default values into auto_increment columns, MySQL returns the corresponding new id in the function LAST_INSERT_ID().

However, if a (before update) trigger changes that id, the result of LAST_INSERT_ID() does not change.

Modern applications like web frameworks often use this function. As a result, errors occur because the application relies on the fact that it gets the id of the new row for further operations.

Several workarounds are not possible. I tried:
- Inserting a dummy row to correct the current auto_increment results in recursive trigger execution on the same table (which is not allowed)
- alter table auto_increment = ...; implicitly commits the transaction inside the trigger which is not allowed
- and if it was allowed no variable with the new auto_increment value can be passed to the alter statement. Additionally no dynamic sql is allowed in triggers.
- updating auto_increment in mysql schema tables is no option (if possible, not allowed for user with limited rights)
- using last_insert_id(new_id) in the transaction. But the new last_insert_id() is reset to the old value when the trigger has finished. This is documented behaviour.
==> I found no way to pass the new id to the application

However we have used VALUES(default) and _therefore_ the application expects to get the new id via LAST_INSERT_ID(). Documentation to LAST_INSERT_ID() says that LAST_INSERT_ID() does not change if the application sets the ID explicitly. Although the trigger sets it explicitly, the application does not. So LAST_INSERT_ID() should return the id from the trigger even if it is set explicitly by the trigger.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 254
Server version: 5.1.54-1ubuntu4 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

mysql> 
mysql> drop table if exists test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (id int auto_increment, primary key(id));
Query OK, 0 rows affected (0.16 sec)

mysql> 
mysql> delimiter //
mysql> drop trigger if exists trigger_test;
    -> CREATE TRIGGER trigger_test BEFORE INSERT ON test
    -> FOR EACH ROW
    -> BEGIN
    ->   IF (NEW.id=0) THEN
    ->     set NEW.id=123;
    ->   END IF;
    -> END;//
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.18 sec)

mysql> delimiter ;
mysql> 
mysql> insert into test values (default);
Query OK, 1 row affected (0.00 sec)

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

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

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

mysql> 
mysql> drop table if exists test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (id int auto_increment, primary key(id));
Query OK, 0 rows affected (0.12 sec)

mysql> 
mysql> delimiter //
mysql> drop trigger if exists trigger_test;
    -> CREATE TRIGGER trigger_test BEFORE INSERT ON test
    -> FOR EACH ROW
    -> BEGIN
    ->   IF (NEW.id=0) THEN
    ->     set NEW.id=last_insert_id(123);
    ->   END IF;
    -> END;//
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.18 sec)

mysql> delimiter ;
mysql> 
mysql> insert into test values (default);
Query OK, 1 row affected (0.00 sec)

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

Suggested fix:
The trigger should be transparent to the application as far as possible so that there would not be a difference for the application if the id comes from the trigger or from auto_increment.
[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.