1
######################## rpl_ddl.test ########################
3
# DDL statements (sometimes with implicit COMMIT) executed #
4
# by the master and it's propagation into the slave #
6
##############################################################
1
################# extra/rpl_tests/rpl_ddl.test ########################
3
# DDL statements (sometimes with implicit COMMIT) and other stuff #
4
# executed on the master and it's propagation into the slave. #
7
# $engine_type -- storage engine to be tested/used for the #
8
# permanent tables within the master #
9
# $temp_engine_type -- storage engine which supports TEMPORARY #
10
# tables <> $engine_type #
11
# $temp_engine_type must point to an all #
12
# time available storage engine #
13
# 2007-02 MySQL 5.1 MyISAM and MEMORY only #
14
# $show_binlog -- print binlog entries #
15
# 0 - no (default) + fits to the file with #
17
# 1 - yes (important for debugging) #
18
# This variable is used within #
19
# include/rpl_stmt_seq.inc. #
20
# $manipulate -- Manipulation of the binary logs #
22
# 1 - so that the output of SHOW BINLOG #
23
# EVENTS IN <current log> contains only #
24
# commands of the current test sequence #
25
# This is especially useful, if the #
26
# $show_binlog is set to 1 and many #
27
# subtest are executed. #
28
# This variable is used within #
29
# include/rpl_stmt_seq.inc. #
30
# have to be set before sourcing this script. #
32
# General assumption about the ideal replication behaviour: #
33
# Whatever on the master is executed the content of the slave must #
34
# be in sync with it. #
36
# Tests of special interest: #
37
# a) Which DDL commands cause an implicit COMMIT ? #
38
# This is also of interest outside of replication. #
39
# b) Transactions modifying table content ending with #
40
# - explicit COMMIT or ROLLBACK #
41
# - implicit COMMIT because the connection to the master #
42
# executed a corresponding DDL statement or runs in #
44
# - something similar to "implicit COMMIT" if the storage #
45
# engine (master) is not transactional #
46
# c) Command which change no data like SELECT or SHOW #
47
# They do not change anything within the master but #
48
# this must be also valid for the slave. #
50
#######################################################################
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 "";
53
# 2007-02-12 ML: - slave needs AUTOCOMMIT = 1, because we want to check only
54
# the propagation of actions of the master connection.
55
# - replace comments via SQL by "--echo ..."
56
# - remove some bugs within the testscripts
57
# - remove the use of include/rpl_stmt_seq2.inc
61
# 2006-11-15 Lars: Matthias (ML) is the "owner" of this test case.
62
# So, please get him to review it whenever you want to
65
# PLEASE BE CAREFUL, WHEN MODIFYING THE TESTS !!
67
# Typical test architecture (--> include/rpl_stmt_seq.inc)
68
# --------------------------------------------------------
69
# 1. Master (no AUTOCOMMIT!): INSERT INTO mysqltest1.t1 without commit
70
# 2. Master and slave: Check the content of mysqltest1.t1
71
# 3. Master (no AUTOCOMMIT!): EXECUTE the statement to be tested
72
# 4. Master and slave: Check the content of mysqltest1.t1
73
# 5. Master (no AUTOCOMMIT!): ROLLBACK
74
# 6. Master and slave: Check the content of mysqltest1.t1
75
# If the previous into mysqltest1.t1 inserted row is visible,
76
# than the statement to be tested caused an explicit COMMIT
77
# (statement = COMMIT) or an implicit COMMIT (example CREATE TABLE).
78
# If the previous into mysqltest1.t1 inserted row is not visible,
79
# than the statement to be tested caused either an explicit ROLLBACK
80
# (statement = ROLLBACK), an implicit ROLLBACK (deadlock etc. but
81
# not tested here) or it does not cause any transaction end.
86
# 1. Any use of mysqltest1.t1 within the statement to be tested must be
87
# avoided if possible. The only known exception is around LOCK TABLE.
89
# 2. The test logics needs for
90
# master connection: AUTOCOMMIT = 0
91
# slave connection: AUTOCOMMIT = 1
92
# The master connection is the actor and the slave connection is
93
# only an observer. I.e. the slave connection must not influence
94
# the activities of master connection.
96
# 3. !All! objects to be dropped, renamed, altered ... must be created
97
# before the tests start.
98
# --> less switching of AUTOCOMMIT mode on master side.
100
# 4. Never use a test object, which was direct or indirect affected by a
101
# preceeding test sequence again.
102
# If one preceeding test sequence hits a (sometimes not visible,
103
# because the sql error code of the statement might be 0) bug
104
# and these rules are ignored, a following test sequence might earn ugly
105
# effects like failing 'sync_slave_with_master', crashes of the slave or
106
# abort of the test case etc.. This means during analysis the first look
107
# points into a totally wrong area.
108
# Except table mysqltest1.t1 where ONLY DML is allowed.
110
# 5. This file is used in several tests (t/rpl_ddl_<whatever>.test).
111
# Please be aware that every change of the current file affects
112
# the results of these tests.
114
# ML: Some maybe banal hints:
115
# 1. The fact that we have here a master - slave replication does
116
# not cause that many general MySQL properties do not apply.
118
# The connection to the slave is just a simple session and not a however
119
# magic working "copy" of the master session or something similar.
120
# - TEMPORARY TABLES and @variables are session specific
121
# - the slave session cannot see these things of the master.
122
# 2. The slave connection must not call sync_slave_with_master.
123
# 3. SHOW STATUS SLAVE must be run within the slave connection.
124
# 4. Testcase analysis becomes much more comfortable if
125
# $show_binlog within include/rpl_stmt_seq.inc is set to 1.
31
128
###############################################################
143
255
let $my_slave_commit= true;
144
256
--source include/rpl_stmt_seq.inc
145
257
SHOW TABLES LIKE 't2';
259
--echo -------- switch to slave --------
146
260
connection slave;
148
SELECT '-------- switch to slave --------' as "";
150
261
SHOW TABLES LIKE 't2';
263
--echo -------- switch to master -------
151
264
connection master;
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
266
let $my_stmt= DROP TEMPORARY TABLE mysqltest1.t23;
163
267
let $my_master_commit= false;
164
let $my_slave_commit= true;
268
let $my_slave_commit= false;
165
269
--source include/rpl_stmt_seq.inc
166
270
SHOW TABLES LIKE 't23';
272
--echo -------- switch to slave --------
167
273
connection slave;
169
SELECT '-------- switch to slave --------' as "";
171
274
SHOW TABLES LIKE 't23';
276
--echo -------- switch to master -------
172
277
connection master;
174
SELECT '-------- switch to master -------' as "";
177
279
let $my_stmt= RENAME TABLE mysqltest1.t3 to mysqltest1.t20;
178
280
let $my_master_commit= true;
179
281
let $my_slave_commit= true;
180
282
--source include/rpl_stmt_seq.inc
181
283
SHOW TABLES LIKE 't20';
285
--echo -------- switch to slave --------
182
286
connection slave;
184
SELECT '-------- switch to slave --------' as "";
186
287
SHOW TABLES LIKE 't20';
289
--echo -------- switch to master -------
187
290
connection master;
189
SELECT '-------- switch to master -------' as "";
192
292
let $my_stmt= ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT;
193
293
let $my_master_commit= true;
194
294
let $my_slave_commit= true;
195
295
--source include/rpl_stmt_seq.inc
196
296
describe mysqltest1.t4;
298
--echo -------- switch to slave --------
197
299
connection slave;
199
SELECT '-------- switch to slave --------' as "";
201
300
describe mysqltest1.t4;
302
--echo -------- switch to master -------
202
303
connection master;
204
SELECT '-------- switch to master -------' as "";
207
let $my_stmt= CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE=;
305
let $my_stmt= CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= $engine_type;
208
306
let $my_master_commit= true;
209
307
let $my_slave_commit= true;
210
--source include/rpl_stmt_seq2.inc
308
--source include/rpl_stmt_seq.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).
219
311
let $eng_type='';
221
let $my_stmt= CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT);
313
let $my_stmt= CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ENGINE=$temp_engine_type;
222
314
let $my_master_commit= false;
223
let $my_slave_commit= true;
315
let $my_slave_commit= false;
224
316
--source include/rpl_stmt_seq.inc
226
318
let $my_stmt= TRUNCATE TABLE mysqltest1.t7;
311
408
let $my_slave_commit= true;
312
409
--source include/rpl_stmt_seq.inc
313
410
SHOW DATABASES LIKE "mysqltest2";
412
--echo -------- switch to slave --------
314
413
connection slave;
316
SELECT '-------- switch to slave --------' as "";
318
414
SHOW DATABASES LIKE "mysqltest2";
416
--echo -------- switch to master -------
319
417
connection master;
321
SELECT '-------- switch to master -------' as "";
324
419
let $my_stmt= CREATE DATABASE mysqltest3;
325
420
let $my_master_commit= true;
326
421
let $my_slave_commit= true;
327
422
--source include/rpl_stmt_seq.inc
328
423
SHOW DATABASES LIKE "mysqltest3";
425
--echo -------- switch to slave --------
329
426
connection slave;
331
SELECT '-------- switch to slave --------' as "";
333
427
SHOW DATABASES LIKE "mysqltest3";
429
--echo -------- switch to master -------
334
430
connection master;
336
SELECT '-------- switch to master -------' as "";
339
432
# End of 4.1 tests
341
434
###############################################################
342
# Cases with stored procedures
435
# Cases with STORED PROCEDUREs
343
436
###############################################################
344
437
let $my_stmt= CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1";
345
438
let $my_master_commit= true;