MySQL Bugs: #36099: replicate-do-db affects replaying RBR events with mysqlbinlog

Bug #36099	replicate-do-db affects replaying RBR events with mysqlbinlog
Submitted:	15 Apr 2008 19:47	Modified:	18 Oct 2008 12:26
Reporter:	David Shrewsbury	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Andrei Elkin	CPU Architecture:	Any

Description:
When replaying a binary log with mysqlbinlog, RBR events may not be executed if --replicate-do-db option is specified. If the event isn't for a database mentioned by this option, the event will not be executed.

Statement-based events are unaffected.

Workaround is to temporarily disable --replicate-do-db option.

How to repeat:
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
user = mysql
server-id = 1
log-bin = mysql-bin
binlog-format = row
replicate-do-db = junk

----------------

mysql> CREATE TABLE t (id INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

##########
SHELL> mysqldump -u root test > /tmp/test.sql
##########

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      266 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| id   |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      340 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE t;
Query OK, 0 rows affected (0.00 sec)

##########
SHELL> mysql -u root test < /tmp/test.sql
##########

mysql> SELECT * FROM t;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

##########
SHELL> mysqlbinlog --start-position=266 --stop-position=340 mysql-bin.000001 | mysql -u root
##########

mysql> SELECT * FROM t;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

I don't see this as a bug (please inform me if anyone disagrees :).

The following should be documented:
1. RBR events are filtered on "actual" db, not "used"
2. Filters are in effect also for BINLOG statements.

There seem to be some misunderstanding of what the issue is about. First, row-based replication filters on the actual table affected and this is clearly documented in the reference manual, but this is not the issue.

The replicate-do-db and replication-ignore-db flags, as well as the other replicate-* options, are effective for the replication, i.e., effective when the changes come via the slave threads, and filters changes for databases or tables out from the changes received from the *master*. This is documented "everywhere" in the manual and is not something that we should change.

When using mysqlbinlog to read a binary log with query log events only, we construct a sequence of SQL statements that can be fed to a server through a normal client thread. However, for all practical purposes, there is nothing specific with that SQL which means that it could not have been written by a normal user, i.e., not from the master. For that reason, it cannot be expected that a normal client thread should treat the output from mysqlbinlog as "master enchanted" SQL code and filter it as if it was sent from a master, simply because it cannot know that it is coming from a master.

And then comes the BINLOG statement. For all practical purposes, it is "just" SQL code in a special form, and for the same reason as above, we should not apply "master filtering rules" because we cannot know that it comes from a master and therefore it is unreasonable to expect it to filter the BINLOG statement as if it came from a slave thread when executed on a normal client thread.

I agree with the triage classification: particularly, I consider the workaround of disabling --replicate-do-db unacceptable since it requires stopping and starting the server.

So, I think there are two possible solutions:

1. Document that --replicate-do-db affect BINLOG statements.
2. Make client thread not check --replicate-do-db conditions.

I can agree with Mats that 1 is not the best solution, so lets aim for
solution 2.

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/53077

2677 Andrei Elkin	2008-09-02
      Bug #36099  replicate-do-db affects replaying RBR events with mysqlbinlog
      
      The bug is about to refuse execution of a BINLOG pseudo-query on the server.
      The reason of the bug appeared to be an inappropiate applying the replication slave side
      filtering rules.
      The rules are supposed to be active only at times when the slave's sql thread executes an
      event.
      
      Fixed with correcting a condition to call replication rules only if the slave sql thread
      executes the event.

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/53138

2677 Andrei Elkin	2008-09-03
      Bug #36099  replicate-do-db affects replaying RBR events with mysqlbinlog
      
      The bug is about to refuse execution of a BINLOG pseudo-query on the
      server.  The reason of the bug appeared to be an inappropiate applying
      the replication slave side filtering rules.  The rules are supposed to
      be active only at times when the slave's sql thread executes an event.
            
      Fixed with correcting a condition to call replication rules only if
      the slave sql thread executes the event.

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/53142

2677 Andrei Elkin	2008-09-03
      Bug #36099  	replicate-do-db affects replaying RBR events with mysqlbinlog
      
      The replication filtering rules were inappropiately applied when
      executing BINLOG pseudo-query.
      The rules are supposed to
      be active only at times when the slave's sql thread executes an event.
            
      Fixed with correcting a condition to call replication rules only if
      the slave sql thread executes the event.

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/53144

2677 Andrei Elkin	2008-09-03
      Bug#36099 replicate-do-db affects replaying RBR events with mysqlbinlog
            
      The replication filtering rules were inappropiately applied when
      executing BINLOG pseudo-query.  The rules are supposed to be active
      only at times when the slave's sql thread executes an event.
                  
      Fixed with correcting a condition to call replication rules only if
      the slave sql thread executes the event.

pushed to 5.1.29 trees.

Pushed into 5.1.29  (revid:aelkin@mysql.com-20080903100118-b945sjs20j2sxwvc) (version source revid:kgeorge@mysql.com-20080910094421-1i1kxv3n1bxskiqa) (pib:3)

Documented in the 5.1.29 changelog as follows:

        Replication filtering rules were inappropiately applied when executing
        BINLOG pseudo-queries. One way in which this problem showed itself was
        that, when replaying a binary log with mysqlbinlog, RBR events were
        sometimes not executed if --replicate-do-db option was specified. Now
        replication rules are applied only if the slave SQL thread executes the
        event.

Pushed into 5.1.29  (revid:aelkin@mysql.com-20080903100118-b945sjs20j2sxwvc) (version source revid:kgeorge@mysql.com-20080910094421-1i1kxv3n1bxskiqa) (pib:4)

Set status to NDI pending merge to 6.0.

Pushed into 6.0.8-alpha  (revid:aelkin@mysql.com-20080903100118-b945sjs20j2sxwvc) (version source revid:kpettersson@mysql.com-20080911114255-81pt7q1uvl1fkojq) (pib:5)

Bugfix is now also documented in the 6.0.8 changelog; closed.

Pushed into 5.1.29-ndb-6.2.17  (revid:aelkin@mysql.com-20080903100118-b945sjs20j2sxwvc) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:aelkin@mysql.com-20080903100118-b945sjs20j2sxwvc) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:aelkin@mysql.com-20080903100118-b945sjs20j2sxwvc) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)