Bug #12487 Stored functions that inserts into tables doesn't replicate in SELECT
Submitted: 10 Aug 2005 9:22 Modified: 24 Aug 2005 0:04
Reporter: Michael Widenius Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0 OS:
Assigned to: Assigned Account CPU Architecture:Any

[10 Aug 2005 9:22] Michael Widenius
Description:
Replication doesn't replicate SELECT statements that calls a function that changes another table (insert, delete or update)

How to repeat:
create table t1 (a int);;
create function sub(i int) returns int
begin
insert into t1 values(i);
return 0;
end;;
select sub(1);;
set @a=sub(1);;
[10 Aug 2005 17:53] Jonathan Miller
This has also shown to be an issues with rbr.
[10 Aug 2005 18:04] Jonathan Miller
Test case:
# Includes
-- source include/have_binlog_format_row.inc
-- source include/master-slave.inc

# Begin clean up test section
connection master;
--disable_warnings
--error 0,1305
DROP FUNCTION test.f1;
DROP TABLE IF EXISTS test.t1;

--enable_warnings

# Section 1 test from bug #12487
#let $message=<Begin test section 1 (function call f1)>;
#--source include/show_msg.inc

create table test.t1 (a int, PRIMARY KEY(a));

delimiter //;
create function test.f1(i int) returns int
begin
insert into test.t1 values(i);
return 0;
end//
delimiter ;//

select test.f1(1);
select test.f1(2);
select * from test.t1;

save_master_pos;
sync_slave_with_master;
connection slave;
show create table test.t1;
select * from test.t1;

connection master;
show binlog events;

#let $message=<End test section 1 (function call f1)>;
#--source include/show_msg.inc

# Lets cleanup

DROP FUNCTION test.f1;
DROP TABLE test.t1;

**** Results *****
*** r/rpl_row_func001.result    2005-08-10 16:49:41.000000000 +0300
--- r/rpl_row_func001.reject    2005-08-10 20:57:03.000000000 +0300
***************
*** 0 ****
--- 1,46 ----
+ stop slave;
+ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+ reset master;
+ reset slave;
+ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+ start slave;
+ DROP FUNCTION test.f1;
+ DROP TABLE IF EXISTS test.t1;
+ create table test.t1 (a int, PRIMARY KEY(a));
+ create function test.f1(i int) returns int
+ begin
+ insert into test.t1 values(i);
+ return 0;
+ end//
+ select test.f1(1);
+ test.f1(1)
+ 0
+ select test.f1(2);
+ test.f1(2)
+ 0
+ select * from test.t1;
+ a
+ 1
+ 2
+ show create table test.t1;
+ Table Create Table
+ t1    CREATE TABLE `t1` (
+   `a` int(11) NOT NULL default '0',
+   PRIMARY KEY  (`a`)
+ ) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ select * from test.t1;
+ a
+ show binlog events;
+ Log_name      Pos     Event_type      Server_id       End_log_pos     Info
+ master-bin.000001     4       Format_desc     1       102     Server ver: 5.0.11-beta-log, Binlog ver: 4
+ master-bin.000001     102     Query   1       193     use `test`; DROP TABLE IF EXISTS test.t1
+ master-bin.000001     193     Query   1       300     use `test`; create table test.t1 (a int, PRIMARY KEY(a))
+ master-bin.000001     300     Query   1       456     use `test`; create function test.f1(i int) returns int
+ begin
+ insert into test.t1 values(i);
+ return 0;
+ end
+ master-bin.000001     456     Write_rows      1       488
+ master-bin.000001     488     Write_rows      1       520
+ DROP FUNCTION test.f1;
+ DROP TABLE test.t1;
[22 Aug 2005 6:55] Sergey Petrunya
This bug will be fixed by the fix for #12335.
[24 Aug 2005 0:04] Sergey Petrunya
Setting to duplicate of BUG#12335 as fix for BUG#12335 [will] fix this bug too.