1
######################## rpl_ddl.test ########################
3
# DDL statements (sometimes with implicit COMMIT) executed #
4
# by the master and it's propagation into the slave #
6
##############################################################
9
# NOTE, PLEASE BE CAREFUL, WHEN MODIFYING THE TESTS !!
11
# 1. !All! objects to be dropped, renamed, altered ... must be created
12
# in AUTOCOMMIT= 1 mode before AUTOCOMMIT is set to 0 and the test
15
# 2. Never use a test object, which was direct or indirect affected by a
16
# preceeding test sequence again.
17
# Except table d1.t1 where ONLY DML is allowed.
19
# If one preceeding test sequence hits a (sometimes not good visible,
20
# because the sql error code of the statement might be 0) bug
21
# and these rules are ignored, a following test sequence might earn ugly
22
# effects like failing 'sync_slave_with_master', crashes of the slave or
23
# abort of the test case etc..
25
# 3. The assignment of the DDL command to be tested to $my_stmt can
26
# be a bit difficult. "'" must be avoided, because the test
27
# routine "include/rpl_stmt_seq.inc" performs a
28
# eval SELECT CONCAT('######## ','$my_stmt',' ########') as "";
31
###############################################################
33
###############################################################
34
# The sync_slave_with_master is needed to make the xids deterministic.
35
sync_slave_with_master;
40
# 1. DROP all objects, which probably already exist, but must be created here
43
DROP DATABASE IF EXISTS mysqltest1;
44
DROP DATABASE IF EXISTS mysqltest2;
45
DROP DATABASE IF EXISTS mysqltest3;
48
# 2. CREATE all objects needed
49
# working database is mysqltest1
50
# working (transactional!) is mysqltest1.t1
52
CREATE DATABASE mysqltest1;
53
CREATE DATABASE mysqltest2;
54
eval CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE=$engine_type;
55
INSERT INTO mysqltest1.t1 SET f1= 0;
56
eval CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE=$engine_type;
57
eval CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE=$engine_type;
58
eval CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE=$engine_type;
59
eval CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE=$engine_type;
60
eval CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE=$engine_type;
61
CREATE INDEX my_idx6 ON mysqltest1.t6(f1);
62
eval CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE=$engine_type;
63
INSERT INTO mysqltest1.t7 SET f1= 0;
64
eval CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE=$engine_type;
65
eval CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE=$engine_type;
66
eval CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE=$engine_type;
67
eval CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE=$engine_type;
68
eval CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE=$engine_type;
69
eval CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE=$engine_type;
70
eval CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE=$engine_type;
71
eval CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE=$engine_type;
72
eval CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE=$engine_type;
73
eval CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE=$engine_type;
74
eval CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE=$engine_type;
75
eval CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE=$engine_type;
76
CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT);
79
# 3. master sessions: never do AUTOCOMMIT
80
# slave sessions: never do AUTOCOMMIT
84
sync_slave_with_master;
87
SELECT '-------- switch to slave --------' as "";
93
SELECT '-------- switch to master -------' as "";
97
# We don't want to abort the whole test if one statement sent
98
# to the server gets an error, because the following test
99
# sequences are nearly independend of the previous statements.
100
--disable_abort_on_error
102
###############################################################
103
# Banal case: (explicit) COMMIT and ROLLBACK
104
# Just for checking if the test sequence is usable
105
###############################################################
107
let $my_stmt= COMMIT;
108
let $my_master_commit= true;
109
let $my_slave_commit= true;
110
--source include/rpl_stmt_seq.inc
112
let $my_stmt= ROLLBACK;
113
let $my_master_commit= false;
114
let $my_slave_commit= false;
115
--source include/rpl_stmt_seq.inc
117
###############################################################
118
# Cases with commands very similar to COMMIT
119
###############################################################
121
let $my_stmt= SET AUTOCOMMIT=1;
122
let $my_master_commit= true;
123
let $my_slave_commit= true;
124
--source include/rpl_stmt_seq.inc
127
let $my_stmt= START TRANSACTION;
128
let $my_master_commit= true;
129
let $my_slave_commit= true;
130
--source include/rpl_stmt_seq.inc
133
let $my_master_commit= true;
134
let $my_slave_commit= true;
135
--source include/rpl_stmt_seq.inc
137
###############################################################
138
# Cases with (BASE) TABLES and (UPDATABLE) VIEWs
139
###############################################################
141
let $my_stmt= DROP TABLE mysqltest1.t2;
142
let $my_master_commit= true;
143
let $my_slave_commit= true;
144
--source include/rpl_stmt_seq.inc
145
SHOW TABLES LIKE 't2';
148
SELECT '-------- switch to slave --------' as "";
150
SHOW TABLES LIKE 't2';
153
SELECT '-------- switch to master -------' as "";
156
# Note: Since this test is executed with a skip-innodb slave, the
157
# slave incorrectly commits the insert. One can *not* have InnoDB on
158
# master and MyISAM on slave and expect that a transactional rollback
159
# after a CREATE TEMPORARY TABLE should work correctly on the slave.
160
# For this to work properly the handler on the slave must be able to
161
# handle transactions (e.g. InnoDB or NDB).
162
let $my_stmt= DROP TEMPORARY TABLE mysqltest1.t23;
163
let $my_master_commit= false;
164
let $my_slave_commit= false;
165
--source include/rpl_stmt_seq.inc
166
SHOW TABLES LIKE 't23';
169
SELECT '-------- switch to slave --------' as "";
171
SHOW TABLES LIKE 't23';
174
SELECT '-------- switch to master -------' as "";
177
let $my_stmt= RENAME TABLE mysqltest1.t3 to mysqltest1.t20;
178
let $my_master_commit= true;
179
let $my_slave_commit= true;
180
--source include/rpl_stmt_seq.inc
181
SHOW TABLES LIKE 't20';
184
SELECT '-------- switch to slave --------' as "";
186
SHOW TABLES LIKE 't20';
189
SELECT '-------- switch to master -------' as "";
192
let $my_stmt= ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT;
193
let $my_master_commit= true;
194
let $my_slave_commit= true;
195
--source include/rpl_stmt_seq.inc
196
describe mysqltest1.t4;
199
SELECT '-------- switch to slave --------' as "";
201
describe mysqltest1.t4;
204
SELECT '-------- switch to master -------' as "";
207
let $my_stmt= CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE=;
208
let $my_master_commit= true;
209
let $my_slave_commit= true;
210
--source include/rpl_stmt_seq2.inc
212
# Note: Since this test is executed with a skip-innodb slave, the
213
# slave incorrectly commits the insert. One can *not* have InnoDB on
214
# master and MyISAM on slave and expect that a transactional rollback
215
# after a CREATE TEMPORARY TABLE should work correctly on the slave.
216
# For this to work properly the handler on the slave must be able to
217
# handle transactions (e.g. InnoDB or NDB).
221
let $my_stmt= CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT);
222
let $my_master_commit= false;
223
let $my_slave_commit= false;
224
--source include/rpl_stmt_seq.inc
226
let $my_stmt= TRUNCATE TABLE mysqltest1.t7;
227
let $my_master_commit= true;
228
let $my_slave_commit= true;
229
--source include/rpl_stmt_seq.inc
230
SELECT * FROM mysqltest1.t7;
231
--echo -------- switch to slave --------
232
sync_slave_with_master;
233
SELECT * FROM mysqltest1.t7;
234
--echo -------- switch to master -------
237
###############################################################
238
# Cases with LOCK/UNLOCK
239
###############################################################
241
# MySQL insists in locking mysqltest1.t1, because rpl_stmt_seq performs an
242
# INSERT into this table.
243
let $my_stmt= LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ;
244
let $my_master_commit= true;
245
let $my_slave_commit= true;
246
--source include/rpl_stmt_seq.inc
250
let $my_stmt= UNLOCK TABLES;
251
let $my_master_commit= false;
252
let $my_slave_commit= false;
253
--source include/rpl_stmt_seq.inc
255
# With prior read locking
256
# Note that this test generate an error since the rpl_stmt_seq.inc
257
# tries to insert into t1.
258
LOCK TABLES mysqltest1.t1 READ;
259
let $my_stmt= UNLOCK TABLES;
260
let $my_master_commit= false;
261
let $my_slave_commit= false;
262
--source include/rpl_stmt_seq.inc
264
# With prior write locking
265
LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ;
266
let $my_stmt= UNLOCK TABLES;
267
let $my_master_commit= true;
268
let $my_slave_commit= true;
269
--source include/rpl_stmt_seq.inc
271
###############################################################
273
###############################################################
275
let $my_stmt= DROP INDEX my_idx6 ON mysqltest1.t6;
276
let $my_master_commit= true;
277
let $my_slave_commit= true;
278
--source include/rpl_stmt_seq.inc
279
SHOW INDEX FROM mysqltest1.t6;
282
SELECT '-------- switch to slave --------' as "";
284
SHOW INDEX FROM mysqltest1.t6;
287
SELECT '-------- switch to master -------' as "";
290
let $my_stmt= CREATE INDEX my_idx5 ON mysqltest1.t5(f1);
291
let $my_master_commit= true;
292
let $my_slave_commit= true;
293
--source include/rpl_stmt_seq.inc
294
SHOW INDEX FROM mysqltest1.t5;
297
SELECT '-------- switch to slave --------' as "";
299
SHOW INDEX FROM mysqltest1.t5;
302
SELECT '-------- switch to master -------' as "";
305
###############################################################
306
# Cases with DATABASE
307
###############################################################
309
let $my_stmt= DROP DATABASE mysqltest2;
310
let $my_master_commit= true;
311
let $my_slave_commit= true;
312
--source include/rpl_stmt_seq.inc
313
SHOW DATABASES LIKE "mysqltest2";
316
SELECT '-------- switch to slave --------' as "";
318
SHOW DATABASES LIKE "mysqltest2";
321
SELECT '-------- switch to master -------' as "";
324
let $my_stmt= CREATE DATABASE mysqltest3;
325
let $my_master_commit= true;
326
let $my_slave_commit= true;
327
--source include/rpl_stmt_seq.inc
328
SHOW DATABASES LIKE "mysqltest3";
331
SELECT '-------- switch to slave --------' as "";
333
SHOW DATABASES LIKE "mysqltest3";
336
SELECT '-------- switch to master -------' as "";
341
###############################################################
342
# Cases with stored procedures
343
###############################################################
344
let $my_stmt= CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1";
345
let $my_master_commit= true;
346
let $my_slave_commit= true;
347
--source include/rpl_stmt_seq.inc
349
--replace_column 5 # 6 #
350
SHOW PROCEDURE STATUS LIKE 'p1';
352
SELECT '-------- switch to slave -------' as "";
355
--replace_column 5 # 6 #
356
SHOW PROCEDURE STATUS LIKE 'p1';
360
let $my_stmt= ALTER PROCEDURE p1 COMMENT "I have been altered";
361
let $my_master_commit= true;
362
let $my_slave_commit= true;
363
--source include/rpl_stmt_seq.inc
365
--replace_column 5 # 6 #
366
SHOW PROCEDURE STATUS LIKE 'p1';
368
SELECT '-------- switch to slave -------' as "";
371
--replace_column 5 # 6 #
372
SHOW PROCEDURE STATUS LIKE 'p1';
376
let $my_stmt= DROP PROCEDURE p1;
377
let $my_master_commit= true;
378
let $my_slave_commit= true;
379
--source include/rpl_stmt_seq.inc
381
SHOW PROCEDURE STATUS LIKE 'p1';
383
SELECT '-------- switch to slave -------' as "";
386
SHOW PROCEDURE STATUS LIKE 'p1';
390
###############################################################
392
###############################################################
393
let $my_stmt= CREATE OR REPLACE VIEW v1 as select * from t1;
394
let $my_master_commit= true;
395
let $my_slave_commit= true;
396
--source include/rpl_stmt_seq.inc
399
SELECT '-------- switch to slave -------' as "";
405
let $my_stmt= ALTER VIEW v1 AS select f1 from t1;
406
let $my_master_commit= true;
407
let $my_slave_commit= true;
408
--source include/rpl_stmt_seq.inc
411
SELECT '-------- switch to slave -------' as "";
417
let $my_stmt= DROP VIEW IF EXISTS v1;
418
let $my_master_commit= true;
419
let $my_slave_commit= true;
420
--source include/rpl_stmt_seq.inc
424
SELECT '-------- switch to slave -------' as "";
431
###############################################################
432
# Cases with TRIGGERs
433
###############################################################
434
let $my_stmt= CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
435
let $my_master_commit= true;
436
let $my_slave_commit= true;
437
--source include/rpl_stmt_seq.inc
440
SELECT '-------- switch to slave -------' as "";
446
let $my_stmt= DROP TRIGGER trg1;
447
let $my_master_commit= true;
448
let $my_slave_commit= true;
449
--source include/rpl_stmt_seq.inc
452
SELECT '-------- switch to slave -------' as "";
458
###############################################################
460
###############################################################
461
let $my_stmt= CREATE USER user1@localhost;
462
let $my_master_commit= true;
463
let $my_slave_commit= true;
464
--source include/rpl_stmt_seq.inc
465
SELECT user FROM mysql.user WHERE user = 'user1';
467
SELECT '-------- switch to slave -------' as "";
470
SELECT user FROM mysql.user WHERE user = 'user1';
473
let $my_stmt= RENAME USER user1@localhost TO rename1@localhost;
474
let $my_master_commit= true;
475
let $my_slave_commit= true;
476
--source include/rpl_stmt_seq.inc
477
SELECT user FROM mysql.user WHERE user = 'rename1';
479
SELECT '-------- switch to slave -------' as "";
482
SELECT user FROM mysql.user WHERE user = 'rename1';
485
let $my_stmt= DROP USER rename1@localhost;
486
let $my_master_commit= true;
487
let $my_slave_commit= true;
488
--source include/rpl_stmt_seq.inc
489
SELECT user FROM mysql.user WHERE user = 'rename1';
491
SELECT '-------- switch to slave -------' as "";
494
SELECT user FROM mysql.user WHERE user = 'rename1';
497
###############################################################
499
###############################################################
501
DROP DATABASE IF EXISTS mysqltest1;
502
DROP DATABASE IF EXISTS mysqltest2;
503
DROP DATABASE IF EXISTS mysqltest3;