Mysql 5.0 LAST_INSERT_ID() differs after upgrading to 5.6

Question

Migrating from MySQL 5.0 to 5.6 i noticed that the INSERT_LAST_ID() function behaves different when using inside a trigger.

My simplified situation:

Table A has a (after insert) trigger: insert into table_B (ID) values(LAST_INSERT_ID());

Table B has the foreignkey: CONSTRAINT table_B FOREIGN KEY (ID) REFERENCES Table_A (ID) ON DELETE CASCADE ON UPDATE RESTRICT

This trigger-foreignkey combination works fine under 5.0 After migrating to 5.6 these triggers have other behavior.

Now the function LAST_INSERT_ID() used in the trigger seems to get a 0 (zero) and the second time the Primarykey-1.

I already searched the web but don't get any further.

Any help would be appreciated.

Answer 1

That's right, 5.0 behavior is unlike 5.1 or higher. The recommendation would apply the following change to the after trigger:

-- insert into table_B (ID) values(LAST_INSERT_ID());
insert into table_B (ID) values (NEW.ID);