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
Triage: D2 (Serious) / R2 (Low) / E2 (Low)

[15 Apr 2008 19:47] David Shrewsbury
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)
[13 May 2008 14:37] Lars Thalmann
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.
[13 May 2008 19:30] Mats Kindahl
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.
[15 May 2008 16:11] Lars Thalmann
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.
[2 Sep 2008 15:49] Bugs System
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.
[3 Sep 2008 9:32] Bugs System
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.
[3 Sep 2008 9:57] Bugs System
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.
[3 Sep 2008 10:01] Bugs System
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.
[8 Sep 2008 11:20] Andrei Elkin
pushed to 5.1.29 trees.
[15 Sep 2008 8:35] Bugs System
Pushed into 5.1.29  (revid:aelkin@mysql.com-20080903100118-b945sjs20j2sxwvc) (version source revid:kgeorge@mysql.com-20080910094421-1i1kxv3n1bxskiqa) (pib:3)
[15 Sep 2008 14:45] Jon Stephens
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.
[1 Oct 2008 16:02] Bugs System
Pushed into 5.1.29  (revid:aelkin@mysql.com-20080903100118-b945sjs20j2sxwvc) (version source revid:kgeorge@mysql.com-20080910094421-1i1kxv3n1bxskiqa) (pib:4)
[7 Oct 2008 22:32] Jon Stephens
Set status to NDI pending merge to 6.0.
[17 Oct 2008 16:44] Bugs System
Pushed into 6.0.8-alpha  (revid:aelkin@mysql.com-20080903100118-b945sjs20j2sxwvc) (version source revid:kpettersson@mysql.com-20080911114255-81pt7q1uvl1fkojq) (pib:5)
[18 Oct 2008 12:26] Jon Stephens
Bugfix is now also documented in the 6.0.8 changelog; closed.
[28 Oct 2008 21:04] Bugs System
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)
[28 Oct 2008 22:23] Bugs System
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)
[1 Nov 2008 9:48] Bugs System
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)