diff options
Diffstat (limited to 'mysql-test/main/lock_multi.test')
-rw-r--r-- | mysql-test/main/lock_multi.test | 1085 |
1 files changed, 1085 insertions, 0 deletions
diff --git a/mysql-test/main/lock_multi.test b/mysql-test/main/lock_multi.test new file mode 100644 index 00000000..a9a9341b --- /dev/null +++ b/mysql-test/main/lock_multi.test @@ -0,0 +1,1085 @@ +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--disable_warnings +drop table if exists t1,t2; +drop DATABASE if exists mysqltest_1; +--enable_warnings + +# Test to see if select will get the lock ahead of low priority update + +--disable_ps2_protocol +connect (locker,localhost,root,,); +connect (locker2,localhost,root,,); +connect (reader,localhost,root,,); +connect (writer,localhost,root,,); + +connection locker; +create table t1(n int); +insert into t1 values (1); +connection locker2; +select get_lock("mysqltest_lock", 100); +connection locker; +send +update t1 set n = 2 and get_lock('mysqltest_lock', 100); +connection writer; +# Wait till above update gets blocked on a user lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "User lock" and info = "update t1 set n = 2 and get_lock('mysqltest_lock', 100)"; +--source include/wait_condition.inc +send +update low_priority t1 set n = 4; +connection reader; +# Sleep a bit till the update of connection writer is in work and hangs +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update low_priority t1 set n = 4"; +--source include/wait_condition.inc +send +select n from t1; +connection locker2; +# Sleep a bit till the select of connection reader is in work and hangs +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "select n from t1"; +--source include/wait_condition.inc +select release_lock("mysqltest_lock"); +connection locker; +reap; +select release_lock("mysqltest_lock"); +connection writer; +reap; +connection reader; +reap; +drop table t1; + +connection locker; +create table t1(n int); +insert into t1 values (1),(2); +connection locker2; +select get_lock("mysqltest_lock", 100); +connection locker; +send +select n from t1 where get_lock('mysqltest_lock', 100); +connection writer; +# Wait till above select gets blocked on a user lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "User lock" and info = "select n from t1 where get_lock('mysqltest_lock', 100)"; +--source include/wait_condition.inc +send +update low_priority t1 set n = 4; +connection reader; +# Sleep a bit till the update of connection writer is in work and hangs +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update low_priority t1 set n = 4"; +--source include/wait_condition.inc +select n from t1; +connection locker2; +select release_lock("mysqltest_lock"); +connection locker; +reap; +select release_lock("mysqltest_lock"); +connection writer; +reap; +drop table t1; +--enable_ps2_protocol + +# +# Test problem when using locks with multi-updates +# It should not block when multi-update is reading on a read-locked table +# + +connection locker; +create table t1 (a int, b int); +create table t2 (c int, d int); +insert into t1 values(1,1); +insert into t1 values(2,2); +insert into t2 values(1,2); +lock table t1 read; +connection writer; +update t1,t2 set c=a where b=d; +connection reader; +select c from t2; +connection locker; +unlock tables; +drop table t1; +drop table t2; + +# +# Test problem when using locks on many tables and dropping a table that +# is to-be-locked by another thread +# +# +connection locker; +create table t1 (a int); +create table t2 (a int); +lock table t1 write, t2 write; +connection reader; +send +insert t1 select * from t2; +connection locker; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "insert t1 select * from t2"; +--source include/wait_condition.inc +drop table t2; +unlock tables; +connection reader; +--error ER_NO_SUCH_TABLE +reap; +connection locker; +drop table t1; + +# +# Same test as above, but with the dropped table locked twice +# + +connection locker; +create table t1 (a int); +create table t2 (a int); +lock table t1 write, t2 write, t1 as t1_2 write, t2 as t2_2 write; +connection reader; +send +insert t1 select * from t2; +connection locker; +# Sleep a bit till the insert of connection reader is in work and hangs +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "insert t1 select * from t2"; +--source include/wait_condition.inc +drop table t2; +unlock tables; +connection reader; +--error ER_NO_SUCH_TABLE +reap; +connection locker; +drop table t1; + + +--echo End of 4.1 tests + +# +# Bug#9998 MySQL client hangs on USE "database" +# +create table t1(a int); +lock tables t1 write; +connection reader; +show columns from t1; +connection locker; +unlock tables; +drop table t1; + +# +# Bug#16986 Deadlock condition with MyISAM tables +# + +# Need a matching user in mysql.user for multi-table select +--source include/add_anonymous_users.inc + +connection locker; +USE mysql; +LOCK TABLES columns_priv WRITE, db WRITE, user WRITE; +FLUSH TABLES; +# +connection reader; +USE mysql; +# Note: This must be a multi-table select, otherwise the deadlock will not occur +send +SELECT global_priv.host FROM global_priv, db WHERE global_priv.user = db.user LIMIT 1; +# +connection locker; +# Sleep a bit till the select of connection reader is in work and hangs +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND info = + "SELECT global_priv.host FROM global_priv, db WHERE global_priv.user = db.user LIMIT 1"; +--source include/wait_condition.inc +# Make test case independent from earlier grants. +--replace_result "Table is already up to date" "OK" +OPTIMIZE TABLES columns_priv, db, global_priv; +UNLOCK TABLES; +# +connection reader; +reap; +USE test; +# +connection locker; +use test; +# +connection default; +# +# Test if CREATE TABLE with LOCK TABLE deadlocks. +# +connection writer; +CREATE TABLE t1 (c1 int); +LOCK TABLE t1 WRITE; +# +# This waits until t1 is unlocked. +connection locker; +send +FLUSH TABLES WITH READ LOCK; +# +connection writer; +# Sleep a bit till the flush of connection locker is in work and hangs +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "FLUSH TABLES WITH READ LOCK"; +--source include/wait_condition.inc +# This must not block. +--error ER_TABLE_NOT_LOCKED +CREATE TABLE t2 (c1 int); +UNLOCK TABLES; +# +# This awakes now. +connection locker; +reap; +UNLOCK TABLES; +# +connection default; +DROP TABLE t1; +# +# Test if CREATE TABLE SELECT with LOCK TABLE deadlocks. +# +connection writer; +CREATE TABLE t1 (c1 int); +LOCK TABLE t1 WRITE; +# +# This waits until t1 is unlocked. +connection locker; +send +FLUSH TABLES WITH READ LOCK; +# +# This must not block. +connection writer; +# Sleep a bit till the flush of connection locker is in work and hangs +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "FLUSH TABLES WITH READ LOCK"; +--source include/wait_condition.inc +--error ER_TABLE_NOT_LOCKED +CREATE TABLE t2 AS SELECT * FROM t1; +UNLOCK TABLES; +# +# This awakes now. +connection locker; +reap; +UNLOCK TABLES; +# +connection default; +DROP TABLE t1; + +--source include/delete_anonymous_users.inc + +# +# Bug#19815 CREATE/RENAME/DROP DATABASE can deadlock on a global read lock +# +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +# +connection con1; +CREATE DATABASE mysqltest_1; +FLUSH TABLES WITH READ LOCK; +# +# With bug in place: acquire LOCK_mysql_create_table and +# wait in wait_if_global_read_lock(). +connection con2; +send +DROP DATABASE mysqltest_1; +# +# With bug in place: try to acquire LOCK_mysql_create_table... +# When fixed: Reject dropping db because of the read lock. +connection con1; +# Wait a bit so that the session con2 is in state +# "Waiting for backup lock" +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" + and info = "DROP DATABASE mysqltest_1"; +--source include/wait_condition.inc +--error ER_CANT_UPDATE_WITH_READLOCK +DROP DATABASE mysqltest_1; +UNLOCK TABLES; +# +connection con2; +reap; +# +connection default; +disconnect con1; +disconnect con2; +# This must have been dropped by connection 2 already, +# which waited until the global read lock was released. +--error ER_DB_DROP_EXISTS +DROP DATABASE mysqltest_1; + +# +# Bug#17264 MySQL Server freeze +# +connection locker; +# Disable warnings to allow test to run also without InnoDB +set sql_mode=""; +--disable_warnings +create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) engine=innodb; +--enable_warnings +set sql_mode=default; +lock tables t1 write; +connection writer; +send +alter table t1 auto_increment=0; +connection reader; +# Wait till connection writer is blocked +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 auto_increment=0"; +--source include/wait_condition.inc +send +alter table t1 auto_increment=0; +connection locker; +# Wait till connection reader is blocked +let $wait_condition= + select count(*) = 2 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 auto_increment=0"; +--source include/wait_condition.inc +unlock tables; +connection writer; +reap; +connection reader; +reap; +connection locker; +drop table t1; + +# +# Bug#43230: SELECT ... FOR UPDATE can hang with FLUSH TABLES WITH READ LOCK indefinitely +# + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connect (con3,localhost,root,,); +connect (con4,localhost,root,,); +connect (con5,localhost,root,,); + +create table t1 (a int); +create table t2 like t1; + +connection con1; +lock tables t1 write; +connection con2; +send flush tables with read lock; +connection con5; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "flush tables with read lock"; +--source include/wait_condition.inc +--echo # global read lock is taken +connection con3; +send select * from t2 for update; +connection con5; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "select * from t2 for update"; +--source include/wait_condition.inc +--echo # waiting for release of read lock +connection con4; +--echo # would hang and later cause a deadlock +--send flush tables t2 +connection con1; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "flush tables t2"; +--source include/wait_condition.inc +--echo # clean up +unlock tables; +connection con2; +--reap +unlock tables; +connection con3; +--reap +connection con4; +--reap +connection default; +disconnect con5; +disconnect con4; +disconnect con3; +disconnect con2; +disconnect con1; + +drop table t1,t2; + +--echo # +--echo # Lightweight version: +--echo # Ensure that the wait for a GRL is done before opening tables. +--echo # + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +create table t1 (a int); +create table t2 like t1; + +--echo # +--echo # UPDATE +--echo # + +connection default; +flush tables with read lock; +connection con1; +send update t2 set a = 1; +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "update t2 set a = 1"; +--source include/wait_condition.inc +--echo # statement is waiting for release of read lock +connection con2; +--send flush table t2 +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "flush table t2"; +--source include/wait_condition.inc +unlock tables; +connection con1; +--reap +connection con2; +--reap + +--echo # +--echo # LOCK TABLES .. WRITE +--echo # + +connection default; +flush tables with read lock; +connection con1; +send lock tables t2 write; +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "lock tables t2 write"; +--source include/wait_condition.inc +--echo # statement is waiting for release of read lock +connection con2; +flush table t2; +connection default; +unlock tables; +connection con1; +--reap +unlock tables; + +connection default; +disconnect con2; +disconnect con1; + +drop table t1,t2; + + +--echo End of 5.0 tests + + +# +# Bug#21281 Pending write lock is incorrectly removed when its +# statement being KILLed +# +create table t1 (i int); +connection locker; +lock table t1 read; +connection writer; +send +update t1 set i= 10; +connection reader; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i= 10"; +--source include/wait_condition.inc +send +select * from t1; +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "select * from t1"; +--source include/wait_condition.inc +let $ID= `select id from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i= 10"`; +--replace_result $ID ID +eval kill query $ID; +connection reader; +--reap +connection writer; +--error ER_QUERY_INTERRUPTED +--reap +connection locker; +unlock tables; +connection default; +drop table t1; + +# Disconnect sessions used in many subtests above +disconnect locker; +disconnect locker2; +disconnect reader; +disconnect writer; + + +# +# Bug#32395 Alter table under a impending global read lock causes a server crash +# + +# +# Test ALTER TABLE under LOCK TABLES and FLUSH TABLES WITH READ LOCK +# + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (i int); +connect (flush,localhost,root,,test,,); +connection default; +lock tables t1 write; +connection flush; +--send flush tables with read lock; +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "flush tables with read lock"; +--source include/wait_condition.inc +alter table t1 add column j int; +connect (insert,localhost,root,,test,,); +connection insert; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "flush tables with read lock"; +--source include/wait_condition.inc +--send insert into t1 values (1,2) +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "insert into t1 values (1,2)"; +--source include/wait_condition.inc +unlock tables; +connection flush; +--reap +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "insert into t1 values (1,2)"; +--source include/wait_condition.inc +select * from t1; +unlock tables; +connection insert; +--reap +connection default; +let $wait_condition= + select count(*) = 1 from t1; +--source include/wait_condition.inc +select * from t1; +drop table t1; +disconnect flush; +disconnect insert; + +# +# Test that FLUSH TABLES under LOCK TABLES protects write locked tables +# from a impending FLUSH TABLES WITH READ LOCK +# + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (i int); +connect (flush,localhost,root,,test,,); +connection default; +lock tables t1 write; +connection flush; +--send flush tables with read lock; +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock"; +--source include/wait_condition.inc +flush tables; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock"; +--source include/wait_condition.inc +unlock tables; +connection flush; +--reap +connection default; +disconnect flush; +drop table t1; + +# +# Bug#30331 Table_locks_waited shows inaccurate values +# + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings +create table t1 (a int); +flush status; +lock tables t1 read; +let $tlwa= `show status like 'Table_locks_waited'`; +connect (waiter,localhost,root,,); +connection waiter; +send insert into t1 values(1); +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "insert into t1 values(1)"; +--source include/wait_condition.inc +let $tlwb= `show status like 'Table_locks_waited'`; +unlock tables; +connection waiter; +--reap +connection default; +drop table t1; +disconnect waiter; +--disable_query_log +eval SET @tlwa= SUBSTRING_INDEX('$tlwa', ' ', -1); +eval SET @tlwb= SUBSTRING_INDEX('$tlwb', ' ', -1); +--enable_query_log +select @tlwa < @tlwb; + +--echo End of 5.1 tests + +# +# Test that DROP TABLES does not wait for a impending FLUSH TABLES +# WITH READ LOCK +# + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (i int); +connect (flush,localhost,root,,test,,); +connection default; +lock tables t1 write; +connection flush; +--send flush tables with read lock; +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock"; +--source include/wait_condition.inc +flush tables; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock"; +--source include/wait_condition.inc +drop table t1; +connection flush; +--reap +connection default; +disconnect flush; + + +--echo # +--echo # Test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock". +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (c1 int primary key, c2 int, c3 int); +insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); +begin; +update t1 set c3=c3+1 where c2=3; + +connect (con46272,localhost,root,,test,,); +connection con46272; +--echo # The below ALTER TABLE statement should wait till transaction +--echo # in connection 'default' is complete and then succeed. +--echo # It should not deadlock or fail with ER_LOCK_DEADLOCK error. +--echo # Sending: +--send alter table t1 add column c4 int; + +connection default; +--echo # Wait until the above ALTER TABLE gets blocked because this +--echo # connection holds SW metadata lock on table to be altered. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add column c4 int"; +--source include/wait_condition.inc + +--echo # The below statement should succeed. It should not +--echo # deadlock or end with ER_LOCK_DEADLOCK error. +update t1 set c3=c3+1 where c2=4; + +--echo # Unblock ALTER TABLE by committing transaction. +commit; + +connection con46272; +--echo # Reaping ALTER TABLE. +--reap + +connection default; +disconnect con46272; +drop table t1; + + +--echo # +--echo # Bug#47249 assert in MDL_global_lock::is_lock_type_compatible +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +--enable_warnings + +--echo # +--echo # Test 1: LOCK TABLES v1 WRITE, t1 READ; +--echo # + +CREATE TABLE t1 ( f1 integer ); +CREATE VIEW v1 AS SELECT f1 FROM t1 ; + +--echo # Connection 2 +connect (con2,localhost,root); +LOCK TABLES v1 WRITE, t1 READ; +FLUSH TABLE t1; +disconnect con2; +--source include/wait_until_disconnected.inc + +--echo # Connection 1 +connection default; +LOCK TABLES t1 WRITE; +FLUSH TABLE t1; # Assertion happened here + +# Cleanup +DROP TABLE t1; +DROP VIEW v1; + +--echo # +--echo # Test 2: LOCK TABLES t1 WRITE, v1 READ; +--echo # + +CREATE TABLE t1 ( f1 integer ); +CREATE VIEW v1 AS SELECT f1 FROM t1 ; + +connect (con2,localhost,root); +LOCK TABLES t1 WRITE, v1 READ; +FLUSH TABLE t1; +disconnect con2; +--source include/wait_until_disconnected.inc + +connection default; +LOCK TABLES t1 WRITE; +FLUSH TABLE t1; # Assertion happened here + +# Cleanup +DROP TABLE t1; +DROP VIEW v1; + + +--echo # +--echo # Test for bug #50913 "Deadlock between open_and_lock_tables_derived +--echo # and MDL". Also see additional coverage in mdl_sync.test. +--echo # +--disable_warnings +drop table if exists t1; +drop view if exists v1; +--enable_warnings +connect (con50913,localhost,root); +connection default; +create table t1 (i int); +create view v1 as select i from t1; +begin; +select * from t1; + +connection con50913; +--echo # Sending: +--send alter table t1 add column j int + +connection default; +--echo # Wait until ALTER TABLE gets blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add column j int"; +--source include/wait_condition.inc +--echo # The below statement should try to acquire SW lock on 't1' +--echo # and therefore should get ER_LOCK_DEADLOCK error. Before +--echo # bug fix it acquired SR lock and hung on thr_lock.c lock. +--error ER_LOCK_DEADLOCK +delete a from t1 as a where i = 1; +--echo # Unblock ALTER TABLE. +commit; + +connection con50913; +--echo # Reaping ALTER TABLE; +--reap + +connection default; +begin; +select * from v1; + +connection con50913; +--echo # Sending: +--send alter table t1 drop column j + +connection default; +--echo # Wait until ALTER TABLE gets blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 drop column j"; +--source include/wait_condition.inc +--echo # The below statement should try to acquire SW lock on 't1' +--echo # and therefore should get ER_LOCK_DEADLOCK error. Before +--echo # bug fix it acquired SR lock and hung on thr_lock.c lock. +--error ER_LOCK_DEADLOCK +insert into v1 values (1); +--echo # Unblock ALTER TABLE. +commit; + +connection con50913; +--echo # Reaping ALTER TABLE; +--reap + +connection default; +disconnect con50913; +drop view v1; +drop table t1; + + +--echo # +--echo # Bug#45225 Locking: hang if drop table with no timeout +--echo # +--echo # These tests also provide function coverage for the +--echo # lock_wait_timeout server variable. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (id int); + +connect(con2, localhost, root,,); +SET SESSION lock_wait_timeout= 1; + +--echo # +--echo # Test 1: acquire exclusive lock +--echo # + +connection default; +START TRANSACTION; +INSERT INTO t1 VALUES (1); + +connection con2; +--error ER_LOCK_WAIT_TIMEOUT +DROP TABLE t1; + +connection default; +COMMIT; + +--echo # +--echo # Test 2: upgrade shared lock +--echo # + +connection default; +START TRANSACTION; +SELECT * FROM t1; + +connection con2; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 RENAME TO t2; + +connection default; +COMMIT; + +--echo # +--echo # Test 3: acquire shared lock +--echo # + +connection default; +LOCK TABLE t1 WRITE; + +connection con2; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1(id) VALUES (2); + +connection default; +UNLOCK TABLES; + +--echo # +--echo # Test 4: table level locks +--echo # + +connection default; +LOCK TABLE t1 READ; + +connection con2; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1(id) VALUES(4); + +connection default; +UNLOCK TABLES; + +--echo # +--echo # Test 5: Waiting on Table Definition Cache (TDC) +--echo # + +connect(con3, localhost, root); + +connection default; +LOCK TABLE t1 READ; + +connection con3; + +# first test that flush tables doesn't block +FLUSH TABLES; + +# Check the FLUSH TABLES t1 waits until table lock is released + +--echo # Sending: +--send FLUSH TABLES t1 + +connection con2; +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND info = "FLUSH TABLES t1"; +--source include/wait_condition.inc +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; + +connection default; +UNLOCK TABLES; + +connection con3; +--echo # Reaping: FLUSH TABLES +--reap + +--echo # +--echo # Test 6: Timeouts in I_S queries +--echo # + +connection default; +CREATE TABLE t2 (id INT); +LOCK TABLE t2 WRITE; + +connection con3; +--echo # Sending: +--send DROP TABLE t1, t2 + +connection con2; +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "DROP TABLE t1, t2"; +--source include/wait_condition.inc +# Note: This query causes two timeouts. +# 1: try_acquire_high_prio_shared_mdl_lock on t1 +# 2: recover_from_failed_open on t1 +SELECT table_name, table_comment FROM information_schema.tables + WHERE table_schema= 'test' AND table_name= 't1'; + +connection default; +UNLOCK TABLES; + +connection con3; +--echo # Reaping: DROP TABLE t1, t2 +--reap + +connection default; +--echo # Cleanup +disconnect con2; +disconnect con3; + + +--echo # +--echo # Test for bug #51134 "Crash in MDL_lock::destroy on a concurrent +--echo # DDL workload". +--echo # +--disable_warnings +drop tables if exists t1, t2, t3; +--enable_warnings +connect (con1, localhost, root, , ); +connect (con2, localhost, root, , ); +connection default; +create table t3 (i int); + +connection con1; +--echo # Lock 't3' so upcoming RENAME is blocked. +lock table t3 read; + +connection con2; +--echo # Remember ID for this connection. +let $ID= `select connection_id()`; +--echo # Start statement which will try to acquire two instances +--echo # of X metadata lock on the same object. +--echo # Sending: +--send rename tables t1 to t2, t2 to t3; + +connection default; +--echo # Wait until RENAME TABLE is blocked on table 't3'. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename tables t1 to t2, t2 to t3"; +--source include/wait_condition.inc +--echo # Kill RENAME TABLE. +--replace_result $ID ID +eval kill query $ID; + +connection con2; +--echo # RENAME TABLE should be aborted but should not crash. +--error ER_QUERY_INTERRUPTED +--reap + +connection con1; +unlock tables; + +connection default; +disconnect con1; +disconnect con2; +drop table t3; + + +--echo # +--echo # Test for the bug where upgradable metadata locks was acquired +--echo # even if the table to altered was temporary. +--echo # Bug found while working on the related bug #51240. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (id INT); +LOCK TABLE t1 WRITE; + +connect (con1, localhost, root); +CREATE TEMPORARY TABLE t1 (id INT); +# This alter should not block and timeout. +ALTER TABLE t1 ADD COLUMN j INT; + +connection default; +disconnect con1; +UNLOCK TABLES; +DROP TABLE t1; + + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc |