MySQL Bugs: #12482: Triggers has side effects with auto

Bug #12482	Triggers has side effects with auto_increment values
Submitted:	10 Aug 2005 6:36	Modified:	18 Aug 2005 20:42
Reporter:	Michael Widenius	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
Triggers has side effects with auto_increment values

If create a trigger that inserts into a table that has an auto-increment column, this will cause side effects on the original statement and may case:

- Wrong data to be inserted into the main table for multi-row inserts
- last_insert_id() will return value from the table in the trigger instead of from the main table
- Slave will in some senarios get different data than the master
 

How to repeat:
Here is a not complete test case that shows some of the issues:

drop table if exists t1,t2,t3;;
create table t1 (a int auto_increment, primary key (a), b int);;
create table t2 (a int auto_increment, primary key (a), b int);;
create table t3 (a int auto_increment, primary key (a), name varchar(64) not nul
l, old_a int, old_b int);;

create trigger t1 before insert on t1 for each row
begin
 insert into t3 values (NULL, "t1", new.a, new.b);
end;;

create trigger t2 before insert on t2 for each row
begin
 insert into t3 values (NULL, "t2", new.a, new.b);
end;;

insert into t3 values(100,"log",0,0);;

insert into t1 values(1,1),(NULL,2);;
insert into t2 (b) values(last_insert_id());;
select * from t1;;
select * from t2;;
select * from t3;;

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 5.0.12

The bug fix removes side effects caused by triggers or stored functions.

Problem areas in triggers/stored functions code before the bug fix:

- Inserting into table with an autoincrement column (in trigger/SP) could cause wrong auto_increment values to be inserted in the main statement and cause replication failures
- last_insert_id() could came from SP/TRIGGER (not wanted side effect)
- FOUND_ROWS() could came from SP/TRIGGER and not from last statement
- Using RAND() in SP/TRIGGER could cause replication to fail

To make it short: This makes triggers/SP usable for replication.

Documented in 5.0.12 changelog:

<listitem><para>Trigger and stored procedure execution could break replication. (Bug #12482)</para></listitem>