Debian Codegraph @

mysql-test/suite/binlog/t/binlog_stm_row.test

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