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#  - Bug#22587377
31
32# Temporarily disabled on Windows due to bug #47638
33--source include/not_windows.inc
34
35source include/have_debug.inc;
36source 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
41let $debug_lock= "debug_lock.before_query_log_event";
42
43######## INITIALIZATION ########
44
45disable_warnings;
46DROP DATABASE IF EXISTS d1;
47DROP DATABASE IF EXISTS d2;
48DROP DATABASE IF EXISTS d3;
49DROP DATABASE IF EXISTS d4;
50DROP EVENT IF EXISTS e1;
51DROP EVENT IF EXISTS e2;
52DROP EVENT IF EXISTS e3;
53DROP EVENT IF EXISTS e4;
54DROP FUNCTION IF EXISTS f1;
55DROP FUNCTION IF EXISTS f2;
56DROP FUNCTION IF EXISTS f3;
57DROP FUNCTION IF EXISTS f4;
58DROP SERVER IF EXISTS s1;
59DROP SERVER IF EXISTS s2;
60DROP SERVER IF EXISTS s3;
61DROP SERVER IF EXISTS s4;
62DROP TABLE IF EXISTS t1;
63DROP TABLE IF EXISTS t2;
64DROP TABLE IF EXISTS t3;
65DROP TABLE IF EXISTS t4;
66DROP PROCEDURE IF EXISTS p1;
67DROP PROCEDURE IF EXISTS p2;
68DROP PROCEDURE IF EXISTS p3;
69DROP PROCEDURE IF EXISTS p4;
70DROP TRIGGER IF EXISTS tr1;
71DROP TRIGGER IF EXISTS tr2;
72DROP TRIGGER IF EXISTS tr3;
73DROP TRIGGER IF EXISTS tr4;
74enable_warnings;
75
76CREATE DATABASE d1;
77
78CREATE EVENT e1
79  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
80  DO INSERT INTO test.t1 VALUES (1);
81
82CREATE FUNCTION f1 () RETURNS INT DETERMINISTIC
83    RETURN 1;
84
85DELIMITER //;
86CREATE PROCEDURE p1 (OUT rows_cnt INT)
87    BEGIN
88        SELECT COUNT(*) INTO rows_cnt FROM t1;
89    END;
90    //
91DELIMITER ;//
92
93CREATE SERVER s1
94FOREIGN DATA WRAPPER mysql
95OPTIONS (USER 'user1', HOST '192.168.1.106', DATABASE 'test');
96
97CREATE TABLE t1 (a int);
98CREATE TABLE t3 (a int);
99
100DELIMITER //;
101CREATE TRIGGER tr1 BEFORE INSERT ON t1
102    FOR EACH ROW BEGIN
103        DELETE FROM t4 WHERE a=NEW.a;
104    END;
105    //
106DELIMITER ;//
107
108CREATE INDEX i1 ON t1 (a);
109
110CREATE VIEW v1 AS SELECT a FROM t1 WHERE a < 100;
111
112sync_slave_with_master;
113
114connection master1;
115let $connection_name= master1;
116let $connection_id= `SELECT CONNECTION_ID()`;
117
118connection master;
119
120# This will block the execution of a statement at the DBUG_SYNC_POINT
121# with given lock name
122if ($debug_lock)
123{
124  disable_query_log;
125  disable_result_log;
126  eval SELECT IS_FREE_LOCK($debug_lock);
127  eval SELECT GET_LOCK($debug_lock, 10);
128  eval SELECT IS_FREE_LOCK($debug_lock);
129  enable_query_log;
130  enable_result_log;
131}
132
133######## START TEST ########
134
135connection master1;
136
137disable_warnings;
138
139######## DATABASE ########
140
141let $rpl_diff_statement= SELECT schema_name FROM information_schema.schemata
142                         WHERE schema_name LIKE \'d%\' ORDER BY schema_name;
143
144send CREATE DATABASE d2;
145source include/kill_query_and_diff_master_slave.inc;
146
147send ALTER DATABASE d1
148       DEFAULT CHARACTER SET = 'utf8';
149source include/kill_query_and_diff_master_slave.inc;
150
151send DROP DATABASE d1;
152source include/kill_query_and_diff_master_slave.inc;
153
154send DROP DATABASE IF EXISTS d2;
155source include/kill_query_and_diff_master_slave.inc;
156
157######## EVENT ########
158
159let $rpl_diff_statement= SELECT event_name, event_body, execute_at
160                         FROM information_schema.events where event_name like \'e%\'
161                         ORDER BY event_name;
162
163send CREATE EVENT e2
164  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
165  DO INSERT INTO test.t1 VALUES (2);
166source include/kill_query_and_diff_master_slave.inc;
167
168# Temporarily disabled,see Bug#22587377-RPL.RPL_KILLED_DDL
169# FAILS SPORADICALLY ON PB2 IN 5.5 AND 5.6
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
174send DROP EVENT e1;
175source include/kill_query_and_diff_master_slave.inc;
176
177send DROP EVENT IF EXISTS e2;
178source include/kill_query_and_diff_master_slave.inc;
179
180######## FUNCTION ########
181
182--let $rpl_diff_statement= SHOW FUNCTION STATUS LIKE \'f%\'
183
184send CREATE FUNCTION f2 () RETURNS INT DETERMINISTIC
185     RETURN 1;
186source include/kill_query_and_diff_master_slave.inc;
187
188send ALTER FUNCTION f1 SQL SECURITY INVOKER;
189source include/kill_query_and_diff_master_slave.inc;
190
191# function f1 probably does not exist because the ALTER query was
192# killed
193send DROP FUNCTION f1;
194source include/kill_query_and_diff_master_slave.inc;
195
196# function f2 probably does not exist because the CREATE query was
197# killed
198send DROP FUNCTION IF EXISTS f2;
199source include/kill_query_and_diff_master_slave.inc;
200
201######## PROCEDURE ########
202
203--let $rpl_diff_statement= SHOW PROCEDURE STATUS LIKE \'p%\'
204
205DELIMITER //;
206send CREATE PROCEDURE p2 (OUT rows_cnt INT)
207     BEGIN
208       SELECT COUNT(*) INTO rows_cnt FROM t2;
209     END;
210     //
211DELIMITER ;//
212source include/kill_query_and_diff_master_slave.inc;
213
214send ALTER PROCEDURE p1 SQL SECURITY INVOKER COMMENT 'return rows_cnt of table t1';
215source include/kill_query_and_diff_master_slave.inc;
216
217send DROP PROCEDURE p1;
218source include/kill_query_and_diff_master_slave.inc;
219
220send DROP PROCEDURE IF EXISTS p2;
221source include/kill_query_and_diff_master_slave.inc;
222
223######## TABLE ########
224
225--let $rpl_diff_statement= SHOW TABLES LIKE \'t%\'
226
227send CREATE TABLE t2 (b int);
228source include/kill_query_and_diff_master_slave.inc;
229
230send ALTER TABLE t1 ADD (d int);
231source include/kill_query_and_diff_master_slave.inc;
232
233send RENAME TABLE t3 TO t4;
234source include/kill_query_and_diff_master_slave.inc;
235
236######## INDEX ########
237
238--let $rpl_diff_statement= SHOW INDEX FROM t1
239
240send CREATE INDEX i2 on t1 (a);
241source include/kill_query_and_diff_master_slave.inc;
242
243send DROP INDEX i1 on t1;
244source 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
272connection master;
273CREATE TABLE IF NOT EXISTS t4 (a int);
274connection master1;
275
276--let $rpl_diff_statement= SHOW TRIGGERS LIKE \'v%\'
277
278DELIMITER //;
279send CREATE TRIGGER tr2 BEFORE INSERT ON t4
280       FOR EACH ROW BEGIN
281         DELETE FROM t1 WHERE a=NEW.a;
282       END;
283       //
284DELIMITER ;//
285source include/kill_query_and_diff_master_slave.inc;
286
287send DROP TRIGGER tr1;
288source include/kill_query_and_diff_master_slave.inc;
289
290send DROP TRIGGER IF EXISTS tr2;
291source include/kill_query_and_diff_master_slave.inc;
292
293######## VIEW ########
294
295--let $rpl_diff_statement= SHOW TABLES LIKE \'v%\'
296
297send CREATE VIEW v2 AS SELECT a FROM t1 WHERE a > 100;
298source include/kill_query_and_diff_master_slave.inc;
299
300send DROP VIEW v1;
301source include/kill_query_and_diff_master_slave.inc;
302
303send DROP VIEW IF EXISTS v2;
304source include/kill_query_and_diff_master_slave.inc;
305
306######## DROP TABLE ########
307
308--let $rpl_diff_statement= SHOW TABLES LIKE \'t%\'
309
310send DROP TABLE t1;
311source include/kill_query_and_diff_master_slave.inc;
312
313send DROP TABLE IF EXISTS t2;
314source include/kill_query_and_diff_master_slave.inc;
315
316######## CLEAN UP ########
317
318connection master;
319
320# The DROP statements above are killed during the process, so they
321# does not make sure the objects are dropped.
322
323disable_warnings;
324DROP DATABASE IF EXISTS d1;
325DROP DATABASE IF EXISTS d2;
326DROP DATABASE IF EXISTS d3;
327DROP DATABASE IF EXISTS d4;
328DROP EVENT IF EXISTS e1;
329DROP EVENT IF EXISTS e2;
330DROP EVENT IF EXISTS e3;
331DROP EVENT IF EXISTS e4;
332DROP FUNCTION IF EXISTS f1;
333DROP FUNCTION IF EXISTS f2;
334DROP FUNCTION IF EXISTS f3;
335DROP FUNCTION IF EXISTS f4;
336DROP SERVER IF EXISTS s1;
337DROP SERVER IF EXISTS s2;
338DROP SERVER IF EXISTS s3;
339DROP SERVER IF EXISTS s4;
340DROP TABLE IF EXISTS t1;
341DROP TABLE IF EXISTS t2;
342DROP TABLE IF EXISTS t3;
343DROP TABLE IF EXISTS t4;
344DROP PROCEDURE IF EXISTS p1;
345DROP PROCEDURE IF EXISTS p2;
346DROP PROCEDURE IF EXISTS p3;
347DROP PROCEDURE IF EXISTS p4;
348enable_warnings;
349--source include/rpl_end.inc
350