Bug #76715 Different behavior of LAST_INSERT_ID() function within a trigger AFTER INSERT

• Submitted: 15 Apr 2015 17:08
• Modified: 9 Mar 2018 0:21
• Reporter: William Chiquito
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0, 5.1+
• OS: Any
• CPU Architecture: Any
• Tags: AFTER INSERT, LAST_INSERT_ID, trigger

[15 Apr 2015 17:08] William Chiquito

Description:
Does the change in behavior of LAST_INSERT_ID() function within a trigger AFTER INSERT from 5.0 to 5.1+ is documented?

I searched the documentation, but I see nothing about the behavior mentioned.

How to repeat:
DROP TABLE IF EXISTS `source_table`;
DROP TABLE IF EXISTS `target_table`;

CREATE TABLE `source_table` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mysql_version` VARCHAR(50) NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `target_table` (
  `value_new_id` INT  NOT NULL,  
  `value_last_insert_id` INT  NOT NULL
);

DELIMITER //

CREATE TRIGGER `trgaitest` AFTER INSERT ON `source_table`
FOR EACH ROW
BEGIN
	INSERT INTO `target_table` (`value_new_id`, `value_last_insert_id`)
	VALUES
	(NEW.`id`, LAST_INSERT_ID());
END//

DELIMITER ;

INSERT INTO `source_table` (`mysql_version`)
VALUES
(VERSION());

SELECT
	`value_new_id`,
	`value_last_insert_id`
FROM
	`target_table`;

5.0
value_new_id  value_last_insert_id
------------  --------------------
           1                     1

5.1+
value_new_id  value_last_insert_id
------------  --------------------
           1                     0

[9 Mar 2018 0:21] MySQL Verification Team

Thank you for the bug report.

mysql 5.0 > SELECT
    ->  `value_new_id`,
    ->  `value_last_insert_id`
    -> FROM
    ->  `target_table`;
+--------------+----------------------+
| value_new_id | value_last_insert_id |
+--------------+----------------------+
|            1 |                    1 |
+--------------+----------------------+
1 row in set (0.00 sec)

mysql 5.0 > SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------------+
| Variable_name           | Value                         |
+-------------------------+-------------------------------+
| protocol_version        | 10                            |
| version                 | 5.0.96-community              |
| version_comment         | MySQL Community Edition (GPL) |
| version_compile_machine | unknown                       |
| version_compile_os      | Win64                         |
+-------------------------+-------------------------------+
5 rows in set (0.00 sec)
----------------------------------------------------------------
mysql 5.1 > SELECT
    ->  `value_new_id`,
    ->  `value_last_insert_id`
    -> FROM
    ->  `target_table`;
+--------------+----------------------+
| value_new_id | value_last_insert_id |
+--------------+----------------------+
|            1 |                    0 |
+--------------+----------------------+
1 row in set (0.00 sec)

mysql 5.1 > SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.1.72-community             |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | unknown                      |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.02 sec)
-------------------------------------------------------------------
mysql 5.5 > SELECT
    ->  `value_new_id`,
    ->  `value_last_insert_id`
    -> FROM
    ->  `target_table`;
+--------------+----------------------+
| value_new_id | value_last_insert_id |
+--------------+----------------------+
|            1 |                    0 |
+--------------+----------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------
mysql 5.7 > SELECT
    ->  `value_new_id`,
    ->  `value_last_insert_id`
    -> FROM
    ->  `target_table`;
+--------------+----------------------+
| value_new_id | value_last_insert_id |
+--------------+----------------------+
|            1 |                    0 |
+--------------+----------------------+
1 row in set (0.01 sec)

mysql 5.7 > show variables like "%version%";
+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 5.7.22                          |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| tls_version             | TLSv1,TLSv1.1                   |
| version                 | 5.7.22-log                      |
| version_comment         | Source distribution 2018-JAN-07 |
| version_compile_machine | x86_64                          |
| version_compile_os      | Win64                           |
+-------------------------+---------------------------------+
8 rows in set (0.01 sec)