Bug #42829 binlogging enabled for all schemas regardless of binlog-db-db / binlog-ignore-db

• Submitted: 13 Feb 2009 12:41
• Modified: 21 Jun 2010 0:46
• Reporter: Nishant Deshpande
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.1.31, 5.1, 6.0 bzr
• OS: Linux (2.6.18-92.el5)
• Assigned to: Luis Soares
• CPU Architecture: Any
• Tags: binary log, binlog-do-db, binlog-ignore-db, ignored, schemas

[13 Feb 2009 12:41] Nishant Deshpande

Description:
i have schemas s1, s2, s3.

i specify in my.cnf:
binlog-do-db = s1

i expect that s2 and s3 will not be binlogged.

but, if i execute a statement on s2/s3 which would fail if binlogging was turned on for that schema, i get:
"""
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
"""

i would expect this *if* i had elected to binlog the schema on which i am carrying out this statement.

How to repeat:
[my.cnf]
transaction-isolation   = READ-COMMITTED
binlog-do-db = s1

===========================

mysql> create schema s2;
mysql> create table t1 (x int, y int);
mysql> create table t2 (x int, y int);
mysql> insert into t1 select * from t2;
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

Suggested fix:
this seems a regression and does not conform to stated functionality on the use of binlog-do-db (or binlog-ignore-db either if i put that in above).

[13 Feb 2009 16:57] Valeriy Kravchuk

Thank you for the problem report. Please, try to execute:

use s2;

immediately after

create schema s2;

and check if that will change anything.

[13 Feb 2009 20:13] Nishant Deshpande

db1102:root metrics_utf8_2> create database s2;
Query OK, 1 row affected (0.00 sec)

db1102:root metrics_utf8_2> use s2;
Database changed
db1102:root s2> create table t1 (x int, y int);
Query OK, 0 rows affected (0.00 sec)

db1102:root s2> create table t2 (x int, y int);
Query OK, 0 rows affected (0.00 sec)

db1102:root s2> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED | 
+----------------+
1 row in set (0.00 sec)

db1102:root s2> insert into t1 select * from t2;
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

[14 Feb 2009 13:01] Sveta Smirnova

Thank you for the report.

Verified as described. More likely feature request though.

Workaround: use session level binary log format.

[15 Feb 2009 3:53] Nishant Deshpande

do you mean switch to 'mixed' or 'row' binlog format in the session?

i believe for 5.1.31, you need 'super' permissions to switch binlog format. i cannot allow clients who want to work on a schema that i don't want to binlog to connect as super just for this workaround.

i feel like this is not a feature request, but a bug which will break existing users who expect that binlog-do-db / binlog-ignore-db will be respected. not respecting this will cause other problems as well.

[15 Feb 2009 8:35] Sveta Smirnova

Yes, I mean binlog_format row. Also user can change session sql_mode.

[23 Apr 2009 11:31] 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/72707

2840 Luis Soares	2009-04-23
      BUG#42829: binlogging enabled for all schemas regardless of
      binlog-db-db / binlog-ignore-db
      
      InnoDB will return an error if statement based replication is used
      along with transaction isolation level READ-COMMITTED (or stricter),
      even if the statement in question is filtered out according to the
      binlog-do-db rules set. In this case, an error should not be printed.
      
      This patch addresses this issue by extending the existing check in
      external_lock to take into account the filter rules before deciding to
      print an error. Furthermore, it also changes decide_logging_format to
      take into consideration whether the statement is filtered out from 
      binlog before decision is made.
     @ mysql-test/suite/binlog/r/binlog_mix_do_db.result
        Result file.
     @ mysql-test/suite/binlog/r/binlog_stm_do_db.result
        Result file.
     @ mysql-test/suite/binlog/t/binlog_mix_do_db.test
        Added test case for mixed format to check that statements are
        turned into unsafe and logged in rows and that the additional
        clause added in decide logging format produces no side-effects.
     @ mysql-test/suite/binlog/t/binlog_stm_do_db.test
        Test case for statement based logging. This format triggered
        the bug reported.
     @ sql/sql_base.cc
        Changed the check on decide_logging_format to take into account
        whether statement is filtered or not (and if it is in mixed mode)
        before making the decision on log format (error checking).
     @ sql/sql_class.cc
        Added the thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set.
     @ storage/innobase/handler/ha_innodb.cc
        Extended check in external_lock to take into consideration the
        filtering when deciding to throw an error.
     @ storage/innobase/handler/ha_innodb.h
        Added declaration of new hook.

[23 Apr 2009 11:36] Luis Soares

Somewhat related bugs: BUG#23051, BUG#40360.

[20 Jun 2009 13:58] 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/76763

2950 Luis Soares	2009-06-20
      BUG#42829: binlogging enabled for all schemas regardless of
      	   binlog-db-db / binlog-ignore-db
            
      InnoDB will return an error if statement based replication is used
      along with transaction isolation level READ-COMMITTED (or stricter),
      even if the statement in question is filtered out according to the
      binlog-do-db rules set. In this case, an error should not be printed.
            
      This patch addresses this issue by:
      
       1. adding a innodb compatibility hook that checks if statement is
          filtered (to be used when logging in STATEMENT mode).
      
       2. extending the existing check in external_lock to verify binlog
          filter hook before deciding to print an error.
      
       3. changing decide_logging_format, so that if statement is ignored,
          then no error is printed. This change also makes
          decide_logging_format to calculate capabilities for both, ROW and
          STMT formats always. 
      
          Collecting STMT mode capabilities is done considering all engines
          involved (this was the behavior before this patch).
      
          Collecting ROW mode capabilities considers binlog filtering
          rules. So, for each engine involved in the statement, its
          capabilities are conly considered if the table does not belong to
          a filtered database.
      
          Decision on which capabilities to take (for checking if logging is
          possible), depends on the logging format chosen.
      
       4. extending rpl_filter with a new method that checks for a list of
          tables if at least one of them belongs to a non filtered
          database. This is useful to find out if a statement logged in ROW
          mode has its all changes (possibly involving different databases)
          filtered. Consider the following:
      
            UPDATE db1.t1, db2.t2 SET db1.t1.a=1, db2.t2.a=2;
      
          if db1 and db2 are filtered and ROW mode is used, then statement
          is completely filtered from binlog. On the other hand, if only db1
          was to be filtered, then changes to db2 will make it into the
          binlog. This new method is most useful in decide_logging_format.
      
         
     @ mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_cleanup.test
        Clean up shared part of the test.
     @ mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_mix_row.test
        Part of the test for mixed and row mode.
     @ mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_prepare.test
        Shared part of the test. (Prepare section)
     @ mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_stm.test
        Part of the test for statement mode.
     @ mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row-master.opt
        Option file stating which database log.
     @ mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row.test
        Mixed and Row mode logging test.
     @ mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm-master.opt
        Option file stating which database log.
     @ mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm.test
        Statement mode logging test.
     @ sql/rpl_filter.cc
        Added Rpl_filter::db_ok(TABLE_LIST* tables) which checks if at least 
        one table in the list provided belongs to a database that is not
        filtered out.
     @ sql/rpl_filter.h
        Added 'bool db_ok(TABLE_LIST *tables)' method declaration.
     @ sql/sql_base.cc
        Reworked decide_logging_format to take into account filtering rules.
        
        After this patch, it decides to skip error reporting if statement is
        filtered out from binlog. Also, capabilities are calculated considering
        filtering rules.
     @ sql/sql_class.cc
        Added thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set.
     @ storage/innobase/handler/ha_innodb.cc
        Extended check in external_lock to take into consideration the
        filtering when deciding to throw an error.
     @ storage/innobase/handler/ha_innodb.h
        Added declaration of new hook.

[14 Jul 2009 20:37] 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/78689

[23 Jul 2009 13:00] 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/79190

3028 Luis Soares	2009-07-23
      BUG#42829: binlogging enabled for all schemas regardless of
      binlog-db-db / binlog-ignore-db
            
      InnoDB will return an error if statement based replication is
      used along with transaction isolation level READ-COMMITTED (or
      weaker), even if the statement in question is filtered out
      according to the binlog-do-db rules set. In this case, an error
      should not be raised.
            
      This patch addresses this issue by extending the existing check
      in external_lock to take into account binlog filtering rules
      before deciding to print an error. After BUG 39934 is fixed,
      decide_logging_format takes into account filtering rules for
      SBL. Thence, there is no need to change decide_logging_format as
      part of this bugfix.
     @ mysql-test/suite/binlog/r/binlog_stm_do_db.result
        Result file.
     @ mysql-test/suite/binlog/t/binlog_stm_do_db-master.opt
        Server option to filter all databases except the one with the
        name 'b42829'.
     @ mysql-test/suite/binlog/t/binlog_stm_do_db.test
        Test case for statement based logging. This format triggered
        the bug reported.
     @ sql/sql_class.cc
        Added the thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set.
     @ storage/innobase/handler/ha_innodb.cc
        Extended check in external_lock to take into consideration the
        filtering when deciding to throw an error.
     @ storage/innobase/handler/ha_innodb.h
        Added declaration of new hook.

[4 Sep 2009 11:22] 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/82448

3094 Luis Soares	2009-09-04
      BUG#42829: binlogging enabled for all schemas regardless of
      binlog-db-db / binlog-ignore-db
            
      InnoDB will return an error if statement based replication is used
      along with transaction isolation level READ-COMMITTED (or weaker),
      even if the statement in question is filtered out according to the
      binlog-do-db rules set. In this case, an error should not be printed.
            
      This patch addresses this issue by extending the existing check in
      external_lock to take into account the filter rules before deciding to
      print an error. Furthermore, it also changes decide_logging_format to
      take into consideration whether the statement is filtered out from 
      binlog before decision is made.
     @ sql/sql_base.cc
        Changed the check on decide_logging_format to take into account
        whether statement is filtered or not in SBR.
     @ sql/sql_class.cc
        Added the thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set.
     @ storage/innobase/handler/ha_innodb.cc
        Extended check in external_lock to take into consideration the
        filtering when deciding to throw an error.
     @ storage/innobase/handler/ha_innodb.h
        Added declaration of new hook.
     @ storage/innodb_plugin/handler/ha_innodb.cc
        Extended check in external_lock to take into consideration the
        filtering when deciding to throw an error.
     @ storage/innodb_plugin/handler/ha_innodb.h
        Added declaration of new hook.

[24 Sep 2009 14:53] 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/84520

3094 Luis Soares	2009-09-24
      BUG#42829: binlogging enabled for all schemas regardless of
      binlog-db-db / binlog-ignore-db
            
      InnoDB will return an error if statement based replication is used
      along with transaction isolation level READ-COMMITTED (or weaker),
      even if the statement in question is filtered out according to the
      binlog-do-db rules set. In this case, an error should not be printed.
            
      This patch addresses this issue by extending the existing check in
      external_lock to take into account the filter rules before deciding to
      print an error. Furthermore, it also changes decide_logging_format to
      take into consideration whether the statement is filtered out from 
      binlog before decision is made.
     @ sql/sql_base.cc
        Changed the check on decide_logging_format to take into account
        whether statement is filtered or not in SBR.
     @ sql/sql_class.cc
        Added the thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set.
     @ storage/innobase/handler/ha_innodb.cc
        Extended check in external_lock to take into consideration the
        filtering when deciding to throw an error.
     @ storage/innobase/handler/ha_innodb.h
        Added declaration of new hook.
     @ storage/innodb_plugin/handler/ha_innodb.cc
        Extended check in external_lock to take into consideration the
        filtering when deciding to throw an error.
     @ storage/innodb_plugin/handler/ha_innodb.h
        Added declaration of new hook.

[1 Nov 2009 23:13] 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/88875

3172 Luis Soares	2009-11-01 [merge]
      BUG#42829: manually merged approved bzr bundle from bug report.
      
      Conflicts
      =========
      
      Text conflict in sql/sql_class.cc
      1 conflicts encountered.

[1 Nov 2009 23:28] 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/88876

3661 Luis Soares	2009-11-01 [merge]
      BUG#42829: manual merge mysql-5.1-bugteam --> mysql-pe
      
      Conflicts
      =========
      
      Conflict adding files to storage/innodb_plugin.  Created directory.
      Conflict because storage/innodb_plugin is not versioned, but has versioned children.  Versioned directory.
      Conflict adding files to storage/innodb_plugin/handler.  Created directory.
      Conflict because storage/innodb_plugin/handler is not versioned, but has versioned children.  Versioned directory.
      Contents conflict in storage/innodb_plugin/handler/ha_innodb.cc
      Contents conflict in storage/innodb_plugin/handler/ha_innodb.h
      6 conflicts encountered.

[1 Nov 2009 23:38] Luis Soares

Queued in mysql-5.1-bugteam and mysql-pe.

[4 Nov 2009 9:25] Bugs System

Pushed into 5.1.41 (revid:[e-mail]-20091104092152-qz96bzlf2o1japwc) (version source revid:[e-mail]-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)

[10 Nov 2009 12:23] Jon Stephens

Documented bugfix in the 5.1.41 changelog as follows:

        When using statement-based replication and the transaction
        isolation level was set to READ COMMITTED or stricter, InnoDB
        returned an error even if the statement in question was filtered
        out according to the --binlog-do-db or --binlog-ignore-db rules 
        in effect at the time.

Closed.

[11 Nov 2009 6:52] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091110093407-rw5g8dys2baqkt67) (version source revid:[e-mail]-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)

[11 Nov 2009 7:00] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091109115615-nuohp02h8mdrz8m2) (version source revid:[e-mail]-20091105105814-cvc2tb7ogm4ewgnh) (merge vers: 5.5.0-beta) (pib:13)

[11 Nov 2009 14:42] Jon Stephens

Also documented in the 5.5.0 and 6.0.14 changelogs.

Closed.

[2 Dec 2009 8:06] Bugs System

Pushed into 5.1.42 (revid:[e-mail]-20091202080033-mndu4sxwx19lz2zs) (version source revid:[e-mail]-20091130120409-pe1abptka1mlq9qy) (merge vers: 5.1.42) (pib:13)

[3 Dec 2009 11:37] Jon Stephens

Already documented in 5.1.41 changelog; re-closed.

[16 Dec 2009 8:39] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091216083311-xorsasf5kopjxshf) (version source revid:[e-mail]-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

[16 Dec 2009 8:45] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091216082430-s0gtzibcgkv4pqul) (version source revid:[e-mail]-20091202140050-nh3ebk6s3bziv8cb) (merge vers: 5.5.0-beta) (pib:14)

[16 Dec 2009 8:52] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20091216083231-rp8ecpnvkkbhtb27) (version source revid:[e-mail]-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

[16 Dec 2009 16:08] Jon Stephens

Also documented in the 5.6.0 changelog. Closed.

[18 Dec 2009 10:32] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:48] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 11:03] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:17] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

[19 Dec 2009 8:48] Jon Stephens

No new changelog entries required. Closing.

[18 Jan 2010 12:06] Bugs System

Pushed into 6.0.14-alpha (revid:alik@ibmvm-20100118120357-hnzhgadkpzqfnvsc) (version source revid:alik@ibmvm-20100118115413-kd3klpine09yyktw) (merge vers: 6.0.14-alpha) (pib:16)

[18 Jan 2010 12:07] Bugs System

Pushed into mysql-next-mr (revid:alik@ibmvm-20100118120111-73dulkgc893it4r9) (version source revid:alik@ibmvm-20100118115335-0stecyzftqm7bqx6) (pib:16)

[18 Jan 2010 13:57] Jon Stephens

See previous comment.

[6 Mar 2010 10:53] Bugs System

Pushed into 5.5.3-m3 (revid:[e-mail]-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@linux-rbsx-20100118220048-5vnyqi5ghsbgmdsd) (merge vers: 5.5.99-m3) (pib:16)

[8 Mar 2010 0:15] Paul DuBois

5.6.0 changelog entry unneeded.

Already fixed in earlier 5.5.x.

[12 Mar 2010 14:12] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

[12 Mar 2010 14:28] Bugs System

Pushed into 5.1.44-ndb-6.2.19 (revid:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

[12 Mar 2010 14:44] Bugs System

Pushed into 5.1.44-ndb-6.3.33 (revid:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

[12 Mar 2010 17:43] Paul DuBois

Fixed in earlier 5.1.x, 5.5.x.

[5 May 2010 15:12] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 11:21] Jon Stephens

Already fixed in 5.1. Closed.

[28 May 2010 6:11] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:39] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:07] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 15:02] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:13] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:29] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 12:16] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:03] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:43] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)