Create 'main' test directory and move 't' and 'r' there

1 files changed, 611 insertions, 0 deletions

diff --git a/mysql-test/main/lock_multi.result b/mysql-test/main/lock_multi.result
new file mode 100644
index 00000000000..d4e0edaf1c2
--- /dev/null
+++ b/mysql-test/main/lock_multi.result
@@ -0,0 +1,611 @@
+drop table if exists t1,t2;
+drop DATABASE if exists mysqltest_1;
+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);
+get_lock("mysqltest_lock", 100)
+1
+connection locker;
+update t1 set n = 2 and get_lock('mysqltest_lock', 100);
+connection writer;
+update low_priority t1 set n = 4;
+connection reader;
+select n from t1;
+connection locker2;
+select release_lock("mysqltest_lock");
+release_lock("mysqltest_lock")
+1
+connection locker;
+select release_lock("mysqltest_lock");
+release_lock("mysqltest_lock")
+1
+connection writer;
+connection reader;
+n
+4
+drop table t1;
+connection locker;
+create table t1(n int);
+insert into t1 values (1),(2);
+connection locker2;
+select get_lock("mysqltest_lock", 100);
+get_lock("mysqltest_lock", 100)
+1
+connection locker;
+select n from t1 where get_lock('mysqltest_lock', 100);
+connection writer;
+update low_priority t1 set n = 4;
+connection reader;
+select n from t1;
+n
+1
+2
+connection locker2;
+select release_lock("mysqltest_lock");
+release_lock("mysqltest_lock")
+1
+connection locker;
+n
+1
+2
+select release_lock("mysqltest_lock");
+release_lock("mysqltest_lock")
+1
+connection writer;
+drop table t1;
+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;
+c
+2
+connection locker;
+unlock tables;
+drop table t1;
+drop table t2;
+connection locker;
+create table t1 (a int);
+create table t2 (a int);
+lock table t1 write, t2 write;
+connection reader;
+insert t1 select * from t2;
+connection locker;
+drop table t2;
+unlock tables;
+connection reader;
+ERROR 42S02: Table 'test.t2' doesn't exist
+connection locker;
+drop table t1;
+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;
+insert t1 select * from t2;
+connection locker;
+drop table t2;
+unlock tables;
+connection reader;
+ERROR 42S02: Table 'test.t2' doesn't exist
+connection locker;
+drop table t1;
+End of 4.1 tests
+create table t1(a int);
+lock tables t1 write;
+connection reader;
+show columns from t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+connection locker;
+unlock tables;
+drop table t1;
+connection locker;
+USE mysql;
+LOCK TABLES columns_priv WRITE, db WRITE, host WRITE, user WRITE;
+FLUSH TABLES;
+connection reader;
+USE mysql;
+SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1;
+connection locker;
+OPTIMIZE TABLES columns_priv, db, host, user;
+Table	Op	Msg_type	Msg_text
+mysql.columns_priv	optimize	status	OK
+mysql.db	optimize	status	OK
+mysql.host	optimize	status	OK
+mysql.user	optimize	status	OK
+UNLOCK TABLES;
+connection reader;
+Select_priv
+N
+USE test;
+connection locker;
+use test;
+connection default;
+connection writer;
+CREATE TABLE t1 (c1 int);
+LOCK TABLE t1 WRITE;
+connection locker;
+FLUSH TABLES WITH READ LOCK;
+connection writer;
+CREATE TABLE t2 (c1 int);
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+UNLOCK TABLES;
+connection locker;
+UNLOCK TABLES;
+connection default;
+DROP TABLE t1;
+connection writer;
+CREATE TABLE t1 (c1 int);
+LOCK TABLE t1 WRITE;
+connection locker;
+FLUSH TABLES WITH READ LOCK;
+connection writer;
+CREATE TABLE t2 AS SELECT * FROM t1;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+UNLOCK TABLES;
+connection locker;
+UNLOCK TABLES;
+connection default;
+DROP TABLE t1;
+connect  con1,localhost,root,,;
+connect  con2,localhost,root,,;
+connection con1;
+CREATE DATABASE mysqltest_1;
+FLUSH TABLES WITH READ LOCK;
+connection con2;
+DROP DATABASE mysqltest_1;
+connection con1;
+DROP DATABASE mysqltest_1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+UNLOCK TABLES;
+connection con2;
+connection default;
+disconnect con1;
+disconnect con2;
+DROP DATABASE mysqltest_1;
+ERROR HY000: Can't drop database 'mysqltest_1'; database doesn't exist
+connection locker;
+set sql_mode="";
+create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) engine=innodb;
+set sql_mode=default;
+lock tables t1 write;
+connection writer;
+alter table t1 auto_increment=0;
+connection reader;
+alter table t1 auto_increment=0;
+connection locker;
+unlock tables;
+connection writer;
+connection reader;
+connection locker;
+drop table t1;
+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;
+flush tables with read lock;
+connection con5;
+# global read lock is taken
+connection con3;
+select * from t2 for update;
+connection con5;
+# waiting for release of read lock
+connection con4;
+# would hang and later cause a deadlock
+flush tables t2;
+connection con1;
+# clean up
+unlock tables;
+connection con2;
+unlock tables;
+connection con3;
+a
+connection default;
+disconnect con5;
+disconnect con4;
+disconnect con3;
+disconnect con2;
+disconnect con1;
+drop table t1,t2;
+#
+# Lightweight version:
+# Ensure that the wait for a GRL is done before opening tables.
+#
+connect  con1,localhost,root,,;
+connect  con2,localhost,root,,;
+create table t1 (a int);
+create table t2 like t1;
+#
+# UPDATE
+#
+connection default;
+flush tables with read lock;
+connection con1;
+update t2 set a = 1;
+connection default;
+# statement is waiting for release of read lock
+connection con2;
+flush table t2;
+connection default;
+unlock tables;
+connection con1;
+#
+# LOCK TABLES .. WRITE
+#
+connection default;
+flush tables with read lock;
+connection con1;
+lock tables t2 write;
+connection default;
+# statement is waiting for release of read lock
+connection con2;
+flush table t2;
+connection default;
+unlock tables;
+connection con1;
+unlock tables;
+connection default;
+disconnect con2;
+disconnect con1;
+drop table t1,t2;
+End of 5.0 tests
+create table t1 (i int);
+connection locker;
+lock table t1 read;
+connection writer;
+update t1 set i= 10;
+connection reader;
+select * from t1;
+connection default;
+kill query ID;
+connection reader;
+i
+connection writer;
+ERROR 70100: Query execution was interrupted
+connection locker;
+unlock tables;
+connection default;
+drop table t1;
+disconnect locker;
+disconnect locker2;
+disconnect reader;
+disconnect writer;
+drop table if exists t1;
+create table t1 (i int);
+connect  flush,localhost,root,,test,,;
+connection default;
+lock tables t1 write;
+connection flush;
+flush tables with read lock;;
+connection default;
+alter table t1 add column j int;
+connect  insert,localhost,root,,test,,;
+connection insert;
+insert into t1 values (1,2);;
+connection default;
+unlock tables;
+connection flush;
+select * from t1;
+i	j
+unlock tables;
+connection insert;
+connection default;
+select * from t1;
+i	j
+1	2
+drop table t1;
+disconnect flush;
+disconnect insert;
+drop table if exists t1;
+create table t1 (i int);
+connect  flush,localhost,root,,test,,;
+connection default;
+lock tables t1 write;
+connection flush;
+flush tables with read lock;;
+connection default;
+flush tables;
+unlock tables;
+connection flush;
+connection default;
+disconnect flush;
+drop table t1;
+drop table if exists t1,t2;
+create table t1 (a int);
+flush status;
+lock tables t1 read;
+connect  waiter,localhost,root,,;
+connection waiter;
+insert into t1 values(1);
+connection default;
+unlock tables;
+connection waiter;
+connection default;
+drop table t1;
+disconnect waiter;
+select @tlwa < @tlwb;
+@tlwa < @tlwb
+1
+End of 5.1 tests
+drop table if exists t1;
+create table t1 (i int);
+connect  flush,localhost,root,,test,,;
+connection default;
+lock tables t1 write;
+connection flush;
+flush tables with read lock;;
+connection default;
+flush tables;
+drop table t1;
+connection flush;
+connection default;
+disconnect flush;
+#
+# Test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock".
+#
+drop table if exists t1;
+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;
+# The below ALTER TABLE statement should wait till transaction
+# in connection 'default' is complete and then succeed.
+# It should not deadlock or fail with ER_LOCK_DEADLOCK error.
+# Sending:
+alter table t1 add column c4 int;;
+connection default;
+# Wait until the above ALTER TABLE gets blocked because this
+# connection holds SW metadata lock on table to be altered.
+# The below statement should succeed. It should not
+# deadlock or end with ER_LOCK_DEADLOCK error.
+update t1 set c3=c3+1 where c2=4;
+# Unblock ALTER TABLE by committing transaction.
+commit;
+connection con46272;
+# Reaping ALTER TABLE.
+connection default;
+disconnect con46272;
+drop table t1;
+#
+# Bug#47249 assert in MDL_global_lock::is_lock_type_compatible
+#
+DROP TABLE IF EXISTS t1;
+DROP VIEW  IF EXISTS v1;
+#
+# Test 1: LOCK TABLES v1 WRITE, t1 READ;
+#
+# Thanks to the fact that we no longer allow DDL on tables
+# which are locked for write implicitly, the exact scenario
+# in which assert was failing is no longer repeatable.
+CREATE TABLE t1 ( f1 integer );
+CREATE VIEW v1 AS SELECT f1 FROM t1 ;
+LOCK TABLES v1 WRITE, t1 READ;
+FLUSH TABLE t1;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+UNLOCK TABLES;
+DROP TABLE t1;
+DROP VIEW v1;
+#
+# Test 2: LOCK TABLES t1 WRITE, v1 READ;
+#
+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;
+connection default;
+LOCK TABLES t1 WRITE;
+FLUSH TABLE t1;
+DROP TABLE t1;
+DROP VIEW v1;
+#
+# Test for bug #50913 "Deadlock between open_and_lock_tables_derived
+# and MDL". Also see additional coverage in mdl_sync.test.
+# 
+drop table if exists t1;
+drop view if exists v1;
+connect  con50913,localhost,root;
+connection default;
+create table t1 (i int);
+create view v1 as select i from t1;
+begin;
+select * from t1;
+i
+connection con50913;
+# Sending:
+alter table t1 add column j int;
+connection default;
+# Wait until ALTER TABLE gets blocked.
+# The below statement should try to acquire SW lock on 't1'
+# and therefore should get ER_LOCK_DEADLOCK error. Before
+# bug fix it acquired SR lock and hung on thr_lock.c lock.
+delete a from t1 as a where i = 1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Unblock ALTER TABLE.
+commit;
+connection con50913;
+# Reaping ALTER TABLE;
+connection default;
+begin;
+select * from v1;
+i
+connection con50913;
+# Sending:
+alter table t1 drop column j;
+connection default;
+# Wait until ALTER TABLE gets blocked.
+# The below statement should try to acquire SW lock on 't1'
+# and therefore should get ER_LOCK_DEADLOCK error. Before
+# bug fix it acquired SR lock and hung on thr_lock.c lock.
+insert into v1 values (1);
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Unblock ALTER TABLE.
+commit;
+connection con50913;
+# Reaping ALTER TABLE;
+connection default;
+disconnect con50913;
+drop view v1;
+drop table t1;
+#
+# Bug#45225 Locking: hang if drop table with no timeout
+#
+# These tests also provide function coverage for the
+# lock_wait_timeout server variable.
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id int);
+connect con2, localhost, root,,;
+SET SESSION lock_wait_timeout= 1;
+#
+# Test 1: acquire exclusive lock
+#
+connection default;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1);
+connection con2;
+DROP TABLE t1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+connection default;
+COMMIT;
+#
+# Test 2: upgrade shared lock
+#
+connection default;
+START TRANSACTION;
+SELECT * FROM t1;
+id
+1
+connection con2;
+ALTER TABLE t1 RENAME TO t2;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+connection default;
+COMMIT;
+#
+# Test 3: acquire shared lock
+#
+connection default;
+LOCK TABLE t1 WRITE;
+connection con2;
+INSERT INTO t1(id) VALUES (2);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+connection default;
+UNLOCK TABLES;
+#
+# Test 4: table level locks
+#
+connection default;
+LOCK TABLE t1 READ;
+connection con2;
+INSERT INTO t1(id) VALUES(4);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+connection default;
+UNLOCK TABLES;
+#
+# Test 5: Waiting on Table Definition Cache (TDC)
+#
+connect con3, localhost, root;
+connection default;
+LOCK TABLE t1 READ;
+connection con3;
+# Sending:
+FLUSH TABLES;
+connection con2;
+SELECT * FROM t1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+connection default;
+UNLOCK TABLES;
+connection con3;
+# Reaping: FLUSH TABLES
+#
+# Test 6: Timeouts in I_S queries
+#
+connection default;
+CREATE TABLE t2 (id INT);
+LOCK TABLE t2 WRITE;
+connection con3;
+# Sending:
+DROP TABLE t1, t2;
+connection con2;
+SELECT table_name, table_comment FROM information_schema.tables
+WHERE table_schema= 'test' AND table_name= 't1';
+table_name	table_comment
+t1	Lock wait timeout exceeded; try restarting transaction
+Warnings:
+Warning	1205	Lock wait timeout exceeded; try restarting transaction
+connection default;
+UNLOCK TABLES;
+connection con3;
+# Reaping: DROP TABLE t1, t2
+connection default;
+# Cleanup
+disconnect con2;
+disconnect con3;
+#
+# Test for bug #51134 "Crash in MDL_lock::destroy on a concurrent
+#                      DDL workload".
+#
+drop tables if exists t1, t2, t3;
+connect  con1, localhost, root, , ;
+connect  con2, localhost, root, , ;
+connection default;
+create table t3 (i int);
+connection con1;
+# Lock 't3' so upcoming RENAME is blocked.
+lock table t3 read;
+connection con2;
+# Remember ID for this connection.
+# Start statement which will try to acquire two instances
+# of X metadata lock on the same object.
+# Sending:
+rename tables t1 to t2, t2 to t3;;
+connection default;
+# Wait until RENAME TABLE is blocked on table 't3'.
+# Kill RENAME TABLE.
+kill query ID;
+connection con2;
+# RENAME TABLE should be aborted but should not crash.
+ERROR 70100: Query execution was interrupted
+connection con1;
+unlock tables;
+connection default;
+disconnect con1;
+disconnect con2;
+drop table t3;
+# 
+# Test for the bug where upgradable metadata locks was acquired
+# even if the table to altered was temporary.
+# Bug found while working on the related bug #51240.
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id INT);
+LOCK TABLE t1 WRITE;
+connect  con1, localhost, root;
+CREATE TEMPORARY TABLE t1 (id INT);
+ALTER TABLE t1 ADD COLUMN j INT;
+connection default;
+disconnect con1;
+UNLOCK TABLES;
+DROP TABLE t1;