#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);
}
Attachment: [text/bzr-bundle] bzr/luis.soares@sun.com-20090723125925-vkn1cti1ptlxd0mp.bundle
