MySQL Bugs: #48858: routine creation in non-replicated database breaks replication

Bug #48858	routine creation in non-replicated database breaks replication
Submitted:	18 Nov 2009 8:44	Modified:	18 Nov 2009 10:55
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.39, 5.1.42-bzr	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	break, filter, IGNORE, PROCEDURE, replication, routine

Description:
If you want to filter table operations on a specific database, you can do replicate-do-db=dbname, and if you want to make sure that even statements belonging to non-current databases are handled, you can do replicate-wild-do-table=dbname.%.
However, the above statement doesn't work for routines. If I set 
replicate-do-db=dbname
then I will filter off statements like "USE otherdb; CREATE PROCEDURE p1()", but it will not filter off statements like "USE dbname; CREATE PROCEDURE otherdb.p1()", and thus replication breaks.

replicate-wild-do-table has no effect on routine creation. It only filters tables, views, triggers.

How to repeat:
# using MySQL Sandbox:

export SLAVE_OPTIONS='-c replicate-wild-do-table=C.% -c replicate-wild-do-table=mysql.%' 
make_replication_sandbox 5.1.39
unset SLAVE_OPTIONS

cd $HOME/sandboxes/rsandbox_5_1_39

./m -e "create database B"
./m -e "create database C"
./m -e "use C; create table B.t1 (i int)"
./check_slaves
# all clear

./m -e "use C; create procedure B.p1() select 'hello'"
./check_slaves
# replication breaks

Suggested fix:
Make replicate-wild-do-table match also routines and events (perhaps with a different, more comprehensive name).

Verified just as described with recent 5.1.42 from bzr on Mac OS X:

77-52-1-11:mysql-test openxs$ ./mysql-test-run.pl --mysqld="--replicate-wild-do-table=c.%" --mysqld="--replicate-wild-do-table=mysql.%" --suite=rpl rpl_bug48858 
Logging: ./mysql-test-run.pl  --mysqld=--replicate-wild-do-table=c.% --mysqld=--replicate-wild-do-table=mysql.% --suite=rpl rpl_bug48858
091118 12:51:36 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/I6ZN2KWF00/ is case insensitive
091118 12:51:36 [Note] Plugin 'FEDERATED' is disabled.
091118 12:51:36 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.1.42
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
 - adding combinations for rpl
vardir: /Users/openxs/dbs/5.1/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.1/mysql-test/var'...
Installing system database...
Using server port 54188

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
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;
create database b;
create database c;
use c;
create table b.t1(i int);
use c;
create procedure b.p1() select 'hello';
rpl.rpl_bug48858 'row'                   [ fail ]
        Test ended at 2009-11-18 12:51:41

CURRENT_TEST: rpl.rpl_bug48858
=== SHOW MASTER STATUS ===
---- 1. ----
File	slave-bin.000001
Position	183
Binlog_Do_DB	
Binlog_Ignore_DB	
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	13000
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	473
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	490
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	c.%,mysql.%
Replicate_Wild_Ignore_Table	
Last_Errno	1049
Last_Error	Error 'Unknown database 'b'' on query. Default database: 'c'. Query: 'CREATE DEFINER=`root`@`localhost` PROCEDURE `b`.`p1`()
select 'hello''
Skip_Counter	0
Exec_Master_Log_Pos	344
Relay_Log_Space	774
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	
Master_SSL_Verify_Server_Cert	No
Last_IO_Errno	0
Last_IO_Error	
Last_SQL_Errno	1049
Last_SQL_Error	Error 'Unknown database 'b'' on query. Default database: 'c'. Query: 'CREATE DEFINER=`root`@`localhost` PROCEDURE `b`.`p1`()
select 'hello''

This is the test file used:

77-52-1-11:mysql-test openxs$ cat suite/rpl/t/rpl_bug48858.test 
source include/master-slave.inc;

connection master;
create database b;
create database c;
use c;
create table b.t1(i int);
save_master_pos;

connection slave;
sync_with_master;

connection master;
use c;
create procedure b.p1() select 'hello';
save_master_pos;

connection slave;
sync_with_master;

connection master;
drop database b;
drop database c;
exit;

Bug#59135 was marked as a duplicate.