Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/transactional_ddl_locking.test

raw
1 ################# t/transactional_ddl_locking.test ###################
2 # #
3 # Tests ensure that concurrent DDL operations should not be allowed #
4 # on the table that are already in use inside an active transaction. #
5 # #
6 # In order to ensure serialization in binary log we will need full #
7 # mirrored replication setup and master and slave will contain the #
8 # same schema/database structure. #
9 # #
10 # Contents of slave and master should be the same ie result sets are #
11 # correct and slave does not fail with any error like row not found. #
12 # #
13 # Creation: #
14 # 2012-10-08 shipjain Implement this test as a part of WL4986 #
15 # Transactional DDL locking #
16 # #
17 ######################################################################
18
19 --source include/big_test.inc
20 --source include/have_myisam.inc
21 --source include/no_ps_protocol.inc
22 --source include/set_privilege_checks_user_as_system_user.inc
23 --let $rpl_privilege_checks_user_grant_option = 1
24 --source include/master-slave.inc
25
26 --echo # Prepare playground for test by creating tables
27
28 CREATE TABLE t1 (a INT) ;
29 CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
30 CREATE TABLE t3 (a INT);
31 CREATE TABLE t4 (a INT);
32 CREATE TABLE t1part (id INT, year_col INT);
33 CREATE TABLE t2part (id INT, year_col INT)
34 PARTITION BY RANGE (year_col) (
35 PARTITION p0 VALUES LESS THAN (1991),
36 PARTITION p1 VALUES LESS THAN (1995),
37 PARTITION p2 VALUES LESS THAN (2013));
38 CREATE TABLE t3part (id INT, year_col INT)
39 PARTITION BY HASH(year_col)
40 PARTITIONS 4;
41 CREATE TABLE t4part (id INT, year_col INT)
42 PARTITION BY RANGE (year_col) (
43 PARTITION p0 VALUES LESS THAN (1970),
44 PARTITION p1 VALUES LESS THAN (1995),
45 PARTITION p2 VALUES LESS THAN (2013));
46 CREATE TABLE t1prim (a INT, b INT PRIMARY KEY);
47 CREATE TABLE t1opt (id INT , content TEXT, author_id INT,
48 article_title VARCHAR(120), article_hash INT);
49 CREATE TABLE t1bin (c INT);
50 CREATE TABLE t2bin SELECT * FROM t1bin;
51 CREATE TABLE t1myisam (c INT) ENGINE = MYISAM;
52 CREATE TABLE t1innodb (c INT) ENGINE = INNODB;
53 CREATE TABLE t3967_1 (id INT(11) NOT NULL AUTO_INCREMENT,
54 b INT(11) DEFAULT NULL, PRIMARY KEY (id))
55 ENGINE = INNODB;
56 CREATE TABLE t3967_2 (id INT(11) NOT NULL AUTO_INCREMENT,
57 b INT(11) DEFAULT NULL, PRIMARY KEY (id))
58 ENGINE = INNODB;
59 CREATE TABLE `t1_30138` (id INT) ENGINE = INNODB;
60 COMMIT;
61
62 connect (master2,localhost,root,,);
63 connect (master3,localhost,root,,);
64
65 # Concurrent execution of DROP TABLE and INSERT inside an active transaction.
66
67 --echo --- connection master ---
68 connection master;
69 BEGIN;
70 INSERT INTO t1 VALUES (1);
71
72 --echo --- connection master1 ---
73 connection master1;
74 SET SESSION lock_wait_timeout = 1;
75
76 --echo send statement and reap result later ---
77 --send DROP TABLE t1;
78
79 --echo --- connection master2 ---
80 connection master2;
81 let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
82 WHERE state = 'Waiting for TABLE metadata lock'
83 AND info = 'DROP TABLE t1'`;
84
85 --echo --- connection master1 ---
86 connection master1;
87 --echo reap result of previous ---
88 --error ER_LOCK_WAIT_TIMEOUT
89 --reap
90
91 --echo --- connection master ---
92 connection master;
93 COMMIT;
94 --sync_slave_with_master
95
96 --echo --- connection slave ---
97 connection slave;
98 SELECT COUNT(*) FROM t1 ;
99 --echo --- connection master ---
100 connection master;
101 SELECT COUNT(*) FROM t1 ;
102
103 --let $diff_tables= master:t1, slave:t1
104 --source include/diff_tables.inc
105
106
107 # Concurrent execution of ALTER TABLE RENAME TO and INSERT inside an active transaction
108
109 --echo --- connection master ---
110 connection master;
111 BEGIN;
112 INSERT INTO t1 VALUES (1);
113
114 --echo --- connection master1 ---
115 connection master1;
116 SET SESSION lock_wait_timeout = 1;
117 --echo send statement and reap result later ---
118 --send ALTER TABLE t1 RENAME TO t1new;
119
120 --echo --- connection master2 ---
121 connection master2;
122 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
123 WHERE state = 'Waiting for TABLE metadata lock'
124 AND info = 'ALTER TABLE t1 RENAME TO t1new'`;
125
126 --echo --- connection master1 ---
127 connection master1;
128 --echo reap result of previous---
129 --error ER_LOCK_WAIT_TIMEOUT
130 --reap
131
132 --echo --- connection master ---
133 connection master;
134 COMMIT;
135 --sync_slave_with_master
136
137 --echo --- connection slave ---
138 connection slave;
139 SELECT COUNT(*) FROM t1 ;
140
141 --echo --- connection master ---
142 connection master;
143 SELECT COUNT(*) FROM t1 ;
144
145 --let $diff_tables= master:t1, slave:t1
146 --source include/diff_tables.inc
147
148
149 # Concurrent execution of ALTER TABLE DROP COLUMN and INSERT inside an active transaction
150
151 --echo --- connection master ---
152 connection master;
153 BEGIN;
154 INSERT INTO t2 VALUES (1,1);
155
156 --echo --- connection master1 ---
157 connection master1;
158 SET SESSION lock_wait_timeout = 1;
159 --echo send statement and reap result later ---
160 --send ALTER TABLE t2 DROP COLUMN a;
161
162 --echo --- connection master2 ---
163 connection master2;
164 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
165 WHERE state = 'Waiting for TABLE metadata lock'
166 AND info = 'ALTER TABLE t2 DROP COLUMN a'`;
167
168 --echo --- connection master1 ---
169 connection master1;
170 --echo reap result of previous---
171 --error ER_LOCK_WAIT_TIMEOUT
172 --reap
173
174 --echo --- connection master ---
175 connection master;
176 COMMIT;
177 --sync_slave_with_master
178
179 --echo --- connection slave ---
180 connection slave;
181 SELECT COUNT(*) FROM t2 ;
182 --echo --- connection master ---
183 connection master;
184 SELECT COUNT(*) FROM t2 ;
185
186 --let $diff_tables= master:t2, slave:t2
187 --source include/diff_tables.inc
188
189
190 # Concurrent execution of ALTER TABLE ADD COLUMN and UPDATE inside an active transaction
191
192 --echo --- connection master ---
193 connection master;
194 BEGIN;
195 UPDATE t1 SET a = 2;
196
197 --echo --- connection master1 ---
198 connection master1;
199 SET SESSION lock_wait_timeout = 1;
200 --echo send statement and reap result later ---
201 --send ALTER TABLE t1 ADD COLUMN b INT;
202
203 --echo --- connection master2 ---
204 connection master2;
205 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
206 WHERE state = 'Waiting for TABLE metadata lock'
207 AND info = 'ALTER TABLE t1 ADD COLUMN b INT'`;
208
209 --echo --- connection master1 ---
210 connection master1;
211 --echo reap result of previous---
212 --error ER_LOCK_WAIT_TIMEOUT
213 --reap
214
215 --echo --- connection master ---
216 connection master;
217 COMMIT;
218 --sync_slave_with_master
219
220 --echo --- connection slave ---
221 connection slave;
222 SELECT COUNT(*) FROM t1 ;
223 --echo --- connection master ---
224 connection master;
225 SELECT COUNT(*) FROM t1 ;
226
227 --let $diff_tables= master:t1, slave:t1
228 --source include/diff_tables.inc
229
230
231 # Concurrent execution of RENAME TABLE and INSERT inside an active transaction
232
233 --echo --- connection master ---
234 connection master;
235 BEGIN;
236 INSERT INTO t1 VALUES (1);
237
238 --echo --- connection master1 ---
239 connection master1;
240 SET SESSION lock_wait_timeout = 1;
241 --echo send statement and reap result later ---
242 --send RENAME TABLE t1 to t2new;
243
244 --echo --- connection master2 ---
245 connection master2;
246 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
247 WHERE state = 'Waiting for TABLE metadata lock'
248 AND info = 'RENAME TABLE t1 to t2new'`;
249
250 --echo --- connection master1 ---
251 connection master1;
252 --echo reap result of previous---
253 --error ER_LOCK_WAIT_TIMEOUT
254 --reap
255
256 --echo --- connection master ---
257 connection master;
258 COMMIT;
259 --sync_slave_with_master
260
261 --echo --- connection slave ---
262 connection slave;
263 SELECT COUNT(*) FROM t1 ;
264 --echo --- connection master ---
265 connection master;
266 SELECT COUNT(*) FROM t1 ;
267
268 --let $diff_tables= master:t1, slave:t1
269 --source include/diff_tables.inc
270
271
272 # Concurrent execution of ALTER TABLE ADD COMMENT and INSERT inside an active transaction
273
274 --echo --- connection master ---
275 connection master;
276 BEGIN;
277 INSERT INTO t1 VALUES (1);
278
279 --echo --- connection master1 ---
280 connection master1;
281 SET SESSION lock_wait_timeout = 1;
282 --echo send statement and reap result later ---
283 --send ALTER TABLE t1 MODIFY a bigINT COMMENT 'first';
284
285 --echo --- connection master2 ---
286 connection master2;
287 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
288 WHERE state = 'Waiting for TABLE metadata lock'
289 AND info = 'ALTER TABLE t1 MODIFY a bigINT COMMENT \'first\''`;
290
291 --echo --- connection master1 ---
292 connection master1;
293 --echo reap result of previous---
294 --error ER_LOCK_WAIT_TIMEOUT
295 --reap
296
297 connection master;
298 COMMIT;
299 --sync_slave_with_master
300
301 connection slave;
302 SELECT COUNT(*) FROM t1 ;
303 connection master;
304 SELECT COUNT(*) FROM t1 ;
305
306 --let $diff_tables= master:t1, slave:t1
307 --source include/diff_tables.inc
308
309
310 # Concurrent execution of ALTER TABLE ADD CONSTRAINT and UPDATE inside an active transaction
311
312 --echo --- connection master ---
313 connection master;
314 BEGIN;
315 UPDATE t1 SET a = 1;
316
317 --echo --- connection master1 --
318 connection master1;
319 SET SESSION lock_wait_timeout = 1;
320 --echo send statement and reap result later ---
321 --send ALTER TABLE t1 ADD PRIMARY KEY (a);
322
323 --echo --- connection master2 ---
324 connection master2;
325 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
326 WHERE state = 'Waiting for TABLE metadata lock'
327 AND info = 'ALTER TABLE t1 ADD PRIMARY Key (a)'`;
328
329 --echo --- connection master1 ---
330 connection master1;
331 --echo reap result of previous---
332 --error ER_LOCK_WAIT_TIMEOUT
333 --reap
334
335 --echo --- connection master ---
336 connection master;
337 COMMIT;
338 --sync_slave_with_master
339
340 --echo --- connection slave ---
341 connection slave;
342 SELECT COUNT(*) FROM t1 ;
343 --echo --- connection master ---
344 connection master;
345 SELECT COUNT(*) FROM t1 ;
346
347 --let $diff_tables= master:t1, slave:t1
348 --source include/diff_tables.inc
349
350
351 # Concurrent execution of ALTER TABLE ADD CONSTRAINT and UPDATE inside an active transaction
352
353 --echo --- connection master ---
354 connection master;
355 BEGIN;
356 UPDATE t1 SET a = 1;
357
358 --echo --- connection master1 ---
359 connection master1;
360 SET SESSION lock_wait_timeout = 1;
361 --echo send statement and reap result later ---
362 --send ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (a) ;
363
364 --echo --- connection master2 ---
365 connection master2;
366 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
367 WHERE state = 'Waiting for TABLE metadata lock'
368 AND info = 'ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (a) '`;
369
370 --echo --- connection master1 ---
371 connection master1;
372 --echo reap result of previous---
373 --error ER_LOCK_WAIT_TIMEOUT
374 --reap
375
376 --echo --- connection master ---
377 connection master;
378 COMMIT;
379 --sync_slave_with_master
380
381 --echo --- connection slave ---
382 connection slave;
383 SELECT COUNT(*) FROM t1 ;
384 --echo --- connection master ---
385 connection master;
386 SELECT COUNT(*) FROM t1 ;
387
388 --let $diff_tables= master:t1, slave:t1
389 --source include/diff_tables.inc
390
391
392 # Concurrent execution of ALTER TABLE ALTER COLUMN and UPDATE inside an active transaction
393
394 --echo --- connection master ---
395 connection master;
396 BEGIN;
397 UPDATE t1 SET a = 1;
398
399 --echo --- connection master1 ---
400 connection master1;
401 SET SESSION lock_wait_timeout = 1;
402 --echo send statement and reap result later ---
403 --send ALTER TABLE t1 ALTER a SET DEFAULT 1;
404
405 --echo --- connection master2 ---
406 connection master2;
407 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
408 WHERE state = 'Waiting for TABLE metadata lock'
409 AND info = 'ALTER TABLE t1 ALTER a SET DEFAULT 1'`;
410
411 --echo --- connection master1 ---
412 connection master1;
413 --echo reap result of previous---
414 --error ER_LOCK_WAIT_TIMEOUT
415 --reap
416
417 --echo --- connection master ---
418 connection master;
419 COMMIT;
420 --sync_slave_with_master
421
422 --echo --- connection slave ---
423 connection slave;
424 SELECT COUNT(*) FROM t1 ;
425 --echo --- connection master ---
426 connection master;
427 SELECT COUNT(*) FROM t1 ;
428
429 --let $diff_tables= master:t1, slave:t1
430 --source include/diff_tables.inc
431
432
433 # Concurrent execution of ALTER TABLE CHANGE COLUMN and UPDATE inside an active transaction
434
435 --echo --- connection master ---
436 connection master;
437 BEGIN;
438 UPDATE t1 SET a = 1;
439
440 --echo --- connection master1 ---
441 connection master1;
442 SET SESSION lock_wait_timeout = 1;
443 --echo send statement and reap result later ---
444 --send ALTER TABLE t1 CHANGE a b smallINT;
445
446 --echo --- connection master2 ---
447 connection master2;
448 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
449 WHERE state = 'Waiting for TABLE metadata lock'
450 AND info = 'ALTER TABLE t1 CHANGE a b smallINT'`;
451
452 --echo --- connection master1 ---
453 connection master1;
454 --echo reap result of previous---
455 --error ER_LOCK_WAIT_TIMEOUT
456 --reap
457
458 --echo --- connection master ---
459 connection master;
460 COMMIT;
461 --sync_slave_with_master
462
463 --echo --- connection slave ---
464 connection slave;
465 SELECT COUNT(*) FROM t1 ;
466 --echo --- connection master ---
467 connection master;
468 SELECT COUNT(*) FROM t1 ;
469
470 --let $diff_tables= master:t1, slave:t1
471 --source include/diff_tables.inc
472
473
474 # Concurrent execution of ALTER TABLE MODIFY COLUMN and UPDATE inside an active transaction
475
476 --echo --- connection master ---
477 connection master;
478 INSERT INTO t1 VALUES(1);
479 BEGIN;
480 UPDATE t1 SET a = 1;
481
482 --echo --- connection master1 ---
483 connection master1;
484 SET SESSION lock_wait_timeout = 1;
485 --echo send statement and reap result later ---
486 --send ALTER TABLE t1 MODIFY a INTEGER;
487
488 --echo --- connection master2 ---
489 connection master2;
490 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
491 WHERE state = 'Waiting for TABLE metadata lock'
492 AND info = 'ALTER TABLE t1 MODIFY a INTEGER'`;
493
494 --echo --- connection master1 ---
495 connection master1;
496 --echo reap result of previous---
497 --error ER_LOCK_WAIT_TIMEOUT
498 --reap
499
500 --echo --- connection master ---
501 connection master;
502 COMMIT;
503 --sync_slave_with_master
504
505
506 --echo --- connection slave ---
507 connection slave;
508 SELECT COUNT(*) FROM t1 ;
509 --echo --- connection master ---
510 connection master;
511 SELECT COUNT(*) FROM t1 ;
512
513 --let $diff_tables= master:t1, slave:t1
514 --source include/diff_tables.inc
515
516
517 # Concurrent execution of ALTER TABLE DROP PRIMARY KEY and UPDATE inside an active transaction
518
519 --echo --- connection master ---
520 connection master;
521 INSERT INTO t1prim VALUES (1,1);
522 INSERT INTO t1prim VALUES (2,2);
523 COMMIT;
524 BEGIN;
525 UPDATE t1prim SET b = 4 WHERE a = 1;
526
527 --echo --- connection master1 ---
528 connection master1;
529 SET SESSION lock_wait_timeout = 1;
530 --echo send statement and reap result later ---
531 --send ALTER TABLE t1prim DROP PRIMARY KEY;
532
533 --echo --- connection master2 ---
534 connection master2;
535 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
536 WHERE state = 'Waiting for TABLE metadata lock'
537 AND info = 'ALTER TABLE t1prim DROP PRIMARY KEY'`;
538
539 --echo --- connection master1 ---
540 connection master1;
541 --echo reap result of previous---
542 --error ER_LOCK_WAIT_TIMEOUT
543 --reap
544
545 --echo --- connection master ---
546 connection master;
547 COMMIT;
548 --sync_slave_with_master
549
550 --echo --- connection slave ---
551 connection slave;
552 SELECT COUNT(*) FROM t1prim ;
553 --echo --- connection master ---
554 connection master;
555 SELECT COUNT(*) FROM t1prim ;
556
557 --let $diff_tables= master:t1prim, slave:t1prim
558 --source include/diff_tables.inc
559
560
561 # Concurrent execution of ALTER TABLE DISABLE KEY and UPDATE inside an active transaction
562
563 --echo --- connection master ---
564 connection master;
565
566 BEGIN;
567 UPDATE t1 SET a = 1;
568
569 --echo --- connection master1 ---
570 connection master1;
571 SET SESSION lock_wait_timeout = 1;
572 --echo send statement and reap result later ---
573 --send ALTER TABLE t1 disable KEYS;
574
575 --echo --- connection master2 ---
576 connection master2;
577 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
578 WHERE state = 'Waiting for TABLE metadata lock'
579 AND info = 'ALTER TABLE t1 disable KEYS'`;
580
581 --echo --- connection master1 ---
582 connection master1;
583 --echo reap result of previous---
584 --error ER_LOCK_WAIT_TIMEOUT
585 --reap
586
587 --echo --- connection master ---
588 connection master;
589 COMMIT;
590 --sync_slave_with_master
591
592 --echo --- connection slave ---
593 connection slave;
594 SELECT COUNT(*) FROM t1 ;
595 --echo --- connection master ---
596 connection master;
597 SELECT COUNT(*) FROM t1 ;
598
599 --let $diff_tables= master:t1, slave:t1
600 --source include/diff_tables.inc
601
602
603 # Concurrent execution of ALTER TABLE PARTITION BY and INSERT inside an active transaction
604
605 --echo --- connection master ---
606 connection master;
607 INSERT INTO t1part VALUES(1, '1998');
608 INSERT INTO t1part VALUES(2, '2012');
609
610 BEGIN;
611 INSERT INTO t1part SELECT * FROM t1part;
612 --echo --- connection master1 ---
613 connection master1;
614 SET SESSION lock_wait_timeout = 1;
615 --echo send statement and reap result later ---
616 --send ALTER TABLE t1part PARTITION BY HASH(id) PARTITIONS 2 ;
617
618 --echo --- connection master2 ---
619 connection master2;
620 let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
621 WHERE state = 'Waiting for TABLE metadata lock'
622 AND info = 'ALTER TABLE t1part PARTITION BY HASH(id) PARTITIONS 2'`;
623
624 --echo --- connection master1 ---
625 connection master1;
626 --echo reap result of previous---
627 --error ER_LOCK_WAIT_TIMEOUT
628 --reap
629
630 --echo --- connection master ---
631 connection master;
632 COMMIT;
633 --sync_slave_with_master
634
635 --echo --- connection slave ---
636 connection slave;
637 SELECT COUNT(*) FROM t1part;
638 --echo --- connection master ---
639 connection master;
640 SELECT COUNT(*) FROM t1part;
641
642 --let $diff_tables= master:t1part, slave:t1part
643 --source include/diff_tables.inc
644
645
646 # Concurrent execution of INSERT and ALTER TABLE ADD PARTITION inside an active transaction.
647
648 --echo --- connection master ---
649 connection master;
650 INSERT INTO t2part VALUES (1, '1988');
651 BEGIN;
652 INSERT INTO t2part VALUES (2, '1998');
653
654 --echo --- connection master1 ---
655 connection master1;
656 SET SESSION lock_wait_timeout = 1;
657 --echo send statement and reap result later ---
658 --send ALTER TABLE t2part ADD PARTITION (PARTITION p3 VALUES LESS THAN (2015));
659
660 --echo --- connection master2 ---
661 connection master2;
662 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
663 WHERE state = 'Waiting for TABLE metadata lock'
664 AND info = 'ALTER TABLE t2part ADD PARTITION (PARTITION p3 VALUES LESS THAN (2015))'`;
665
666 --echo --- connection master1 ---
667 connection master1;
668 --echo reap result of previous---
669 --error ER_LOCK_WAIT_TIMEOUT
670 --reap
671
672 --echo --- connection master ---
673 connection master;
674 COMMIT;
675 --sync_slave_with_master
676
677 --echo --- connection slave ---
678 connection slave;
679 SELECT COUNT(*) FROM t2part;
680 --echo --- connection master ---
681 connection master;
682 SELECT COUNT(*) FROM t2part;
683
684 --let $diff_tables= master:t2part, slave:t2part
685 --source include/diff_tables.inc
686
687
688 # Concurrent execution of INSERT and ALTER TABLE DROP PARTITION inside an active transaction.
689
690 --echo --- connection master ---
691 connection master;
692 INSERT INTO t2part VALUES (1, '1912');
693 BEGIN;
694 INSERT INTO t2part VALUES (2, '1978');
695 --echo --- connection master1 ---
696 connection master1;
697 SET SESSION lock_wait_timeout = 1;
698 --echo send statement and reap result later ---
699 --send ALTER TABLE t2part Drop PARTITION p1 ;
700
701 --echo --- connection master2 ---
702 connection master2;
703 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
704 WHERE state = 'Waiting for TABLE metadata lock'
705 AND info = 'ALTER TABLE t2part Drop PARTITION p1'`;
706
707 --echo --- connection master1 ---
708 connection master1;
709 --echo reap result of previous---
710 --error ER_LOCK_WAIT_TIMEOUT
711 --reap
712
713 --echo --- connection master ---
714 connection master;
715 COMMIT;
716 --sync_slave_with_master
717
718 --echo --- connection slave ---
719 connection slave;
720 SELECT COUNT(*) FROM t2part;
721 --echo --- connection master ---
722 connection master;
723 SELECT COUNT(*) FROM t2part;
724
725 --let $diff_tables= master:t2part, slave:t2part
726 --source include/diff_tables.inc
727
728 # Concurrent execution of INSERT and ALTER TABLE COALESCE PARTITION inside an active transaction.
729
730 --echo --- connection master ---
731 connection master;
732 INSERT INTO t3part VALUES (1, '1902');
733 BEGIN;
734 INSERT INTO t3part VALUES (2, '1965');
735
736 --echo --- connection master1 ---
737 connection master1;
738 SET SESSION lock_wait_timeout = 1;
739 --echo send statement and reap result later ---
740 --send ALTER TABLE t3part COALESCE PARTITION 2 ;
741
742 --echo --- connection master2 ---
743 connection master2;
744 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
745 WHERE state = 'Waiting for TABLE metadata lock'
746 AND info = 'ALTER TABLE t3part COALESCE PARTITION 2'`;
747
748 --echo --- connection master1 ---
749 connection master1;
750 --echo reap result of previous---
751 --error ER_LOCK_WAIT_TIMEOUT
752 --reap
753
754 --echo --- connection master ---
755 connection master;
756 COMMIT;
757 --sync_slave_with_master
758
759 --echo --- connection slave ---
760 connection slave;
761 SELECT COUNT(*) FROM t3part;
762 --echo --- connection master ---
763 connection master;
764 SELECT COUNT(*) FROM t3part;
765
766 --let $diff_tables= master:t3part, slave:t3part
767 --source include/diff_tables.inc
768
769
770 # Concurrent execution of INSERT on a partitioned table and REORGANIZE PARTITION inside an active transaction.
771
772 --echo --- connection master ---
773 connection master;
774 INSERT INTO t4part VALUES (1, '1932');
775 --echo --- connection master ---
776 connection master;
777 BEGIN;
778 INSERT INTO t4part VALUES (2, '1998');
779
780 --echo --- connection master1 ---
781 connection master1;
782 SET SESSION lock_wait_timeout = 1;
783 --echo send statement and reap result later ---
784 --send ALTER TABLE t4part REORGANIZE PARTITION p0,p1 INTO (PARTITION s0 VALUES LESS THAN (1995));
785
786 --echo --- connection master2 ---
787 connection master2;
788 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
789 WHERE state = 'Waiting for TABLE metadata lock'
790 AND info = 'ALTER TABLE t4part
791 REORGANIZE PARTITION p0 INTO (PARTITION s0 VALUES LESS THAN (1960),
792 PARTITION s1 VALUES LESS THAN (1970))'`;
793
794 --echo --- connection master1 ---
795 connection master1;
796 --echo reap result of previous---
797 --error ER_LOCK_WAIT_TIMEOUT
798 --reap
799
800 --echo --- connection master ---
801 connection master;
802 COMMIT;
803 --sync_slave_with_master
804
805 --echo --- connection slave ---
806 connection slave;
807 SELECT COUNT(*) FROM t4part ;
808 --echo --- connection master ---
809 connection master;
810 SELECT COUNT(*) FROM t4part ;
811
812 --let $diff_tables= master:t4part, slave:t4part
813 --source include/diff_tables.inc
814
815
816 # Concurrent execution of INSERT and ALTER TABLE ANALYSE/CHECK PARTITION inside an active transaction.
817
818 --echo --- connection master ---
819 connection master;
820 INSERT INTO t2part VALUES (3, '1892');
821 --echo --- connection master ---
822 connection master;
823 BEGIN;
824 INSERT INTO t2part VALUES (4, '2012');
825 --echo --- connection master1 ---
826 connection master1;
827 SET SESSION lock_wait_timeout = 1;
828 --echo send statement and reap result later ---
829 --send ALTER TABLE t2part CHECK PARTITION ALL;
830
831 --echo --- connection master2 ---
832 connection master2;
833 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
834 WHERE state = 'Waiting for TABLE metadata lock'
835 AND info = 'ALTER TABLE t2part CHECK PARTITION ALL'`;
836
837 --echo --- connection master1 ---
838 connection master1;
839 --echo reap result of previous---
840 --reap
841
842 --echo --- connection master ---
843 connection master;
844 COMMIT;
845 --sync_slave_with_master
846
847
848 --echo --- connection slave ---
849 connection slave;
850 SELECT COUNT(*) FROM t2part ;
851 --echo --- connection master ---
852 connection master;
853 SELECT COUNT(*) FROM t2part ;
854
855 --let $diff_tables= master:t2part, slave:t2part
856 --source include/diff_tables.inc
857
858
859 # Concurrent execution of INSERT and ALTER TABLE ANALYSE PARTITION inside an active transaction.
860
861 --echo --- connection master ---
862 connection master;
863 INSERT INTO t2part VALUES (5, '1975');
864 BEGIN;
865 INSERT INTO t2part VALUES (6, '1980');
866 --echo --- connection master1 ---
867 connection master1;
868 SET SESSION lock_wait_timeout = 1;
869 --echo send statement and reap result later ---
870 --send ALTER TABLE t2part CHECK PARTITION ALL;
871
872 --echo --- connection master2 ---
873 connection master2;
874 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
875 WHERE state = 'Waiting for TABLE metadata lock'
876 AND info = 'ALTER TABLE t2part ANALYSE PARTITION ALL'`;
877
878 --echo --- connection master1 ---
879 connection master1;
880 --echo reap result of previous---
881 --reap
882
883 --echo --- connection master ---
884 connection master;
885 COMMIT;
886 --sync_slave_with_master
887
888 --echo --- connection slave ---
889 connection slave;
890 SELECT COUNT(*) FROM t2part ;
891 --echo --- connection master ---
892 connection master;
893 SELECT COUNT(*) FROM t2part ;
894
895 --let $diff_tables= master:t2part, slave:t2part
896 --source include/diff_tables.inc
897
898
899 # Concurrent execution of INSERT and ALTER TABLE OPTIMIZE PARTITION inside an active transaction.
900
901 --echo --- connection master ---
902 connection master;
903 INSERT INTO t2part VALUES (7, '1912');
904 BEGIN;
905 INSERT INTO t2part VALUES (8, '1998');
906 --echo --- connection master1 ---
907 connection master1;
908 SET SESSION lock_wait_timeout = 1;
909 --echo send statement and reap result later ---
910 --send ALTER TABLE t2part OPTIMIZE PARTITION ALL;
911
912 --echo --- connection master2 ---
913 connection master2;
914 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
915 WHERE state = 'Waiting for TABLE metadata lock'
916 AND info = 'ALTER TABLE t2part OPTIMIZE PARTITION ALL'`;
917
918 --echo --- connection master1 ---
919 connection master1;
920 --echo reap result of previous---
921 --reap
922
923 --echo --- connection master ---
924 connection master;
925 COMMIT;
926 --sync_slave_with_master
927
928
929 --echo --- connection slave ---
930 connection slave;
931 SELECT COUNT(*) FROM t2part ;
932 --echo --- connection master ---
933 connection master;
934 SELECT COUNT(*) FROM t2part ;
935
936 --let $diff_tables= master:t2part, slave:t2part
937 --source include/diff_tables.inc
938
939 # Concurrent execution of INSERT and ALTER TABLE REBUILD PARTITION inside an active transaction.
940
941 --echo --- connection master ---
942 connection master;
943 INSERT INTO t2part VALUES (9, '2011');
944 BEGIN;
945 INSERT INTO t2part VALUES (10, '1982');
946
947 --echo --- connection master1 ---
948 connection master1;
949 SET SESSION lock_wait_timeout = 1;
950 --echo send statement and reap result later ---
951 --send ALTER TABLE t2part REBUILD PARTITION ALL;
952
953 --echo --- connection master2 ---
954 connection master2;
955 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
956 WHERE state = 'Waiting for TABLE metadata lock'
957 AND info = 'ALTER TABLE t2part REBUILD PARTITION ALL'`;
958
959 --echo --- connection master1 ---
960 connection master1;
961 --echo reap result of previous---
962 --error ER_LOCK_WAIT_TIMEOUT
963 --reap
964
965 --echo --- connection master ---
966 connection master;
967 COMMIT;
968 --sync_slave_with_master
969
970 --echo --- connection slave ---
971 connection slave;
972 SELECT COUNT(*) FROM t2part ;
973 --echo --- connection master ---
974 connection master;
975 SELECT COUNT(*) FROM t2part ;
976
977 --let $diff_tables= master:t2part, slave:t2part
978 --source include/diff_tables.inc
979
980 # Concurrent execution of INSERT and ALTER TABLE REPAIR PARTITION inside an active transaction.
981
982 --echo --- connection master ---
983 connection master;
984 INSERT INTO t2part VALUES (11, '1912');
985 BEGIN;
986 INSERT INTO t2part VALUES (12, '1999');
987
988 --echo --- connection master1 ---
989 connection master1;
990 SET SESSION lock_wait_timeout = 1;
991 --echo send statement and reap result later ---
992 --send ALTER TABLE t2part REPAIR PARTITION ALL;
993
994 --echo --- connection master2 ---
995 connection master2;
996 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
997 WHERE state = 'Waiting for TABLE metadata lock'
998 AND info = 'ALTER TABLE t2part REPAIR PARTITION ALL'`;
999
1000 --echo --- connection master1 ---
1001 connection master1;
1002 --echo reap result of previous---
1003 --reap
1004
1005 --echo --- connection master ---
1006 connection master;
1007 COMMIT;
1008 --sync_slave_with_master
1009
1010 --echo --- connection slave ---
1011 connection slave;
1012 SELECT COUNT(*) FROM t2part ;
1013 --echo --- connection master ---
1014 connection master;
1015 SELECT COUNT(*) FROM t2part ;
1016
1017 --let $diff_tables= master:t2part, slave:t2part
1018 --source include/diff_tables.inc
1019
1020 # Concurrent execution of INSERT and ALTER TABLE REPAIR PARTITION inside an active transaction.
1021
1022 --echo --- connection master ---
1023 connection master;
1024 INSERT INTO t2part VALUES (13, '1923');
1025 BEGIN;
1026 INSERT INTO t2part VALUES (14, '1981');
1027
1028 --echo --- connection master1 ---
1029 connection master1;
1030 SET SESSION lock_wait_timeout = 1;
1031 --echo send statement and reap result later ---
1032 --send ALTER TABLE t2part REMOVE PARTITIONING ;
1033
1034 --echo --- connection master2 ---
1035 connection master2;
1036 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1037 WHERE state = 'Waiting for TABLE metadata lock'
1038 AND info = 'ALTER TABLE t2part REMOVE PARTITIONING' `;
1039
1040 --echo --- connection master1 ---
1041 connection master1;
1042 --echo reap result of previous---
1043 --error ER_LOCK_WAIT_TIMEOUT
1044 --reap
1045
1046 --echo --- connection master ---
1047 connection master;
1048 COMMIT;
1049 --sync_slave_with_master
1050
1051 --echo --- connection slave ---
1052 connection slave;
1053 SELECT COUNT(*) FROM t2part ;
1054 --echo --- connection master ---
1055 connection master;
1056 SELECT COUNT(*) FROM t2part ;
1057
1058 --let $diff_tables= master:t2part, slave:t2part
1059 --source include/diff_tables.inc
1060
1061 # Concurrent execution of INSERT and ALTER TABLE ALGORITHM COPY inside an active transaction.
1062
1063 --echo --- connection master ---
1064 connection master;
1065 BEGIN;
1066 INSERT INTO t1 VALUES (1);
1067
1068 --echo --- connection master1 ---
1069 connection master1;
1070 SET SESSION lock_wait_timeout = 1;
1071 --echo send statement and reap result later ---
1072 --send ALTER TABLE t1 ALGORITHM = COPY;
1073
1074 --echo --- connection master2 ---
1075 connection master2;
1076 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1077 WHERE state = 'Waiting for TABLE metadata lock'
1078 AND info = 'ALTER TABLE t1 ALGORITHM = COPY;'`;
1079
1080 --echo --- connection master1 ---
1081 connection master1;
1082 --echo reap result of previous---
1083 --error ER_LOCK_WAIT_TIMEOUT
1084 --reap
1085
1086 --echo --- connection master ---
1087 connection master;
1088 COMMIT;
1089 --sync_slave_with_master
1090
1091
1092 --echo --- connection slave ---
1093 connection slave;
1094 SELECT COUNT(*) FROM t1 ;
1095 --echo --- connection master ---
1096 connection master;
1097 SELECT COUNT(*) FROM t1 ;
1098
1099 --let $diff_tables= master:t1, slave:t1
1100 --source include/diff_tables.inc
1101
1102
1103 # Concurrent execution of INSERT and ALTER TABLE ALGORITHM INPLACE inside an active transaction.
1104
1105 --echo --- connection master ---
1106 connection master;
1107 BEGIN;
1108 INSERT INTO t1 VALUES (1);
1109
1110 --echo --- connection master1 ---
1111 connection master1;
1112 SET SESSION lock_wait_timeout = 1;
1113 --echo send statement and reap result later ---
1114 --send ALTER TABLE t1 ALGORITHM = INPLACE;
1115
1116 --echo --- connection master2 ---
1117 connection master2;
1118 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1119 WHERE state = 'Waiting for TABLE metadata lock'
1120 AND info = 'ALTER TABLE t1 ALGORITHM = INPLACE;'`;
1121
1122 --echo --- connection master1 ---
1123 connection master1;
1124 --echo reap result of previous---
1125 --reap
1126
1127 --echo --- connection master ---
1128 connection master;
1129 COMMIT;
1130 --sync_slave_with_master
1131
1132 --echo --- connection slave ---
1133 connection slave;
1134 SELECT COUNT(*) FROM t1 ;
1135 --echo --- connection master ---
1136 connection master;
1137 SELECT COUNT(*) FROM t1 ;
1138
1139 --let $diff_tables= master:t1, slave:t1
1140 --source include/diff_tables.inc
1141
1142 # Meta data locking for HANDLER statement
1143
1144 CREATE TABLE t1hand (a INT, KEY a(a));
1145
1146 --echo --- connection master ---
1147 connection master;
1148 SET SESSION lock_wait_timeout = 1;
1149 BEGIN;
1150 SELECT * FROM t1hand;
1151 HANDLER t1hand OPEN;
1152 HANDLER t1hand READ a NEXT;
1153
1154 --echo --- connection master1 ---
1155 connection master1;
1156 let $lock_status = `SELECT COUNT(*) FROM information_schema.processlist
1157 WHERE state = 'Waiting for TABLE metadata lock'
1158 AND info = 'HANDLER t1hand READ a NEXT'`;
1159 SET SESSION lock_wait_timeout = 1;
1160 --echo send statement and reap result later ---
1161 --send DROP TABLE t1hand;
1162
1163 --echo --- connection master2 ---
1164 connection master2;
1165 let $wait_condition = SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
1166 WHERE STATE = "Waiting for table"
1167 AND INFO = "DROP TABLE t1hand";
1168
1169 --echo --- connection master1 ---
1170 connection master1;
1171 --echo reap result of previous---
1172 --error ER_LOCK_WAIT_TIMEOUT
1173 --reap
1174
1175 --echo --- connection master ---
1176 connection master;
1177 HANDLER t1hand CLOSE;
1178 --sync_slave_with_master
1179
1180 --echo --- connection slave ---
1181 connection slave;
1182 SELECT COUNT(*) FROM t1hand ;
1183 --echo --- connection master ---
1184 connection master;
1185 SELECT COUNT(*) FROM t1hand ;
1186
1187 --let $diff_tables= master:t1hand, slave:t1hand
1188 --source include/diff_tables.inc
1189 DROP TABLE t1hand;
1190
1191 # Triggers - Tables from different engines cause full table lock
1192
1193 USE test;
1194
1195 --echo --- connection master ---
1196 connection master;
1197 CREATE TABLE t1trig(c INT) ENGINE = INNODB;
1198 CREATE TABLE t2trig(c INT) ENGINE = MYISAM;
1199 DELIMITER |;
1200 CREATE TRIGGER trig AFTER UPDATE ON t1trig FOR EACH ROW
1201 BEGIN
1202 INSERT INTO t2trig(c) VALUES (1);
1203 END|
1204 DELIMITER ;|
1205 INSERT INTO t1trig(c) VALUES (1);
1206 --disable_warnings
1207 UPDATE t1trig SET c = 2 WHERE c = 1;
1208 --enable_warnings
1209
1210 --echo --- connection master1 ---
1211 connection master1;
1212 INSERT INTO t1trig VALUES (9);
1213
1214 --echo --- connection master ---
1215 connection master;
1216 --sync_slave_with_master
1217
1218 --echo --- connection slave ---
1219 connection slave;
1220 SELECT COUNT(*) FROM t1trig;
1221 --echo --- connection master ---
1222 connection master;
1223 SELECT COUNT(*) FROM t1trig;
1224
1225 --let $diff_tables= master:t1trig, slave:t1trig
1226 --source include/diff_tables.inc
1227
1228 # Concurrent execution of INSERT and REVOKE PRIVILEGES inside an active transaction.
1229
1230 USE test;
1231 CREATE USER 'user1'@'localhost' ;
1232 GRANT ALL ON test.t1 TO user1@localhost;
1233
1234 --echo --- connection master ---
1235 connection master;
1236 BEGIN;
1237 INSERT INTO t1 VALUES (1);
1238
1239 --echo --- connection master1 ---
1240 connection master1;
1241 SET SESSION lock_wait_timeout = 1;
1242 --echo send statement and reap result later ---
1243 --send REVOKE ALL ON test.t1 FROM user1@localhost;
1244
1245 --echo --- connection master2 ---
1246 connection master2;
1247 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1248 WHERE state = 'Waiting for TABLE metadata lock'
1249 AND info = 'REVOKE ALL ON test.t1 FROM user1@localhost'`;
1250
1251 --echo --- connection master1 ---
1252 connection master1;
1253 --echo reap result of previous---
1254 --reap
1255
1256 --echo --- connection master ---
1257 connection master;
1258 COMMIT;
1259 --sync_slave_with_master
1260
1261 --echo --- connection slave ---
1262 connection slave;
1263 SELECT COUNT(*) FROM t1 ;
1264 --echo --- connection master ---
1265 connection master;
1266 SELECT COUNT(*) FROM t1 ;
1267
1268 --let $diff_tables= master:t1, slave:t1
1269 --source include/diff_tables.inc
1270
1271 # Four way deadlock - Waiting for locks and hang
1272 USE test;
1273 --echo --- connection master ---
1274 connection master;
1275 CREATE TABLE tz (s1 INT UNIQUE) ENGINE = INNODB;
1276 INSERT INTO tz VALUES (1);
1277
1278 --echo --- connection master1 ---
1279 connection master1;
1280 INSERT INTO tz VALUES (2);
1281 INSERT INTO tz VALUES (3);
1282
1283 --echo --- connection master2 ---
1284 connection master2;
1285 DROP TABLE tz;
1286
1287 --echo --- connection master3 ---
1288 connection master3;
1289 --error ER_BAD_TABLE_ERROR
1290 --echo send statement and reap result later ---
1291 --send DROP TABLE tz;
1292
1293
1294 --echo --- connection master ---
1295 connection master;
1296 --sync_slave_with_master
1297
1298 --let $diff_tables= master:t1, slave:t1
1299 --source include/diff_tables.inc
1300
1301 # Concurrent execution of ROLLBACK and INSERT inside an active transaction.
1302
1303 --echo --- connection master ---
1304 connection master;
1305 BEGIN;
1306 INSERT INTO t1 VALUES (1);
1307 INSERT INTO t1 VALUES (5);
1308
1309 --echo --- connection master1 ---
1310 connection master1;
1311 SET SESSION lock_wait_timeout = 1;
1312 --echo send statement and reap result later ---
1313 --send ROLLBACK;
1314
1315 --echo --- connection master2 ---
1316 connection master2;
1317 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1318 WHERE state = 'Waiting for TABLE metadata lock'
1319 AND info = 'ROLLBACK;'`;
1320
1321
1322 --echo --- connection master1 ---
1323 connection master1;
1324 --echo reap result of previous---
1325 --reap
1326
1327 --echo --- connection master ---
1328 connection master;
1329 COMMIT;
1330 --sync_slave_with_master
1331
1332 --echo --- connection slave ---
1333 connection slave;
1334 SELECT COUNT(*) FROM t1 ;
1335 --echo --- connection master ---
1336 connection master;
1337 SELECT COUNT(*) FROM t1 ;
1338
1339 --let $diff_tables= master:t1, slave:t1
1340 --source include/diff_tables.inc
1341
1342
1343 # Concurrent execution of Multiple DML statement on different tables with DROP TABLE on other connection.
1344 --echo --- connection master ---
1345 connection master;
1346 BEGIN;
1347 INSERT INTO t1 VALUES (1);
1348 INSERT INTO t2 VALUES (2,3);
1349
1350 --echo --- connection master1 ---
1351 connection master1;
1352 SET SESSION lock_wait_timeout = 1;
1353 --echo send statement and reap result later ---
1354 --send DROP TABLE t2;
1355
1356 --echo --- connection master2 ---
1357 connection master2;
1358 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1359 WHERE state = 'Waiting for TABLE metadata lock'
1360 AND info = 'DROP TABLE t2;'`;
1361
1362 --echo --- connection master1 ---
1363 connection master1;
1364 --echo reap result of previous---
1365 --error ER_LOCK_WAIT_TIMEOUT
1366 --reap
1367
1368 --echo --- connection master ---
1369 connection master;
1370 COMMIT;
1371 --sync_slave_with_master
1372
1373 --echo --- connection slave ---
1374 connection slave;
1375 SELECT COUNT(*) FROM t1 ;
1376 --echo --- connection master ---
1377 connection master;
1378 SELECT COUNT(*) FROM t1 ;
1379
1380 --let $diff_tables= master:t1, slave:t1
1381 --source include/diff_tables.inc
1382
1383
1384 # Concurrent execution of Multiple DML statement on different tables with RENAME on other connection.
1385
1386 --echo --- connection master ---
1387 connection master;
1388 BEGIN;
1389 INSERT INTO t1 VALUES (1);
1390 INSERT INTO t2 VALUES (3,4);
1391 INSERT INTO t3 VALUES (2);
1392 INSERT INTO t4 VALUES (1);
1393
1394 --echo --- connection master1 ---
1395 connection master1;
1396 SET SESSION lock_wait_timeout = 1;
1397 --echo send statement and reap result later ---
1398 --send RENAME TABLE t1 to t2, t2 to t3, t3 to t4, t4 to t1;
1399
1400 --echo --- connection master2 ---
1401 connection master2;
1402 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1403 WHERE state = 'Waiting for TABLE metadata lock'
1404 AND info = 'RENAME TABLE t1 to t2, t2 to t3, t3 to t4, t4 to t1;'`;
1405
1406 --echo --- connection master1 ---
1407 connection master1;
1408 --echo reap result of previous---
1409 --error ER_LOCK_WAIT_TIMEOUT
1410 --reap
1411
1412 --echo --- connection master ---
1413 connection master;
1414 COMMIT;
1415 --sync_slave_with_master
1416
1417 --echo --- connection slave ---
1418 connection slave;
1419 SELECT COUNT(*) FROM t1 ;
1420 SELECT COUNT(*) FROM t2 ;
1421 SELECT COUNT(*) FROM t3 ;
1422 SELECT COUNT(*) FROM t4 ;
1423
1424 --echo --- connection master ---
1425 connection master;
1426 SELECT COUNT(*) FROM t1 ;
1427 SELECT COUNT(*) FROM t2 ;
1428 SELECT COUNT(*) FROM t3 ;
1429 SELECT COUNT(*) FROM t4 ;
1430
1431 --let $diff_tables= master:t1, slave:t1
1432 --let $diff_tables= master:t2, slave:t2
1433 --let $diff_tables= master:t3, slave:t3
1434 --let $diff_tables= master:t4, slave:t4
1435 --source include/diff_tables.inc
1436
1437
1438 # Rename tables on innodb tables with pending clause slave data issue
1439
1440 --echo --- connection master ---
1441 connection master;
1442 INSERT INTO t3967_1 (b) VALUES (1),(2),(3);
1443 BEGIN;
1444 INSERT INTO t3967_1(b) VALUES (4);
1445
1446 --echo --- connection master1 ---
1447 connection master1;
1448 SET SESSION lock_wait_timeout = 1;
1449 --echo send statement and reap result later ---
1450 --send RENAME TABLE t3967_1 to t1_backup, t3967_2 to t3967_1;
1451
1452 --echo --- connection master2 ---
1453 connection master2;
1454 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1455 WHERE state = 'Waiting for TABLE metadata lock'
1456 AND info = 'RENAME TABLE t3967_1 to t1_backup, t3967_2 to t3967_1;'`;
1457
1458 --echo --- connection master1 ---
1459 connection master1;
1460 --echo reap result of previous---
1461 --error ER_LOCK_WAIT_TIMEOUT
1462 --reap
1463
1464 --echo --- connection master ---
1465 connection master;
1466 COMMIT;
1467 --sync_slave_with_master
1468
1469 --echo --- connection slave ---
1470 connection slave;
1471 SELECT COUNT(*) FROM t3967_1 ;
1472 --echo --- connection master ---
1473 connection master;
1474 SELECT COUNT(*) FROM t3967_1 ;
1475
1476 --let $diff_tables= master:t3967_1, slave:t3967_1
1477 --source include/diff_tables.inc
1478
1479
1480 # WITH CONSISTENT SNAPSHOT does not isolate against TABLE CHANGES.
1481
1482 INSERT INTO t1_30138 VALUES (1),(2),(3);
1483 SET AUTOCOMMIT = 0;
1484 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1485 START TRANSACTION WITH CONSISTENT SNAPSHOT;
1486 SELECT COUNT(*) FROM t1_30138;
1487 CREATE TABLE t1_30138_new (id INT) ENGINE = INNODB;
1488 INSERT INTO t1_30138_new VALUES (4),(5),(6),(7);
1489 INSERT INTO t1_30138 VALUES(8);
1490 RENAME TABLE t1_30138 TO t1_30138_old, t1_30138_new TO t1_30138;
1491 SELECT COUNT(*) FROM t1_30138;
1492 SELECT COUNT(*) FROM t1_30138_old;
1493 --sync_slave_with_master
1494
1495
1496 --echo --- connection slave ---
1497 connection slave;
1498 SELECT COUNT(*) FROM t1_30138 ;
1499 SELECT COUNT(*) FROM t1_30138_old ;
1500 --echo --- connection master ---
1501 connection master;
1502 SELECT COUNT(*) FROM t1_30138 ;
1503 SELECT COUNT(*) FROM t1_30138_old ;
1504
1505 --let $diff_tables= master:t1_30138, slave:t1_30138
1506 --let $diff_tables= master:t1_30138_old, slave:t1_30138_old
1507 --source include/diff_tables.inc
1508
1509 # Concurrent execution of Multi UPDATE statements on transactional (INNODB) and non transactional tables(MyISAM).
1510 CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT');
1511 --echo --- connection master ---
1512 connection master;
1513 INSERT INTO t1innodb (c) VALUES (1);
1514 INSERT INTO t1myisam (c) VALUES (1);
1515 COMMIT;
1516 BEGIN;
1517 --disable_warnings
1518 UPDATE t1myisam, t1innodb SET t1myisam.c = 10, t1innodb.c = NULL;
1519 --enable_warnings
1520
1521 --echo --- connection master1 ---
1522 connection master1;
1523 SET SESSION lock_wait_timeout = 1;
1524 --echo send statement and reap result later ---
1525 --send DROP TABLE t1myisam;
1526
1527 --echo --- connection master2 ---
1528 connection master2;
1529 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1530 WHERE state = 'Waiting for TABLE metadata lock'
1531 AND info = 'DROP TABLE t1myisam'`;
1532
1533 --echo --- connection master1 ---
1534 connection master1;
1535 --echo reap result of previous---
1536 --error ER_LOCK_WAIT_TIMEOUT
1537 --reap
1538
1539 --echo --- connection master ---
1540 connection master;
1541 COMMIT;
1542 --sync_slave_with_master
1543
1544 --echo --- connection slave ---
1545 connection slave;
1546 SELECT COUNT(*) FROM t1myisam ;
1547 SELECT COUNT(*) FROM t1innodb ;
1548 --echo --- connection master ---
1549 connection master;
1550 SELECT COUNT(*) FROM t1myisam ;
1551 SELECT COUNT(*) FROM t1innodb ;
1552
1553 --let $diff_tables= master:t1myisam, slave:t1myisam
1554 --let $diff_tables= master:t1innodb, slave:t1innodb
1555 --source include/diff_tables.inc
1556
1557 # Binlogging of "CREATE SELECT" which unfold into two or more events in RBL mode.
1558
1559 --echo --- connection master ---
1560 connection master;
1561 BEGIN;
1562 INSERT INTO t1 SELECT * FROM t1;
1563
1564 --echo --- connection master1 ---
1565 connection master1;
1566 SET SESSION lock_wait_timeout = 1;
1567 --echo send statement and reap result later ---
1568 --send DROP TABLE t1;
1569
1570 --echo --- connection master2 ---
1571 connection master2;
1572 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1573 WHERE state = 'Waiting for TABLE metadata lock'
1574 AND info = 'DROP TABLE t1'`;
1575
1576 --echo --- connection master1 ---
1577 connection master1;
1578 --echo reap result of previous---
1579 --error ER_LOCK_WAIT_TIMEOUT
1580 --reap
1581
1582 --echo --- connection master ---
1583 connection master;
1584 COMMIT;
1585 --sync_slave_with_master
1586
1587 --echo --- connection slave ---
1588 connection slave;
1589 SELECT COUNT(*) FROM t1 ;
1590 --echo --- connection master ---
1591 connection master;
1592 SELECT COUNT(*) FROM t1 ;
1593
1594 --let $diff_tables= master:t1, slave:t1
1595 --source include/diff_tables.inc
1596
1597
1598 # Concurrent execution of DROP DATABASE and CREATE FUNCTION inside in an active transaction.
1599
1600 --echo --- connection master ---
1601 connection master;
1602 CREATE DATABASE db1;
1603 USE db1;
1604 CREATE TABLE db1.t1 (a INT);
1605 BEGIN;
1606 CREATE FUNCTION db1.f1() RETURNS INT RETURN 1;
1607 INSERT INTO db1.t1 VALUES (f1());
1608
1609 --echo --- connection master1 ---
1610 connection master1;
1611 SET SESSION lock_wait_timeout = 1;
1612 --echo send statement and reap result later ---
1613 --send DROP DATABASE db1;
1614
1615 --echo --- connection master2 ---
1616 connection master2;
1617 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1618 WHERE state = 'Waiting for TABLE metadata lock'
1619 AND info = 'DROP database db1'`;
1620
1621 --echo --- connection master1 ---
1622 connection master1;
1623 --echo reap result of previous---
1624 --error ER_LOCK_WAIT_TIMEOUT
1625 --reap
1626
1627 --echo --- connection master ---
1628 connection master;
1629 COMMIT;
1630 --sync_slave_with_master
1631
1632 --echo --- connection slave ---
1633 connection slave;
1634 USE db1;
1635 SELECT COUNT(*) FROM t1 ;
1636 --echo --- connection master ---
1637 connection master;
1638 SELECT COUNT(*) FROM t1 ;
1639
1640 --let $diff_tables= master:t1, slave:t1
1641 --source include/diff_tables.inc
1642 DROP TABLE db1.t1;
1643 DROP FUNCTION db1.f1;
1644
1645 # Concurrent execution of DML statements that uses stored FUNCTION and DROP/MODIFY the same FUNCTION.
1646
1647 USE test;
1648 DROP TABLE t1;
1649 CREATE TABLE t1 (a INT);
1650 CREATE FUNCTION f1() RETURNS INT RETURN 1;
1651
1652 BEGIN;
1653 INSERT INTO t1 VALUES (f1());
1654
1655 --echo --- connection master1 ---
1656 connection master1;
1657 SET SESSION lock_wait_timeout = 1;
1658 --echo send statement and reap result later ---
1659 --send DROP FUNCTION f1;
1660
1661 --echo --- connection master2 ---
1662 connection master2;
1663 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1664 WHERE state = 'Waiting for TABLE metadata lock'
1665 AND info = 'DROP FUNCTION f1';`;
1666
1667 --echo --- connection master1 ---
1668 connection master1;
1669 --echo reap result of previous---
1670 --error ER_LOCK_WAIT_TIMEOUT
1671 --reap
1672
1673 --echo --- connection master ---
1674 connection master;
1675 COMMIT;
1676 --sync_slave_with_master
1677
1678 --echo --- connection slave ---
1679 connection slave;
1680 USE test;
1681 SELECT COUNT(*) FROM t1 ;
1682 --echo --- connection master ---
1683 connection master;
1684 SELECT COUNT(*) FROM t1 ;
1685
1686 --let $diff_tables= master:t1, slave:t1
1687 --source include/diff_tables.inc
1688
1689
1690 # Concurrent execution of DML statements that uses STORED PROCEDURE and DROP the same.
1691
1692 --echo --- connection master ---
1693 connection master;
1694 CREATE PROCEDURE p1() INSERT INTO t1 SELECT * FROM t3;
1695 BEGIN;
1696 CALL p1();
1697
1698 --echo --- connection master1 ---
1699 connection master1;
1700 SET SESSION lock_wait_timeout = 1;
1701 --echo send statement and reap result later ---
1702 --send DROP PROCEDURE p1;
1703
1704 --echo --- connection master2 ---
1705 connection master2;
1706 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1707 WHERE state = 'Waiting for TABLE metadata lock'
1708 AND info = 'DROP PROCEDURE p1';`;
1709
1710 --echo --- connection master1 ---
1711 connection master1;
1712 --echo reap result of previous---
1713 --error ER_LOCK_WAIT_TIMEOUT
1714 --reap
1715
1716 --echo --- connection master ---
1717 connection master;
1718 COMMIT;
1719 --sync_slave_with_master
1720
1721 --echo --- connection slave ---
1722 connection slave;
1723 SELECT COUNT(*) FROM t1 ;
1724 --echo --- connection master ---
1725 connection master;
1726 SELECT COUNT(*) FROM t1 ;
1727
1728 --let $diff_tables= master:t1, slave:t1
1729 --source include/diff_tables.inc
1730
1731
1732 # Concurrent execution of DML statements that uses STORED PROCEDURE and DROP the same.
1733
1734 --echo --- connection master ---
1735 connection master;
1736 DELIMITER |;
1737 CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND STARTS NOW() DO
1738 BEGIN
1739 DECLARE EXIT HANDLER FOR 1136 BEGIN
1740 INSERT INTO t3 VALUES (1);
1741 END; -- ER_WRONG_VALUE_COUNT_ON_ROW
1742 INSERT INTO t2 SELECT * FROM t1;
1743 END|
1744 DELIMITER ;|
1745
1746 --echo --- connection master1 ---
1747 connection master1;
1748 SET SESSION lock_wait_timeout = 1;
1749 --echo send statement and reap result later ---
1750 --send DROP EVENT e1;
1751
1752 --echo --- connection master2 ---
1753 connection master2;
1754 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1755 WHERE state = 'Waiting for TABLE metadata lock' AND info = 'DROP EVENT e1';`;
1756
1757 --echo --- connection master1 ---
1758 connection master1;
1759 --echo reap result of previous---
1760 --reap
1761
1762 --echo --- connection master ---
1763 connection master;
1764 COMMIT;
1765 --sync_slave_with_master
1766
1767 --echo --- connection slave ---
1768 connection slave;
1769 SELECT COUNT(*) FROM t2 ;
1770 --echo --- connection master ---
1771 connection master;
1772 SELECT COUNT(*) FROM t2 ;
1773
1774 --let $diff_tables= master:t2, slave:t2
1775 --source include/diff_tables.inc
1776
1777 # Concurrent execution of INSERT and ALTER on VIEW inside an active transaction.
1778
1779 --echo --- connection master ---
1780 connection master;
1781 CREATE VIEW v1 AS SELECT * FROM t1;
1782 BEGIN;
1783 INSERT INTO v1 VALUES (11);
1784
1785 --echo --- connection master1 ---
1786 connection master1;
1787 SET SESSION lock_wait_timeout = 1;
1788 --echo send statement and reap result later ---
1789 --send ALTER VIEW v1 AS SELECT * FROM t2;
1790 --echo --- connection master2 ---
1791 connection master2;
1792 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1793 WHERE state = 'Waiting for TABLE metadata lock'
1794 AND info = 'ALTER VIEW v1 AS SELECT * FROM t2'`;
1795
1796 --echo --- connection master1 ---
1797 connection master1;
1798 --echo reap result of previous---
1799 --error ER_LOCK_WAIT_TIMEOUT
1800 --reap
1801
1802 --echo --- connection master ---
1803 connection master;
1804 COMMIT;
1805 --sync_slave_with_master
1806
1807 --echo --- connection slave ---
1808 connection slave;
1809 SELECT COUNT(*) FROM v1 ;
1810 --echo --- connection master ---
1811 connection master;
1812 SELECT COUNT(*) FROM v1;
1813
1814 --let $diff_tables= master:v1, slave:v1
1815 --source include/diff_tables.inc
1816
1817
1818 # Concurrent execution of INSERT and DROP on VIEW inside an active transaction.
1819
1820 --echo --- connection master ---
1821 connection master;
1822 DROP VIEW v1;
1823 CREATE VIEW v1 AS SELECT * FROM t1;
1824 BEGIN;
1825 INSERT INTO v1 VALUES (12);
1826
1827 --echo --- connection master1 ---
1828 connection master1;
1829 SET SESSION lock_wait_timeout = 1;
1830 --echo send statement and reap result later ---
1831 --send DROP VIEW v1;
1832 --echo --- connection master2 ---
1833 connection master2;
1834 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1835 WHERE state = 'Waiting for TABLE metadata lock'
1836 AND info = 'DROP VIEW v1'`;
1837
1838 --echo --- connection master1 ---
1839 connection master1;
1840 --echo reap result of previous---
1841 --error ER_LOCK_WAIT_TIMEOUT
1842 --reap
1843
1844 --echo --- connection master ---
1845 connection master;
1846 COMMIT;
1847 --sync_slave_with_master
1848
1849 --echo --- connection slave ---
1850 connection slave;
1851 SELECT COUNT(*) FROM v1 ;
1852 --echo --- connection master ---
1853 connection master;
1854 SELECT COUNT(*) FROM v1 ;
1855
1856 --let $diff_tables= master:v1, slave:v1
1857 --source include/diff_tables.inc
1858
1859 # Concurrent execution of ALTER and UPDATE on VIEW inside an active transaction.
1860
1861 --echo --- connection master ---
1862 connection master;
1863 DROP VIEW v1;
1864 CREATE VIEW v1 AS SELECT a, 1 AS col2 FROM t1;
1865 BEGIN;
1866 UPDATE v1 SET a = 0;
1867
1868 --echo --- connection master1 ---
1869 connection master1;
1870 SET SESSION lock_wait_timeout = 1;
1871 --echo send statement and reap result later ---
1872 --send ALTER VIEW v1 AS SELECT a,1 AS col2 FROM t2;
1873 --echo --- connection master ---
1874 connection master2;
1875 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1876 WHERE state = 'Waiting for TABLE metadata lock'
1877 AND info = 'ALTER VIEW v1 AS SELECT a,1 AS col2 FROM t2'`;
1878
1879 --echo --- connection master2 ---
1880 connection master1;
1881 --echo reap result of previous---
1882 --error ER_LOCK_WAIT_TIMEOUT
1883 --reap
1884
1885 --echo --- connection master ---
1886 connection master;
1887 COMMIT;
1888 --sync_slave_with_master
1889
1890 --echo --- connection slave ---
1891 connection slave;
1892 SELECT COUNT(*) FROM v1 ;
1893 --echo --- connection master ---
1894 connection master;
1895 SELECT COUNT(*) FROM v1 ;
1896
1897 --let $diff_tables= master:v1, slave:v1
1898 --source include/diff_tables.inc
1899
1900
1901 # Concurrent execution of INSERT and CREATE REPLACE ON VIEW inside an active transaction.
1902
1903 --echo --- connection master ---
1904 connection master;
1905 DROP VIEW v1;
1906 CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
1907 BEGIN;
1908 INSERT INTO v1 VALUES(13);
1909
1910 --echo --- connection master1 ---
1911 connection master1;
1912 SET SESSION lock_wait_timeout = 1;
1913 --echo send statement and reap result later ---
1914 --send ALTER VIEW v1 AS SELECT * FROM t2;
1915 --echo --- connection master2 ---
1916 connection master2;
1917 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1918 WHERE state = 'Waiting for TABLE metadata lock'
1919 AND info = 'ALTER VIEW v1 AS SELECT * FROM t2'`;
1920
1921 --echo --- connection master1 ---
1922 connection master1;
1923 --echo reap result of previous---
1924 --error ER_LOCK_WAIT_TIMEOUT
1925 --reap
1926
1927 --echo --- connection master ---
1928 connection master;
1929 COMMIT;
1930 --sync_slave_with_master
1931
1932 --echo --- connection slave ---
1933 connection slave;
1934 SELECT COUNT(*) FROM v1 ;
1935 --echo --- connection master ---
1936 connection master;
1937 SELECT COUNT(*) FROM v1 ;
1938
1939 --let $diff_tables= master:v1, slave:v1
1940 --source include/diff_tables.inc
1941
1942
1943 # Concurrent execution of OPTIMIZE TABLE and DROP TABLE
1944
1945 --echo --- connection master ---
1946 connection master;
1947 INSERT INTO t1opt VALUES (1,'Mysql locking',20,'Transactional locking',10);
1948 INSERT INTO t1opt SELECT * FROM t1opt;
1949 BEGIN;
1950 --disable_result_log
1951 OPTIMIZE TABLE t1opt;
1952 --enable_result_log
1953
1954 --echo --- connection master1 ---
1955 connection master1;
1956 SET SESSION lock_wait_timeout = 1;
1957 --echo send statement and reap result later ---
1958 --send DROP TABLE t1opt;
1959
1960 --echo --- connection master2 ---
1961 connection master2;
1962 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
1963 WHERE state = 'Waiting for TABLE metadata lock'
1964 AND info = 'DROP TABLE t1opt'`;
1965
1966 --echo --- connection master1 ---
1967 connection master1;
1968 --echo reap result of previous---
1969 --reap
1970
1971 --echo --- connection master ---
1972 connection master;
1973 COMMIT;
1974 --sync_slave_with_master
1975
1976 --echo --- connection slave ---
1977 connection slave;
1978 SELECT COUNT(*) FROM t1 ;
1979 --echo --- connection master ---
1980 connection master;
1981 SELECT COUNT(*) FROM t1 ;
1982
1983 --let $diff_tables= master:v1, slave:v1
1984 --source include/diff_tables.inc
1985
1986
1987 # Concurrent execution of OPTIMIZE TABLE and DROP TABLE
1988
1989 --echo --- connection master ---
1990 connection master;
1991 CREATE TABLE t1opt (id INT , content TEXT, author_id INT,
1992 article_title VARCHAR(120), article_hash INT);
1993 INSERT INTO t1opt VALUES (2,'Mysql locking1',22,'Transactional locking1',11);
1994 INSERT INTO t1opt SELECT * FROM t1opt;
1995 BEGIN;
1996 --disable_result_log
1997 OPTIMIZE TABLE t1opt;
1998 --enable_result_log
1999
2000 --echo --- connection master1 ---
2001 connection master1;
2002 SET SESSION lock_wait_timeout = 1;
2003 --echo send statement and reap result later ---
2004 --send ALTER TABLE t1opt RENAME TO t1opt;
2005 --echo --- connection master2 ---
2006 connection master2;
2007 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
2008 WHERE state = 'Waiting for TABLE metadata lock'
2009 AND info = 'ALTER TABLE t1opt RENAME TO t1opt'`;
2010
2011 --echo --- connection master1 ---
2012 connection master1;
2013 --echo reap result of previous---
2014 --reap
2015
2016 --echo --- connection master ---
2017 connection master;
2018 COMMIT;
2019 --sync_slave_with_master
2020
2021 --echo --- connection slave ---
2022 connection slave;
2023 SELECT COUNT(*) FROM t1opt ;
2024 --echo --- connection master ---
2025 connection master;
2026 SELECT COUNT(*) FROM t1opt ;
2027
2028 --let $diff_tables= master:t1opt, slave:t1opt
2029 --source include/diff_tables.inc
2030
2031
2032 # Concurrent execution of INSERT and DROP using more than one table
2033
2034 --echo --- connection master ---
2035 connection master;
2036 BEGIN;
2037 INSERT INTO t1 VALUES (15);
2038 INSERT INTO t3 VALUES (10);
2039
2040 --echo --- connection master1 ---
2041 connection master1;
2042 SET SESSION lock_wait_timeout = 1;
2043 --echo send statement and reap result later ---
2044 --send DROP TABLE t1,t3;
2045 --echo --- connection master2 ---
2046 connection master2;
2047 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
2048 WHERE state = 'Waiting for TABLE metadata lock'
2049 AND info = 'DROP TABLE t1,t3'`;
2050
2051 --echo --- connection master1 ---
2052 connection master1;
2053 --echo reap result of previous---
2054 --error ER_LOCK_WAIT_TIMEOUT
2055 --reap
2056
2057 --echo --- connection master ---
2058 connection master;
2059 COMMIT;
2060 --sync_slave_with_master
2061
2062 --echo --- connection slave ---
2063 connection slave;
2064 SELECT COUNT(*) FROM t1 ;
2065 SELECT COUNT(*) FROM t3 ;
2066 --echo --- connection master ---
2067 connection master;
2068 SELECT COUNT(*) FROM t1 ;
2069 SELECT COUNT(*) FROM t3 ;
2070
2071 --let $diff_tables= master:t1, slave:t1
2072 --let $diff_tables= master:t3, slave:t3
2073 --source include/diff_tables.inc
2074
2075
2076 # Concurrent execution of DELETE and DROP using more than one table
2077
2078 --echo --- connection master ---
2079 connection master;
2080 BEGIN;
2081 DELETE t3.*, t4.* FROM t3, t4 WHERE t3.a = t4.a AND t3.a = 1;
2082
2083 --echo --- connection master1 ---
2084 connection master1;
2085 SET SESSION lock_wait_timeout = 1;
2086 --echo send statement and reap result later ---
2087 --send DROP TABLE t3, t4;
2088 --echo --- connection master2 ---
2089 connection master2;
2090 let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
2091 WHERE state = 'Waiting for TABLE metadata lock'
2092 AND info = 'DROP TABLE t3, t4'`;
2093
2094 --echo --- connection master1 ---
2095 connection master1;
2096 --echo reap result of previous---
2097 --error ER_LOCK_WAIT_TIMEOUT
2098 --reap
2099
2100 --echo --- connection master ---
2101 connection master;
2102 COMMIT;
2103 --sync_slave_with_master
2104
2105 --echo --- connection slave ---
2106 connection slave;
2107 SELECT COUNT(*) FROM t3 ;
2108 SELECT COUNT(*) FROM t4 ;
2109 --echo --- connection master ---
2110 connection master;
2111 SELECT COUNT(*) FROM t3 ;
2112 SELECT COUNT(*) FROM t4 ;
2113
2114 --let $diff_tables= master:t3, slave:t3
2115 --let $diff_tables= master:t4, slave:t4
2116 --source include/diff_tables.inc
2117
2118
2119 # Concurrent execution of DROP DATABASE and INSERT in an active transaction
2120
2121 --echo --- connection master ---
2122 connection master;
2123 DROP DATABASE db1;
2124 CREATE DATABASE db1;
2125 USE db1;
2126 CREATE TABLE db1.t1(a INT, b INT);
2127 BEGIN;
2128 INSERT INTO db1.t1 VALUES (1, 1);
2129
2130 --echo --- connection master1 ---
2131 connection master1;
2132 SET SESSION lock_wait_timeout = 1;
2133 --echo send statement and reap result later ---
2134 --send DROP DATABASE db1;
2135
2136 --echo --- connection master2 ---
2137 connection master2;
2138 let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
2139 WHERE state = 'Table lock' AND info = 'DROP DATABASE db1'`;
2140
2141 --echo --- connection master1 ---
2142 connection master1;
2143 --echo reap result of previous---
2144 --error ER_LOCK_WAIT_TIMEOUT
2145 --reap
2146
2147 --echo --- connection master ---
2148 connection master;
2149 COMMIT;
2150 --sync_slave_with_master
2151
2152 --echo --- connection slave ---
2153 connection slave;
2154 USE db1;
2155 SELECT COUNT(*) FROM t1;
2156 --echo --- connection master ---
2157 connection master;
2158 SELECT COUNT(*) FROM t1;
2159
2160 --let $diff_tables= master:t1, slave:t1
2161 --source include/diff_tables.inc
2162
2163 DROP TABLE db1.t1;
2164
2165 # Concurrent execution of ALTER DATABASE and INSERT in an active transaction
2166 USE test;
2167 --echo --- connection master ---
2168 connection master;
2169 DROP DATABASE db1;
2170 CREATE DATABASE db1;
2171 USE db1;
2172 CREATE TABLE db1.t1(a INT, b INT);
2173 BEGIN;
2174 INSERT INTO db1.t1 VALUES (1,1);
2175
2176 --echo --- connection master1 ---
2177 connection master1;
2178 SET SESSION lock_wait_timeout = 1;
2179 --echo send statement and reap result later ---
2180 --send ALTER DATABASE db1 CHARACTER SET big5;
2181
2182 --echo --- connection master2 ---
2183 connection master2;
2184 let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
2185 WHERE state = 'Table lock' AND info = 'ALTER DATABASE db1 CHARACTER SET big5;'`;
2186
2187 --echo --- connection master1 ---
2188 connection master1;
2189 --echo reap result of previous---
2190 --error ER_LOCK_WAIT_TIMEOUT
2191 --reap
2192
2193 --echo --- connection master ---
2194 connection master;
2195 COMMIT;
2196 --sync_slave_with_master
2197
2198 --echo --- connection slave ---
2199 connection slave;
2200 USE db1;
2201 SELECT COUNT(*) FROM t1;
2202 --echo --- connection master ---
2203 connection master;
2204 SELECT COUNT(*) FROM t1;
2205
2206 --let $diff_tables= master:t1, slave:t1
2207 --source include/diff_tables.inc
2208
2209 DROP TABLE db1.t1;
2210
2211 USE test;
2212 # Select inside FUNCTION takes a shared lock
2213 --echo --- connection master ---
2214 connection master;
2215 USE test;
2216 CREATE TABLE tab1(x INT PRIMARY KEY, y INT) ENGINE = INNODB;
2217 INSERT tab1 VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0);
2218 DELIMITER |;
2219 CREATE FUNCTION func(z INT) RETURNS INT
2220 READS SQL DATA
2221 RETURN (SELECT x FROM tab1 WHERE x = z)
2222 |
2223 DELIMITER ;|
2224 BEGIN;
2225 SELECT func(1);
2226
2227 --echo --- connection master1 ---
2228 connection master1;
2229 USE test;
2230 BEGIN;
2231 SELECT func(1);
2232 UPDATE tab1 SET y = 1 WHERE x = 1;
2233
2234 --echo --- connection master ---
2235 connection master;
2236 COMMIT;
2237
2238 --echo --- connection master1 ---
2239 connection master1;
2240 COMMIT;
2241
2242 --echo --- connection master ---
2243 connection master;
2244 --sync_slave_with_master
2245
2246 --echo --- connection slave ---
2247 connection slave;
2248 USE test;
2249 SELECT COUNT(*) FROM tab1 ;
2250 --echo --- connection master ---
2251 connection master;
2252 SELECT COUNT(*) FROM tab1;
2253
2254
2255 --let $diff_tables= master:tab1, slave:tab1
2256 --source include/diff_tables.inc
2257
2258 DROP FUNCTION func;
2259 DROP TABLE tab1;
2260
2261 # Exclusive locking on table
2262
2263 USE test;
2264 CREATE TABLE t1lock (a INT);
2265 CREATE TABLE t2lock (a INT);
2266 --echo --- connection master ---
2267 connection master;
2268 LOCK TABLE t1lock WRITE, t2lock WRITE;
2269 DROP TABLE t1lock;
2270
2271 --echo --- connection master1 ---
2272 connection master1;
2273 SET SESSION lock_wait_timeout = 1;
2274 --echo send statement and reap result later ---
2275 --send ALTER TABLE t2lock RENAME t1lock;
2276
2277 --echo --- connection master2 ---
2278 connection master2;
2279 let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
2280 WHERE state = 'Table lock' AND info = 'ALTER TABLE t1lock RENAME t2lock;'`;
2281
2282 --echo --- connection master1 ---
2283 connection master1;
2284 --echo reap result of previous---
2285 --error ER_LOCK_WAIT_TIMEOUT
2286 --reap
2287
2288 --echo --- connection master ---
2289 connection master;
2290 COMMIT;
2291 --sync_slave_with_master
2292
2293
2294 --echo --- connection slave ---
2295 connection slave;
2296 USE test;
2297 SELECT COUNT(*) FROM t2lock;
2298 --echo --- connection master ---
2299 connection master;
2300 SELECT COUNT(*) FROM t2lock;
2301
2302 --let $diff_tables= master:t2lock, slave:t2lock
2303 --source include/diff_tables.inc
2304
2305
2306 --echo --- connection master ---
2307 connection master;
2308 UNLOCK TABLES;
2309 CREATE TABLE t1lock (a INT);
2310 LOCK TABLE t1lock WRITE;
2311 UNLOCK TABLES;
2312 DROP TABLE t2lock;
2313
2314 --echo --- connection master1 ---
2315 connection master1;
2316 SET SESSION lock_wait_timeout = 1;
2317 --echo send statement and reap result later ---
2318 --send ALTER TABLE t1lock RENAME t2lock;
2319
2320 --echo --- connection master2 ---
2321 connection master2;
2322 let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
2323 WHERE state = 'Table lock' and info = 'ALTER TABLE t1lock RENAME t2lock;'`;
2324
2325 --echo --- connection master1 ---
2326 connection master1;
2327 --echo reap result of previous---
2328
2329 --reap
2330
2331 --echo --- connection master ---
2332 connection master;
2333 COMMIT;
2334 --sync_slave_with_master
2335
2336 --echo --- connection slave ---
2337 connection slave;
2338 USE test;
2339 SELECT COUNT(*) FROM t2lock;
2340 --echo --- connection master ---
2341 connection master;
2342 SELECT COUNT(*) FROM t2lock;
2343
2344 --let $diff_tables= master:t2lock, slave:t2lock
2345 --source include/diff_tables.inc
2346
2347 DROP TABLE t2lock;
2348
2349 # Waiting for lock and hangs
2350 CREATE TABLE t1lock1 (a INT, b INT);
2351 CREATE TABLE t2lock1 (a INT, b INT);
2352 --echo --- connection master ---
2353 connection master;
2354 LOCK TABLES t1lock1 WRITE;
2355 # InnoDB shows: 'OK' ,whereas, MyISAM shows: 'Table is already up to date'
2356 --replace_result OK "Table is already up to date"
2357 ANALYZE TABLE t1lock1;
2358 UNLOCK TABLES;
2359
2360 --echo --- connection master1 ---
2361 connection master1;
2362 SET SESSION lock_wait_timeout = 1;
2363 LOCK TABLE t2lock1 WRITE;
2364 FLUSH TABLE t2lock1;
2365 UNLOCK TABLES;
2366
2367 --sync_slave_with_master
2368
2369 --echo --- connection slave ---
2370 connection slave;
2371 USE test;
2372 SELECT COUNT(*) FROM t1lock1;
2373 --echo --- connection master ---
2374 connection master;
2375 SELECT COUNT(*) FROM t1lock1;
2376
2377 --let $diff_tables= master:t1lock1, slave:t1lock1
2378 --source include/diff_tables.inc
2379
2380 # Cleanup
2381 DROP TABLE t1lock1,t2lock1;
2382 DROP TABLE t1,t2,t3,t4;
2383 DROP TABLE t1part,t2part,t3part,t4part;
2384 DROP TABLE t1prim,t1opt;
2385 DROP TABLE t1trig,t2trig;
2386 DROP TABLE t1bin,t2bin,t1myisam,t1innodb;
2387 DROP TABLE t3967_1,t3967_2,t1_30138,t1_30138_old;
2388 DROP FUNCTION f1;
2389 DROP PROCEDURE p1;
2390 DROP VIEW v1;
2391 DROP USER user1@localhost;
2392 DROP DATABASE db1;
2393
2394 --sync_slave_with_master
2395 --source include/rpl_end.inc
2396 connection slave;
2397 disconnect slave;
2398 --source include/wait_until_disconnected.inc
2399 connection master3;
2400 disconnect master3;
2401 --source include/wait_until_disconnected.inc
2402 connection master2;
2403 disconnect master2;
2404 --source include/wait_until_disconnected.inc
2405 connection master1;
2406 disconnect master1;
2407 --source include/wait_until_disconnected.inc
2408 connection master;
2409 disconnect master;
2410 --source include/wait_until_disconnected.inc
2411