Luis, great work.
Comments on the test case:
0 - Please, make sure that the documentation will be properly changed.
1 - Please, make sure that someone in the Innodb Team reviews it.
2 - In the test case, replace the error number by the constant.
3 - After every assertion, print out the content that was inserted in
the binary log.
4 - Please, try to combine the core of the test case in one single file.
Find further comments in-line.
Cheers.
Luis Soares wrote:
> #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),
>
I would not use the word stricter. 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:
>
> 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;
>
Show also the opposite: $not_filtered.t1, $filtered.t1 ft1
Although this not really important at this test, I think it would be good
as a documentation because there is a filter on the slave side that
takes this
into account.
> +
> +# 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)
>
What is the difference between this assertion and the one presented above?
> +
> +-- eval use $filtered
> +SET SQL_LOG_BIN=0;
>
Do you need the SET SQL_LOG_BIN? Won't the statements be filtered due to
the "use $filtered"?
> +-- 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
>
You are missing an include in here.
#include "rpl_filter.h"
> @@ -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)));
> +
>
Is there any reason to keep the if/else?
If there isn't, please, change this part of the logic.
> + /**
> + 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);
> }
>
> ------------------------------------------------------------------------
>
> This body part will be downloaded on demand.
