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

[10 Aug 2005 6:36] Michael Widenius
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;;
[15 Aug 2005 17:32] Michael Widenius
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
[17 Aug 2005 23:16] Michael Widenius
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.
[18 Aug 2005 20:42] Mike Hillyer
Documented in 5.0.12 changelog:

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