MySQL Bugs: #50678: Restore does not stop replication when server option --binlog-do-db is enabled.

Bug #50678	Restore does not stop replication when server option --binlog-do-db is enabled.
Submitted:	27 Jan 2010 23:45	Modified:	19 Feb 2010 18:52
Reporter:	Hema Sridharan	Email Updates:
Status:	Patch queued	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	6.0-backup	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
* Set up master and slave servers and enable the server option --binlog-do-db=db1 (replication is possible only for db1 and db2 will be ignored)]
* Create database db1, db2 in master
* Create tables t11, t12 in db1 and t21, t22 in db2
* Check that db1 is replicated in slave and not db2
* Perform backup of database db1 in master
* Execute restore of db1 in master. 

Restore of db1 will not generate incident event when server option is enabled.

How to repeat:
Please find the attached test and result file. Repeat the test when server option --binlog-do-db=db1 is enabled.

As per the documentation, incident event should be generated when restore is performed of replicated database db1. In reality, this is not the case and the replication continues.

Test file

Attachment: rpl_backup_incident.test (application/test, text), 2.63 KiB.

Result file

Attachment: rpl_backup__incident.result (application/octet-stream, text), 3.30 KiB.

Thank you for the report.

Verified as described.

I think that incident event is generated by RESTORE but is either not written to binlog or not replicated because of the filtering option --binlog-do-db.

As Rafal has commented incidence event does gets generated 
but gets filtered because of  --binlog-do-db option.
If the default database is set to db1 (with --binlog-do-db=db1)
before performing restore the incidence event gets logged and
slave gets RESTORE_ON_MASTER incidence.

This behaviour is be in compliance  with binlog documentation.

http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_b...

As noted in the documentation except for CREATE DATABASE, 
ALTER DATABASE, and DROP DATABASE statements binlog events are 
filtered based on default database. So for incident event to be 
logged and propagated to slave the default database should be one 
which has binlog enabled.

Please let me know if the bug is about to include RESTORE operation in the
set of operations where the database named in the statement should be used
to decide whether to log or not the event.

Ingo clarified the issue setting it back to in progress.

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

3096 Anurag Shekhar	2010-02-16
      Bug #50678  Restore does not stop replication when server 
                  option --binlog-do-db is enabled.
      
      Binlog filtering depends on the default database 
      (set using "USE db"). Except for CREATE database, 
      ALTER database and DROP database the database in 
      the operations is not used for filtering. This causes 
      the INCIDENT event generation non intuitive, event 
      is generated if restore starts from a binlogged database 
      irrespective of whether the database being restored is 
      binlogged or not.
      
      To fix this problem changed restore to switch the default 
      database to a binlogged database, if one or more of the 
      database being restored is binloged, and unset the default 
      database (by setting it to NULL) if none of the database 
      is binlogged.
     @ mysql-test/suite/backup/r/backup_rpl_incident.result
        Result file for newly added test case.
     @ mysql-test/suite/backup/t/backup_rpl_incident-master.opt
        Option file to set binlog filtering for test case.
     @ mysql-test/suite/backup/t/backup_rpl_incident.test
        Test case to test incident event during restore.
     @ sql/backup/kernel.cc
        Added code to unset the default database if none 
        of the database being restored is binlogged and 
        set to a binlogged database if one or more database 
        being restored is binlogged.

Approved pending changes. Please see email for details.

Requested testing using different binlog formats (stmt, row, mixed).

Test should be renamed to rpl_backup_<something> and moved to ./mysql-test/suite/rpl.

This will ensure the test will be run with all binlog formats. See rpl_backup test for examples.

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

3101 Anurag Shekhar	2010-02-19
      3096 Anurag Shekhar	2010-02-16
      Bug #50678  Restore does not stop replication when server 
                  option --binlog-do-db is enabled.
      
      Binlog filtering depends on the default database 
      (set using "USE db"). Except for CREATE database, 
      ALTER database and DROP database the database in 
      the operations is not used for filtering. This causes 
      the INCIDENT event generation non intuitive, event 
      is generated if restore starts from a binlogged database 
      irrespective of whether the database being restored is 
      binlogged or not.
      
      To fix this problem changed restore to switch the default 
      database to a binlogged database, if one or more of the 
      database being restored is binloged, and unset the default 
      database (by setting it to NULL) if none of the database 
      is binlogged.
     @ mysql-test/suite/binlog/r/binlog_backup_incident.result
        Result file for newly added test case.
     @ mysql-test/suite/binlog/t/binlog_backup_incident-master.opt
        Option file to set binlog filtering for test case.
     @ mysql-test/suite/binlog/t/binlog_backup_incident.test
        Test case to test incident event during restore.
     @ sql/backup/kernel.cc
        Added code to unset the default database if none 
        of the database being restored is binlogged and 
        set to a binlogged database if one or more database 
        being restored is binlogged.

Good to push after fixing 1st reviewer's requests.

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

3101 Anurag Shekhar	2010-02-19
      3096 Anurag Shekhar	2010-02-16
      Bug #50678  Restore does not stop replication when server 
                  option --binlog-do-db is enabled.
      
      Binlog filtering depends on the default database 
      (set using "USE db"). Except for CREATE database, 
      ALTER database and DROP database the database in 
      the operations is not used for filtering. This causes 
      the INCIDENT event generation non intuitive, event 
      is generated if restore starts from a binlogged database 
      irrespective of whether the database being restored is 
      binlogged or not.
      
      To fix this problem changed restore to switch the default 
      database to a binlogged database, if one or more of the 
      database being restored is binlogged, and unset the default 
      database (by setting it to NULL) if none of the database 
      is binlogged.
     @ mysql-test/suite/binlog/r/binlog_backup_incident.result
        Result file for newly added test case.
     @ mysql-test/suite/binlog/t/binlog_backup_incident-master.opt
        Option file to set binlog filtering for test case.
     @ mysql-test/suite/binlog/t/binlog_backup_incident.test
        Test case to test incident event during restore.
     @ sql/backup/kernel.cc
        Added code to unset the default database if none 
        of the database being restored is binlogged and 
        set to a binlogged database if one or more database 
        being restored is binlogged.