Debian Codegraph @

mysql-test/suite/rpl/include/rpl_ddl.test

raw
1 ################# suite/rpl/include/rpl_ddl.test ########################
2 # #
3 # DDL statements (sometimes with implicit COMMIT) and other stuff #
4 # executed on the master and it's propagation into the slave. #
5 # #
6 # The variables #
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 #
16 # results #
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 #
21 # 0 - do nothing #
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. #
31 # #
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. #
35 # #
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 #
43 # AUTOCOMMIT mode #
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. #
49 # #
50 #######################################################################
51
52 # Last update:
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
58 #
59 #
60 # NOTES:
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
63 # do changes to it.
64 #
65 # PLEASE BE CAREFUL, WHEN MODIFYING THE TESTS !!
66 #
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.
82 # 7. Flush the logs
83 #
84 # Some rules:
85 # -----------
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.
88 #
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.
95 #
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.
99 #
100 # 4. Never use a test object, which was direct or indirect affected by a
101 # preceding test sequence again.
102 # If one preceding 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.
109 #
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.
113 #
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.
117 # Example:
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.
126 #
127
128 ###############################################################
129 # Some preparations
130 ###############################################################
131 set global sql_mode='';
132 set local sql_mode='';
133 # The sync_slave_with_master is needed to make the xids deterministic.
134 sync_slave_with_master;
135
136 connection master;
137 SET AUTOCOMMIT = 1;
138 #
139 # 2. CREATE all objects needed
140 # working database is mysqltest1
141 # working table (transactional!) is mysqltest1.t1
142 #
143 CREATE DATABASE mysqltest1;
144 CREATE DATABASE mysqltest2;
145 eval CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE=$engine_type;
146 # Prevent Bug#26687 rpl_ddl test fails if run with --innodb option
147 # The testscript (suite/rpl/rpl_ddl.test) + the expected result need that the
148 # slave uses MyISAM for the table mysqltest.t1.
149 sync_slave_with_master;
150 connection slave;
151 if (`SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
152 WHERE TABLE_SCHEMA = 'mysqltest1' AND TABLE_NAME = 't1' AND ENGINE <> 'MyISAM'`)
153 {
154 skip This test needs on slave side: InnoDB disabled, default engine: MyISAM;
155 }
156 connection master;
157 INSERT INTO mysqltest1.t1 SET f1= 0;
158 eval CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE=$engine_type;
159 eval CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE=$engine_type;
160 eval CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE=$engine_type;
161 eval CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE=$engine_type;
162 eval CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE=$engine_type;
163 CREATE INDEX my_idx6 ON mysqltest1.t6(f1);
164 eval CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE=$engine_type;
165 INSERT INTO mysqltest1.t7 SET f1= 0;
166 eval CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE=$engine_type;
167 eval CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE=$engine_type;
168 eval CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE=$engine_type;
169 eval CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE=$engine_type;
170 eval CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE=$engine_type;
171 eval CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE=$engine_type;
172 eval CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE=$engine_type;
173 eval CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE=$engine_type;
174 eval CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE=$engine_type;
175 eval CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE=$engine_type;
176 eval CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE=$engine_type;
177 eval CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE=$engine_type;
178 eval CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) ENGINE=$temp_engine_type;
179
180 #
181 # 3. master sessions: never do AUTOCOMMIT
182 # slave sessions: do AUTOCOMMIT
183 #
184 SET AUTOCOMMIT = 0;
185 use mysqltest1;
186 sync_slave_with_master;
187 connection slave;
188 SET AUTOCOMMIT = 1;
189 use mysqltest1;
190 connection master;
191
192
193 # We don't want to abort the whole test if one statement sent
194 # to the server gets an error, because the following test
195 # sequences are nearly independend of the previous statements.
196 --disable_abort_on_error
197
198 ###############################################################
199 # Banal case: commands which should never commit
200 # Just for checking if the test sequence is usable
201 ###############################################################
202
203 let $my_stmt= SELECT 1;
204 let $my_master_commit= false;
205 let $my_slave_commit= false;
206 --source include/rpl_stmt_seq.inc
207
208 let $my_stmt= SELECT COUNT(*) FROM t1;
209 let $my_master_commit= false;
210 let $my_slave_commit= false;
211 --source include/rpl_stmt_seq.inc
212
213 ###############################################################
214 # Banal case: (explicit) COMMIT and ROLLBACK
215 # Just for checking if the test sequence is usable
216 ###############################################################
217
218 let $my_stmt= COMMIT;
219 let $my_master_commit= true;
220 let $my_slave_commit= true;
221 --source include/rpl_stmt_seq.inc
222
223 let $my_stmt= ROLLBACK;
224 let $my_master_commit= false;
225 let $my_slave_commit= false;
226 --source include/rpl_stmt_seq.inc
227
228 ###############################################################
229 # Cases with commands very similar to COMMIT
230 ###############################################################
231
232 let $my_stmt= SET AUTOCOMMIT=1;
233 let $my_master_commit= true;
234 let $my_slave_commit= true;
235 --source include/rpl_stmt_seq.inc
236 SET AUTOCOMMIT=0;
237
238 let $my_stmt= START TRANSACTION;
239 let $my_master_commit= true;
240 let $my_slave_commit= true;
241 --source include/rpl_stmt_seq.inc
242
243 let $my_stmt= BEGIN;
244 let $my_master_commit= true;
245 let $my_slave_commit= true;
246 --source include/rpl_stmt_seq.inc
247
248 ###############################################################
249 # Cases with (BASE) TABLES and (UPDATABLE) VIEWs
250 ###############################################################
251
252 let $my_stmt= DROP TABLE mysqltest1.t2;
253 let $my_master_commit= true;
254 let $my_slave_commit= true;
255 --source include/rpl_stmt_seq.inc
256 SHOW TABLES LIKE 't2';
257 connection slave;
258 SHOW TABLES LIKE 't2';
259 connection master;
260
261 let $my_stmt= DROP TEMPORARY TABLE mysqltest1.t23;
262 let $my_master_commit= false;
263 let $my_slave_commit= false;
264 --source include/rpl_stmt_seq.inc
265 SHOW TABLES LIKE 't23';
266 connection slave;
267 SHOW TABLES LIKE 't23';
268 connection master;
269
270 let $my_stmt= RENAME TABLE mysqltest1.t3 to mysqltest1.t20;
271 let $my_master_commit= true;
272 let $my_slave_commit= true;
273 --source include/rpl_stmt_seq.inc
274 SHOW TABLES LIKE 't20';
275 connection slave;
276 SHOW TABLES LIKE 't20';
277 connection master;
278
279 let $my_stmt= ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT;
280 let $my_master_commit= true;
281 let $my_slave_commit= true;
282 --source include/rpl_stmt_seq.inc
283 describe mysqltest1.t4;
284 connection slave;
285 describe mysqltest1.t4;
286 connection master;
287
288 let $my_stmt= CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= $engine_type;
289 let $my_master_commit= true;
290 let $my_slave_commit= true;
291 --source include/rpl_stmt_seq.inc
292
293 let $engine='';
294 let $eng_type='';
295
296 let $my_stmt= CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ENGINE=$temp_engine_type;
297 let $my_master_commit= false;
298 let $my_slave_commit= false;
299 --source include/rpl_stmt_seq.inc
300
301 let $my_stmt= TRUNCATE TABLE mysqltest1.t7;
302 let $my_master_commit= true;
303 let $my_slave_commit= true;
304 --source include/rpl_stmt_seq.inc
305 SELECT * FROM mysqltest1.t7;
306 sync_slave_with_master;
307 connection slave;
308 SELECT * FROM mysqltest1.t7;
309 connection master;
310
311 ###############################################################
312 # Cases with LOCK/UNLOCK
313 ###############################################################
314
315 # Attention:
316 # We have to LOCK mysqltest1.t1 here, though it violates the testing
317 # philosophy.
318 # Mysql response in case without previous LOCK TABLES mysqltest1.t1
319 # is:
320 # SELECT MAX(...) FROM mysqltest1.t1 is
321 # ERROR HY000: Table 't1' was not locked with LOCK TABLES
322 let $my_stmt= LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ;
323 let $my_master_commit= true;
324 let $my_slave_commit= true;
325 --source include/rpl_stmt_seq.inc
326 UNLOCK TABLES;
327
328 # No prior locking
329 let $my_stmt= UNLOCK TABLES;
330 let $my_master_commit= false;
331 let $my_slave_commit= false;
332 --source include/rpl_stmt_seq.inc
333
334 # With prior read locking
335 # Attention:
336 # This subtest generates an error since the rpl_stmt_seq.inc
337 # tries to insert into t1.
338 LOCK TABLES mysqltest1.t1 READ;
339 let $my_stmt= UNLOCK TABLES;
340 let $my_master_commit= false;
341 let $my_slave_commit= false;
342 --source include/rpl_stmt_seq.inc
343
344 # With prior write locking
345 LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ;
346 let $my_stmt= UNLOCK TABLES;
347 let $my_master_commit= true;
348 let $my_slave_commit= true;
349 --source include/rpl_stmt_seq.inc
350
351 ###############################################################
352 # Cases with INDEXES
353 ###############################################################
354
355 let $my_stmt= DROP INDEX my_idx6 ON mysqltest1.t6;
356 let $my_master_commit= true;
357 let $my_slave_commit= true;
358 --source include/rpl_stmt_seq.inc
359 SHOW INDEX FROM mysqltest1.t6;
360 connection slave;
361 SHOW INDEX FROM mysqltest1.t6;
362 connection master;
363
364 let $my_stmt= CREATE INDEX my_idx5 ON mysqltest1.t5(f1);
365 let $my_master_commit= true;
366 let $my_slave_commit= true;
367 --source include/rpl_stmt_seq.inc
368 SHOW INDEX FROM mysqltest1.t5;
369 connection slave;
370 SHOW INDEX FROM mysqltest1.t5;
371 connection master;
372
373 ###############################################################
374 # Cases with DATABASE
375 ###############################################################
376
377 let $my_stmt= DROP DATABASE mysqltest2;
378 let $my_master_commit= true;
379 let $my_slave_commit= true;
380 --source include/rpl_stmt_seq.inc
381 SHOW DATABASES LIKE "mysqltest2";
382 connection slave;
383 SHOW DATABASES LIKE "mysqltest2";
384 connection master;
385
386 let $my_stmt= CREATE DATABASE mysqltest3;
387 let $my_master_commit= true;
388 let $my_slave_commit= true;
389 --source include/rpl_stmt_seq.inc
390 SHOW DATABASES LIKE "mysqltest3";
391 connection slave;
392 SHOW DATABASES LIKE "mysqltest3";
393 connection master;
394
395 # End of 4.1 tests
396
397 ###############################################################
398 # Cases with STORED PROCEDUREs
399 ###############################################################
400 let $my_stmt= CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1";
401 let $my_master_commit= true;
402 let $my_slave_commit= true;
403 --source include/rpl_stmt_seq.inc
404 --vertical_results
405 --replace_column 5 # 6 #
406 SHOW PROCEDURE STATUS LIKE 'p1';
407 connection slave;
408 --replace_column 5 # 6 #
409 SHOW PROCEDURE STATUS LIKE 'p1';
410 connection master;
411 --horizontal_results
412
413 let $my_stmt= ALTER PROCEDURE p1 COMMENT "I have been altered";
414 let $my_master_commit= true;
415 let $my_slave_commit= true;
416 --source include/rpl_stmt_seq.inc
417 --vertical_results
418 --replace_column 5 # 6 #
419 SHOW PROCEDURE STATUS LIKE 'p1';
420 connection slave;
421 --replace_column 5 # 6 #
422 SHOW PROCEDURE STATUS LIKE 'p1';
423 connection master;
424 --horizontal_results
425
426 let $my_stmt= DROP PROCEDURE p1;
427 let $my_master_commit= true;
428 let $my_slave_commit= true;
429 --source include/rpl_stmt_seq.inc
430 --vertical_results
431 SHOW PROCEDURE STATUS LIKE 'p1';
432 connection slave;
433 SHOW PROCEDURE STATUS LIKE 'p1';
434 connection master;
435 --horizontal_results
436
437 ###############################################################
438 # Cases with VIEWs
439 ###############################################################
440 let $my_stmt= CREATE OR REPLACE VIEW v1 as select * from t1;
441 let $my_master_commit= true;
442 let $my_slave_commit= true;
443 --source include/rpl_stmt_seq.inc
444 SHOW CREATE VIEW v1;
445 connection slave;
446 SHOW CREATE VIEW v1;
447 connection master;
448
449 let $my_stmt= ALTER VIEW v1 AS select f1 from t1;
450 let $my_master_commit= true;
451 let $my_slave_commit= true;
452 --source include/rpl_stmt_seq.inc
453 SHOW CREATE VIEW v1;
454 connection slave;
455 SHOW CREATE VIEW v1;
456 connection master;
457
458 let $my_stmt= DROP VIEW IF EXISTS v1;
459 let $my_master_commit= true;
460 let $my_slave_commit= true;
461 --source include/rpl_stmt_seq.inc
462 --error 1146
463 SHOW CREATE VIEW v1;
464 connection slave;
465 --error 1146
466 SHOW CREATE VIEW v1;
467 connection master;
468
469 ###############################################################
470 # Cases with TRIGGERs
471 ###############################################################
472 let $my_stmt= CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
473 let $my_master_commit= true;
474 let $my_slave_commit= true;
475 --source include/rpl_stmt_seq.inc
476 --replace_column 6 #
477 SHOW TRIGGERS;
478 connection slave;
479 --replace_column 6 #
480 SHOW TRIGGERS;
481 connection master;
482
483 let $my_stmt= DROP TRIGGER trg1;
484 let $my_master_commit= true;
485 let $my_slave_commit= true;
486 --source include/rpl_stmt_seq.inc
487 SHOW TRIGGERS;
488 connection slave;
489 SHOW TRIGGERS;
490 connection master;
491
492 ###############################################################
493 # Cases with USERs
494 ###############################################################
495 let $my_stmt= CREATE USER user1@localhost;
496 let $my_master_commit= true;
497 let $my_slave_commit= true;
498 --source include/rpl_stmt_seq.inc
499 SELECT user FROM mysql.user WHERE user = 'user1';
500 connection slave;
501 SELECT user FROM mysql.user WHERE user = 'user1';
502 connection master;
503
504 let $my_stmt= RENAME USER user1@localhost TO rename1@localhost;
505 let $my_master_commit= true;
506 let $my_slave_commit= true;
507 --source include/rpl_stmt_seq.inc
508 SELECT user FROM mysql.user WHERE user = 'rename1';
509 connection slave;
510 SELECT user FROM mysql.user WHERE user = 'rename1';
511 connection master;
512
513 let $my_stmt= DROP USER rename1@localhost;
514 let $my_master_commit= true;
515 let $my_slave_commit= true;
516 --source include/rpl_stmt_seq.inc
517 SELECT user FROM mysql.user WHERE user = 'rename1';
518 connection slave;
519 SELECT user FROM mysql.user WHERE user = 'rename1';
520
521 ###############################################################
522 # Cleanup
523 ###############################################################
524 use test;
525 connection master;
526 DROP TEMPORARY TABLE mysqltest1.t22;
527 DROP DATABASE mysqltest1;
528 # mysqltest2 was alreday DROPPED some tests before.
529 DROP DATABASE mysqltest3;
530
531 set global sql_mode=default;