1
# BUG#42829: binlogging enabled for all schemas regardless of
2
# binlog-db-db / binlog-ignore-db
7
# We want to test whether filtered events from binlog will cause
8
# raising an error mentioning that statement is unable to be logged or
11
# 1. isolation level READ-COMMITTED; AND
13
# 2. using InnoDB engine; AND
15
# 3. using SBL (in which case InnoDB will only allow RBL).
20
# The test is implemented as follows:
22
# i) set tx_isolation to read-committed.
24
# ii) create two databases (one filtered other not - using
27
# iii) Create statements that are to be filtered on filtered db
29
# - At this point, before fix, an error would be raised
31
# iv) do the same thing for not the filtered database and check
32
# that events throw an error:
34
# - Error: ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
37
-- source include/have_log_bin.inc
38
-- source include/have_innodb.inc
39
-- source include/have_binlog_format_statement.inc
41
SET @old_isolation_level= @@session.tx_isolation;
42
SET @@session.tx_isolation= 'READ-COMMITTED';
44
-- let $engine= InnoDB
45
-- let $filtered= b42829_filtered
46
-- let $not_filtered= b42829
48
-- eval CREATE DATABASE $not_filtered
49
-- eval use $not_filtered
50
-- eval CREATE TABLE t1 (x int, y int) engine=$engine
51
-- eval CREATE TABLE t2 (x int, y int) engine=$engine
53
-- eval CREATE DATABASE $filtered
55
-- eval CREATE TABLE t1 (x int, y int) engine=$engine
56
-- eval CREATE TABLE t2 (x int, y int) engine=$engine
58
SET @@session.sql_log_bin= 0;
59
-- eval INSERT INTO $filtered.t1 VALUES (100,100)
60
-- eval INSERT INTO $not_filtered.t1 VALUES (100,100)
61
SET @@session.sql_log_bin= 1;
63
-- echo ### assertion: the inserts will not raise log error because
64
-- echo ### binlog-do-db is filtering used database
65
INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
66
INSERT INTO t1 SELECT * FROM t2;
68
-- echo ### assertion: assert that despite updating a not filtered
69
-- echo ### database this wont trigger an error as the
70
-- echo ### used database is the filtered one.
71
-- eval UPDATE $filtered.t1 ft1, $not_filtered.t1 nft1 SET ft1.x=1, nft1.x=2
73
-- eval use $not_filtered
74
-- echo ### assertion: the statements *will* raise log error because
75
-- echo ### binlog-do-db is not filtering used database
77
-- error ER_BINLOG_LOGGING_IMPOSSIBLE
78
INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
79
-- error ER_BINLOG_LOGGING_IMPOSSIBLE
80
-- eval UPDATE $filtered.t1 ft1, $not_filtered.t1 nft1 SET ft1.x=1, nft1.x=2
81
-- error ER_BINLOG_LOGGING_IMPOSSIBLE
82
INSERT INTO t1 SELECT * FROM t2;
85
-- echo ### assertion: filtered events did not make into the binlog
86
source include/show_binlog_events.inc;
88
-- eval DROP DATABASE $not_filtered
89
-- eval DROP DATABASE $filtered
90
SET @@session.tx_isolation= @old_isolation_level;