1
-- source include/not_embedded.inc
3
# Save the initial number of concurrent sessions
4
--source include/count_sessions.inc
7
drop table if exists t1,t2;
10
# Test to see if select will get the lock ahead of low priority update
12
connect (locker,localhost,root,,);
13
connect (reader,localhost,root,,);
14
connect (writer,localhost,root,,);
17
create table t1(n int);
18
insert into t1 values (1);
22
update low_priority t1 set n = 4;
24
# Sleep a bit till the update of connection writer is in work and hangs
26
select count(*) = 1 from information_schema.processlist
27
where state = "Locked" and info = "update low_priority t1 set n = 4";
28
--source include/wait_condition.inc
32
# Sleep a bit till the select of connection reader is in work and hangs
34
select count(*) = 1 from information_schema.processlist
35
where state = "Locked" and info = "select n from t1";
36
--source include/wait_condition.inc
45
create table t1(n int);
46
insert into t1 values (1);
50
update low_priority t1 set n = 4;
52
# Sleep a bit till the update of connection writer is in work and hangs
54
select count(*) = 1 from information_schema.processlist
55
where state = "Locked" and info = "update low_priority t1 set n = 4";
56
--source include/wait_condition.inc
65
# Test problem when using locks with multi-updates
66
# It should not block when multi-update is reading on a read-locked table
70
create table t1 (a int, b int);
71
create table t2 (c int, d int);
72
insert into t1 values(1,1);
73
insert into t1 values(2,2);
74
insert into t2 values(1,2);
77
update t1,t2 set c=a where b=d;
85
# Test problem when using locks on many tables and dropping a table that
86
# is to-be-locked by another thread
90
create table t1 (a int);
91
create table t2 (a int);
92
lock table t1 write, t2 write;
95
insert t1 select * from t2;
98
select count(*) = 1 from information_schema.processlist
99
where state = "Locked" and info = "insert t1 select * from t2";
100
--source include/wait_condition.inc
103
--error ER_NO_SUCH_TABLE
109
# Same test as above, but with the dropped table locked twice
113
create table t1 (a int);
114
create table t2 (a int);
115
lock table t1 write, t2 write, t1 as t1_2 write, t2 as t2_2 write;
118
insert t1 select * from t2;
120
# Sleep a bit till the insert of connection reader is in work and hangs
122
select count(*) = 1 from information_schema.processlist
123
where state = "Locked" and info = "insert t1 select * from t2";
124
--source include/wait_condition.inc
127
--error ER_NO_SUCH_TABLE
133
--echo End of 4.1 tests
136
# Bug#9998 MySQL client hangs on USE "database"
138
create table t1(a int);
139
lock tables t1 write;
141
show columns from t1;
147
# Bug#16986 Deadlock condition with MyISAM tables
150
# Need a matching user in mysql.user for multi-table select
151
--source include/add_anonymous_users.inc
155
LOCK TABLES columns_priv WRITE, db WRITE, host WRITE, user WRITE;
160
# Note: This must be a multi-table select, otherwise the deadlock will not occur
162
SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1;
165
# Sleep a bit till the select of connection reader is in work and hangs
167
select count(*) = 1 from information_schema.processlist
168
where state = "Waiting for table" and info =
169
"SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1";
170
--source include/wait_condition.inc
171
# Make test case independent from earlier grants.
172
--replace_result "Table is already up to date" "OK"
173
OPTIMIZE TABLES columns_priv, db, host, user;
185
# Test if CREATE TABLE with LOCK TABLE deadlocks.
188
CREATE TABLE t1 (c1 int);
191
# This waits until t1 is unlocked.
194
FLUSH TABLES WITH READ LOCK;
197
# Sleep a bit till the flush of connection locker is in work and hangs
199
select count(*) = 1 from information_schema.processlist
200
where state = "Flushing tables" and info = "FLUSH TABLES WITH READ LOCK";
201
--source include/wait_condition.inc
202
# This must not block.
203
CREATE TABLE t2 (c1 int);
214
# Test if CREATE TABLE SELECT with LOCK TABLE deadlocks.
217
CREATE TABLE t1 (c1 int);
220
# This waits until t1 is unlocked.
223
FLUSH TABLES WITH READ LOCK;
225
# This must not block.
227
# Sleep a bit till the flush of connection locker is in work and hangs
229
select count(*) = 1 from information_schema.processlist
230
where state = "Flushing tables" and info = "FLUSH TABLES WITH READ LOCK";
231
--source include/wait_condition.inc
232
--error ER_TABLE_NOT_LOCKED
233
CREATE TABLE t2 AS SELECT * FROM t1;
244
--source include/delete_anonymous_users.inc
247
# Bug#19815 CREATE/RENAME/DROP DATABASE can deadlock on a global read lock
249
connect (con1,localhost,root,,);
250
connect (con2,localhost,root,,);
253
CREATE DATABASE mysqltest_1;
254
FLUSH TABLES WITH READ LOCK;
256
# With bug in place: acquire LOCK_mysql_create_table and
257
# wait in wait_if_global_read_lock().
260
DROP DATABASE mysqltest_1;
262
# With bug in place: try to acquire LOCK_mysql_create_table...
263
# When fixed: Reject dropping db because of the read lock.
265
# Wait a bit so that the session con2 is in state "Waiting for release of readlock"
267
select count(*) = 1 from information_schema.processlist
268
where state = "Waiting for release of readlock"
269
and info = "DROP DATABASE mysqltest_1";
270
--source include/wait_condition.inc
271
--error ER_CANT_UPDATE_WITH_READLOCK
272
DROP DATABASE mysqltest_1;
281
# This must have been dropped by connection 2 already,
282
# which waited until the global read lock was released.
283
--error ER_DB_DROP_EXISTS
284
DROP DATABASE mysqltest_1;
287
# Bug#17264 MySQL Server freeze
290
# Disable warnings to allow test to run also without InnoDB
292
create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) engine=innodb;
294
lock tables t1 write;
297
alter table t1 auto_increment=0;
299
# Wait till connection writer is blocked
301
select count(*) = 1 from information_schema.processlist
302
where state = "Locked" and info = "alter table t1 auto_increment=0";
303
--source include/wait_condition.inc
305
alter table t1 auto_increment=0;
307
# Wait till connection reader is blocked
309
select count(*) = 2 from information_schema.processlist
310
where state = "Locked" and info = "alter table t1 auto_increment=0";
311
--source include/wait_condition.inc
321
# Bug#43230: SELECT ... FOR UPDATE can hang with FLUSH TABLES WITH READ LOCK indefinitely
324
connect (con1,localhost,root,,);
325
connect (con2,localhost,root,,);
326
connect (con3,localhost,root,,);
327
connect (con4,localhost,root,,);
328
connect (con5,localhost,root,,);
330
create table t1 (a int);
331
create table t2 like t1;
335
lock tables t1 write;
338
send flush tables with read lock;
341
let $show_statement= SHOW PROCESSLIST;
343
let $condition= = 'Flushing tables';
344
--source include/wait_show_condition.inc
345
--echo # global read lock is taken
348
send select * from t2 for update;
350
let $show_statement= SHOW PROCESSLIST;
352
let $condition= = 'Waiting for release of readlock';
353
--source include/wait_show_condition.inc
354
--echo # waiting for release of read lock
357
--echo # would hang and later cause a deadlock
377
--echo # Lightweight version:
378
--echo # Ensure that the wait for a GRL is done before opening tables.
381
connect (con1,localhost,root,,);
382
connect (con2,localhost,root,,);
384
create table t1 (a int);
385
create table t2 like t1;
393
flush tables with read lock;
396
send update t2 set a = 1;
399
let $show_statement= SHOW PROCESSLIST;
401
let $condition= = 'Waiting for release of readlock';
402
--source include/wait_show_condition.inc
403
--echo # statement is waiting for release of read lock
415
--echo # LOCK TABLES .. WRITE
420
flush tables with read lock;
423
send lock tables t2 write;
426
let $show_statement= SHOW PROCESSLIST;
428
let $condition= = 'Waiting for release of readlock';
429
--source include/wait_show_condition.inc
430
--echo # statement is waiting for release of read lock
449
--echo End of 5.0 tests
453
# Bug#21281 Pending write lock is incorrectly removed when its
454
# statement being KILLed
456
create table t1 (i int);
464
select count(*) = 1 from information_schema.processlist
465
where state = "Locked" and info = "update t1 set i= 10";
466
--source include/wait_condition.inc
471
select count(*) = 1 from information_schema.processlist
472
where state = "Locked" and info = "select * from t1";
473
--source include/wait_condition.inc
474
let $ID= `select id from information_schema.processlist where state = "Locked" and info = "update t1 set i= 10"`;
475
--replace_result $ID ID
480
--error ER_QUERY_INTERRUPTED
488
# Bug#25856 HANDLER table OPEN in one connection lock DROP TABLE in another one
491
drop table if exists t1;
493
create table t1 (a int) ENGINE=MEMORY;
496
--error ER_ILLEGAL_HA
503
# Disconnect sessions used in many subtests above
510
# Bug#32395 Alter table under a impending global read lock causes a server crash
514
# Test ALTER TABLE under LOCK TABLES and FLUSH TABLES WITH READ LOCK
518
drop table if exists t1;
520
create table t1 (i int);
521
connect (flush,localhost,root,,test,,);
523
--echo connection: default
524
lock tables t1 write;
526
--echo connection: flush
527
--send flush tables with read lock;
529
--echo connection: default
531
select count(*) = 1 from information_schema.processlist
532
where state = "Flushing tables";
533
--source include/wait_condition.inc
534
alter table t1 add column j int;
535
connect (insert,localhost,root,,test,,);
537
--echo connection: insert
539
select count(*) = 1 from information_schema.processlist
540
where state = "Flushing tables";
541
--source include/wait_condition.inc
542
--send insert into t1 values (1,2);
543
--echo connection: default
546
select count(*) = 1 from information_schema.processlist
547
where state = "Waiting for release of readlock";
548
--source include/wait_condition.inc
551
--echo connection: flush
554
select count(*) = 1 from information_schema.processlist
555
where state = "Waiting for release of readlock";
556
--source include/wait_condition.inc
563
select count(*) = 1 from t1;
564
--source include/wait_condition.inc
571
# Test that FLUSH TABLES under LOCK TABLES protects write locked tables
572
# from a impending FLUSH TABLES WITH READ LOCK
576
drop table if exists t1;
578
create table t1 (i int);
579
connect (flush,localhost,root,,test,,);
581
--echo connection: default
582
lock tables t1 write;
584
--echo connection: flush
585
--send flush tables with read lock;
587
--echo connection: default
589
select count(*) = 1 from information_schema.processlist
590
where state = "Flushing tables";
591
--source include/wait_condition.inc
594
select count(*) = 1 from information_schema.processlist
595
where state = "Flushing tables";
596
--source include/wait_condition.inc
599
select count(*) = 0 from information_schema.processlist
600
where state = "Flushing tables";
601
--source include/wait_condition.inc
609
# Bug#30331 Table_locks_waited shows inaccurate values
613
drop table if exists t1,t2;
615
create table t1 (a int);
618
let $tlwa= `show status like 'Table_locks_waited'`;
619
connect (waiter,localhost,root,,);
621
--send insert into t1 values(1);
624
select count(*) = 1 from information_schema.processlist
625
where state = "Locked" and info = "insert into t1 values(1)";
626
--source include/wait_condition.inc
627
let $tlwb= `show status like 'Table_locks_waited'`;
635
eval SET @tlwa= SUBSTRING_INDEX('$tlwa', ' ', -1);
636
eval SET @tlwb= SUBSTRING_INDEX('$tlwb', ' ', -1);
638
select @tlwa < @tlwb;
640
--echo End of 5.1 tests
642
# Wait till all disconnects are completed
643
--source include/wait_until_count_sessions.inc