Hi Luis,
Patch approved!
Luis Soares wrote:
> #At file:///home/lsoares/Workspace/mysql-server/bugfix/42829/mysql-5.1-bug39934/
> based on revid:luis.soares@stripped
>
> 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.
>
> added:
> mysql-test/suite/binlog/r/binlog_stm_do_db.result
> mysql-test/suite/binlog/t/binlog_stm_do_db-master.opt
> mysql-test/suite/binlog/t/binlog_stm_do_db.test
> modified:
> sql/sql_class.cc
> storage/innobase/handler/ha_innodb.cc
> storage/innobase/handler/ha_innodb.h
> === added file 'mysql-test/suite/binlog/r/binlog_stm_do_db.result'
> --- a/mysql-test/suite/binlog/r/binlog_stm_do_db.result 1970-01-01 00:00:00 +0000
> +++ b/mysql-test/suite/binlog/r/binlog_stm_do_db.result 2009-07-23 12:59:25 +0000
> @@ -0,0 +1,42 @@
> +SET @old_isolation_level= @@session.tx_isolation;
> +SET @@session.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 DATABASE b42829_filtered;
> +use b42829_filtered;
> +CREATE TABLE t1 (x int, y int) engine=InnoDB;
> +CREATE TABLE t2 (x int, y int) engine=InnoDB;
> +SET @@session.sql_log_bin= 0;
> +INSERT INTO b42829_filtered.t1 VALUES (100,100);
> +INSERT INTO b42829.t1 VALUES (100,100);
> +SET @@session.sql_log_bin= 1;
> +### assertion: the inserts will not raise log error because
> +### binlog-do-db is filtering used database
> +INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
> +INSERT INTO t1 SELECT * FROM t2;
> +### assertion: assert that despite updating a not filtered
> +### database this wont trigger an error as the
> +### used database is the filtered one.
> +UPDATE b42829_filtered.t1 ft1, b42829.t1 nft1 SET ft1.x=1, nft1.x=2;
> +use b42829;
> +### assertion: the statements *will* raise log error because
> +### binlog-do-db is not filtering used database
> +BEGIN;
> +INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
> +ERROR HY000: Cannot execute statement: binlogging impossible since BINLOG_FORMAT =
> STATEMENT and at least one table uses a storage engine limited to row-logging. InnoDB is
> limited to row-logging when transaction isolation level is READ COMMITTED or READ
> UNCOMMITTED.
> +UPDATE b42829_filtered.t1 ft1, b42829.t1 nft1 SET ft1.x=1, nft1.x=2;
> +ERROR HY000: Cannot execute statement: binlogging impossible since BINLOG_FORMAT =
> STATEMENT and at least one table uses a storage engine limited to row-logging. InnoDB is
> limited to row-logging when transaction isolation level is READ COMMITTED or READ
> UNCOMMITTED.
> +INSERT INTO t1 SELECT * FROM t2;
> +ERROR HY000: Cannot execute statement: binlogging impossible since BINLOG_FORMAT =
> STATEMENT and at least one table uses a storage engine limited to row-logging. InnoDB is
> limited to row-logging when transaction isolation level is READ COMMITTED or READ
> UNCOMMITTED.
> +COMMIT;
> +### assertion: filtered events did not make into the binlog
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # CREATE DATABASE b42829
> +master-bin.000001 # Query # # use `b42829`; CREATE TABLE t1 (x int, y int)
> engine=InnoDB
> +master-bin.000001 # Query # # use `b42829`; CREATE TABLE t2 (x int, y int)
> engine=InnoDB
> +DROP DATABASE b42829;
> +DROP DATABASE b42829_filtered;
> +SET @@session.tx_isolation= @old_isolation_level;
>
> === added file 'mysql-test/suite/binlog/t/binlog_stm_do_db-master.opt'
> --- a/mysql-test/suite/binlog/t/binlog_stm_do_db-master.opt 1970-01-01 00:00:00
> +0000
> +++ b/mysql-test/suite/binlog/t/binlog_stm_do_db-master.opt 2009-07-23 12:59:25
> +0000
> @@ -0,0 +1 @@
> +--binlog-do-db=b42829
>
> === added file 'mysql-test/suite/binlog/t/binlog_stm_do_db.test'
> --- a/mysql-test/suite/binlog/t/binlog_stm_do_db.test 1970-01-01 00:00:00 +0000
> +++ b/mysql-test/suite/binlog/t/binlog_stm_do_db.test 2009-07-23 12:59:25 +0000
> @@ -0,0 +1,90 @@
> +# BUG#42829: binlogging enabled for all schemas regardless of
> +# binlog-db-db / binlog-ignore-db
> +#
> +# WHAT
> +# ====
> +#
> +# We want to test whether filtered events from binlog will cause
> +# raising an error mentioning that statement is unable to be logged or
> +# not, when:
> +#
> +# 1. isolation level READ-COMMITTED; AND
> +#
> +# 2. using InnoDB engine; AND
> +#
> +# 3. using SBL (in which case InnoDB will only allow RBL).
> +#
> +# HOW
> +# ===
> +#
> +# 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 fix, an error would be raised
> +#
> +# iv) do the same thing for not the filtered database and check
> +# that events throw an error:
> +#
> +# - Error: ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
> +#
> +
> +-- source include/have_log_bin.inc
> +-- source include/have_innodb.inc
> +-- source include/have_binlog_format_statement.inc
> +
> +SET @old_isolation_level= @@session.tx_isolation;
> +SET @@session.tx_isolation= 'READ-COMMITTED';
> +
> +-- let $engine= InnoDB
> +-- let $filtered= b42829_filtered
> +-- let $not_filtered= b42829
> +
> +-- 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 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
> +
> +SET @@session.sql_log_bin= 0;
> +-- eval INSERT INTO $filtered.t1 VALUES (100,100)
> +-- eval INSERT INTO $not_filtered.t1 VALUES (100,100)
> +SET @@session.sql_log_bin= 1;
> +
> +-- echo ### assertion: the inserts will not raise log error because
> +-- echo ### binlog-do-db is filtering used database
> +INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
> +INSERT INTO t1 SELECT * FROM t2;
> +
> +-- echo ### assertion: assert that despite updating a not filtered
> +-- echo ### database this wont trigger an error as the
> +-- echo ### used database is the filtered one.
> +-- eval UPDATE $filtered.t1 ft1, $not_filtered.t1 nft1 SET ft1.x=1, nft1.x=2
> +
> +-- eval use $not_filtered
> +-- echo ### assertion: the statements *will* raise log error because
> +-- echo ### binlog-do-db is not filtering used database
> +BEGIN;
> +-- error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
> +INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
> +-- error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
> +-- eval UPDATE $filtered.t1 ft1, $not_filtered.t1 nft1 SET ft1.x=1, nft1.x=2
> +-- error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
> +INSERT INTO t1 SELECT * FROM t2;
> +COMMIT;
> +
> +-- echo ### assertion: filtered events did not make into the binlog
> +source include/show_binlog_events.inc;
> +
> +-- eval DROP DATABASE $not_filtered
> +-- eval DROP DATABASE $filtered
> +SET @@session.tx_isolation= @old_isolation_level;
> \ No newline at end of file
>
> === modified file 'sql/sql_class.cc'
> --- a/sql/sql_class.cc 2009-07-23 10:38:03 +0000
> +++ b/sql/sql_class.cc 2009-07-23 12:59:25 +0000
> @@ -43,6 +43,7 @@
>
> #include "sp_rcontext.h"
> #include "sp_cache.h"
> +#include "rpl_filter.h"
>
> /*
> The following is used to initialise Table_ident with a internal
> @@ -2915,6 +2916,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-07-23 10:38:03 +0000
> +++ b/storage/innobase/handler/ha_innodb.cc 2009-07-23 12:59:25 +0000
> @@ -6860,7 +6860,8 @@ ha_innobase::external_lock(
> */
> if (lock_type == F_WRLCK &&
> !(table_flags() & HA_BINLOG_STMT_CAPABLE) &&
> - thd_binlog_format(thd) == BINLOG_FORMAT_STMT) {
> + thd_binlog_format(thd) == BINLOG_FORMAT_STMT &&
> + thd_binlog_filter_ok(thd) != 0) {
> /* The error may be suppressed by test cases, by setting
> the no_innodb_binlog_errors debug symbol. */
> if (DBUG_EVALUATE_IF("no_innodb_binlog_errors", 0, 1)) {
>
> === 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-07-23 12:59:25 +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);
> }
>
