Debian Codegraph @

mysql-test/suite/rpl/t/rpl_killed_ddl.test

raw
1 # ==== Purpose ====
2 #
3 # This test check if DDL statements are correctly binlogged when the
4 # thread is killed
5 #
6 # ==== Method ====
7 #
8 # Start a DDL query and kill it, check if the error code of the binlog
9 # event is correct.
10 #
11 # DDL statements tested:
12 # CREATE/ALTER/RENAME/DROP DATABASE
13 # CREATE/ALTER/DROP EVENT
14 # CREATE/ALTER/DROP FUNCTION
15 # CREATE/ALTER/DROP PROCEDURE
16 # CREATE/ALTER/DROP SERVER
17 # CREATE/ALTER/RENAME/DROP TABLE
18 # CREATE/DROP TRIGGER
19 # CREATE/ALTER/DROP VIEW
20 #
21 # ==== Bugs =====
22 #
23 # BUG#37145
24 #
25 # ==== TODO ====
26 #
27 # There are some part of the test are temporarily disabled because of
28 # the following bugs, please enable then once they get fixed:
29 # - BUG#22473427
30
31 # This test takes long time, so only run it with the --big-test mtr-flag.
32 --source include/not_group_replication_plugin.inc
33 source include/big_test.inc;
34 source include/have_debug.inc;
35 source include/set_privilege_checks_user_as_system_user.inc;
36 source include/master-slave.inc;
37
38 # Use the DBUG_SYNC_POINT to make sure the thread running the DDL is
39 # waiting before creating the query log event
40
41 let $debug_lock= "debug_lock.before_query_log_event";
42
43 ######## INITIALIZATION ########
44
45 disable_warnings;
46 DROP DATABASE IF EXISTS d1;
47 DROP DATABASE IF EXISTS d2;
48 DROP DATABASE IF EXISTS d3;
49 DROP DATABASE IF EXISTS d4;
50 DROP EVENT IF EXISTS e1;
51 DROP EVENT IF EXISTS e2;
52 DROP EVENT IF EXISTS e3;
53 DROP EVENT IF EXISTS e4;
54 DROP FUNCTION IF EXISTS f1;
55 DROP FUNCTION IF EXISTS f2;
56 DROP FUNCTION IF EXISTS f3;
57 DROP FUNCTION IF EXISTS f4;
58 DROP SERVER IF EXISTS s1;
59 DROP SERVER IF EXISTS s2;
60 DROP SERVER IF EXISTS s3;
61 DROP SERVER IF EXISTS s4;
62 DROP TABLE IF EXISTS t1;
63 DROP TABLE IF EXISTS t2;
64 DROP TABLE IF EXISTS t3;
65 DROP TABLE IF EXISTS t4;
66 DROP PROCEDURE IF EXISTS p1;
67 DROP PROCEDURE IF EXISTS p2;
68 DROP PROCEDURE IF EXISTS p3;
69 DROP PROCEDURE IF EXISTS p4;
70 DROP TRIGGER IF EXISTS tr1;
71 DROP TRIGGER IF EXISTS tr2;
72 DROP TRIGGER IF EXISTS tr3;
73 DROP TRIGGER IF EXISTS tr4;
74 enable_warnings;
75
76 CREATE DATABASE d1;
77
78 CREATE EVENT e1
79 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
80 DO INSERT INTO test.t1 VALUES (1);
81
82 CREATE FUNCTION f1 () RETURNS INT DETERMINISTIC
83 RETURN 1;
84
85 DELIMITER //;
86 CREATE PROCEDURE p1 (OUT rownum INT)
87 BEGIN
88 SELECT COUNT(*) INTO rownum FROM t1;
89 END;
90 //
91 DELIMITER ;//
92
93 CREATE SERVER s1
94 FOREIGN DATA WRAPPER mysql
95 OPTIONS (USER 'user1', HOST '192.168.1.106', DATABASE 'test');
96
97 CREATE TABLE t1 (a int);
98 CREATE TABLE t3 (a int);
99
100 DELIMITER //;
101 CREATE TRIGGER tr1 BEFORE INSERT ON t1
102 FOR EACH ROW BEGIN
103 DELETE FROM t4 WHERE a=NEW.a;
104 END;
105 //
106 DELIMITER ;//
107
108 CREATE INDEX i1 ON t1 (a);
109
110 CREATE VIEW v1 AS SELECT a FROM t1 WHERE a < 100;
111
112 --source include/sync_slave_sql_with_master.inc
113
114 connection master1;
115 let $connection_name= master1;
116 let $connection_id= `SELECT CONNECTION_ID()`;
117
118 connection master;
119 echo [on master];
120
121 # This will block the execution of a statement at the DBUG_SYNC_POINT
122 # with given lock name
123 if ($debug_lock)
124 {
125 disable_query_log;
126 disable_result_log;
127 eval SELECT IS_FREE_LOCK($debug_lock);
128 eval SELECT GET_LOCK($debug_lock, 10);
129 eval SELECT IS_FREE_LOCK($debug_lock);
130 enable_query_log;
131 enable_result_log;
132 }
133
134 ######## START TEST ########
135
136 connection master1;
137 echo [on master1];
138
139 disable_warnings;
140
141 ######## DATABASE ########
142
143 let $rpl_diff_statement= SELECT schema_name FROM information_schema.schemata
144 WHERE schema_name LIKE \'d%\' ORDER BY schema_name;
145
146 send CREATE DATABASE d2;
147 source include/kill_query_and_diff_master_slave.inc;
148
149 send ALTER DATABASE d1
150 DEFAULT CHARACTER SET = 'utf8';
151 source include/kill_query_and_diff_master_slave.inc;
152
153 send DROP DATABASE d1;
154 source include/kill_query_and_diff_master_slave.inc;
155
156 send DROP DATABASE IF EXISTS d2;
157 source include/kill_query_and_diff_master_slave.inc;
158
159 ######## EVENT ########
160
161 let $rpl_diff_statement= SELECT event_name, event_body, execute_at
162 FROM information_schema.events where event_name like \'e%\'
163 ORDER BY event_name;
164
165 send CREATE EVENT e2
166 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
167 DO INSERT INTO test.t1 VALUES (2);
168 source include/kill_query_and_diff_master_slave.inc;
169
170 send ALTER EVENT e1
171 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY;
172 source include/kill_query_and_diff_master_slave.inc;
173
174 send DROP EVENT e1;
175 source include/kill_query_and_diff_master_slave.inc;
176
177 send DROP EVENT IF EXISTS e2;
178 source include/kill_query_and_diff_master_slave.inc;
179
180 ######## FUNCTION ########
181
182 --let $rpl_diff_statement= SHOW FUNCTION STATUS LIKE \'f%\'
183
184 send CREATE FUNCTION f2 () RETURNS INT DETERMINISTIC
185 RETURN 1;
186 source include/kill_query_and_diff_master_slave.inc;
187
188 send ALTER FUNCTION f1 SQL SECURITY INVOKER;
189 source include/kill_query_and_diff_master_slave.inc;
190
191 # function f1 probably does not exist because the ALTER query was
192 # killed
193 send DROP FUNCTION f1;
194 source include/kill_query_and_diff_master_slave.inc;
195
196 # function f2 probably does not exist because the CREATE query was
197 # killed
198 send DROP FUNCTION IF EXISTS f2;
199 source include/kill_query_and_diff_master_slave.inc;
200
201 ######## PROCEDURE ########
202
203 --let $rpl_diff_statement= SHOW PROCEDURE STATUS LIKE \'p%\'
204
205 DELIMITER //;
206 send CREATE PROCEDURE p2 (OUT rownum INT)
207 BEGIN
208 SELECT COUNT(*) INTO rownum FROM t2;
209 END;
210 //
211 DELIMITER ;//
212 source include/kill_query_and_diff_master_slave.inc;
213
214 send ALTER PROCEDURE p1 SQL SECURITY INVOKER COMMENT 'return rows of table t1';
215 source include/kill_query_and_diff_master_slave.inc;
216
217 send DROP PROCEDURE p1;
218 source include/kill_query_and_diff_master_slave.inc;
219
220 send DROP PROCEDURE IF EXISTS p2;
221 source include/kill_query_and_diff_master_slave.inc;
222
223 ######## TABLE ########
224
225 --let $rpl_diff_statement= SHOW TABLES LIKE \'t%\'
226
227 send CREATE TABLE t2 (b int);
228 source include/kill_query_and_diff_master_slave.inc;
229
230 send ALTER TABLE t1 ADD (d int);
231 source include/kill_query_and_diff_master_slave.inc;
232
233 send RENAME TABLE t3 TO t4;
234 source include/kill_query_and_diff_master_slave.inc;
235
236 ######## INDEX ########
237
238 --let $rpl_diff_statement= SHOW INDEX FROM t1
239
240 send CREATE INDEX i2 on t1 (a);
241 source include/kill_query_and_diff_master_slave.inc;
242
243 send DROP INDEX i1 on t1;
244 source include/kill_query_and_diff_master_slave.inc;
245
246
247 ######## SERVER ########
248
249 # Temporarily disabled, see Bug #22473427 - DROP SERVER FAILS
250 # AFTER ALTER SERVER+KILL QUERY
251
252 # --let $rpl_diff_statement= SELECT * FROM mysql.servers WHERE Server_name like \'s%\'
253
254 # send CREATE SERVER s2
255 # FOREIGN DATA WRAPPER mysql
256 # OPTIONS (USER 'user2', HOST '192.168.1.108', DATABASE 'test');
257 # source include/kill_query_and_diff_master_slave.inc;
258
259 # send ALTER SERVER s1
260 # OPTIONS (DATABASE 'test1');
261 # source include/kill_query_and_diff_master_slave.inc;
262
263 # send DROP SERVER s1;
264 # source include/kill_query_and_diff_master_slave.inc;
265
266 # send DROP SERVER IF EXIST s1;
267 # source include/kill_query_and_diff_master_slave.inc;
268
269 ######## TRIGGER ########
270
271 # Make sure table t4 exists
272 connection master;
273 CREATE TABLE IF NOT EXISTS t4 (a int);
274 connection master1;
275
276 --let $rpl_diff_statement= SHOW TRIGGERS LIKE \'v%\'
277
278 DELIMITER //;
279 send CREATE TRIGGER tr2 BEFORE INSERT ON t4
280 FOR EACH ROW BEGIN
281 DELETE FROM t1 WHERE a=NEW.a;
282 END;
283 //
284 DELIMITER ;//
285 source include/kill_query_and_diff_master_slave.inc;
286
287 send DROP TRIGGER tr1;
288 source include/kill_query_and_diff_master_slave.inc;
289
290 send DROP TRIGGER IF EXISTS tr2;
291 source include/kill_query_and_diff_master_slave.inc;
292
293 ######## VIEW ########
294
295 --let $rpl_diff_statement= SHOW TABLES LIKE \'v%\'
296
297 send CREATE VIEW v2 AS SELECT a FROM t1 WHERE a > 100;
298 source include/kill_query_and_diff_master_slave.inc;
299
300 send DROP VIEW v1;
301 source include/kill_query_and_diff_master_slave.inc;
302
303 send DROP VIEW IF EXISTS v2;
304 source include/kill_query_and_diff_master_slave.inc;
305
306 ######## DROP TABLE ########
307
308 --let $rpl_diff_statement= SHOW TABLES LIKE \'t%\'
309
310 send DROP TABLE t1;
311 source include/kill_query_and_diff_master_slave.inc;
312
313 send DROP TABLE IF EXISTS t2;
314 source include/kill_query_and_diff_master_slave.inc;
315
316 ######## CLEAN UP ########
317
318 connection master;
319
320 # The DROP statements above are killed during the process, so they
321 # does not make sure the objects are dropped.
322
323 disable_warnings;
324 DROP DATABASE IF EXISTS d1;
325 DROP DATABASE IF EXISTS d2;
326 DROP DATABASE IF EXISTS d3;
327 DROP DATABASE IF EXISTS d4;
328 DROP EVENT IF EXISTS e1;
329 DROP EVENT IF EXISTS e2;
330 DROP EVENT IF EXISTS e3;
331 DROP EVENT IF EXISTS e4;
332 DROP FUNCTION IF EXISTS f1;
333 DROP FUNCTION IF EXISTS f2;
334 DROP FUNCTION IF EXISTS f3;
335 DROP FUNCTION IF EXISTS f4;
336 DROP SERVER IF EXISTS s1;
337 DROP SERVER IF EXISTS s2;
338 DROP SERVER IF EXISTS s3;
339 DROP SERVER IF EXISTS s4;
340 DROP TABLE IF EXISTS t1;
341 DROP TABLE IF EXISTS t2;
342 DROP TABLE IF EXISTS t3;
343 DROP TABLE IF EXISTS t4;
344 DROP VIEW IF EXISTS v1;
345 DROP VIEW IF EXISTS v2;
346 DROP PROCEDURE IF EXISTS p1;
347 DROP PROCEDURE IF EXISTS p2;
348 DROP PROCEDURE IF EXISTS p3;
349 DROP PROCEDURE IF EXISTS p4;
350 enable_warnings;
351 --source include/rpl_end.inc