1SET @old_isolation_level= @@session.tx_isolation; 2SET @@session.tx_isolation= 'READ-COMMITTED'; 3CREATE DATABASE b42829; 4use b42829; 5CREATE TABLE t1 (x int, y int) engine=InnoDB; 6CREATE TABLE t2 (x int, y int) engine=InnoDB; 7CREATE DATABASE b42829_filtered; 8use b42829_filtered; 9CREATE TABLE t1 (x int, y int) engine=InnoDB; 10CREATE TABLE t2 (x int, y int) engine=InnoDB; 11SET @@session.sql_log_bin= 0; 12INSERT INTO b42829_filtered.t1 VALUES (100,100); 13INSERT INTO b42829.t1 VALUES (100,100); 14SET @@session.sql_log_bin= 1; 15### assertion: the inserts will not raise log error because 16### binlog-do-db is filtering used database 17INSERT INTO t2 VALUES (1,2), (1,3), (1,4); 18INSERT INTO t1 SELECT * FROM t2; 19### assertion: assert that despite updating a not filtered 20### database this wont trigger an error as the 21### used database is the filtered one. 22UPDATE b42829_filtered.t1 ft1, b42829.t1 nft1 SET ft1.x=1, nft1.x=2; 23use b42829; 24### assertion: the statements *will* raise log error because 25### binlog-do-db is not filtering used database 26BEGIN; 27INSERT INTO t2 VALUES (1,2), (1,3), (1,4); 28ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. 29UPDATE b42829_filtered.t1 ft1, b42829.t1 nft1 SET ft1.x=1, nft1.x=2; 30ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. 31INSERT INTO t1 SELECT * FROM t2; 32ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. 33COMMIT; 34### assertion: filtered events did not make into the binlog 35include/show_binlog_events.inc 36Log_name Pos Event_type Server_id End_log_pos Info 37master-bin.000001 # Gtid # # GTID #-#-# 38master-bin.000001 # Query # # CREATE DATABASE b42829 39master-bin.000001 # Gtid # # GTID #-#-# 40master-bin.000001 # Query # # use `b42829`; CREATE TABLE t1 (x int, y int) engine=InnoDB 41master-bin.000001 # Gtid # # GTID #-#-# 42master-bin.000001 # Query # # use `b42829`; CREATE TABLE t2 (x int, y int) engine=InnoDB 43DROP DATABASE b42829; 44DROP DATABASE b42829_filtered; 45SET @@session.tx_isolation= @old_isolation_level; 46