2
# Locking related tests which use DEBUG_SYNC facility.
4
--source include/have_debug_sync.inc
5
# We need InnoDB to be able use TL_WRITE_ALLOW_WRITE type of locks in our tests.
6
--source include/have_innodb.inc
7
# This test requires statement/mixed mode binary logging.
8
# Row-based mode puts weaker serializability requirements
9
# so weaker locks are acquired for it.
10
--source include/have_binlog_format_mixed_or_statement.inc
11
# Until bug#41971 'Thread state on embedded server is always "Writing to net"'
12
# is fixed this test can't be run on embedded version of server.
13
--source include/not_embedded.inc
15
# Save the initial number of concurrent sessions.
16
--source include/count_sessions.inc
20
--echo # Test how we handle locking in various cases when
21
--echo # we read data from MyISAM tables.
23
--echo # In this test we mostly check that the SQL-layer correctly
24
--echo # determines the type of thr_lock.c lock for a table being
26
--echo # I.e. that it disallows concurrent inserts when the statement
27
--echo # is going to be written to the binary log and therefore
28
--echo # should be serialized, and allows concurrent inserts when
29
--echo # such serialization is not necessary (e.g. when
30
--echo # the statement is not written to binary log).
33
--echo # Force concurrent inserts to be performed even if the table
34
--echo # has gaps. This allows to simplify clean up in scripts
35
--echo # used below (instead of backing up table being inserted
36
--echo # into and then restoring it from backup at the end of the
37
--echo # script we can simply delete rows which were inserted).
38
set @old_concurrent_insert= @@global.concurrent_insert;
39
set @@global.concurrent_insert= 2;
40
select @@global.concurrent_insert;
42
--echo # Prepare playground by creating tables, views,
43
--echo # routines and triggers used in tests.
44
connect (con1, localhost, root,,);
45
connect (con2, localhost, root,,);
48
drop table if exists t0, t1, t2, t3, t4, t5;
49
drop view if exists v1, v2;
50
drop procedure if exists p1;
51
drop procedure if exists p2;
52
drop function if exists f1;
53
drop function if exists f2;
54
drop function if exists f3;
55
drop function if exists f4;
56
drop function if exists f5;
57
drop function if exists f6;
58
drop function if exists f7;
59
drop function if exists f8;
60
drop function if exists f9;
61
drop function if exists f10;
62
drop function if exists f11;
63
drop function if exists f12;
64
drop function if exists f13;
65
drop function if exists f14;
66
drop function if exists f15;
68
create table t1 (i int primary key);
69
insert into t1 values (1), (2), (3), (4), (5);
70
create table t2 (j int primary key);
71
insert into t2 values (1), (2), (3), (4), (5);
72
create table t3 (k int primary key);
73
insert into t3 values (1), (2), (3);
74
create table t4 (l int primary key);
75
insert into t4 values (1);
76
create table t5 (l int primary key);
77
insert into t5 values (1);
78
create view v1 as select i from t1;
79
create view v2 as select j from t2 where j in (select i from t1);
80
create procedure p1(k int) insert into t2 values (k);
82
create function f1() returns int
85
select i from t1 where i = 1 into j;
88
create function f2() returns int
91
select i from t1 where i = 1 into k;
92
insert into t2 values (k + 5);
95
create function f3() returns int
97
return (select i from t1 where i = 3);
99
create function f4() returns int
101
if (select i from t1 where i = 3) then
107
create function f5() returns int
109
insert into t2 values ((select i from t1 where i = 1) + 5);
112
create function f6() returns int
115
select i from v1 where i = 1 into k;
118
create function f7() returns int
121
select j from v2 where j = 1 into k;
124
create function f8() returns int
127
select i from v1 where i = 1 into k;
128
insert into t2 values (k+5);
131
create function f9() returns int
133
update v2 set j=j+10 where j=1;
136
create function f10() returns int
140
create function f11() returns int
144
insert into t2 values (k+5);
147
create function f12(p int) returns int
149
insert into t2 values (p);
152
create function f13(p int) returns int
156
create procedure p2(inout p int)
158
select i from t1 where i = 1 into p;
160
create function f14() returns int
164
insert into t2 values (k+5);
167
create function f15() returns int
173
create trigger t4_bi before insert on t4 for each row
176
select i from t1 where i=1 into k;
179
create trigger t4_bu before update on t4 for each row
181
if (select i from t1 where i=1) then
185
create trigger t4_bd before delete on t4 for each row
187
if !(select i from v1 where i=1) then
188
signal sqlstate '45000';
191
create trigger t5_bi before insert on t5 for each row
195
create trigger t5_bu before update on t5 for each row
204
--echo # Set common variables to be used by the scripts
205
--echo # called below.
211
--echo # Switch to connection 'con1'.
213
--echo # Cache all functions used in the tests below so statements
214
--echo # calling them won't need to open and lock mysql.proc table
215
--echo # and we can assume that each statement locks its tables
216
--echo # once during its execution.
218
show create procedure p1;
219
show create procedure p2;
220
show create function f1;
221
show create function f2;
222
show create function f3;
223
show create function f4;
224
show create function f5;
225
show create function f6;
226
show create function f7;
227
show create function f8;
228
show create function f9;
229
show create function f10;
230
show create function f11;
231
show create function f12;
232
show create function f13;
233
show create function f14;
234
show create function f15;
236
--echo # Switch back to connection 'default'.
240
--echo # 1. Statements that read tables and do not use subqueries.
244
--echo # 1.1 Simple SELECT statement.
246
--echo # No locks are necessary as this statement won't be written
247
--echo # to the binary log and thanks to how MyISAM works SELECT
248
--echo # will see version of the table prior to concurrent insert.
249
let $statement= select * from t1;
250
let $restore_table= ;
251
--source include/check_concurrent_insert.inc
254
--echo # 1.2 Multi-UPDATE statement.
256
--echo # Has to take shared locks on rows in the table being read as this
257
--echo # statement will be written to the binary log and therefore should
258
--echo # be serialized with concurrent statements.
259
let $statement= update t2, t1 set j= j - 1 where i = j;
260
let $restore_table= t2;
261
--source include/check_no_concurrent_insert.inc
264
--echo # 1.3 Multi-DELETE statement.
266
--echo # The above is true for this statement as well.
267
let $statement= delete t2 from t1, t2 where i = j;
268
let $restore_table= t2;
269
--source include/check_no_concurrent_insert.inc
272
--echo # 1.4 DESCRIBE statement.
274
--echo # This statement does not really read data from the
275
--echo # target table and thus does not take any lock on it.
276
--echo # We check this for completeness of coverage.
278
--echo # Switching to connection 'con1'.
280
--echo # This statement should not be blocked.
284
--echo # Switching to connection 'default'.
289
--echo # 1.5 SHOW statements.
291
--echo # The above is true for SHOW statements as well.
293
--echo # Switching to connection 'con1'.
295
--echo # These statements should not be blocked.
296
# The below test for SHOW CREATE TABLE is disabled until bug 52593
297
# "SHOW CREATE TABLE is blocked if table is locked for write by another
298
# connection" is fixed.
300
show create table t1;
305
--echo # Switching to connection 'default'.
311
--echo # 2. Statements which read tables through subqueries.
315
--echo # 2.1 CALL with a subquery.
317
--echo # A strong lock is not necessary as this statement is not
318
--echo # written to the binary log as a whole (it is written
319
--echo # statement-by-statement).
320
let $statement= call p1((select i + 5 from t1 where i = 1));
321
let $restore_table= t2;
322
--source include/check_concurrent_insert.inc
325
--echo # 2.2 CREATE TABLE with a subquery.
327
--echo # Has to take a strong lock on the table being read as
328
--echo # this statement is written to the binary log and therefore
329
--echo # should be serialized with concurrent statements.
330
let $statement= create table t0 select * from t1;
331
let $restore_table= ;
332
--source include/check_no_concurrent_insert.inc
334
let $statement= create table t0 select j from t2 where j in (select i from t1);
335
let $restore_table= ;
336
--source include/check_no_concurrent_insert.inc
340
--echo # 2.3 DELETE with a subquery.
342
--echo # The above is true for this statement as well.
343
let $statement= delete from t2 where j in (select i from t1);
344
let $restore_table= t2;
345
--source include/check_no_concurrent_insert.inc
348
--echo # 2.4 MULTI-DELETE with a subquery.
350
--echo # Same is true for this statement as well.
351
let $statement= delete t2 from t3, t2 where k = j and j in (select i from t1);
352
let $restore_table= t2;
353
--source include/check_no_concurrent_insert.inc
357
--echo # 2.5 DO with a subquery.
359
--echo # A strong lock is not necessary as it is not logged.
360
let $statement= do (select i from t1 where i = 1);
361
let $restore_table= ;
362
--source include/check_concurrent_insert.inc
365
--echo # 2.6 INSERT with a subquery.
367
--echo # Has to take a strong lock on the table being read as
368
--echo # this statement is written to the binary log and therefore
369
--echo # should be serialized with concurrent inserts.
370
let $statement= insert into t2 select i+5 from t1;
371
let $restore_table= t2;
372
--source include/check_no_concurrent_insert.inc
373
let $statement= insert into t2 values ((select i+5 from t1 where i = 4));
374
let $restore_table= t2;
375
--source include/check_no_concurrent_insert.inc
378
--echo # 2.7 LOAD DATA with a subquery.
380
--echo # The above is true for this statement as well.
381
let $statement= load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1);
382
let $restore_table= t2;
383
--source include/check_no_concurrent_insert.inc
386
--echo # 2.8 REPLACE with a subquery.
388
--echo # Same is true for this statement as well.
389
let $statement= replace into t2 select i+5 from t1;
390
let $restore_table= t2;
391
--source include/check_no_concurrent_insert.inc
392
let $statement= replace into t2 values ((select i+5 from t1 where i = 4));
393
let $restore_table= t2;
394
--source include/check_no_concurrent_insert.inc
397
--echo # 2.9 SELECT with a subquery.
399
--echo # Strong locks are not necessary as this statement is not written
400
--echo # to the binary log and thanks to how MyISAM works this statement
401
--echo # sees a version of the table prior to the concurrent insert.
402
let $statement= select * from t2 where j in (select i from t1);
403
let $restore_table= ;
404
--source include/check_concurrent_insert.inc
407
--echo # 2.10 SET with a subquery.
409
--echo # The same is true for this statement as well.
410
let $statement= set @a:= (select i from t1 where i = 1);
411
let $restore_table= ;
412
--source include/check_concurrent_insert.inc
415
--echo # 2.11 SHOW with a subquery.
417
--echo # And for this statement too.
418
let $statement= show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1);
419
let $restore_table= ;
420
--source include/check_concurrent_insert.inc
421
let $statement= show columns from t2 where (select i from t1 where i = 1);
422
let $restore_table= ;
423
--source include/check_concurrent_insert.inc
426
--echo # 2.12 UPDATE with a subquery.
428
--echo # Has to take a strong lock on the table being read as
429
--echo # this statement is written to the binary log and therefore
430
--echo # should be serialized with concurrent inserts.
431
let $statement= update t2 set j= j-10 where j in (select i from t1);
432
let $restore_table= t2;
433
--source include/check_no_concurrent_insert.inc
436
--echo # 2.13 MULTI-UPDATE with a subquery.
438
--echo # Same is true for this statement as well.
439
let $statement= update t2, t3 set j= j -10 where j=k and j in (select i from t1);
440
let $restore_table= t2;
441
--source include/check_no_concurrent_insert.inc
445
--echo # 3. Statements which read tables through a view.
449
--echo # 3.1 SELECT statement which uses some table through a view.
451
--echo # Since this statement is not written to the binary log and
452
--echo # an old version of the table is accessible thanks to how MyISAM
453
--echo # handles concurrent insert, no locking is necessary.
454
let $statement= select * from v1;
455
let $restore_table= ;
456
--source include/check_concurrent_insert.inc
457
let $statement= select * from v2;
458
let $restore_table= ;
459
--source include/check_concurrent_insert.inc
460
let $statement= select * from t2 where j in (select i from v1);
461
let $restore_table= ;
462
--source include/check_concurrent_insert.inc
463
let $statement= select * from t3 where k in (select j from v2);
464
let $restore_table= ;
465
--source include/check_concurrent_insert.inc
468
--echo # 3.2 Statements which modify a table and use views.
470
--echo # Since such statements are going to be written to the binary
471
--echo # log they need to be serialized against concurrent statements
472
--echo # and therefore should take strong locks on the data read.
473
let $statement= update t2 set j= j-10 where j in (select i from v1);
474
let $restore_table= t2;
475
--source include/check_no_concurrent_insert.inc
476
let $statement= update t3 set k= k-10 where k in (select j from v2);
477
let $restore_table= t2;
478
--source include/check_no_concurrent_insert.inc
479
let $statement= update t2, v1 set j= j-10 where j = i;
480
let $restore_table= t2;
481
--source include/check_no_concurrent_insert.inc
482
let $statement= update v2 set j= j-10 where j = 3;
483
let $restore_table= t2;
484
--source include/check_no_concurrent_insert.inc
488
--echo # 4. Statements which read tables through stored functions.
492
--echo # 4.1 SELECT/SET with a stored function which does not
493
--echo # modify data and uses SELECT in its turn.
495
--echo # In theory there is no need to take strong locks on the table
496
--echo # being selected from in SF as the call to such function
497
--echo # won't get into the binary log. In practice, however, we
498
--echo # discover that fact too late in the process to be able to
499
--echo # affect the decision what locks should be taken.
500
--echo # Hence, strong locks are taken in this case.
501
let $statement= select f1();
502
let $restore_table= ;
503
--source include/check_no_concurrent_insert.inc
504
let $statement= set @a:= f1();
505
let $restore_table= ;
506
--source include/check_no_concurrent_insert.inc
509
--echo # 4.2 INSERT (or other statement which modifies data) with
510
--echo # a stored function which does not modify data and uses
513
--echo # Since such statement is written to the binary log it should
514
--echo # be serialized with concurrent statements affecting the data
515
--echo # it uses. Therefore it should take strong lock on the data
517
let $statement= insert into t2 values (f1() + 5);
518
let $restore_table= t2;
519
--source include/check_no_concurrent_insert.inc
522
--echo # 4.3 SELECT/SET with a stored function which
523
--echo # reads and modifies data.
525
--echo # Since a call to such function is written to the binary log,
526
--echo # it should be serialized with concurrent statements affecting
527
--echo # the data it uses. Hence, a strong lock on the data read
528
--echo # should be taken.
529
let $statement= select f2();
530
let $restore_table= t2;
531
--source include/check_no_concurrent_insert.inc
532
let $statement= set @a:= f2();
533
let $restore_table= t2;
534
--source include/check_no_concurrent_insert.inc
537
--echo # 4.4. SELECT/SET with a stored function which does not
538
--echo # modify data and reads a table through subselect
539
--echo # in a control construct.
541
--echo # Again, in theory a call to this function won't get to the
542
--echo # binary log and thus no strong lock is needed. But in practice
543
--echo # we don't detect this fact early enough (get_lock_type_for_table())
544
--echo # to avoid taking a strong lock.
545
let $statement= select f3();
546
let $restore_table= ;
547
--source include/check_no_concurrent_insert.inc
548
let $statement= set @a:= f3();
549
let $restore_table= ;
550
--source include/check_no_concurrent_insert.inc
551
let $statement= select f4();
552
let $restore_table= ;
553
--source include/check_no_concurrent_insert.inc
554
let $statement= set @a:= f4();
555
let $restore_table= ;
556
--source include/check_no_concurrent_insert.inc
559
--echo # 4.5. INSERT (or other statement which modifies data) with
560
--echo # a stored function which does not modify data and reads
561
--echo # the table through a subselect in one of its control
564
--echo # Since such statement is written to the binary log it should
565
--echo # be serialized with concurrent statements affecting data it
566
--echo # uses. Therefore it should take a strong lock on the data
568
let $statement= insert into t2 values (f3() + 5);
569
let $restore_table= t2;
570
--source include/check_no_concurrent_insert.inc
571
let $statement= insert into t2 values (f4() + 6);
572
let $restore_table= t2;
573
--source include/check_no_concurrent_insert.inc
576
--echo # 4.6 SELECT/SET which uses a stored function with
577
--echo # DML which reads a table via a subquery.
579
--echo # Since call to such function is written to the binary log
580
--echo # it should be serialized with concurrent statements.
581
--echo # Hence reads should take a strong lock.
582
let $statement= select f5();
583
let $restore_table= t2;
584
--source include/check_no_concurrent_insert.inc
585
let $statement= set @a:= f5();
586
let $restore_table= t2;
587
--source include/check_no_concurrent_insert.inc
590
--echo # 4.7 SELECT/SET which uses a stored function which
591
--echo # doesn't modify data and reads tables through
594
--echo # Once again, in theory, calls to such functions won't
595
--echo # get into the binary log and thus don't need strong
596
--echo # locks. But in practice this fact is discovered
597
--echo # too late to have any effect.
598
let $statement= select f6();
599
let $restore_table= t2;
600
--source include/check_no_concurrent_insert.inc
601
let $statement= set @a:= f6();
602
let $restore_table= t2;
603
--source include/check_no_concurrent_insert.inc
604
let $statement= select f7();
605
let $restore_table= t2;
606
--source include/check_no_concurrent_insert.inc
607
let $statement= set @a:= f7();
608
let $restore_table= t2;
609
--source include/check_no_concurrent_insert.inc
612
--echo # 4.8 INSERT which uses stored function which
613
--echo # doesn't modify data and reads a table
614
--echo # through a view.
616
--echo # Since such statement is written to the binary log and
617
--echo # should be serialized with concurrent statements affecting
618
--echo # the data it uses. Therefore it should take a strong lock on
619
--echo # the table it reads.
620
let $statement= insert into t3 values (f6() + 5);
621
let $restore_table= t3;
622
--source include/check_no_concurrent_insert.inc
623
let $statement= insert into t3 values (f7() + 5);
624
let $restore_table= t3;
625
--source include/check_no_concurrent_insert.inc
629
--echo # 4.9 SELECT which uses a stored function which
630
--echo # modifies data and reads tables through a view.
632
--echo # Since a call to such function is written to the binary log
633
--echo # it should be serialized with concurrent statements.
634
--echo # Hence, reads should take strong locks.
635
let $statement= select f8();
636
let $restore_table= t2;
637
--source include/check_no_concurrent_insert.inc
638
let $statement= select f9();
639
let $restore_table= t2;
640
--source include/check_no_concurrent_insert.inc
643
--echo # 4.10 SELECT which uses a stored function which doesn't modify
644
--echo # data and reads a table indirectly, by calling another
647
--echo # In theory, calls to such functions won't get into the binary
648
--echo # log and thus don't need to acquire strong locks. But in practice
649
--echo # this fact is discovered too late to have any effect.
650
let $statement= select f10();
651
let $restore_table= ;
652
--source include/check_no_concurrent_insert.inc
655
--echo # 4.11 INSERT which uses a stored function which doesn't modify
656
--echo # data and reads a table indirectly, by calling another
659
--echo # Since such statement is written to the binary log, it should
660
--echo # be serialized with concurrent statements affecting the data it
661
--echo # uses. Therefore it should take strong locks on data it reads.
662
let $statement= insert into t2 values (f10() + 5);
663
let $restore_table= t2;
664
--source include/check_no_concurrent_insert.inc
667
--echo # 4.12 SELECT which uses a stored function which modifies
668
--echo # data and reads a table indirectly, by calling another
671
--echo # Since a call to such function is written to the binary log
672
--echo # it should be serialized from concurrent statements.
673
--echo # Hence, read should take a strong lock.
674
let $statement= select f11();
675
let $restore_table= t2;
676
--source include/check_no_concurrent_insert.inc
679
--echo # 4.13 SELECT that reads a table through a subquery passed
680
--echo # as a parameter to a stored function which modifies
683
--echo # Even though a call to this function is written to the
684
--echo # binary log, values of its parameters are written as literals.
685
--echo # So there is no need to acquire strong locks for tables used in
686
--echo # the subquery.
687
let $statement= select f12((select i+10 from t1 where i=1));
688
let $restore_table= t2;
689
--source include/check_concurrent_insert.inc
692
--echo # 4.14 INSERT that reads a table via a subquery passed
693
--echo # as a parameter to a stored function which doesn't
694
--echo # modify data.
696
--echo # Since this statement is written to the binary log it should
697
--echo # be serialized with concurrent statements affecting the data it
698
--echo # uses. Therefore it should take strong locks on the data it reads.
699
let $statement= insert into t2 values (f13((select i+10 from t1 where i=1)));
700
let $restore_table= t2;
701
--source include/check_no_concurrent_insert.inc
705
--echo # 5. Statements that read tables through stored procedures.
709
--echo # 5.1 CALL statement which reads a table via SELECT.
711
--echo # Since neither this statement nor its components are
712
--echo # written to the binary log, there is no need to take
713
--echo # strong locks on the data it reads.
714
let $statement= call p2(@a);
715
let $restore_table= ;
716
--source include/check_concurrent_insert.inc
719
--echo # 5.2 Function that modifies data and uses CALL,
720
--echo # which reads a table through SELECT.
722
--echo # Since a call to such function is written to the binary
723
--echo # log, it should be serialized with concurrent statements.
724
--echo # Hence, in this case reads should take strong locks on data.
725
let $statement= select f14();
726
let $restore_table= t2;
727
--source include/check_no_concurrent_insert.inc
730
--echo # 5.3 SELECT that calls a function that doesn't modify data and
731
--echo # uses a CALL statement that reads a table via SELECT.
733
--echo # In theory, calls to such functions won't get into the binary
734
--echo # log and thus don't need to acquire strong locks. But in practice
735
--echo # this fact is discovered too late to have any effect.
736
let $statement= select f15();
737
let $restore_table= ;
738
--source include/check_no_concurrent_insert.inc
741
--echo # 5.4 INSERT which calls function which doesn't modify data and
742
--echo # uses CALL statement which reads table through SELECT.
744
--echo # Since such statement is written to the binary log it should
745
--echo # be serialized with concurrent statements affecting data it
746
--echo # uses. Therefore it should take strong locks on data it reads.
747
let $statement= insert into t2 values (f15()+5);
748
let $restore_table= t2;
749
--source include/check_no_concurrent_insert.inc
753
--echo # 6. Statements that use triggers.
757
--echo # 6.1 Statement invoking a trigger that reads table via SELECT.
759
--echo # Since this statement is written to the binary log it should
760
--echo # be serialized with concurrent statements affecting the data
761
--echo # it uses. Therefore, it should take strong locks on the data
763
let $statement= insert into t4 values (2);
764
let $restore_table= t4;
765
--source include/check_no_concurrent_insert.inc
768
--echo # 6.2 Statement invoking a trigger that reads table through
769
--echo # a subquery in a control construct.
771
--echo # The above is true for this statement as well.
772
let $statement= update t4 set l= 2 where l = 1;
773
let $restore_table= t4;
774
--source include/check_no_concurrent_insert.inc
777
--echo # 6.3 Statement invoking a trigger that reads a table through
780
--echo # And for this statement.
781
let $statement= delete from t4 where l = 1;
782
let $restore_table= t4;
783
--source include/check_no_concurrent_insert.inc
786
--echo # 6.4 Statement invoking a trigger that reads a table through
787
--echo # a stored function.
789
--echo # And for this statement.
790
let $statement= insert into t5 values (2);
791
let $restore_table= t5;
792
--source include/check_no_concurrent_insert.inc
795
--echo # 6.5 Statement invoking a trigger that reads a table through
796
--echo # stored procedure.
798
--echo # And for this statement.
799
let $statement= update t5 set l= 2 where l = 1;
800
let $restore_table= t5;
801
--source include/check_no_concurrent_insert.inc
823
drop table t1, t2, t3, t4, t5;
828
set @@global.concurrent_insert= @old_concurrent_insert;
832
--echo # Test for bug #45143 "All connections hang on concurrent ALTER TABLE".
834
--echo # Concurrent execution of statements which required weak write lock
835
--echo # (TL_WRITE_ALLOW_WRITE) on several instances of the same table and
836
--echo # statements which tried to acquire stronger write lock (TL_WRITE,
837
--echo # TL_WRITE_ALLOW_READ) on this table might have led to deadlock.
839
# Suppress warnings for INSERTs that use get_lock().
842
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
846
drop table if exists t1;
847
drop view if exists v1;
849
--echo # Create auxiliary connections used through the test.
850
connect (con_bug45143_1,localhost,root,,test,,);
851
connect (con_bug45143_3,localhost,root,,test,,);
852
connect (con_bug45143_2,localhost,root,,test,,);
854
--echo # Reset DEBUG_SYNC facility before using it.
855
set debug_sync= 'RESET';
856
--echo # Turn off logging so calls to locking subsystem performed
857
--echo # for general_log table won't interfere with our test.
858
set @old_general_log = @@global.general_log;
859
set @@global.general_log= OFF;
861
create table t1 (i int) engine=InnoDB;
862
--echo # We have to use view in order to make LOCK TABLES avoid
863
--echo # acquiring SNRW metadata lock on table.
864
create view v1 as select * from t1;
865
insert into t1 values (1);
866
--echo # Prepare user lock which will be used for resuming execution of
867
--echo # the first statement after it acquires TL_WRITE_ALLOW_WRITE lock.
868
select get_lock("lock_bug45143_wait", 0);
870
--echo # Switch to connection 'con_bug45143_1'.
871
connection con_bug45143_1;
873
--send insert into t1 values (get_lock("lock_bug45143_wait", 100));
875
--echo # Switch to connection 'con_bug45143_2'.
876
connection con_bug45143_2;
877
--echo # Wait until the above INSERT takes TL_WRITE_ALLOW_WRITE lock on 't1'
878
--echo # and then gets blocked on user lock 'lock_bug45143_wait'.
879
let $wait_condition= select count(*)= 1 from information_schema.processlist
880
where state= 'User lock' and
881
info='insert into t1 values (get_lock("lock_bug45143_wait", 100))';
882
--source include/wait_condition.inc
883
--echo # Ensure that upcoming SELECT waits after acquiring TL_WRITE_ALLOW_WRITE
884
--echo # lock for the first instance of 't1'.
885
set debug_sync='thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go';
887
--send select count(*) > 0 from t1 as a, t1 as b for update;
889
--echo # Switch to connection 'con_bug45143_3'.
890
connection con_bug45143_3;
891
--echo # Wait until the above SELECT ... FOR UPDATE is blocked after
892
--echo # acquiring lock for the the first instance of 't1'.
893
set debug_sync= 'now WAIT_FOR parked';
894
--echo # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1':
895
--send lock table v1 write;
897
--echo # Switch to connection 'default'.
899
--echo # Wait until this LOCK TABLES statement starts waiting for table lock.
900
let $wait_condition= select count(*)= 1 from information_schema.processlist
901
where state= 'Waiting for table level lock' and
902
info='lock table v1 write';
903
--source include/wait_condition.inc
904
--echo # Allow SELECT ... FOR UPDATE to resume.
905
--echo # Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance
906
--echo # of 't1' it should be able to get lock on the second instance without
907
--echo # waiting, even although there is another thread which has such lock
908
--echo # on this table and also there is a thread waiting for a TL_WRITE on it.
909
set debug_sync= 'now SIGNAL go';
911
--echo # Switch to connection 'con_bug45143_2'.
912
connection con_bug45143_2;
913
--echo # Reap SELECT ... FOR UPDATE
916
--echo # Switch to connection 'default'.
918
--echo # Resume execution of the INSERT statement.
919
select release_lock("lock_bug45143_wait");
921
--echo # Switch to connection 'con_bug45143_1'.
922
connection con_bug45143_1;
923
--echo # Reap INSERT statement.
924
--echo # In Statement and Mixed replication mode we get here "Unsafe
925
--echo # for binlog" warnings. In row mode there are no warnings.
926
--echo # Hide the discrepancy.
932
--echo # Switch to connection 'con_bug45143_3'.
933
connection con_bug45143_3;
934
--echo # Reap LOCK TABLES statement.
938
--echo # Switch to connection 'default'.
940
--echo # Do clean-up.
941
disconnect con_bug45143_1;
942
disconnect con_bug45143_2;
943
disconnect con_bug45143_3;
944
set debug_sync= 'RESET';
945
set @@global.general_log= @old_general_log;
951
--echo # Bug#50821 Deadlock between LOCK TABLES and ALTER TABLE
955
DROP TABLE IF EXISTS t1, t2;
958
CREATE TABLE t1(id INT);
959
CREATE TABLE t2(id INT);
961
--echo # Connection con2
962
connect (con2, localhost, root);
966
--echo # Connection default
969
--send ALTER TABLE t1 ADD COLUMN j INT
971
--echo # Connection con2
974
SELECT COUNT(*) = 1 FROM information_schema.processlist
975
WHERE state = "Waiting for table metadata lock"
976
AND info = "ALTER TABLE t1 ADD COLUMN j INT";
977
--source include/wait_condition.inc
979
--echo # This used to cause a deadlock.
980
INSERT INTO t2 SELECT * FROM t1;
984
--echo # Connection default
986
--echo # Reaping ALTER TABLE t1 ADD COLUMN j INT
994
--echo # Bug#51391 Deadlock involving events during rqg_info_schema test
997
CREATE EVENT e1 ON SCHEDULE EVERY 5 HOUR DO SELECT 1;
998
CREATE EVENT e2 ON SCHEDULE EVERY 5 HOUR DO SELECT 2;
1000
--echo # Connection con1
1001
connect(con1, localhost, root);
1002
SET DEBUG_SYNC="before_lock_tables_takes_lock SIGNAL drop WAIT_FOR query";
1004
--send DROP EVENT e1;
1006
--echo # Connection default
1008
SET DEBUG_SYNC="now WAIT_FOR drop";
1009
SELECT name FROM mysql.event, INFORMATION_SCHEMA.GLOBAL_VARIABLES
1010
WHERE definer = VARIABLE_VALUE;
1011
SET DEBUG_SYNC="now SIGNAL query";
1013
--echo # Connection con1
1015
--echo # Reaping: DROP EVENT t1
1018
--source include/wait_until_disconnected.inc
1020
--echo # Connection default
1023
SET DEBUG_SYNC="RESET";
1027
--echo # Bug#55930 Assertion `thd->transaction.stmt.is_empty() ||
1028
--echo # thd->in_sub_stmt || (thd->state..
1032
DROP TABLE IF EXISTS t1;
1035
CREATE TABLE t1(a INT) engine=InnoDB;
1036
INSERT INTO t1 VALUES (1), (2);
1038
connect (con1, localhost, root);
1039
connect (con2, localhost, root);
1041
--echo # Connection con1
1043
SET SESSION lock_wait_timeout= 1;
1044
SET DEBUG_SYNC= 'ha_admin_open_ltable SIGNAL opti_recreate WAIT_FOR opti_analyze';
1046
--send OPTIMIZE TABLE t1
1048
--echo # Connection con2
1050
SET DEBUG_SYNC= 'now WAIT_FOR opti_recreate';
1051
SET DEBUG_SYNC= 'after_lock_tables_takes_lock SIGNAL thrlock WAIT_FOR release_thrlock';
1053
--send INSERT INTO t1 VALUES (3)
1055
--echo # Connection default
1057
SET DEBUG_SYNC= 'now WAIT_FOR thrlock';
1058
SET DEBUG_SYNC= 'now SIGNAL opti_analyze';
1060
--echo # Connection con1
1062
--echo # Reaping: OPTIMIZE TABLE t1
1064
SET DEBUG_SYNC= 'now SIGNAL release_thrlock';
1066
--source include/wait_until_disconnected.inc
1068
--echo # Connection con2
1070
--echo # Reaping: INSERT INTO t1 VALUES (3)
1073
--source include/wait_until_disconnected.inc
1075
--echo # Connection default
1078
SET DEBUG_SYNC= 'RESET';
1082
--echo # Bug#57130 crash in Item_field::print during SHOW CREATE TABLE or VIEW
1086
DROP TABLE IF EXISTS t1;
1087
DROP VIEW IF EXISTS v1;
1088
DROP FUNCTION IF EXISTS f1;
1091
CREATE TABLE t1(a INT);
1092
CREATE FUNCTION f1() RETURNS INTEGER RETURN 1;
1093
CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1() = 1;
1095
connect(con2, localhost, root);
1097
--echo # Connection con1
1098
connect (con1, localhost, root);
1099
# Need to trigger this sync point at least twice in order to
1100
# get valgrind test failures without the patch
1101
SET DEBUG_SYNC= 'open_tables_after_open_and_process_table SIGNAL opened WAIT_FOR dropped EXECUTE 2';
1103
--send SHOW CREATE VIEW v1
1105
--echo # Connection con2
1107
SET DEBUG_SYNC= 'now WAIT_FOR opened';
1108
SET DEBUG_SYNC= 'now SIGNAL dropped';
1109
SET DEBUG_SYNC= 'now WAIT_FOR opened';
1113
--echo # Connection default
1115
--echo # Waiting for FLUSH TABLES to be blocked.
1116
let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist
1117
WHERE state= 'Waiting for table flush' AND info= 'FLUSH TABLES';
1118
--source include/wait_condition.inc
1119
SET DEBUG_SYNC= 'now SIGNAL dropped';
1121
--echo # Connection con1
1123
--echo # Reaping: SHOW CREATE VIEW v1
1126
--echo # Connection con2
1128
--echo # Reaping: FLUSH TABLES
1131
--echo # Connection default
1133
SET DEBUG_SYNC= 'RESET';
1140
# Check that all connections opened by test cases in this file are really
1141
# gone so execution of other tests won't be affected by their presence.
1142
--source include/wait_until_count_sessions.inc