Description:
Some statements which invoke triggers which use same user variable twice in their body can be replicated incorrectly.
The same happens for table-modifying statement which use stored functions which use same user variable twice.
See "How to repeat" section for more info.
Also bug #20141 is related to this bug.
How to repeat:
# Here is small test for mysqltest which demonstrates the problem
create table t1 (i int);
create table t2 (k int);
delimiter |;
create trigger t1_bi before insert on t1 for each row
begin
insert into t2 values (@a);
set @a:=42;
insert into t2 values (@a);
end|
delimiter ;|
set @a:=100;
insert into t1 values (5);
show binlog events;
# Log_name Pos Event_type Server_id End_log_pos Info
# master-bin.000001 4 Format_desc 1 98 Server ver: 5.0.32-debug-log, Binlog ver: 4
# master-bin.000001 98 Query 1 184 use `test`; create table t1 (i int)
# master-bin.000001 184 Query 1 270 use `test`; create table t2 (k int)
# master-bin.000001 270 Query 1 491 use `test`; CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1 for each row
# begin
# insert into t2 values (@a);
# set @a:=42;
# insert into t2 values (@a);
# end
#
# Here we have two User_var events for the same variable, as only
# second of them will affect execution of insert statement replication
# will be broken.
#
# master-bin.000001 491 User var 1 533 @`a`=100
# master-bin.000001 533 User var 1 575 @`a`=42
# master-bin.000001 575 Query 1 663 use `test`; insert into t1 values (5)
# Another test snippet that demonstrates problem for stored functions
create table t1 (i int);
create table t2 (k int);
delimiter |;
create function f1() returns int
begin
insert into t2 values (@a);
set @a:=42;
insert into t2 values (@a);
return 5;
end|
delimiter ;|
set @a:=100;
insert into t1 values (f1());
show binlog events;
# Log_name Pos Event_type Server_id End_log_pos Info
# master-bin.000001 4 Format_desc 1 98 Server ver: 5.0.32-debug-log, Binlog ver: 4
# master-bin.000001 98 Query 1 184 use `test`; create table t1 (i int)
# master-bin.000001 184 Query 1 270 use `test`; create table t2 (k int)
# master-bin.000001 270 Query 1 480 use `test`; CREATE DEFINER=`root`@`localhost` function f1() returns int
# begin
# insert into t2 values (@a);
# set @a:=42;
# insert into t2 values (@a);
# return 5;
# end
#
# Ok, below we can see problem as with triggers
#
# master-bin.000001 480 User var 1 522 @`a`=100
# master-bin.000001 522 User var 1 564 @`a`=42
# master-bin.000001 564 Query 1 663 use `test`; insert into t1 values (f1())
