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 2009 11:21	Modified:	29 Nov 2010 19:33
Reporter:	Luis Soares	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1, 4.1, 5.0, azalea	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	replicate-do-db, replicate-wild-do-db, rpl filters

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;

BUG#56362 has been marked as duplicate of this one.

Just a note that bug #56362 (duplicate of this one) was verified using 5.1.50.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/118618

3514 Luis Soares	2010-09-20
      BUG#46110: --replicate-wild-do-table invalidates 
                 --replicate-do-db matching rule
      
        When using --replicate-do-db and --replicate-ignore-db with
      --replicate-wild-do-table, some database level statements, eg
      DROP DATABASE or CREATE DATABASE, might not be replayed even if
      the DATABASE name was inline with the rules set in
      --replicate-do-db and/or --replicate-ignore-db. The following
      example would not replicate correctly:
      
        SLAVE, configured with: 
        --replicate-do-db=dbx --replicate-wild-do-table=db%.t1
      
        MASTER, dba issues:
        MASTER> CREATE DATABASE dbx;
      
        Although, at first sight, there is nothing in the filter rules
      that might prevent this statement to be replayed, the slave would
      not execute it. In fact, "replicate-wild-do-table=db%.t1" 
      invalidates the "OK" from "replicate-do-db=dbx", making the event
      to be skipped/filtered out.
      
        We fix this, by making --replicate-do/ignore-db rules take
      precedence over replicate-wild-do-table for *database* level
      statements only:
      
        - CREATE/DROP/ALTER DATABASE ...
      
        As such, when replicating such statements, we only look into
      replicate-wild-do-table if and only if replicate-do/ignore-db 
      rules are void. Otherwise, --replicate-do/ignore-db rules 
      dictate the fate of the statement: either filtered out or 
      executed.

Yes, I think the last patch is the way forward. This is probably what people would expect anyway.
However, it is a behavior change which might make trouble where people rely on this not to work.

Probably not good putting in MySQL 5.1 or 5.5 ..

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119484

3309 Luis Soares	2010-09-30
      BUG#46110: --replicate-wild-do-table invalidates 
                 --replicate-do-db matching rule
            
      When using --replicate-do-db and --replicate-ignore-db with
      --replicate-wild-do-table, some database level statements, eg
      DROP DATABASE or CREATE DATABASE, might not be replayed even if
      the DATABASE name was inline with the rules set in
      --replicate-do-db and/or --replicate-ignore-db. The following
      example would not replicate correctly:
            
        SLAVE, configured with: 
        --replicate-do-db=dbx --replicate-wild-do-table=db%.t1
            
        MASTER, dba issues:
        MASTER> CREATE DATABASE dbx;
            
      Although, at first sight, there is nothing in the filter rules
      that might prevent this statement to be replayed, the slave would
      not execute it. In fact, "replicate-wild-do-table=db%.t1" 
      invalidates the "OK" from "replicate-do-db=dbx", making the event
      to be skipped/filtered out.
            
      We fix this, by making --replicate-do/ignore-db rules take
      precedence over replicate-wild-do-table for *database* level
      statements only:
            
        - CREATE/DROP/ALTER DATABASE ...
            
      As such, when replicating such statements, we only look into
      replicate-wild-do-table if and only if replicate-do/ignore-db 
      rules are void. Otherwise, --replicate-do/ignore-db rules 
      dictate the fate of the statement: either filtered out or 
      executed.

Queued in mysql-next-mr-bugfixing:
- http://pb2.norway.sun.com/web.py?template=push_details&push=1627475

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)

Documented bugfix as follows in the 5.6.1 changelog:

        When determining whether to replicate a CREATE DATABASE, DROP
        DATABASE, or ALTER DATABASE statement, database-level options
        now take precedence over any --replicate-do-wild-table options. 
        In other words, when trying to replicate one of these statements, 
        --replicate-do-wild options are now checked if and only if there 
        are no database-level options that apply to the statement.

Also updated replication-rules section to note example observed by Luís et al. in pre-5.6 versions and new behavior in 5.6 version. See http://lists.mysql.com/commits/125417 for additional docs info.

Closed.