Bug #76715 Different behavior of LAST_INSERT_ID() function within a trigger AFTER INSERT
Submitted: 15 Apr 2015 17:08
Reporter: William Chiquito Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0, 5.1+ OS:Any
Assigned to:
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