List:Commits« Previous MessageNext Message »
From:Luis Soares Date:June 20 2009 1:57pm
Subject:bzr commit into mysql-5.1-bugteam branch (luis.soares:2950) Bug#42829
View as plain text  
#At file:///home/lsoares/Workspace/mysql-server/bugfix/42829/5.1-bt/ based on revid:joro@stripped

 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.

    added:
      mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_cleanup.test
      mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_mix_row.test
      mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_prepare.test
      mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_stm.test
      mysql-test/suite/rpl/r/rpl_innodb_rc_do_db_mix_row.result
      mysql-test/suite/rpl/r/rpl_innodb_rc_do_db_stm.result
      mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row-master.opt
      mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row.test
      mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm-master.opt
      mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm.test
    modified:
      sql/rpl_filter.cc
      sql/rpl_filter.h
      sql/sql_base.cc
      sql/sql_class.cc
      storage/innobase/handler/ha_innodb.cc
      storage/innobase/handler/ha_innodb.h
=== added file 'mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_cleanup.test'
--- a/mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_cleanup.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_cleanup.test	2009-06-20 13:57:39 +0000
@@ -0,0 +1,10 @@
+#
+# BUG#42829
+#   Shared part of the test for cleanup
+#
+
+# cleanup
+-- eval DROP DATABASE $not_filtered
+-- eval DROP DATABASE $filtered
+
+SET @@tx_isolation= @old_isolation_level;

=== added file 'mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_mix_row.test'
--- a/mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_mix_row.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_mix_row.test	2009-06-20 13:57:39 +0000
@@ -0,0 +1,91 @@
+# BUG#42829
+
+-- echo ### assertion: assert that filtered events no longer throw error
+-- eval use $filtered
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t1 SELECT * FROM t2;
+
+-- echo ### assertion: assert that events not filtered make it into binlog
+-- eval use $not_filtered
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t1 SELECT * FROM t2;
+
+-- sync_slave_with_master
+
+-- let $diff_table_1=slave:$not_filtered.t1
+-- let $diff_table_2=master:$not_filtered.t1
+-- source include/diff_tables.inc
+
+-- let $diff_table_1=slave:$not_filtered.t2
+-- let $diff_table_2=master:$not_filtered.t2
+-- source include/diff_tables.inc
+
+-- connection master
+
+-- echo ### assertion: assert that statements referencing filtered tables
+-- echo ###            while using "not filtered database" don't throw error
+
+-- eval INSERT INTO $filtered.t1 VALUES (1, 2)
+-- eval INSERT INTO $filtered.t3 VALUES (UUID())
+
+-- echo ### assertion: assert that multi-value multi-table update succeeds
+-- echo ###            on not filtered database
+
+-- eval use $not_filtered
+DELETE FROM t1;
+INSERT INTO t1 VALUES (1,2);
+INSERT INTO t1 VALUES (1,3);
+
+DELETE FROM t2;
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t2 VALUES (1,3);
+
+UPDATE t1, t2 SET t1.y=4, t2.y=3;
+
+-- sync_slave_with_master
+
+-- let $diff_table_1=slave:$not_filtered.t1
+-- let $diff_table_2=master:$not_filtered.t1
+-- source include/diff_tables.inc
+
+-- let $diff_table_1=slave:$not_filtered.t2
+-- let $diff_table_2=master:$not_filtered.t2
+-- source include/diff_tables.inc
+
+-- connection master
+
+-- echo ### assertion: assert that multi-value multi-table update succeeds
+-- echo ###            while cross-referencing filtered and not filtered
+-- echo ###            databases 
+
+
+-- eval use $not_filtered
+-- eval DELETE FROM t2;
+
+-- eval INSERT INTO $not_filtered.t2 VALUES (8,1);
+-- eval INSERT INTO $not_filtered.t2 VALUES (8,1);
+
+-- eval use $filtered
+DELETE FROM t1;
+
+INSERT INTO t1 VALUES (8,2);
+INSERT INTO t1 VALUES (8,3);
+
+INSERT INTO t2 VALUES (8,3);
+INSERT INTO t2 VALUES (8,3);
+
+-- eval UPDATE $filtered.t1 ft1, $not_filtered.t2 nft2 SET ft1.y=4, nft2.y=3;
+
+-- sync_slave_with_master
+
+-- eval SELECT * FROM $not_filtered.t2;
+
+-- connection master
+
+-- eval SELECT * FROM $not_filtered.t2;
+
+-- let $diff_table_1=slave:$not_filtered.t2
+-- let $diff_table_2=master:$not_filtered.t2
+-- source include/diff_tables.inc
+
+-- connection master

=== added file 'mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_prepare.test'
--- a/mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_prepare.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_prepare.test	2009-06-20 13:57:39 +0000
@@ -0,0 +1,19 @@
+#
+# BUG#42829
+#   Shared part of the test for prepare.
+#
+
+SET @old_isolation_level= @@tx_isolation;
+SET tx_isolation= 'READ-COMMITTED';
+
+-- eval CREATE DATABASE $not_filtered
+-- eval use $not_filtered
+-- eval CREATE TABLE t1 (x int, y int) engine=$engine
+-- eval CREATE TABLE t2 (x int, y int) engine=$engine
+-- eval CREATE TABLE t3 (x varchar(100)) engine=$engine
+
+-- eval CREATE DATABASE $filtered
+-- eval use $filtered
+-- eval CREATE TABLE t1 (x int, y int) engine=$engine
+-- eval CREATE TABLE t2 (x int, y int) engine=$engine
+-- eval CREATE TABLE t3 (x varchar(100)) engine=$engine

=== added file 'mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_stm.test'
--- a/mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_stm.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_stm.test	2009-06-20 13:57:39 +0000
@@ -0,0 +1,91 @@
+# BUG#42829
+
+-- echo ### assertion: assert that filtered events no longer throw error
+-- eval use $filtered
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t1 SELECT * FROM t2;
+
+-- echo ### assertion: assert that events not filtered do throw error 
+-- eval use $not_filtered
+-- error 1598
+INSERT INTO t2 VALUES (1,2);
+-- error 1598
+INSERT INTO t1 SELECT * FROM t2;
+
+-- echo ### comparing tables on master/slave. Should hold no values
+-- sync_slave_with_master
+-- eval use $not_filtered
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+-- connection master
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+-- echo ### assertion: assert that multi-values multi-table update fails
+-- echo ###            on not filtered database
+
+-- eval use $not_filtered
+SET SQL_LOG_BIN=0;
+DELETE FROM t1;
+DELETE FROM t2;
+INSERT INTO t1 VALUES (9,2);
+INSERT INTO t1 VALUES (9,3);
+INSERT INTO t2 VALUES (9,2);
+INSERT INTO t2 VALUES (9,3);
+SET SQL_LOG_BIN=1;
+
+-- error 1598
+UPDATE t1,t2 SET t1.y=4, t2.y=4 WHERE x=9;
+
+-- echo ### assertion: assert that multi-values multi-table update succeeds
+-- echo ###            while cross-referencing filtered and not filtered
+-- echo ###            databases but statement is filtered (master and slave
+-- echo ###            become inconsistent because in STMT mode filtering is
+-- echo ###            done based on used database - in this case is $filtered)
+
+-- eval use $filtered
+-- eval DELETE FROM $filtered.t1;
+-- eval DELETE FROM $not_filtered.t1;
+
+INSERT INTO t1 VALUES (8,2);
+INSERT INTO t1 VALUES (8,3);
+-- eval INSERT INTO $not_filtered.t1 VALUES (8,2);
+-- eval INSERT INTO $not_filtered.t1 VALUES (8,2);
+
+-- eval UPDATE $filtered.t1 ft1, $not_filtered.t1 nft1 SET ft1.y=1000, nft1.y=100;
+
+# compare not_filtered contents on slave and master
+-- sync_slave_with_master
+-- eval use $not_filtered
+SELECT * FROM t1 WHERE y=100;
+
+-- connection master
+-- eval use $not_filtered
+SELECT * FROM t1 WHERE y=100;
+
+-- echo ### assertion: assert that multi-value multi-table update succeeds
+-- echo ###            while cross-referencing filtered and not filtered
+-- echo ###            databases  (statement is filterd because used database
+-- echo ###            is filtered)
+
+-- eval use $filtered
+SET SQL_LOG_BIN=0;
+-- eval DELETE FROM $filtered.t1;
+-- eval DELETE FROM $not_filtered.t2;
+
+INSERT INTO t1 VALUES (7,1);
+INSERT INTO t1 VALUES (7,1);
+-- eval INSERT INTO $not_filtered.t2 VALUES (7,1);
+-- eval INSERT INTO $not_filtered.t2 VALUES (7,1);
+SET SQL_LOG_BIN=1;
+
+-- eval UPDATE $filtered.t1 ft1, $not_filtered.t2 nft2 SET ft1.y=100, nft2.y=100;
+
+# compare not_filtered contents on slave and master
+-- sync_slave_with_master
+-- eval use $not_filtered
+SELECT * FROM t1 WHERE y=100;
+
+-- connection master
+SELECT * FROM t1 WHERE y=100;

=== added file 'mysql-test/suite/rpl/r/rpl_innodb_rc_do_db_mix_row.result'
--- a/mysql-test/suite/rpl/r/rpl_innodb_rc_do_db_mix_row.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_innodb_rc_do_db_mix_row.result	2009-06-20 13:57:39 +0000
@@ -0,0 +1,70 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+SET @old_isolation_level= @@tx_isolation;
+SET tx_isolation= 'READ-COMMITTED';
+CREATE DATABASE b42829;
+use b42829;
+CREATE TABLE t1 (x int, y int) engine=InnoDB;
+CREATE TABLE t2 (x int, y int) engine=InnoDB;
+CREATE TABLE t3 (x varchar(100)) engine=InnoDB;
+CREATE DATABASE b42829_filtered;
+use b42829_filtered;
+CREATE TABLE t1 (x int, y int) engine=InnoDB;
+CREATE TABLE t2 (x int, y int) engine=InnoDB;
+CREATE TABLE t3 (x varchar(100)) engine=InnoDB;
+### assertion: assert that filtered events no longer throw error
+use b42829_filtered;
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t1 SELECT * FROM t2;
+### assertion: assert that events not filtered make it into binlog
+use b42829;
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t1 SELECT * FROM t2;
+Comparing tables slave:b42829.t1 and master:b42829.t1
+Comparing tables slave:b42829.t2 and master:b42829.t2
+### assertion: assert that statements referencing filtered tables
+###            while using "not filtered database" don't throw error
+INSERT INTO b42829_filtered.t1 VALUES (1, 2);
+INSERT INTO b42829_filtered.t3 VALUES (UUID());
+### assertion: assert that multi-value multi-table update succeeds
+###            on not filtered database
+use b42829;
+DELETE FROM t1;
+INSERT INTO t1 VALUES (1,2);
+INSERT INTO t1 VALUES (1,3);
+DELETE FROM t2;
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t2 VALUES (1,3);
+UPDATE t1, t2 SET t1.y=4, t2.y=3;
+Comparing tables slave:b42829.t1 and master:b42829.t1
+Comparing tables slave:b42829.t2 and master:b42829.t2
+### assertion: assert that multi-value multi-table update succeeds
+###            while cross-referencing filtered and not filtered
+###            databases 
+use b42829;
+DELETE FROM t2;;
+INSERT INTO b42829.t2 VALUES (8,1);;
+INSERT INTO b42829.t2 VALUES (8,1);;
+use b42829_filtered;
+DELETE FROM t1;
+INSERT INTO t1 VALUES (8,2);
+INSERT INTO t1 VALUES (8,3);
+INSERT INTO t2 VALUES (8,3);
+INSERT INTO t2 VALUES (8,3);
+UPDATE b42829_filtered.t1 ft1, b42829.t2 nft2 SET ft1.y=4, nft2.y=3;;
+SELECT * FROM b42829.t2;;
+x	y
+8	3
+8	3
+SELECT * FROM b42829.t2;;
+x	y
+8	3
+8	3
+Comparing tables slave:b42829.t2 and master:b42829.t2
+DROP DATABASE b42829;
+DROP DATABASE b42829_filtered;
+SET @@tx_isolation= @old_isolation_level;

=== added file 'mysql-test/suite/rpl/r/rpl_innodb_rc_do_db_stm.result'
--- a/mysql-test/suite/rpl/r/rpl_innodb_rc_do_db_stm.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_innodb_rc_do_db_stm.result	2009-06-20 13:57:39 +0000
@@ -0,0 +1,96 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+SET @old_isolation_level= @@tx_isolation;
+SET tx_isolation= 'READ-COMMITTED';
+CREATE DATABASE b42829;
+use b42829;
+CREATE TABLE t1 (x int, y int) engine=InnoDB;
+CREATE TABLE t2 (x int, y int) engine=InnoDB;
+CREATE TABLE t3 (x varchar(100)) engine=InnoDB;
+CREATE DATABASE b42829_filtered;
+use b42829_filtered;
+CREATE TABLE t1 (x int, y int) engine=InnoDB;
+CREATE TABLE t2 (x int, y int) engine=InnoDB;
+CREATE TABLE t3 (x varchar(100)) engine=InnoDB;
+### assertion: assert that filtered events no longer throw error
+use b42829_filtered;
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t1 SELECT * FROM t2;
+### assertion: assert that events not filtered do throw error 
+use b42829;
+INSERT INTO t2 VALUES (1,2);
+ERROR HY000: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
+INSERT INTO t1 SELECT * FROM t2;
+ERROR HY000: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
+### comparing tables on master/slave. Should hold no values
+use b42829;
+SELECT * FROM t1;
+x	y
+SELECT * FROM t2;
+x	y
+SELECT * FROM t1;
+x	y
+SELECT * FROM t2;
+x	y
+### assertion: assert that multi-values multi-table update fails
+###            on not filtered database
+use b42829;
+SET SQL_LOG_BIN=0;
+DELETE FROM t1;
+DELETE FROM t2;
+INSERT INTO t1 VALUES (9,2);
+INSERT INTO t1 VALUES (9,3);
+INSERT INTO t2 VALUES (9,2);
+INSERT INTO t2 VALUES (9,3);
+SET SQL_LOG_BIN=1;
+UPDATE t1,t2 SET t1.y=4, t2.y=4 WHERE x=9;
+ERROR HY000: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
+### assertion: assert that multi-values multi-table update succeeds
+###            while cross-referencing filtered and not filtered
+###            databases but statement is filtered (master and slave
+###            become inconsistent because in STMT mode filtering is
+###            done based on used database - in this case is b42829_filtered)
+use b42829_filtered;
+DELETE FROM b42829_filtered.t1;;
+DELETE FROM b42829.t1;;
+INSERT INTO t1 VALUES (8,2);
+INSERT INTO t1 VALUES (8,3);
+INSERT INTO b42829.t1 VALUES (8,2);;
+INSERT INTO b42829.t1 VALUES (8,2);;
+UPDATE b42829_filtered.t1 ft1, b42829.t1 nft1 SET ft1.y=1000, nft1.y=100;;
+use b42829;
+SELECT * FROM t1 WHERE y=100;
+x	y
+use b42829;
+SELECT * FROM t1 WHERE y=100;
+x	y
+8	100
+8	100
+### assertion: assert that multi-value multi-table update succeeds
+###            while cross-referencing filtered and not filtered
+###            databases  (statement is filterd because used database
+###            is filtered)
+use b42829_filtered;
+SET SQL_LOG_BIN=0;
+DELETE FROM b42829_filtered.t1;;
+DELETE FROM b42829.t2;;
+INSERT INTO t1 VALUES (7,1);
+INSERT INTO t1 VALUES (7,1);
+INSERT INTO b42829.t2 VALUES (7,1);;
+INSERT INTO b42829.t2 VALUES (7,1);;
+SET SQL_LOG_BIN=1;
+UPDATE b42829_filtered.t1 ft1, b42829.t2 nft2 SET ft1.y=100, nft2.y=100;;
+use b42829;
+SELECT * FROM t1 WHERE y=100;
+x	y
+SELECT * FROM t1 WHERE y=100;
+x	y
+7	100
+7	100
+DROP DATABASE b42829;
+DROP DATABASE b42829_filtered;
+SET @@tx_isolation= @old_isolation_level;

=== added file 'mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row-master.opt'
--- a/mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row-master.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row-master.opt	2009-06-20 13:57:39 +0000
@@ -0,0 +1 @@
+--binlog-do-db=b42829

=== added file 'mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row.test'
--- a/mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row.test	2009-06-20 13:57:39 +0000
@@ -0,0 +1,28 @@
+#
+# BUG#42829: binlogging enabled for all schemas regardless of binlog-db-db /
+# binlog-ignore-db
+#
+# DESCRIPTION
+# ===========
+#
+#   The test is implemented as follows:
+#
+#     i) set tx_isolation to read-committed.
+#    ii) create two databases (one filtered other not - using binlog-do-db)
+#   iii) Create statements that are to be filtered on filtered db
+#       - At this point before the patch an error would be thrown.
+#    iv) Do some other combinations of statements and filtered/not filtered
+#        databases and check expected result. See
+#        extra/rpl_tests/rpl_innodb_rc_do_db_mixed_or_row.test for details.
+
+-- source include/master-slave.inc
+-- source include/have_innodb.inc
+-- source include/have_binlog_format_mixed_or_row.inc
+
+-- let $engine= InnoDB
+-- let $filtered= b42829_filtered
+-- let $not_filtered= b42829
+
+-- source extra/rpl_tests/rpl_innodb_rc_do_db_prepare.test
+-- source extra/rpl_tests/rpl_innodb_rc_do_db_mix_row.test
+-- source extra/rpl_tests/rpl_innodb_rc_do_db_cleanup.test

=== added file 'mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm-master.opt'
--- a/mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm-master.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm-master.opt	2009-06-20 13:57:39 +0000
@@ -0,0 +1 @@
+--binlog-do-db=b42829

=== added file 'mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm.test'
--- a/mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm.test	2009-06-20 13:57:39 +0000
@@ -0,0 +1,28 @@
+#
+# BUG#42829: binlogging enabled for all schemas regardless of binlog-db-db /
+# binlog-ignore-db
+#
+# DESCRIPTION
+# ===========
+#
+#   The test is implemented as follows:
+#
+#     i) set tx_isolation to read-committed.
+#    ii) create two databases (one filtered other not - using binlog-do-db)
+#   iii) Create statements that are to be filtered on filtered db
+#       - At this point before the patch an error would be thrown.
+#    iv) Do some other combinations of statements and filtered/not filtered
+#        databases and check expected result. See
+#        extra/rpl_tests/rpl_innodb_rc_do_db_stm.test for details.
+
+-- source include/master-slave.inc
+-- source include/have_innodb.inc
+-- source include/have_binlog_format_statement.inc
+
+-- let $engine= InnoDB
+-- let $filtered= b42829_filtered
+-- let $not_filtered= b42829
+
+-- source extra/rpl_tests/rpl_innodb_rc_do_db_prepare.test
+-- source extra/rpl_tests/rpl_innodb_rc_do_db_stm.test
+-- source extra/rpl_tests/rpl_innodb_rc_do_db_cleanup.test

=== modified file 'sql/rpl_filter.cc'
--- a/sql/rpl_filter.cc	2009-04-29 02:59:10 +0000
+++ b/sql/rpl_filter.cc	2009-06-20 13:57:39 +0000
@@ -129,6 +129,30 @@ Rpl_filter::tables_ok(const char* db, TA
               !do_table_inited && !wild_do_table_inited);
 }
 
+/*
+  Checks if at least one table in the list belongs to a database that is not
+  filtered out.
+
+  SYNOPSIS
+    db_ok()
+    tables          list of tables accessed
+
+  RETURN VALUES
+    0           no table belongs to database not filtered out
+    1           some table(s) belong(s) to database(s) not filtered out
+*/
+
+bool
+Rpl_filter::db_ok(TABLE_LIST* tables)
+{
+  bool res= FALSE;
+  for (; tables; tables= tables->next_global)
+  {
+    res= res || this->db_ok(tables->db);
+  }
+
+  return res;
+} 
 
 /*
   Checks whether a db matches some do_db and ignore_db rules

=== modified file 'sql/rpl_filter.h'
--- a/sql/rpl_filter.h	2007-05-10 09:59:39 +0000
+++ b/sql/rpl_filter.h	2009-06-20 13:57:39 +0000
@@ -44,6 +44,7 @@ public:
 
   bool tables_ok(const char* db, TABLE_LIST* tables);
   bool db_ok(const char* db);
+  bool db_ok(TABLE_LIST *tables);
   bool db_ok_with_wild_table(const char *db);
 
   bool is_on();

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2009-05-30 13:32:28 +0000
+++ b/sql/sql_base.cc	2009-06-20 13:57:39 +0000
@@ -5050,7 +5050,6 @@ static void mark_real_tables_as_free_for
       table->table->query_id= 0;
 }
 
-
 /**
    Decide on logging format to use for the statement.
 
@@ -5097,12 +5096,17 @@ int decide_logging_format(THD *thd, TABL
       set with all the capabilities bits set and one with no
       capabilities bits set.
      */
+    handler::Table_flags flags_some_set_row= 0;
     handler::Table_flags flags_some_set= 0;
     handler::Table_flags flags_all_set=
       HA_BINLOG_ROW_CAPABLE | HA_BINLOG_STMT_CAPABLE;
+    handler::Table_flags flags_all_set_row=
+      HA_BINLOG_ROW_CAPABLE | HA_BINLOG_STMT_CAPABLE;
 
+    my_bool log_as_row= FALSE;
     my_bool multi_engine= FALSE;
     void* prev_ht= NULL;
+    int error= 0;
     for (TABLE_LIST *table= tables; table; table= table->next_global)
     {
       if (table->placeholder())
@@ -5119,8 +5123,22 @@ int decide_logging_format(THD *thd, TABL
         if (prev_ht && prev_ht != table->table->file->ht)
           multi_engine= TRUE;
         prev_ht= table->table->file->ht;
+        /*
+          Collect capabilities for engines involved in the
+          statement. These are the capabilities to check when logging
+          in statement mode.
+        */
         flags_all_set &= flags;
         flags_some_set |= flags;
+        /*
+          Collect capabilities for tables in databases that would not
+          be filtered when logging in row mode.
+        */
+        if (binlog_filter->db_ok(table->db))
+        {
+          flags_all_set_row &= flags;
+          flags_some_set_row |= flags;
+        }
       }
     }
 
@@ -5135,23 +5153,98 @@ int decide_logging_format(THD *thd, TABL
     DBUG_PRINT("info", ("multi_engine: %s",
                         multi_engine ? "TRUE" : "FALSE"));
 
-    int error= 0;
+    /**
+      Flag stating whether this statement should be logged as row or
+      not.
+    */
+    log_as_row= (thd->variables.binlog_format==BINLOG_FORMAT_ROW ||
+		 (thd->variables.binlog_format==BINLOG_FORMAT_MIXED && 
+		  (thd->lex->is_stmt_unsafe() || 
+		   (flags_all_set & HA_BINLOG_STMT_CAPABLE) ==0)));
+
+    /**
+      Check whether statement is filtered from binlog or not.
+    */
+    if ((log_as_row && !binlog_filter->db_ok(tables)) || 
+	(!log_as_row && !binlog_filter->db_ok(thd->db)))
+    {
+
+      /*	
+	We are filtering this statement from log. As such, we skip
+	error checking.
+
+	Still, we mark statement to be logged as ROW if in MIXED and
+	switch should happen. This way, the statement is subject to
+	the same filtering rules when the time comes to log it, that
+	were considered here.
+      */
+      goto end;
+    }
+    else
+    {
+      /*        
+	Choose which capabilities to take based on the filtering
+	analysis and the binlog format that one is to choose.
+        
+	So, if using RBR or MIXED+switch_to_row, the capabilities
+	considered are the ones collected for events on *not* filtered
+	tables (databases).
+
+	For STMT or MIXED+not_switch_to_row, the capabilities to
+	consider are the ones collected from all the engines involved.
+
+	Q. What is this trying to solve?
+        A. Some cases involving filtered and not filtered out
+           databases, in the same statement and when using row mode
+           logging. Consider scenario description and questions below.
+
+        Scenario:
+          1. MIXED mode
+          2. filtered: db2; !filtered: db1
+          3. capabilities: db1 => ROW, db2 => STMT
+          4. UPDATE db1.t1 A, db2.t1 B SET A.x=1, B.x=2;
+
+	Q. Which is the value of flags_all_set_row?
+	A. HA_BINLOG_ROW_CAPABLE, because, db2 is filtered (MIXED
+           should switch to row because flags_all_set would not have STMT
+	   capability).
+
+	Q. Which is the value of flags_all_set?
+	A. 0
+
+	Q. This statement should log the update on db1.t1 when using
+           ROW and MIXED mode. As such, errors should not be thrown
+           for this statement. In this case, which capabilities flags
+           should one use to conduct error checking?
+	A. flags_all_set_row
+
+	Basically, when one finds that is indeed logging in ROW mode,
+	then the vector of capabilities should just hold the
+	capabilities for the engines which are referenced by tables in
+	databases not filtered out from binlog.
+      */
+      if (log_as_row)
+      {
+        flags_all_set= flags_all_set_row;
+	flags_some_set= flags_some_set_row;
+      }
+    }
+
     if (flags_all_set == 0)
     {
       my_error((error= ER_BINLOG_LOGGING_IMPOSSIBLE), MYF(0),
                "Statement cannot be logged to the binary log in"
                " row-based nor statement-based format");
     }
-    else if (thd->variables.binlog_format == BINLOG_FORMAT_STMT &&
+    else if (!log_as_row &&
              (flags_all_set & HA_BINLOG_STMT_CAPABLE) == 0)
     {
       my_error((error= ER_BINLOG_LOGGING_IMPOSSIBLE), MYF(0),
                 "Statement-based format required for this statement,"
                 " but not allowed by this combination of engines");
     }
-    else if ((thd->variables.binlog_format == BINLOG_FORMAT_ROW ||
-              thd->lex->is_stmt_unsafe()) &&
-             (flags_all_set & HA_BINLOG_ROW_CAPABLE) == 0)
+    else if (log_as_row && 
+	     (flags_all_set & HA_BINLOG_ROW_CAPABLE) == 0)
     {
       my_error((error= ER_BINLOG_LOGGING_IMPOSSIBLE), MYF(0),
                 "Row-based format required for this statement,"
@@ -5179,6 +5272,7 @@ int decide_logging_format(THD *thd, TABL
     if (error)
       return -1;
 
+end:
     /*
       We switch to row-based format if we are in mixed mode and one of
       the following are true:
@@ -5191,8 +5285,7 @@ int decide_logging_format(THD *thd, TABL
       this code in reset_current_stmt_binlog_row_based(), it has to be
       here.
     */
-    if (thd->lex->is_stmt_unsafe() ||
-        (flags_all_set & HA_BINLOG_STMT_CAPABLE) == 0)
+    if (log_as_row)
     {
       thd->set_current_stmt_binlog_row_based_if_mixed();
     }

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2009-06-15 15:53:45 +0000
+++ b/sql/sql_class.cc	2009-06-20 13:57:39 +0000
@@ -41,6 +41,7 @@
 
 #include "sp_rcontext.h"
 #include "sp_cache.h"
+#include "rpl_filter.h"
 
 /*
   The following is used to initialise Table_ident with a internal
@@ -2912,6 +2913,11 @@ extern "C" void thd_mark_transaction_to_
 {
   mark_transaction_to_rollback(thd, all);
 }
+
+extern "C" bool thd_binlog_filter_ok(const MYSQL_THD thd)
+{
+  return binlog_filter->db_ok(thd->db);
+}
 #endif // INNODB_COMPATIBILITY_HOOKS */
 
 /****************************************************************************

=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc	2009-05-19 08:20:28 +0000
+++ b/storage/innobase/handler/ha_innodb.cc	2009-06-20 13:57:39 +0000
@@ -6843,8 +6843,10 @@ ha_innobase::external_lock(
 	{
 		ulong const binlog_format= thd_binlog_format(thd);
 		ulong const tx_isolation = thd_tx_isolation(current_thd);
+                bool const is_binlog_filter_ok= thd_binlog_filter_ok(thd);
 		if (tx_isolation <= ISO_READ_COMMITTED &&
-		    binlog_format == BINLOG_FORMAT_STMT)
+		    binlog_format == BINLOG_FORMAT_STMT &&
+                    is_binlog_filter_ok)
 		{
 			char buf[256];
 			my_snprintf(buf, sizeof(buf),

=== modified file 'storage/innobase/handler/ha_innodb.h'
--- a/storage/innobase/handler/ha_innodb.h	2009-04-24 11:28:46 +0000
+++ b/storage/innobase/handler/ha_innodb.h	2009-06-20 13:57:39 +0000
@@ -252,4 +252,11 @@ int thd_binlog_format(const MYSQL_THD th
   @param  all   TRUE <=> rollback main transaction.
 */
 void thd_mark_transaction_to_rollback(MYSQL_THD thd, bool all);
+
+/**
+  Check if binary logging is filtered for thread's current db.
+  @param  thd   Thread handle
+  @retval 1 the query is not filtered, 0 otherwise.
+*/
+bool thd_binlog_filter_ok(const MYSQL_THD thd);
 }


Attachment: [text/bzr-bundle] bzr/luis.soares@sun.com-20090620135739-ckyrhwnmx95ut4px.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (luis.soares:2950) Bug#42829Luis Soares20 Jun
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2950)Bug#42829Andrei Elkin24 Jun
    • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2950)Bug#42829Alfranio Correia24 Jun
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2950)Bug#42829He Zhenxing24 Jun
  • Re: bzr commit into mysql-5.1-bugteam branch (luis.soares:2950)Bug#42829Alfranio Correia2 Jul