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