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 21:47	Modified:	18 Oct 2008 14:26
Reporter:	David Shrewsbury
Status:	Closed
Category:	Server: RBR	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Andrei Elkin	Target Version:	5.1.29+
Triage:	D2 (Serious) / R2 (Low) / E2 (Low)

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)