3
# This test check if DDL statements are correctly binlogged when the
8
# Start a DDL query and kill it, check if the error code of the binlog
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
19
# CREATE/ALTER/DROP VIEW
27
# There are some part of the test are temporarily disabled because of
28
# the following bugs, please enable then once they get fixed:
34
source include/have_debug.inc;
35
source include/master-slave.inc;
37
# Use the DBUG_SYNC_POINT to make sure the thread running the DDL is
38
# waiting before creating the query log event
40
let $debug_lock= "debug_lock.before_query_log_event";
42
######## INITIALIZATION ########
45
DROP DATABASE IF EXISTS d1;
46
DROP DATABASE IF EXISTS d2;
47
DROP DATABASE IF EXISTS d3;
48
DROP DATABASE IF EXISTS d4;
49
DROP EVENT IF EXISTS e1;
50
DROP EVENT IF EXISTS e2;
51
DROP EVENT IF EXISTS e3;
52
DROP EVENT IF EXISTS e4;
53
DROP FUNCTION IF EXISTS f1;
54
DROP FUNCTION IF EXISTS f2;
55
DROP FUNCTION IF EXISTS f3;
56
DROP FUNCTION IF EXISTS f4;
57
DROP SERVER IF EXISTS s1;
58
DROP SERVER IF EXISTS s2;
59
DROP SERVER IF EXISTS s3;
60
DROP SERVER IF EXISTS s4;
61
DROP TABLE IF EXISTS t1;
62
DROP TABLE IF EXISTS t2;
63
DROP TABLE IF EXISTS t3;
64
DROP TABLE IF EXISTS t4;
65
DROP PROCEDURE IF EXISTS p1;
66
DROP PROCEDURE IF EXISTS p2;
67
DROP PROCEDURE IF EXISTS p3;
68
DROP PROCEDURE IF EXISTS p4;
69
DROP TRIGGER IF EXISTS tr1;
70
DROP TRIGGER IF EXISTS tr2;
71
DROP TRIGGER IF EXISTS tr3;
72
DROP TRIGGER IF EXISTS tr4;
78
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
79
DO INSERT INTO test.t1 VALUES (1);
81
CREATE FUNCTION f1 () RETURNS INT DETERMINISTIC
85
CREATE PROCEDURE p1 (OUT rows INT)
87
SELECT COUNT(*) INTO rows FROM t1;
93
FOREIGN DATA WRAPPER mysql
94
OPTIONS (USER 'user1', HOST '192.168.1.106', DATABASE 'test');
96
CREATE TABLE t1 (a int);
97
CREATE TABLE t3 (a int);
100
CREATE TRIGGER tr1 BEFORE INSERT ON t1
102
DELETE FROM t4 WHERE a=NEW.a;
107
CREATE INDEX i1 ON t1 (a);
109
CREATE VIEW v1 AS SELECT a FROM t1 WHERE a < 100;
111
sync_slave_with_master;
114
let $connection_name= master1;
115
let $connection_id= `SELECT CONNECTION_ID()`;
120
# This will block the execution of a statement at the DBUG_SYNC_POINT
121
# with given lock name
122
if (`SELECT '$debug_lock' != ''`)
126
eval SELECT IS_FREE_LOCK($debug_lock);
127
eval SELECT GET_LOCK($debug_lock, 10);
128
eval SELECT IS_FREE_LOCK($debug_lock);
133
######## START TEST ########
140
######## DATABASE ########
142
let $diff_statement= SHOW DATABASES LIKE 'd%';
144
send CREATE DATABASE d2;
145
source include/kill_query_and_diff_master_slave.inc;
147
# Temporarily disabled, see BUG#44041, the ALTER DATABASE can affect the
148
# collation of other database on slave
149
#send ALTER DATABASE d1
150
# DEFAULT CHARACTER SET = 'utf8';
151
#source include/kill_query_and_diff_master_slave.inc;
153
send DROP DATABASE d1;
154
source include/kill_query_and_diff_master_slave.inc;
156
send DROP DATABASE d2;
157
source include/kill_query_and_diff_master_slave.inc;
159
######## EVENT ########
161
let $diff_statement= SELECT event_name, event_body, execute_at
162
FROM information_schema.events where event_name like 'e%';
165
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
166
DO INSERT INTO test.t1 VALUES (2);
167
source include/kill_query_and_diff_master_slave.inc;
169
# Temporarily disabled because of BUG#44171, killing ALTER EVENT can
172
# ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY;
173
#source include/kill_query_and_diff_master_slave.inc;
176
source include/kill_query_and_diff_master_slave.inc;
178
send DROP EVENT IF EXISTS e2;
179
source include/kill_query_and_diff_master_slave.inc;
181
######## FUNCTION ########
183
let $diff_statement= SHOW FUNCTION STATUS LIKE 'f%';
185
send CREATE FUNCTION f2 () RETURNS INT DETERMINISTIC
187
source include/kill_query_and_diff_master_slave.inc;
189
send ALTER FUNCTION f1 SQL SECURITY INVOKER;
190
source include/kill_query_and_diff_master_slave.inc;
192
# function f1 probably does not exist because the ALTER query was
194
send DROP FUNCTION f1;
195
source include/kill_query_and_diff_master_slave.inc;
197
# function f2 probably does not exist because the CREATE query was
200
# Temporarily disabled. Because of BUG#43353, KILL the query may
201
# result in function not found, and for 5.1, DROP statements will be
202
# logged if the function is not found on master, so the following DROP
203
# FUNCTION statement may be interrupted and not drop the function on
204
# master, but still get logged and executed on slave and cause
205
# inconsistence. Also disable the following DROP PROCEDURE IF EXITS
207
#send DROP FUNCTION IF EXISTS f2;
208
#source include/kill_query_and_diff_master_slave.inc;
210
######## PROCEDURE ########
212
let $diff_statement= SHOW PROCEDURE STATUS LIKE 'p%';
215
send CREATE PROCEDURE p2 (OUT rows INT)
217
SELECT COUNT(*) INTO rows FROM t2;
221
source include/kill_query_and_diff_master_slave.inc;
223
send ALTER PROCEDURE p1 SQL SECURITY INVOKER COMMENT 'return rows of table t1';
224
source include/kill_query_and_diff_master_slave.inc;
226
send DROP PROCEDURE p1;
227
source include/kill_query_and_diff_master_slave.inc;
229
# Temporarily disabled, see comment above for DROP FUNCTION IF EXISTS
230
#send DROP PROCEDURE IF EXISTS p2;
231
#source include/kill_query_and_diff_master_slave.inc;
233
######## TABLE ########
235
let $diff_statement= SHOW TABLES LIKE 't%';
237
send CREATE TABLE t2 (b int);
238
source include/kill_query_and_diff_master_slave.inc;
240
send ALTER TABLE t1 ADD (d int);
241
source include/kill_query_and_diff_master_slave.inc;
243
send RENAME TABLE t3 TO t4;
244
source include/kill_query_and_diff_master_slave.inc;
246
######## INDEX ########
248
let $diff_statement= SHOW INDEX FROM t1;
250
send CREATE INDEX i2 on t1 (a);
251
source include/kill_query_and_diff_master_slave.inc;
253
send DROP INDEX i1 on t1;
254
source include/kill_query_and_diff_master_slave.inc;
257
######## SERVER ########
259
# Tempoarily disabled, see bug#25705
261
# let $diff_statement= SELECT * FROM mysql.server WHERE name like 's%';
263
# send CREATE SERVER s2
264
# FOREIGN DATA WRAPPER mysql
265
# OPTIONS (USER 'user2', HOST '192.168.1.108', DATABASE 'test');
266
# source include/kill_query_and_diff_master_slave.inc;
268
# send ALTER SERVER s1
269
# OPTIONS (DATABASE 'test1');
270
# source include/kill_query_and_diff_master_slave.inc;
272
# send DROP SERVER s1;
273
# source include/kill_query_and_diff_master_slave.inc;
275
# send DROP SERVER IF EXIST s1;
276
# source include/kill_query_and_diff_master_slave.inc;
278
######## TRIGGER ########
280
let $diff_statement= SHOW TRIGGERS LIKE 'v%';
283
send CREATE TRIGGER tr2 BEFORE INSERT ON t4
285
DELETE FROM t1 WHERE a=NEW.a;
289
source include/kill_query_and_diff_master_slave.inc;
291
send DROP TRIGGER tr1;
292
source include/kill_query_and_diff_master_slave.inc;
294
send DROP TRIGGER IF EXISTS tr2;
295
source include/kill_query_and_diff_master_slave.inc;
297
######## VIEW ########
299
let $diff_statement= SHOW TABLES LIKE 'v%';
301
send CREATE VIEW v2 AS SELECT a FROM t1 WHERE a > 100;
302
source include/kill_query_and_diff_master_slave.inc;
305
source include/kill_query_and_diff_master_slave.inc;
307
send DROP VIEW IF EXISTS v2;
308
source include/kill_query_and_diff_master_slave.inc;
310
######## DROP TABLE ########
312
let $diff_statement= SHOW TABLES LIKE 't%';
315
source include/kill_query_and_diff_master_slave.inc;
317
send DROP TABLE IF EXISTS t2;
318
source include/kill_query_and_diff_master_slave.inc;
320
######## CLEAN UP ########
324
# The DROP statements above are killed during the process, so they
325
# does not make sure the objects are dropped.
328
DROP DATABASE IF EXISTS d1;
329
DROP DATABASE IF EXISTS d2;
330
DROP DATABASE IF EXISTS d3;
331
DROP DATABASE IF EXISTS d4;
332
DROP EVENT IF EXISTS e1;
333
DROP EVENT IF EXISTS e2;
334
DROP EVENT IF EXISTS e3;
335
DROP EVENT IF EXISTS e4;
336
DROP FUNCTION IF EXISTS f1;
337
DROP FUNCTION IF EXISTS f2;
338
DROP FUNCTION IF EXISTS f3;
339
DROP FUNCTION IF EXISTS f4;
340
DROP SERVER IF EXISTS s1;
341
DROP SERVER IF EXISTS s2;
342
DROP SERVER IF EXISTS s3;
343
DROP SERVER IF EXISTS s4;
344
DROP TABLE IF EXISTS t1;
345
DROP TABLE IF EXISTS t2;
346
DROP TABLE IF EXISTS t3;
347
DROP TABLE IF EXISTS t4;
348
DROP PROCEDURE IF EXISTS p1;
349
DROP PROCEDURE IF EXISTS p2;
350
DROP PROCEDURE IF EXISTS p3;
351
DROP PROCEDURE IF EXISTS p4;