#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
