1--source include/have_log_bin.inc 2# Test sets its own binlog_format, so we restrict it to run only once 3--source include/have_binlog_format_row.inc 4 5CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 6 7# Get rid of previous tests binlog 8--disable_query_log 9reset master; 10--enable_query_log 11 12# 13# Bug#34306: Can't make copy of log tables when server binary log is enabled 14# 15# This is an additional test for Bug#34306 in order to ensure that INSERT INTO 16# .. SELECT FROM is properly replicated under SBR and RBR and that the proper 17# read lock type are acquired. 18# 19 20--disable_warnings 21DROP TABLE IF EXISTS t1; 22DROP TABLE IF EXISTS t2; 23--enable_warnings 24 25set @saved_global_binlog_format = @@global.binlog_format; 26set @saved_local_binlog_format = @@session.binlog_format; 27SET GLOBAL BINLOG_FORMAT = STATEMENT; 28SET SESSION BINLOG_FORMAT = STATEMENT; 29 30CREATE TABLE t1 (a INT); 31CREATE TABLE t2 LIKE t1; 32select @@SESSION.BINLOG_FORMAT; 33INSERT INTO t1 VALUES(1); 34INSERT INTO t2 VALUES(2); 35 36--connect(con1,localhost,root,,) 37--connect(con2,localhost,root,,) 38 39--echo # 40--echo # Ensure that INSERT INTO .. SELECT FROM under SBR takes a read 41--echo # lock that will prevent the source table from being modified. 42--echo # 43 44--connection con1 45SELECT GET_LOCK('Bug#34306', 120); 46--connection con2 47PREPARE stmt FROM "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)"; 48--send EXECUTE stmt; 49--connection default 50let $wait_condition= 51 SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE 52 state = "User lock" AND 53 info = "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)"; 54--source include/wait_condition.inc 55--send INSERT INTO t2 VALUES (3); 56--connection con1 57let $wait_condition= 58 SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE 59 state = "Waiting for table level lock" and info = "INSERT INTO t2 VALUES (3)"; 60--source include/wait_condition.inc 61SELECT RELEASE_LOCK('Bug#34306'); 62--connection con2 63--reap 64SELECT RELEASE_LOCK('Bug#34306'); 65--connection default 66--reap 67 68--echo # 69--echo # Ensure that INSERT INTO .. SELECT FROM prepared under SBR does 70--echo # not prevent the source table from being modified if under RBR. 71--echo # 72 73--connection con2 74SET SESSION BINLOG_FORMAT = ROW; 75--connection con1 76SELECT GET_LOCK('Bug#34306', 120); 77--connection con2 78--send EXECUTE stmt; 79--connection default 80let $wait_condition= 81 SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE 82 state = "User lock" AND 83 info = "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)"; 84--source include/wait_condition.inc 85--connection con1 86INSERT INTO t2 VALUES (4); 87SELECT RELEASE_LOCK('Bug#34306'); 88--connection con2 89--reap 90 91--disconnect con1 92--disconnect con2 93--connection default 94 95--echo # Show binlog events 96source include/show_binlog_events.inc; 97 98DROP TABLE t1; 99DROP TABLE t2; 100SET GLOBAL BINLOG_FORMAT = @saved_global_binlog_format; 101SET SESSION BINLOG_FORMAT = @saved_local_binlog_format; 102