Alter the LAST_INSERT_ID() from within a TRIGGER in MySQL

Question

I have a BEFORE INSERT TRIGGER which is used to calculate the AUTO_INCREMENT value of a column (id_2).

id_1 | id_2 | data
1    | 1    | 'a'
1    | 2    | 'b'
1    | 3    | 'c'
2    | 1    | 'a'
2    | 2    | 'b'
2    | 3    | 'c'
2    | 4    | 'a'
3    | 1    | 'b'
3    | 2    | 'c'

I have PRIMARY(id_1, id_2) and I am using InnoDB. Before, the table was using MyISAM and I've had no problems: id_2 was set to AUTO_INCREMENT, so each new entry for id_1 would generate new id_2 on its own. Now, after switching to InnoDB, I have this trigger to do the same thing:

SET @id = NULL;
SELECT COALESCE(MAX(id_2) + 1, 1) INTO @id FROM tbl WHERE id_1 = NEW.id_1;
SET NEW.id_2= @id;

It works perfectly, except now the LAST_INSERT_ID() has wrong value (it returns 0). A lot of code depends on the LAST_INSERT_ID() being correct. However since MySQL 5.0.12 any changes made to LAST_INSERT_ID within TRIGGERS are not affecting the global value. Is there any way to bypass this? I can easily set the AFTER UPDATE TRIGGER which changes the LAST_INSERT_ID by calling LAST_INSERT_ID(NEW.id_2), however any client-side would get LAST_INSERT_ID set to 0.

Is there any working work-around to force MySQL to maintain the state of LAST_INSERT_ID which was changed inside the trigger? Is there any alternative, other than switching back to MyISAM which supports this out of the box or running another SELECT max(id_2) FROM tbl WHERE id_1 = :id as part of the transaction to ensure that the row found will be the one inserted earlier?

> SHOW CREATE TABLE tbl;

CREATE TABLE `tbl` (
   `id_1` int(11) NOT NULL,
   `id_2` int(11) NOT NULL,
   `data` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id_1`,`id_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Example:

INSERT INTO tbl (id_1, id_2, data) VALUES (1, NULL, 'd');
SELECT LAST_INSERT_ID();

The first statement will insert the row 1 | 4 | 'd' into the table. The second statement will return 0, but I need it to return 4.

As asked by Ravinder Reddy, adding the short explanation about the system:

I have a table that contains baskets, and I have another table (tbl) that contains items. The basket is created by the application and is assigned an ID from AUTO_INCREMENT on baskets' table. The task is to insert items in basket with id = id_1, into tbl, assigning them a unique ID within that basket's scope. Each item has some data associated with it, which may repeat within the same basket. So in practice, I am trying to store all the data entries within a single basket, and then be able to refer to (and retrieve) these individual entries by their id_1-id_2 pairs.

Answer 1

With your table structure description, it is clear that it does not have a primary key field whose values can be auto generated. MySQL's information_schema.tables does not hold auto_increment value but null for those fields which are not defined auto_increment.

Trigger issue:

The code block used in your trigger body seems depending on explicit calculation and input for the id fields. It did not use the default behaviour of an auto_increment field.

As per MySQL's documentation on LAST_INSERT_ID:

LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value
representing the first automatically generated value
successfully inserted for an AUTO_INCREMENT column
as a result of the most recently executed INSERT statement.

It is clear that it is for auto_increment fields only.
None of the fields id_1 and id_2 are attributed auto_increment.
Due to the reason, though you pass null as input for those fields while inserting, no value will be auto generated and assigned to them.

Alter your table to set auto_increment to one of those id_x fields, and then start inserting values. One caution is that passing a value explicitly to an auto_increment field during insertion will cause last_insert_id return a zero or most recent auto generated value, but not the NEW.id. Passing a null or not choosing the auto_increment field during insertion will trigger generation of NEW value for that field and last_insert_id can pick and return it.

Following example demonstrates above behaviour:

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

mysql> create table so_q27476005( i int primary key );
Query OK, 0 rows affected (0.33 sec)

Following statement shows next applicable auto_increment value for a field.

mysql> select auto_increment
    ->   from information_schema.tables
    ->  where table_name='so_q27476005';
+----------------+
| auto_increment |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

Let us try inserting a null value into the field.

mysql> insert into so_q27476005 values( null );
ERROR 1048 (23000): Column 'i' cannot be null

Above statement failed because input was into a not null primary key field but not attributed for auto_increment. Only for auto_increment fields, you can pass null inputs.

Now let us see the behaviour of last_insert_id:

mysql> insert into so_q27476005 values( 1 );
Query OK, 1 row affected (0.04 sec)

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

As the input was explicit and also the field is not attributed for auto_increment,
call for last_insert_id resulted a 0. Note that, this can also be some value else, if there was another insert call for any other auto_increment field of another table, in the same database connection session.

Let us see the records in the table.

mysql> select * from so_q27476005;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Now, let us apply auto_increment to the field i.

mysql> alter table so_q27476005 change column i i int auto_increment;
Query OK, 1 row affected (0.66 sec)
Records: 1  Duplicates: 0  Warnings: 0

Following statement shows next applicable auto_increment value for the field i.

mysql> select auto_increment
    ->   from information_schema.tables
    ->  where table_name='so_q27476005';
+----------------+
| auto_increment |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

You can cross check that the last_insert_id still is the same.

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

Let us insert a null value into the field i.

mysql> insert into so_q27476005 values( null );
Query OK, 1 row affected (0.03 sec)

It succeeded though passing a null to a primary key field, because the field is attributed for auto_increment.
Let us see which value was generated and inserted.

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

And the next applicable auto_increment value for the field i is:

mysql> select auto_increment
    ->   from information_schema.tables
    ->  where table_name='so_q27476005';
+----------------+
| auto_increment |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from so_q27476005;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

Now, let us observe how last_insert_id results when explicit input is given for the field.

mysql> insert into so_q27476005 values( 3 );
Query OK, 1 row affected (0.07 sec)

mysql> select * from so_q27476005;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)


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

You can see that last_insert_id did not capture the value due to explicit input.
But, information schema do registered next applicable value.

mysql> select auto_increment
    ->   from information_schema.tables
    ->  where table_name='so_q27476005';
+----------------+
| auto_increment |
+----------------+
|              4 |
+----------------+
1 row in set (0.08 sec)

Now, let us observe how last_insert_id results when input for the field is auto/implicit.

mysql> insert into so_q27476005 values( null );
Query OK, 1 row affected (0.10 sec)

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

Hope, these details help you.