MySQL Bugs: #46110: --replicate-wild-do-table invalidates --replicate-do-db matching rule

Bug #46110	--replicate-wild-do-table invalidates --replicate-do-db matching rule
Submitted:	10 Jul 13:21	Modified:	25 Aug 17:14
Reporter:	Luís Soares
Status:	Verified
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1, 4.1, 5.0, azalea	OS:	Any
Assigned to:	Luís Soares	Target Version:
Tags:	rpl filters, replicate-do-db, replicate-wild-do-db
Triage:	Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

Description:
When using --replicate-do-db and --replicate-wild-do-table
together the result may not be as expected. For instance, using
the slave options:

--replicate-do-db=dbx --replicate-wild-do-table=db%.t1

and then, issuing a 'CREATE DATABASE dbx' on master, the slave
will not create dbx.

From the manual (Section 16.4.3.2):

"The slave checks for and evaluates table options only if no
matching database options were found."

so I would expect the slave to replicate the create database.

Also, from the execution flow chart (Section 16.4.3.1), I see:
that if any --replicate-do-db rule exist AND match then execute
the "update" and exit. If not, then check replicate-ignore-db. If
no match in this last step, finally, proceed to table checking
rules.

I observed this on Linux with a 5.1-bugteam clone:
revno: 3017
revision-id: joro@sun.com-20090707125234-fe4my9xoq1fk7xj2
parent: v.narayanan@sun.com-20090709103406-6gpdkarw16dsmh05

How to repeat:
1. Test case: (place it, for instance, in suite/rpl/t/rpl_filter_bug.test)

-- source include/master-slave.inc

CREATE DATABASE dbx;

-- echo ### ON MASTER ### 
SHOW DATABASES;

-- sync_slave_with_master

-- echo ### ON SLAVE ### 
SHOW DATABASES;

-- connection master
-- echo ### ON MASTER ### 
DROP DATABASE dbx;

-- sync_slave_with_master

-- exit

2. Slave option file: (place it, for instance, in suite/rpl/t/rpl_filter_bug-slave.opt)

--replicate-do-db=dbx --replicate-wild-do-table=db%.t1

3. Run test case on MTR:

.../mysql-test$ ./mtr rpl.rpl_filter_bug

4. Notice that the absence of 'dbx' in the output of slave's SHOW DATABASES.

Suggested fix:
Make behavior consistent to manual description or document this.

Thank you for the report.

Verifeid as described. Workaround: don't use replicate-wild-do-table

Could you please add the result of executing SHOW BINLOG EVENTS after execution of the
CREATE DATABASE statement?

Output from version 5.1:

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 dbx;
### ON MASTER ### 
SHOW DATABASES;
Database
information_schema
dbx
mtr
mysql
test
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       106     Server ver:
5.1.39-debug-log, Binlog ver: 4
master-bin.000001       106     Query   1       187     CREATE DATABASE dbx
### ON SLAVE ### 
SHOW DATABASES;
Database
information_schema
mtr
mysql
test
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
slave-bin.000001        4       Format_desc     2       106     Server ver:
5.1.39-debug-log, Binlog ver: 4
### ON MASTER ### 
DROP DATABASE dbx;