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
Triage: Triaged: D2 (Serious)

[27 Jan 2010 23:45] Hema Sridharan
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.
[27 Jan 2010 23:46] Hema Sridharan
Test file

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

[27 Jan 2010 23:47] Hema Sridharan
Result file

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

[28 Jan 2010 6:37] Sveta Smirnova
Thank you for the report.

Verified as described.
[2 Feb 2010 10:09] Rafal Somla
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.
[8 Feb 2010 9:51] Anurag Shekhar
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.
[8 Feb 2010 10:36] Anurag Shekhar
Ingo clarified the issue setting it back to in progress.
[16 Feb 2010 7:51] 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/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.
[18 Feb 2010 13:47] Ingo Strüwing
Approved pending changes. Please see email for details.
[18 Feb 2010 15:56] Rafal Somla
Requested testing using different binlog formats (stmt, row, mixed).
[18 Feb 2010 16:26] Chuck Bell
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.
[19 Feb 2010 6:29] 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/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.
[19 Feb 2010 10:51] Rafal Somla
Good to push after fixing 1st reviewer's requests.
[19 Feb 2010 15:29] 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/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.