Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 3015 insertions, 0 deletions

diff --git a/mysql-test/main/mdl_sync.result b/mysql-test/main/mdl_sync.result
new file mode 100644
index 00000000..0ffe2f74
--- /dev/null
+++ b/mysql-test/main/mdl_sync.result
@@ -0,0 +1,3015 @@
+SET DEBUG_SYNC= 'RESET';
+connect  con1,localhost,root,,test,,;
+connect  con2,localhost,root,,test,,;
+connect  con3,localhost,root,,test,,;
+connection default;
+drop table if exists t1,t2,t3;
+create table t1 (i int);
+create table t2 (i int);
+connection: default
+lock tables t2 read;
+connection con1;
+connection: con1
+set debug_sync='mdl_upgrade_lock SIGNAL parked WAIT_FOR go';
+alter table t1 rename t3;
+connection default;
+connection: default
+set debug_sync= 'now WAIT_FOR parked';
+connection con2;
+connection: con2
+set debug_sync='mdl_acquire_lock_wait SIGNAL go';
+drop table t1,t2;
+connection con1;
+connection: con1
+connection default;
+connection: default
+unlock tables;
+connection con2;
+connection: con2
+ERROR 42S02: Unknown table 'test.t1'
+connection default;
+drop table t3;
+disconnect con1;
+disconnect con2;
+disconnect con3;
+SET DEBUG_SYNC= 'RESET';
+#
+# Basic test coverage for type-of-operation aware metadata locks.
+#
+drop table if exists t1, t2, t3;
+connect mdl_con1,localhost,root,,;
+connect mdl_con2,localhost,root,,;
+connect mdl_con3,localhost,root,,;
+connection default;
+set debug_sync= 'RESET';
+create table t1 (c1 int);
+# 
+# A) First let us check compatibility rules between differend kinds of
+#    type-of-operation aware metadata locks.
+#    Of course, these rules are already covered by the tests scattered
+#    across the test suite. But it still makes sense to have one place
+#    which covers all of them.
+#
+# 1) Acquire S (simple shared) lock on the table (by using HANDLER):
+#
+handler t1 open;
+# 
+connection mdl_con1;
+# Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open t;
+handler t close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+0
+insert into t1 values (1), (1);
+# Check that SU lock is compatible with it. To do this use ALTER TABLE
+# which will fail when constructing .frm and thus obtaining SU metadata
+# lock.
+alter table t1 add index (not_exist);
+ERROR 42000: Key column 'not_exist' doesn't exist in table
+# Check that SNW lock is compatible with it. To do this use ALTER TABLE
+# which will fail during copying the table and thus obtaining SNW metadata
+# lock.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Check that X lock is incompatible with S lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con2;
+# Check that the above RENAME is blocked because of S lock.
+# 
+connection default;
+# Unblock RENAME TABLE.
+handler t1 close;
+# 
+connection mdl_con1;
+# Reaping RENAME TABLE.
+# Restore the original state of the things.
+rename table t2 to t1;
+# 
+connection default;
+handler t1 open;
+# 
+connection mdl_con1;
+# Check that upgrade from SNW to X is blocked by presence of S lock.
+# Sending:
+alter table t1 add column c2 int;;
+# 
+connection mdl_con2;
+# Check that the above ALTER TABLE is blocked because of S lock.
+# 
+connection default;
+# Unblock ALTER TABLE.
+handler t1 close;
+# 
+connection mdl_con1;
+# Reaping ALTER TABLE.
+# Restore the original state of the things.
+alter table t1 drop column c2;
+# 
+connection default;
+#
+# 2) Acquire SH (shared high-priority) lock on the table.
+#    We have to involve DEBUG_SYNC facility for this as usually
+#    such kind of locks are short-lived.
+#
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+2
+insert into t1 values (1);
+# Check that SU lock is compatible with it. To do this use ALTER TABLE
+# which will fail when constructing .frm and thus obtaining SU metadata
+# lock.
+alter table t1 add index (not_exist);
+ERROR 42000: Key column 'not_exist' doesn't exist in table
+# Check that SNW lock is compatible with it. To do this use ALTER TABLE
+# which will fail during copying the table and thus obtaining SNW metadata
+# lock.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Check that SNRW lock is compatible with SH lock.
+lock table t1 write;
+delete from t1 limit 1;
+unlock tables;
+# Check that X lock is incompatible with SH lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con2;
+# Check that the above RENAME is blocked because of SH lock.
+# Unblock RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Reaping SELECT ... FROM I_S.
+table_name	table_type	auto_increment	table_comment
+t1	BASE TABLE	NULL	
+# 
+connection mdl_con1;
+# Reaping RENAME TABLE.
+# Restore the original state of the things.
+rename table t2 to t1;
+# 
+connection default;
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that upgrade from SNW to X is blocked by presence of SH lock.
+# Sending:
+alter table t1 add column c2 int;;
+# 
+connection mdl_con2;
+# Check that the above ALTER TABLE is blocked because of SH lock.
+# Unblock RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Reaping SELECT ... FROM I_S.
+table_name	table_type	auto_increment	table_comment
+t1	BASE TABLE	NULL	
+# 
+connection mdl_con1;
+# Reaping ALTER TABLE.
+# Restore the original state of the things.
+alter table t1 drop column c2;
+# 
+connection default;
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that upgrade from SNRW to X is blocked by presence of S lock.
+lock table t1 write;
+# Sending:
+alter table t1 add column c2 int;;
+# 
+connection mdl_con2;
+# Check that the above upgrade of SNRW to X in ALTER TABLE is blocked
+# because of S lock.
+# Unblock RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Reaping SELECT ... FROM I_S.
+table_name	table_type	auto_increment	table_comment
+t1	BASE TABLE	NULL	
+# 
+connection mdl_con1;
+# Reaping ALTER TABLE.
+# Restore the original state of the things.
+alter table t1 drop column c2;
+unlock tables;
+# 
+connection default;
+#
+#
+# 3) Acquire SR lock on the table.
+#
+#
+begin;
+select count(*) from t1;
+count(*)
+2
+# 
+connection mdl_con1;
+# Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+2
+insert into t1 values (1);
+# Check that SU lock is compatible with it. To do this use ALTER TABLE
+# which will fail when constructing .frm and thus obtaining SU metadata
+# lock.
+alter table t1 add index (not_exist);
+ERROR 42000: Key column 'not_exist' doesn't exist in table
+# Check that SNW lock is compatible with it. To do this use ALTER TABLE
+# which will fail during copying the table and thus obtaining SNW metadata
+# lock.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Check that SNRW lock is not compatible with SR lock.
+# Sending:
+lock table t1 write;;
+# 
+connection default;
+# Check that the above LOCK TABLES is blocked because of SR lock.
+# Unblock LOCK TABLES.
+commit;
+# 
+connection mdl_con1;
+# Reaping LOCK TABLES.
+delete from t1 limit 1;
+unlock tables;
+# 
+connection default;
+begin;
+select count(*) from t1;
+count(*)
+2
+# 
+connection mdl_con1;
+# Check that X lock is incompatible with SR lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con2;
+# Check that the above RENAME is blocked because of SR lock.
+# 
+connection default;
+# Unblock RENAME TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reaping RENAME TABLE.
+# Restore the original state of the things.
+rename table t2 to t1;
+# 
+connection default;
+begin;
+select count(*) from t1;
+count(*)
+2
+# 
+connection mdl_con1;
+# Check that upgrade from SNW to X is blocked by presence of SR lock.
+# Sending:
+alter table t1 add column c2 int;;
+# 
+connection mdl_con2;
+# Check that the above ALTER TABLE is blocked because of SR lock.
+# 
+connection default;
+# Unblock ALTER TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reaping ALTER TABLE.
+# Restore the original state of the things.
+alter table t1 drop column c2;
+#
+# There is no need to check that upgrade from SNRW to X is blocked
+# by presence of SR lock because SNRW is incompatible with SR anyway.
+# 
+# 
+connection default;
+#
+#
+# 4) Acquire SW lock on the table.
+#
+#
+begin;
+insert into t1 values (1);
+# 
+connection mdl_con1;
+# Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+# Disable result log to make test robust against
+# effects of concurrent insert.
+select * from t1;
+insert into t1 values (1);
+# Check that SU lock is compatible with it. To do this use ALTER TABLE
+# which will fail when constructing .frm and thus obtaining SU metadata
+# lock.
+alter table t1 add index (not_exist);
+ERROR 42000: Key column 'not_exist' doesn't exist in table
+# Check that SNW lock is not compatible with SW lock.
+# Again we use ALTER TABLE which fails during copying
+# the table to avoid upgrade of SNW -> X.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection default;
+# Check that the above ALTER TABLE is blocked because of SW lock.
+# Unblock ALTER TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection default;
+begin;
+insert into t1 values (1);
+# 
+connection mdl_con1;
+# Check that SNRW lock is not compatible with SW lock.
+# Sending:
+lock table t1 write;;
+# 
+connection default;
+# Check that the above LOCK TABLES is blocked because of SW lock.
+# Unblock LOCK TABLES.
+commit;
+# 
+connection mdl_con1;
+# Reaping LOCK TABLES.
+delete from t1 limit 2;
+unlock tables;
+# 
+connection default;
+begin;
+insert into t1 values (1);
+# 
+connection mdl_con1;
+# Check that X lock is incompatible with SW lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con2;
+# Check that the above RENAME is blocked because of SW lock.
+# 
+connection default;
+# Unblock RENAME TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reaping RENAME TABLE.
+# Restore the original state of the things.
+rename table t2 to t1;
+#
+# There is no need to check that upgrade from SNW/SNRW to X is
+# blocked by presence of SW lock because SNW/SNRW is incompatible
+# with SW anyway.
+# 
+# 
+connection default;
+#
+#
+# 5) Acquire SU lock on the table. We have to use DEBUG_SYNC for
+#    this, to prevent SU from being immediately upgraded to X.
+#
+set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that S, SH, SR and SW locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+4
+delete from t1 limit 1;
+# Check that SU lock is incompatible with SU lock.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection mdl_con2;
+# Check that the above ALTER is blocked because of SU lock.
+# Unblock ALTERs.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Reaping first ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection mdl_con1;
+# Reaping another ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection default;
+set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that SNRW lock is incompatible with SU lock.
+# Sending:
+lock table t1 write;;
+# 
+connection mdl_con2;
+# Check that the above LOCK TABLES is blocked because of SU lock.
+# Unblock ALTER and thus LOCK TABLES.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection mdl_con1;
+# Reaping LOCK TABLES
+insert into t1 values (1);
+unlock tables;
+# 
+connection default;
+set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that X lock is incompatible with SU lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con2;
+# Check that the above RENAME is blocked because of SU lock.
+# Unblock ALTER and thus RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Now we have ALTER TABLE with SU->SNW and RENAME TABLE with pending
+# X-lock. In this case ALTER TABLE should be chosen as victim.
+# Reaping ALTER TABLE.
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# 
+connection mdl_con1;
+# Reaping RENAME TABLE
+# Revert back to original state of things.
+rename table t2 to t1;
+#
+# There is no need to check that upgrade from SNW/SNRW to X is
+# blocked by presence of another SU lock because SNW/SNRW is
+# incompatible with SU anyway.
+# 
+connection default;
+#
+#
+# 6) Acquire SNW lock on the table. We have to use DEBUG_SYNC for
+#    this, to prevent SNW from being immediately upgraded to X.
+#
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1), lock=shared, algorithm=copy;;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that S, SH and SR locks are compatible with it.
+handler t1 open;
+handler t1 close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+4
+# Check that SW lock is incompatible with SNW lock.
+# Sending:
+delete from t1 limit 2;;
+# 
+connection mdl_con2;
+# Check that the above DELETE is blocked because of SNW lock.
+# Unblock ALTER and thus DELETE.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection mdl_con1;
+# Reaping DELETE.
+# 
+connection default;
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1), lock=shared, algorithm=copy;;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that SU lock is incompatible with SNW lock.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection mdl_con2;
+# Check that the above ALTER is blocked because of SNW lock.
+# Unblock ALTERs.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Reaping first ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection mdl_con1;
+# Reaping another ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Note that we can't easily check SNW vs SNW locks since
+# SNW is only used by ALTER TABLE after upgrading from SU
+# and SU is also incompatible with SNW.
+#
+connection default;
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1), lock=shared, algorithm=copy;;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that SNRW lock is incompatible with SNW lock.
+# Sending:
+lock table t1 write;;
+# 
+connection mdl_con2;
+# Check that the above LOCK TABLES is blocked because of SNW lock.
+# Unblock ALTER and thus LOCK TABLES.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection mdl_con1;
+# Reaping LOCK TABLES
+insert into t1 values (1);
+unlock tables;
+# 
+connection default;
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1), algorithm=copy, lock=shared;;
+# 
+connection mdl_con1;
+set debug_sync= 'now WAIT_FOR locked';
+# Check that X lock is incompatible with SNW lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con2;
+# Check that the above RENAME is blocked because of SNW lock.
+# Unblock ALTER and thus RENAME TABLE.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection default;
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection mdl_con1;
+# Reaping RENAME TABLE
+# Revert back to original state of things.
+rename table t2 to t1;
+#
+# There is no need to check that upgrade from SNW/SNRW to X is
+# blocked by presence of another SNW lock because SNW/SNRW is
+# incompatible with SNW anyway.
+# 
+connection default;
+#
+#
+# 7) Acquire SNRW lock on the table. 
+#
+#
+lock table t1 write;
+# 
+connection mdl_con1;
+# Check that S and SH locks are compatible with it.
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+# Check that SR lock is incompatible with SNRW lock.
+# Sending:
+select count(*) from t1;;
+# 
+connection default;
+# Check that the above SELECT is blocked because of SNRW lock.
+# Unblock SELECT.
+unlock tables;
+# 
+connection mdl_con1;
+# Reaping SELECT.
+count(*)
+3
+# 
+connection default;
+lock table t1 write;
+# 
+connection mdl_con1;
+# Check that SW lock is incompatible with SNRW lock.
+# Sending:
+delete from t1 limit 1;;
+# 
+connection default;
+# Check that the above DELETE is blocked because of SNRW lock.
+# Unblock DELETE.
+unlock tables;
+# 
+connection mdl_con1;
+# Reaping DELETE.
+# 
+connection default;
+lock table t1 write;
+# 
+connection mdl_con1;
+# Check that SU lock is incompatible with SNRW lock.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection default;
+# Check that the above ALTER is blocked because of SNRW lock.
+# Unblock ALTER.
+unlock tables;
+# 
+connection mdl_con1;
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+# Note that we can't easily check SNW vs SNRW locks since
+# SNW is only used by ALTER TABLE after upgrading from SU
+# and SU is also incompatible with SNRW.
+#
+connection default;
+lock table t1 write;
+# 
+connection mdl_con1;
+# Check that SNRW lock is incompatible with SNRW lock.
+# Sending:
+lock table t1 write;;
+# 
+connection default;
+# Check that the above LOCK TABLES is blocked because of SNRW lock.
+# Unblock waiting LOCK TABLES.
+unlock tables;
+# 
+connection mdl_con1;
+# Reaping LOCK TABLES
+insert into t1 values (1);
+unlock tables;
+# 
+connection default;
+lock table t1 write;
+# 
+connection mdl_con1;
+# Check that X lock is incompatible with SNRW lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection default;
+# Check that the above RENAME is blocked because of SNRW lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+connection mdl_con1;
+# Reaping RENAME TABLE
+# Revert back to original state of things.
+rename table t2 to t1;
+#
+# There is no need to check that upgrade from SNW/SNRW to X is
+# blocked by presence of another SNRW lock because SNW/SNRW is
+# incompatible with SNRW anyway.
+# 
+connection default;
+#
+#
+# 8) Now do the same round of tests for X lock. We use additional
+#    table to get long-lived lock of this type.
+#
+create table t2 (c1 int);
+# 
+connection mdl_con2;
+# Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked
+# after acquiring X lock on t1.
+lock tables t2 read;
+# 
+connection default;
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that S lock in incompatible with X lock.
+# Sending:
+handler t1 open;;
+# 
+connection mdl_con2;
+# Check that the above HANDLER statement is blocked because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+connection default;
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping HANDLER.
+handler t1 close;
+# 
+connection mdl_con2;
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+connection default;
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SH lock in incompatible with X lock.
+# Sending:
+select column_name from information_schema.columns where table_schema='test' and table_name='t1';;
+# 
+connection mdl_con2;
+# Check that the above SELECT ... FROM I_S ... statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+connection default;
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping SELECT ... FROM I_S.
+column_name
+c1
+# 
+connection mdl_con2;
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+connection default;
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SR lock in incompatible with X lock.
+# Sending:
+select count(*) from t1;;
+# 
+connection mdl_con2;
+# Check that the above SELECT statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+connection default;
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping SELECT.
+count(*)
+3
+# 
+connection mdl_con2;
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+connection default;
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SW lock in incompatible with X lock.
+# Sending:
+delete from t1 limit 1;;
+# 
+connection mdl_con2;
+# Check that the above DELETE statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+connection default;
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping DELETE.
+# 
+connection mdl_con2;
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+connection default;
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SU lock is incompatible with X lock.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection mdl_con2;
+# Check that the above ALTER statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+connection default;
+# Reaping RENAME TABLE
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping ALTER.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+#
+# Note that we can't easily check SNW vs X locks since
+# SNW is only used by ALTER TABLE after upgrading from SU
+# and SU is also incompatible with X.
+#
+connection mdl_con2;
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+connection default;
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that SNRW lock is incompatible with X lock.
+# Sending:
+lock table t1 write;;
+# 
+connection mdl_con2;
+# Check that the above LOCK TABLE statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+connection default;
+# Reaping RENAME TABLE
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping LOCK TABLE.
+unlock tables;
+# 
+connection mdl_con2;
+# Prepare for blocking RENAME TABLE.
+lock tables t2 read;
+# 
+connection default;
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME has acquired X lock on t1 and is waiting for t2.
+# Check that X lock is incompatible with X lock.
+# Sending:
+rename table t1 to t3;;
+# 
+connection mdl_con2;
+# Check that the above RENAME statement is blocked
+# because of X lock.
+# Unblock RENAME TABLE
+unlock tables;
+# 
+connection default;
+# Reaping RENAME TABLE
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping RENAME.
+rename table t3 to t1;
+#
+# B) Now let us test compatibility in cases when both locks
+#    are pending. I.e. let us test rules for priorities between
+#    different types of metadata locks.
+#
+#    Note: No tests for pending SU lock as this lock requires
+#          even stronger active or pending lock.
+#
+# 
+connection mdl_con2;
+#
+# 1) Check compatibility for pending SNW lock.
+#
+# Acquire SW lock in order to create pending SNW lock later.
+begin;
+insert into t1 values (1);
+# 
+connection default;
+# Add pending SNW lock.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection mdl_con1;
+# Check that ALTER TABLE is waiting with pending SNW lock.
+# Check that S, SH and SR locks are compatible with pending SNW
+handler t1 open t;
+handler t close;
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+select count(*) from t1;
+count(*)
+3
+# Check that SW is incompatible with pending SNW
+# Sending:
+delete from t1 limit 1;;
+# 
+connection mdl_con2;
+# Check that the above DELETE is blocked because of pending SNW lock.
+# Unblock ALTER TABLE.
+commit;
+# 
+connection default;
+# Reaping ALTER.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection mdl_con1;
+# Reaping DELETE.
+#
+# We can't do similar check for SNW, SNRW and X locks because
+# they will also be blocked by active SW lock.
+#
+# 
+connection mdl_con2;
+#
+# 2) Check compatibility for pending SNRW lock.
+#
+# Acquire SR lock in order to create pending SNRW lock.
+begin;
+select count(*) from t1;
+count(*)
+2
+# 
+connection default;
+# Add pending SNRW lock.
+# Sending:
+lock table t1 write;;
+# 
+connection mdl_con1;
+# Check that LOCK TABLE is waiting with pending SNRW lock.
+# Check that S and SH locks are compatible with pending SNRW
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+# Check that SR is incompatible with pending SNRW
+# Sending:
+select count(*) from t1;;
+# 
+connection mdl_con2;
+# Check that the above SELECT is blocked because of pending SNRW lock.
+# Unblock LOCK TABLE.
+commit;
+# 
+connection default;
+# Reaping LOCK TABLE.
+unlock tables;
+# 
+connection mdl_con1;
+# Reaping SELECT.
+count(*)
+2
+# Restore pending SNRW lock.
+# 
+connection mdl_con2;
+begin;
+select count(*) from t1;
+count(*)
+2
+# 
+connection default;
+# Sending:
+lock table t1 write;;
+# 
+connection mdl_con1;
+# Check that LOCK TABLE is waiting with pending SNRW lock.
+# Check that SW is incompatible with pending SNRW
+# Sending:
+insert into t1 values (1);;
+# 
+connection mdl_con2;
+# Check that the above INSERT is blocked because of pending SNRW lock.
+# Unblock LOCK TABLE.
+commit;
+# 
+connection default;
+# Reaping LOCK TABLE.
+unlock tables;
+# 
+connection mdl_con1;
+# Reaping INSERT.
+# Restore pending SNRW lock.
+# 
+connection mdl_con2;
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection default;
+# Sending:
+lock table t1 write;;
+# 
+connection mdl_con1;
+# Check that LOCK TABLE is waiting with pending SNRW lock.
+# Check that SNW is compatible with pending SNRW
+# So ALTER TABLE statements are not starved by LOCK TABLEs.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection mdl_con2;
+# Unblock LOCK TABLE.
+commit;
+# 
+connection default;
+# Reaping LOCK TABLE.
+unlock tables;
+#
+# We can't do similar check for SNRW and X locks because
+# they will also be blocked by active SR lock.
+#
+# 
+connection mdl_con2;
+#
+# 3) Check compatibility for pending X lock.
+#
+# Acquire SR lock in order to create pending X lock.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection default;
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SH locks are compatible with pending X
+select column_name from information_schema.columns where
+table_schema='test' and table_name='t1';
+column_name
+c1
+# Check that S is incompatible with pending X
+# Sending:
+handler t1 open;;
+# 
+connection mdl_con2;
+# Check that the above HANDLER OPEN is blocked because of pending X lock.
+# Unblock RENAME TABLE.
+commit;
+# 
+connection default;
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping HANDLER t1 OPEN.
+handler t1 close;
+# Restore pending X lock.
+# 
+connection mdl_con2;
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection default;
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SR is incompatible with pending X
+# Sending:
+select count(*) from t1;;
+# 
+connection mdl_con2;
+# Check that the above SELECT is blocked because of pending X lock.
+# Unblock RENAME TABLE.
+commit;
+# 
+connection default;
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping SELECT.
+count(*)
+3
+# Restore pending X lock.
+# 
+connection mdl_con2;
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection default;
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SW is incompatible with pending X
+# Sending:
+delete from t1 limit 1;;
+# 
+connection mdl_con2;
+# Check that the above DELETE is blocked because of pending X lock.
+# Unblock RENAME TABLE.
+commit;
+# 
+connection default;
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping DELETE.
+# Restore pending X lock.
+# 
+connection mdl_con2;
+begin;
+select count(*) from t1;
+count(*)
+2
+# 
+connection default;
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SNW is incompatible with pending X
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection mdl_con2;
+# Check that the above ALTER TABLE is blocked because of pending X lock.
+# Unblock RENAME TABLE.
+commit;
+# 
+connection default;
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Restore pending X lock.
+# 
+connection mdl_con2;
+handler t1 open;
+# 
+connection default;
+# Add pending X lock.
+# Sending:
+rename table t1 to t2;;
+# 
+connection mdl_con1;
+# Check that RENAME TABLE is waiting with pending X lock.
+# Check that SNRW is incompatible with pending X
+# Sending:
+lock table t1 write;;
+# 
+connection mdl_con3;
+# Check that the above LOCK TABLES is blocked because of pending X lock.
+# 
+connection mdl_con2;
+# Unblock RENAME TABLE.
+handler t1 close;
+# 
+connection default;
+# Reaping RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection mdl_con1;
+# Reaping LOCK TABLES.
+unlock tables;
+# 
+connection default;
+#
+#
+# C) Now let us test how type-of-operation locks are handled in
+#    transactional context. Obviously we are mostly interested
+#    in conflicting types of locks.
+#
+#    Note: No tests for active/pending SU lock since
+#          ALTER TABLE is in its own transaction.
+#
+#
+# 1) Let us check how various locks used within transactional
+#    context interact with active/pending SNW lock.
+#
+#    We start with case when we are acquiring lock on the table
+#    which was not used in the transaction before.
+begin;
+select count(*) from t1;
+count(*)
+2
+# 
+connection mdl_con1;
+# Create an active SNW lock on t2.
+# We have to use DEBUG_SYNC facility as otherwise SNW lock
+# will be immediately released (or upgraded to X lock).
+insert into t2 values (1), (1);
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t2 add primary key (c1), algorithm=copy, lock=shared;;
+# 
+connection default;
+set debug_sync= 'now WAIT_FOR locked';
+# SR lock should be acquired without any waiting.
+select count(*) from t2;
+count(*)
+2
+commit;
+# Now let us check that we will wait in case of SW lock.
+begin;
+select count(*) from t1;
+count(*)
+2
+# Sending:
+insert into t2 values (1);;
+# 
+connection mdl_con2;
+# Check that the above INSERT is blocked.
+# Unblock ALTER TABLE and thus INSERT.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection mdl_con1;
+# Reap ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection default;
+# Reap INSERT.
+commit;
+#
+# Now let us see what happens when we are acquiring lock on the table
+# which is already used in transaction.
+#
+# *) First, case when transaction which has SR lock on the table also
+#    locked in SNW mode acquires yet another SR lock and then tries
+#    to acquire SW lock.
+begin;
+select count(*) from t1;
+count(*)
+2
+# 
+connection mdl_con1;
+# Create an active SNW lock on t1.
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
+# Sending:
+alter table t1 add primary key (c1), algorithm=copy, lock=shared;;
+# 
+connection default;
+set debug_sync= 'now WAIT_FOR locked';
+# We should still be able to get SR lock without waiting.
+select count(*) from t1;
+count(*)
+2
+# Since the above ALTER TABLE is not upgrading SNW lock to X by waiting
+# for SW lock we won't create deadlock.
+# So the below INSERT should not end-up with ER_LOCK_DEADLOCK error.
+# Sending:
+insert into t1 values (1);;
+# 
+connection mdl_con2;
+# Check that the above INSERT is blocked.
+# Unblock ALTER TABLE and thus INSERT.
+set debug_sync= 'now SIGNAL finish';
+# 
+connection mdl_con1;
+# Reap ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection default;
+# Reap INSERT.
+commit;
+#
+# **) Now test in which transaction that has SW lock on the table
+#     against which there is pending SNW lock acquires SR and SW
+#     locks on this table.
+#
+begin;
+insert into t1 values (1);
+# 
+connection mdl_con1;
+# Create pending SNW lock on t1.
+# Sending:
+alter table t1 add primary key (c1);;
+# 
+connection default;
+# Wait until ALTER TABLE starts waiting for SNW lock.
+# We should still be able to get both SW and SR locks without waiting.
+select count(*) from t1;
+count(*)
+4
+delete from t1 limit 1;
+# Unblock ALTER TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reap ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# 
+connection default;
+#
+# 2) Now similar tests for active SNW lock which is being upgraded
+#    to X lock.
+#
+#    Again we start with case when we are acquiring lock on the
+#    table which was not used in the transaction before.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection mdl_con2;
+# Start transaction which will prevent SNW -> X upgrade from
+# completing immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+# 
+connection mdl_con1;
+# Create SNW lock pending upgrade to X on t2.
+# Sending:
+alter table t2 add column c2 int;;
+# 
+connection default;
+# Wait until ALTER TABLE starts waiting X lock.
+# Check that attempt to acquire SR lock on t2 causes waiting.
+# Sending:
+select count(*) from t2;;
+# 
+connection mdl_con2;
+# Check that the above SELECT is blocked.
+# Unblock ALTER TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reap ALTER TABLE.
+# 
+connection default;
+# Reap SELECT.
+count(*)
+3
+commit;
+# Do similar check for SW lock.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection mdl_con2;
+# Start transaction which will prevent SNW -> X upgrade from
+# completing immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+# 
+connection mdl_con1;
+# Create SNW lock pending upgrade to X on t2.
+# Sending:
+alter table t2 drop column c2;;
+# 
+connection default;
+# Wait until ALTER TABLE starts waiting X lock.
+# Check that attempt to acquire SW lock on t2 causes waiting.
+# Sending:
+insert into t2 values (1);;
+# 
+connection mdl_con2;
+# Check that the above INSERT is blocked.
+# Unblock ALTER TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reap ALTER TABLE.
+# 
+connection default;
+# Reap INSERT.
+commit;
+#
+# Test for the case in which we are acquiring lock on the table
+# which is already used in transaction.
+#
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection mdl_con1;
+# Create SNW lock pending upgrade to X.
+# Sending:
+alter table t1 add column c2 int;;
+# 
+connection default;
+# Wait until ALTER TABLE starts waiting X lock.
+# Check that transaction is still able to acquire SR lock.
+select count(*) from t1;
+count(*)
+3
+# Waiting trying to acquire SW lock will cause deadlock and
+# therefore should cause an error.
+delete from t1 limit 1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Unblock ALTER TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reap ALTER TABLE.
+# 
+connection default;
+#
+# 3) Check how various locks used within transactional context
+#    interact with active/pending SNRW lock.
+# 
+#    Once again we start with case when we are acquiring lock on
+#    the table which was not used in the transaction before.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection mdl_con1;
+lock table t2 write;
+# 
+connection default;
+# Attempt to acquire SR should be blocked. It should
+# not cause errors as it does not creates deadlock.
+# Sending:
+select count(*) from t2;;
+# 
+connection mdl_con1;
+# Check that the above SELECT is blocked 
+# Unblock SELECT.
+unlock tables;
+# 
+connection default;
+# Reap SELECT.
+count(*)
+4
+commit;
+# Repeat the same test for SW lock.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection mdl_con1;
+lock table t2 write;
+# 
+connection default;
+# Again attempt to acquire SW should be blocked and should
+# not cause any errors.
+# Sending:
+delete from t2 limit 1;;
+# 
+connection mdl_con1;
+# Check that the above DELETE is blocked 
+# Unblock DELETE.
+unlock tables;
+# 
+connection default;
+# Reap DELETE.
+commit;
+#
+# Now coverage for the case in which we are acquiring lock on
+# the table which is already used in transaction and against
+# which there is a pending SNRW lock request.
+#
+# *) Let us start with case when transaction has only a SR lock.
+#
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection mdl_con1;
+# Sending:
+lock table t1 write;;
+# 
+connection default;
+# Wait until LOCK TABLE is blocked creating pending request for X lock.
+# Check that another instance of SR lock is granted without waiting.
+select count(*) from t1;
+count(*)
+3
+# Attempt to wait for SW lock will lead to deadlock, thus
+# the below statement should end with ER_LOCK_DEADLOCK error.
+delete from t1 limit 1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Unblock LOCK TABLES.
+commit;
+# 
+connection mdl_con1;
+# Reap LOCK TABLES.
+unlock tables;
+# 
+connection default;
+#
+# **) Now case when transaction has a SW lock.
+#
+begin;
+delete from t1 limit 1;
+# 
+connection mdl_con1;
+# Sending:
+lock table t1 write;;
+# 
+connection default;
+# Wait until LOCK TABLE is blocked creating pending request for X lock.
+# Check that both SR and SW locks are granted without waiting
+# and errors.
+select count(*) from t1;
+count(*)
+2
+insert into t1 values (1, 1);
+# Unblock LOCK TABLES.
+commit;
+# 
+connection mdl_con1;
+# Reap LOCK TABLES.
+unlock tables;
+# 
+connection default;
+#
+# 4) Check how various locks used within transactional context
+#    interact with active/pending X lock.
+# 
+#    As usual we start with case when we are acquiring lock on
+#    the table which was not used in the transaction before.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection mdl_con2;
+# Start transaction which will prevent X lock from going away
+# immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+# 
+connection mdl_con1;
+# Create pending X lock on t2.
+# Sending:
+rename table t2 to t3;;
+# 
+connection default;
+# Wait until RENAME TABLE starts waiting with pending X lock.
+# Check that attempt to acquire SR lock on t2 causes waiting.
+# Sending:
+select count(*) from t2;;
+# 
+connection mdl_con2;
+# Check that the above SELECT is blocked.
+# Unblock RENAME TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reap RENAME TABLE.
+# 
+connection default;
+# Reap SELECT.
+ERROR 42S02: Table 'test.t2' doesn't exist
+commit;
+rename table t3 to t2;
+# The same test for SW lock.
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection mdl_con2;
+# Start transaction which will prevent X lock from going away
+# immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+# 
+connection mdl_con1;
+# Create pending X lock on t2.
+# Sending:
+rename table t2 to t3;;
+# 
+connection default;
+# Wait until RENAME TABLE starts waiting with pending X lock.
+# Check that attempt to acquire SW lock on t2 causes waiting.
+# Sending:
+delete from t2 limit 1;;
+# 
+connection mdl_con2;
+# Check that the above DELETE is blocked.
+# Unblock RENAME TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reap RENAME TABLE.
+# 
+connection default;
+# Reap DELETE.
+ERROR 42S02: Table 'test.t2' doesn't exist
+commit;
+rename table t3 to t2;
+#
+# Coverage for the case in which we are acquiring lock on
+# the table which is already used in transaction and against
+# which there is a pending X lock request.
+#
+# *) The first case is when transaction has only a SR lock.
+#
+begin;
+select count(*) from t1;
+count(*)
+3
+# 
+connection mdl_con1;
+# Sending:
+rename table t1 to t2;;
+# 
+connection default;
+# Wait until RENAME TABLE is blocked creating pending request for X lock.
+# Check that another instance of SR lock is granted without waiting.
+select count(*) from t1;
+count(*)
+3
+# Attempt to wait for SW lock will lead to deadlock, thus
+# the below statement should end with ER_LOCK_DEADLOCK error.
+delete from t1 limit 1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+# Unblock RENAME TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reap RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection default;
+#
+# **) The second case is when transaction has a SW lock.
+#
+begin;
+delete from t1 limit 1;
+# 
+connection mdl_con1;
+# Sending:
+rename table t1 to t2;;
+# 
+connection default;
+# Wait until RENAME TABLE is blocked creating pending request for X lock.
+# Check that both SR and SW locks are granted without waiting
+# and errors.
+select count(*) from t1;
+count(*)
+2
+insert into t1 values (1, 1);
+# Unblock RENAME TABLE.
+commit;
+# 
+connection mdl_con1;
+# Reap RENAME TABLE.
+ERROR 42S01: Table 't2' already exists
+# 
+connection default;
+# Clean-up.
+disconnect mdl_con1;
+disconnect mdl_con2;
+disconnect mdl_con3;
+set debug_sync= 'RESET';
+drop table t1, t2;
+#
+# Test coverage for basic deadlock detection in metadata
+# locking subsystem.
+#
+drop tables if exists t0, t1, t2, t3, t4, t5;
+set debug_sync= 'RESET';
+connect deadlock_con1,localhost,root,,;
+connect deadlock_con2,localhost,root,,;
+connect deadlock_con3,localhost,root,,;
+connection default;
+create table t1 (i int);
+create table t2 (j int);
+create table t3 (k int);
+create table t4 (k int);
+#
+# Test for the case in which no deadlock occurs.
+#
+#
+connection deadlock_con1;
+begin;
+insert into t1 values (1);
+#
+connection deadlock_con2;
+begin;
+insert into t2 values (1);
+#
+connection default;
+# Send:
+rename table t2 to t0, t3 to t2, t0 to t3;;
+#
+connection deadlock_con1;
+# Wait until the above RENAME TABLE is blocked because it has to wait
+# for 'deadlock_con2' which holds shared metadata lock on 't2'.
+# The below statement should wait for exclusive metadata lock
+# on 't2' to go away and should not produce ER_LOCK_DEADLOCK
+# as no deadlock is possible in this situation.
+# Send:
+select * from t2;;
+#
+connection deadlock_con2;
+# Wait until the above SELECT * FROM t2 is starts waiting
+# for an exclusive metadata lock to go away.
+#
+# Unblock RENAME TABLE by releasing shared metadata lock on t2.
+commit;
+#
+connection default;
+# Reap RENAME TABLE.
+#
+connection deadlock_con1;
+# Reap SELECT.
+k
+#
+connection default;
+#
+# Let us check that in the process of waiting for conflicting lock
+# on table 't2' to go away transaction in connection 'deadlock_con1'
+# has not released metadata lock on table 't1'.
+# Send:
+rename table t1 to t0, t3 to t1, t0 to t3;;
+#
+connection deadlock_con1;
+# Wait until the above RENAME TABLE is blocked because it has to wait
+# for 'deadlock_con1' which should still hold shared metadata lock on
+# table 't1'.
+# Commit transaction to unblock RENAME TABLE.
+commit;
+#
+connection default;
+# Reap RENAME TABLE.
+#
+# Test for case when deadlock occurs and should be detected immediately.
+#
+#
+connection deadlock_con1;
+begin;
+insert into t2 values (2);
+#
+connection default;
+# Send:
+rename table t2 to t0, t1 to t2, t0 to t1;;
+#
+connection deadlock_con1;
+# Wait until the above RENAME TABLE is blocked because it has to wait
+# for 'deadlock_con1' which holds shared metadata lock on 't2'.
+# 
+# The below statement should not wait as doing so will cause deadlock.
+# Instead it should fail and emit ER_LOCK_DEADLOCK statement and 
+# transaction should be rolled back.
+select * from t1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+#
+connection default;
+# Reap RENAME TABLE.
+#
+# Test for the case in which deadlock also occurs but not immediately.
+#
+#
+connection deadlock_con1;
+begin;
+insert into t2 values (1);
+#
+connection default;
+lock table t1 write;
+#
+connection deadlock_con1;
+# The below SELECT statement should wait for metadata lock
+# on table 't1' and should not produce ER_LOCK_DEADLOCK
+# immediately as no deadlock is possible at the moment.
+select * from t1;;
+#
+connection deadlock_con2;
+# Wait until the above SELECT * FROM t1 is starts waiting
+# for an UNRW metadata lock to go away.
+# Send RENAME TABLE statement that will deadlock with the
+# SELECT statement and thus should abort the latter.
+rename table t1 to t0, t2 to t1, t0 to t2;;
+#
+connection default;
+# Wait till above RENAME TABLE is blocked while holding
+# pending X lock on t1.
+# Allow the above RENAME TABLE to acquire lock on t1 and
+# create pending lock on t2 thus creating deadlock.
+unlock tables;
+#
+connection deadlock_con1;
+# Since the latest RENAME TABLE entered in deadlock with SELECT
+# statement the latter should be aborted and emit ER_LOCK_DEADLOCK
+# error and transaction should be rolled back.
+# Reap SELECT * FROM t1.
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+#
+connection deadlock_con2;
+# Reap RENAME TABLE ... .
+#
+connection default;
+drop tables t1, t2, t3, t4;
+#
+# Now, test case which shows that deadlock detection empiric
+# also takes into account requests for metadata lock upgrade.
+#
+create table t1 (i int);
+insert into t1 values (1);
+# Avoid race which occurs when SELECT in 'deadlock_con1' connection
+# accesses table before the above INSERT unlocks the table and thus
+# its result becomes visible to other connections.
+select * from t1;
+i
+1
+#
+connection deadlock_con1;
+begin;
+select * from t1;
+i
+1
+#
+connection default;
+# Send:
+alter table t1 add column j int, rename to t2;;
+#
+connection deadlock_con1;
+# Wait until the above ALTER TABLE ... RENAME acquires exclusive
+# metadata lock on 't2' and starts waiting for connection
+# 'deadlock_con1' which holds shared lock on 't1'.
+# The below statement should not wait as it will cause deadlock.
+# An appropriate error should be reported instead and transaction
+# should be rolled back.
+select * from t2;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+#
+connection default;
+# Reap ALTER TABLE ... RENAME.
+drop table t2;
+#
+# Test that in situation when MDL subsystem detects a deadlock
+# but it turns out that it can be resolved by backing-off locks
+# acquired by one of participating transactions (which is
+# possible when one of transactions consists only of currently
+# executed statement, e.g. in autocommit mode) no error is
+# reported.
+#
+create table t1 (i int);
+create table t2 (j int);
+# Ensure that the below SELECT stops once it has acquired metadata
+# lock on table 't2'.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+select * from t2, t1;
+#
+connection deadlock_con1;
+# Wait till SELECT acquires MDL on 't2' and starts waiting for signal.
+set debug_sync= 'now WAIT_FOR locked';
+# Sending:
+lock tables t1 write, t2 write;
+#
+connection deadlock_con2;
+# Wait until LOCK TABLES acquires SNRW lock on 't1' and is blocked
+# while trying to acquire SNRW lock on 't1'.
+# Resume SELECT execution, this should eventually unblock LOCK TABLES.
+set debug_sync= 'now SIGNAL finish';
+#
+connection deadlock_con1;
+# Reaping LOCK TABLES.
+unlock tables;
+#
+connection default;
+# Reaping SELECT. It succeed and not report ER_LOCK_DEADLOCK error.
+j	i
+drop tables t1, t2;
+#
+# Test coverage for situation in which a race has happened
+# during deadlock detection process which led to unwarranted
+# ER_LOCK_DEADLOCK error.
+#
+create table t1 (i int);
+# Ensure that ALTER waits once it has acquired SNW lock.
+set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL parked1 WAIT_FOR go1';
+# Sending:
+alter table t1 add column j int;
+#
+connection deadlock_con1;
+# Wait till ALTER acquires SNW lock and stops.
+set debug_sync='now WAIT_FOR parked1';
+# Ensure that INSERT is paused once it detects that there is
+# a conflicting metadata lock so it has to wait, but before
+# deadlock detection is run.
+set debug_sync='mdl_acquire_lock_wait SIGNAL parked2 WAIT_FOR go2';
+# Sending:
+insert into t1 values ();
+#
+connection deadlock_con2;
+# Wait till INSERT is paused.
+set debug_sync='now WAIT_FOR parked2';
+# Resume ALTER execution. Eventually it will release its
+# metadata lock and INSERT's request for SW lock will be
+# satisified.
+set debug_sync='now SIGNAL go1';
+#
+connection default;
+# Reaping ALTER TABLE.
+# Add a new request for SNW lock to waiting graph.
+# Sending:
+alter table t1 drop column j;
+#
+connection deadlock_con2;
+# Wait until ALTER is blocked.
+# Resume INSERT so it can start deadlock detection.
+#
+# At this point there is a discrepancy between the fact that INSERT's
+# SW lock is already satisfied, but INSERT's connection is still
+# marked as waiting for it. Looking for a loop in waiters graph
+# without additional checks has detected a deadlock (INSERT waits
+# for SW lock; which is not granted because of pending SNW lock from
+# ALTER; which waits for active SW lock from INSERT). Since requests
+# for SW and SNW locks have same weight ALTER was selected as a victim
+# and ended with ER_LOCK_DEADLOCK error.
+set debug_sync='now SIGNAL go2';
+#
+connection deadlock_con1;
+# Reaping INSERT.
+#
+connection default;
+# Reaping ALTER. It should succeed and not produce ER_LOCK_DEADLOCK.
+drop table t1;
+set debug_sync= 'RESET';
+disconnect deadlock_con1;
+disconnect deadlock_con2;
+disconnect deadlock_con3;
+#
+# Test for a scenario in which FLUSH TABLES <list> WITH READ LOCK
+# used to erroneously release metadata locks.
+# 
+connect con1,localhost,root,,;
+connect con2,localhost,root,,;
+connection default;
+drop tables if exists t1, t2;
+set debug_sync= 'RESET';
+create table t1(i int);
+create table t2(j int);
+connection con2;
+set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go';
+# The below FLUSH TABLES <list> WITH READ LOCK should acquire
+# SNW locks on t1 and t2, open table t1 and block on the debug
+# sync point.
+# Sending:
+flush tables t1, t2 with read lock;
+connection con1;
+# Wait till FLUSH TABLES <list> WITH READ LOCK stops.
+set debug_sync='now WAIT_FOR parked';
+flush tables;
+# Start a statement which will flush all tables and thus
+# invalidate table t1 open by FLUSH TABLES <list> WITH READ LOCK.
+# Sending:
+flush tables t1;
+connection default;
+# Wait till the above FLUSH TABLES blocks.
+# Resume FLUSH TABLES <list> WITH READ LOCK, so it tries to open t2
+# discovers that its t1 is obsolete and tries to reopen all tables.
+# Such reopen should not cause releasing of SNW metadata locks
+# which would result in assertion failures.
+set debug_sync='now SIGNAL go';
+connection con2;
+# Reap FLUSH TABLES <list> WITH READ LOCK.
+unlock tables;
+connection con1;
+# Reap FLUSH TABLES.
+# Clean-up.
+connection default;
+drop tables t1, t2;
+set debug_sync= 'RESET';
+disconnect con1;
+disconnect con2;
+#
+# Test for bug #46748 "Assertion in MDL_context::wait_for_locks()
+# on INSERT + CREATE TRIGGER".
+#
+drop tables if exists t1, t2, t3, t4, t5;
+# Let us simulate scenario in which we open some tables from extended
+# part of prelocking set but then encounter conflicting metadata lock,
+# so have to back-off and wait for it to go away.
+connect  con1root,localhost,root,,test,,;
+connect  con2root,localhost,root,,test,,;
+connection default;
+create table t1 (i int);
+create table t2 (j int);
+create table t3 (k int);
+create table t4 (l int);
+create trigger t1_bi before insert on t1 for each row
+insert into t2 values (new.i);
+create trigger t2_bi before insert on t2 for each row
+insert into t3 values (new.j);
+#
+connection con1root;
+lock tables t4 read;
+#
+connection con2root;
+# Send :
+rename table t3 to t5, t4 to t3;;
+#
+connection default;
+# Wait until the above RENAME TABLE adds pending requests for exclusive
+# metadata lock on its tables and blocks due to 't4' being used by LOCK
+# TABLES.
+# Send :
+insert into t1 values (1);;
+#
+connection con1root;
+# Wait until INSERT statement waits due to encountering pending
+# exclusive metadata lock on 't3'.
+unlock tables;
+#
+connection con2root;
+# Reap RENAME TABLE.
+#
+connection default;
+# Reap INSERT.
+# Clean-up.
+disconnect con1root;
+disconnect con2root;
+drop tables t1, t2, t3, t5;
+#
+# Bug#42546 - Backup: RESTORE fails, thinking it finds an existing table
+#
+DROP TABLE IF EXISTS t1;
+set @save_log_output=@@global.log_output;
+set global log_output=file;
+connect con2, localhost, root,,;
+#
+# Test 1: CREATE TABLE
+#
+connection con2;
+# Start insert on the not-yet existing table
+# Wait after taking the MDL lock
+SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+INSERT INTO t1 VALUES(1,"def");
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Now INSERT has a MDL on the non-existent table t1.
+#
+# Continue the INSERT once CREATE waits for exclusive lock
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish';
+# Try to create that table.
+CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1));
+# Insert fails
+connection con2;
+ERROR 42S02: Table 'test.t1' doesn't exist
+connection default;
+SET DEBUG_SYNC= 'RESET';
+SHOW TABLES;
+Tables_in_test
+t1
+DROP TABLE IF EXISTS t1;
+#
+# Test 2: CREATE TABLE LIKE
+#
+CREATE TABLE t2 (c1 INT, c2 VARCHAR(100), KEY(c1));
+connection con2;
+# Start insert on the not-yet existing table
+# Wait after taking the MDL
+SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+INSERT INTO t1 VALUES(1,"def");
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Now INSERT has a MDL on the non-existent table t1.
+#
+# Continue the INSERT once CREATE waits for exclusive lock
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish';
+# Try to create that table.
+CREATE TABLE t1 LIKE t2;
+# Insert fails
+connection con2;
+ERROR 42S02: Table 'test.t1' doesn't exist
+connection default;
+SET DEBUG_SYNC= 'RESET';
+SHOW TABLES;
+Tables_in_test
+t1
+t2
+DROP TABLE t2;
+disconnect con2;
+DROP TABLE IF EXISTS t1;
+set global log_output=@save_log_output;
+#
+# Bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY
+#             FOR UPDATE"
+#
+drop tables if exists t1, t2;
+connect  con46044, localhost, root,,;
+connect  con46044_2, localhost, root,,;
+connect  con46044_3, localhost, root,,;
+connection default;
+create table t1 (i int);
+insert into t1 values(1);
+# Let us check that we won't deadlock if during filling
+# of I_S table we encounter conflicting metadata lock
+# which owner is in its turn waiting for our connection.
+lock tables t1 read;
+connection con46044_2;
+# Sending:
+update t1 set i = 2;
+connection con46044;
+# Waiting until UPDATE t1 SET ... is blocked.
+# Sending:
+create table t2 select * from t1;;
+connection default;
+# Waiting until CREATE TABLE ... SELECT ... is blocked.
+# First let us check that SHOW FIELDS/DESCRIBE doesn't
+# gets blocked and emits and error.
+show fields from t2;
+ERROR HY000: Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
+# Now test for I_S query which reads only .FRMs.
+#
+# Query below should only emit a warning.
+select column_name from information_schema.columns
+where table_schema='test' and table_name='t2';
+column_name
+Warnings:
+Warning	1684	Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
+# Finally, test for I_S query which does full-blown table open.
+#
+# Query below should not be blocked. Warning message should be
+# stored in the 'table_comment' column.
+select table_name, table_type, auto_increment, table_comment
+from information_schema.tables where table_schema='test' and table_name='t2';
+table_name	table_type	auto_increment	table_comment
+t2	BASE TABLE	NULL	Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
+Warnings:
+Warning	1684	Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
+connection default;
+unlock tables;
+connection con46044;
+# Reaping CREATE TABLE ... SELECT ... .
+drop table t2;
+connection con46044_2;
+# Reaping UPDATE t1 statement
+#
+# Let us also check that queries to I_S wait for conflicting metadata
+# locks to go away instead of skipping table with a warning in cases
+# when deadlock is not possible. This is a nice thing from compatibility
+# and ease of use points of view.
+#
+# We check same three queries to I_S in this new situation.
+connection con46044_2;
+lock tables t1 read;
+connection con46044_3;
+# Sending:
+update t1 set i = 3;
+connection con46044;
+# Waiting until UPDATE t1 SET ... is blocked.
+# Sending:
+create table t2 select * from t1;;
+connection default;
+# Waiting until CREATE TABLE ... SELECT ... is blocked.
+# Let us check that SHOW FIELDS/DESCRIBE gets blocked.
+# Sending:
+show fields from t2;;
+connection con46044_2;
+# Wait until SHOW FIELDS gets blocked.
+unlock tables;
+connection con46044;
+# Reaping CREATE TABLE ... SELECT ... .
+connection default;
+# Reaping SHOW FIELDS ...
+Field	Type	Null	Key	Default	Extra
+i	int(11)	YES		NULL	
+drop table t2;
+connection con46044_3;
+# Reaping UPDATE t1 statement
+connection con46044_2;
+lock tables t1 read;
+connection con46044_3;
+# Sending:
+update t1 set i = 4;
+connection con46044;
+# Waiting until UPDATE t1 SET ... is blocked.
+# Sending:
+create table t2 select * from t1;;
+connection default;
+# Waiting until CREATE TABLE ... SELECT ... is blocked.
+# Check that I_S query which reads only .FRMs gets blocked.
+# Sending:
+select column_name from information_schema.columns where table_schema='test' and table_name='t2';;
+connection con46044_2;
+# Wait until SELECT COLUMN_NAME FROM I_S.COLUMNS  gets blocked.
+unlock tables;
+connection con46044;
+# Reaping CREATE TABLE ... SELECT ... .
+connection default;
+# Reaping SELECT COLUMN_NAME FROM I_S.COLUMNS
+column_name
+i
+drop table t2;
+connection con46044_3;
+# Reaping UPDATE t1 statement
+connection con46044_2;
+lock tables t1 read;
+connection con46044_3;
+# Sending:
+update t1 set i = 5;
+connection con46044;
+# Waiting until UPDATE t1 SET ... is blocked.
+# Sending:
+create table t2 select * from t1;;
+connection default;
+# Waiting until CREATE TABLE ... SELECT ... is blocked.
+# Finally, check that I_S query which does full-blown table open
+# also gets blocked.
+# Sending:
+select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t2';;
+connection con46044_2;
+# Wait until SELECT ... FROM I_S.TABLES gets blocked.
+unlock tables;
+connection con46044;
+# Reaping CREATE TABLE ... SELECT ... .
+connection default;
+# Reaping SELECT ... FROM I_S.TABLES
+table_name	table_type	auto_increment	table_comment
+t2	BASE TABLE	NULL	
+drop table t2;
+connection con46044_3;
+# Reaping UPDATE t1 statement
+connection default;
+# Clean-up.
+disconnect con46044;
+disconnect con46044_2;
+disconnect con46044_3;
+drop table t1;
+#
+# Test for bug #46273 "MySQL 5.4.4 new MDL: Bug#989 is not fully fixed
+#                      in case of ALTER".
+#
+drop table if exists t1;
+set debug_sync= 'RESET';
+connect  con46273,localhost,root,,test,,;
+connection default;
+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;
+select * from t1 where c2 = 3;
+c1	c2	c3
+3	3	0
+#
+connection con46273;
+set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL alter_table_locked WAIT_FOR alter_go';
+alter table t1 add column e int, rename to t2;;
+#
+connection default;
+set debug_sync='now WAIT_FOR alter_table_locked';
+set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go';
+# The below statement should get ER_LOCK_DEADLOCK error
+# (i.e. it should not allow ALTER to proceed, and then
+# fail due to 't1' changing its name to 't2').
+update t1 set c3=c3+1 where c2 = 3;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+#
+connection con46273;
+# Reap ALTER TABLE.
+#
+connection default;
+disconnect con46273;
+# Clean-up.
+set debug_sync= 'RESET';
+drop table t2;
+#
+# Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK
+#                      and DML".
+#
+drop tables if exists t1;
+connect  con46673, localhost, root,,;
+connection default;
+create table t1 (i int);
+connection con46673;
+begin;
+insert into t1 values (1);
+connection default;
+# Statement below should not get blocked. And if after some
+# changes to code it is there should not be a deadlock between
+# it and transaction from connection 'con46673'.
+flush tables with read lock;
+unlock tables;
+connection con46673;
+delete from t1 where i = 1;
+commit;
+connection default;
+# Clean-up
+disconnect con46673;
+drop table t1;
+#
+# Bug#48210 FLUSH TABLES WITH READ LOCK deadlocks 
+#           against concurrent CREATE PROCEDURE
+#
+connect  con2, localhost, root;
+# Test 1: CREATE PROCEDURE
+connection default;
+# Start CREATE PROCEDURE and open mysql.proc
+SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait';
+CREATE PROCEDURE p1() SELECT 1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
+# Check that FLUSH must wait to get the GRL
+# and let CREATE PROCEDURE continue
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait';
+FLUSH TABLES WITH READ LOCK;
+connection default;
+connection con2;
+UNLOCK TABLES;
+connection default;
+SET DEBUG_SYNC= 'RESET';
+# Test 2: DROP PROCEDURE
+connection default;
+# Start DROP PROCEDURE and open tables
+SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait';
+DROP PROCEDURE p1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
+# Check that FLUSH must wait to get the GRL
+# and let DROP PROCEDURE continue
+SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info;
+LOCK_MODE	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+MDL_BACKUP_DDL	Backup lock		
+MDL_EXCLUSIVE	Stored procedure metadata lock	test	p1
+MDL_INTENTION_EXCLUSIVE	Schema metadata lock	test	
+MDL_SHARED_WRITE	Table metadata lock	mysql	proc
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait';
+FLUSH TABLES WITH READ LOCK;
+connection default;
+# Once FLUSH TABLES WITH READ LOCK starts waiting
+# DROP PROCEDURE will be waked up and will drop
+# procedure. Global read lock will be granted after
+# this statement ends.
+#
+# Reaping DROP PROCEDURE.
+connection con2;
+# Reaping FTWRL.
+UNLOCK TABLES;
+connection default;
+SET DEBUG_SYNC= 'RESET';
+#
+# UPDATE should wait for FTWRL with non transactional table second
+#
+create table t1 (a int) engine=myisam;
+create table t2 (a int) engine=innodb;
+insert into t1 values (1);
+insert into t2 values (1);
+SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait execute 2';
+update t1,t2 set t1.a=2,t2.a=3;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
+SET DEBUG_SYNC= 'now SIGNAL grlwait';
+SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait';
+FLUSH TABLES WITH READ LOCK;
+connection default;
+# Reaping UPDATE
+connection con2;
+UNLOCK TABLES;
+connection default;
+SET DEBUG_SYNC= 'RESET';
+SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait execute 2';
+update t2,t1 set t1.a=2,t2.a=3;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
+SET DEBUG_SYNC= 'now SIGNAL grlwait';
+SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait';
+FLUSH TABLES WITH READ LOCK;
+SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info;
+LOCK_MODE	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+MDL_BACKUP_FTWRL2	Backup lock		
+unlock tables;
+connection default;
+# Reaping UPDATE
+SET DEBUG_SYNC= 'RESET';
+drop table t1,t2;
+disconnect con2;
+#
+# Bug#50786 Assertion `thd->mdl_context.trans_sentinel() == __null' 
+#           failed in open_ltable()
+#
+# Supress warnings written to the log file
+call mtr.add_suppression("Wait on a lock was aborted due to a pending exclusive lock");
+connect  con1,localhost,root;
+connect  con2,localhost,root;
+connect  con3,localhost,root;
+connection default;
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+SET @old_general_log= @@global.general_log;
+SET @@global.general_log= 1;
+SET @old_log_output= @@global.log_output;
+SET @@global.log_output= 'TABLE';
+SET @old_sql_log_off= @@session.sql_log_off;
+SET @@session.sql_log_off= 1;
+# connection: con1
+connection con1;
+HANDLER t1 OPEN;
+# connection: con3
+connection con3;
+SET @@session.sql_log_off= 1;
+# connection: con2
+connection con2;
+SET DEBUG_SYNC= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go';
+# Sending:
+SELECT 1;
+# connection: con3
+connection con3;
+SET DEBUG_SYNC= 'now WAIT_FOR parked';
+# connection: con1
+connection con1;
+# Sending:
+SELECT 1;
+# connection: con3
+connection con3;
+ALTER TABLE t1 ADD COLUMN j INT;
+# connection: default
+connection default;
+SET DEBUG_SYNC= 'now SIGNAL go';
+# connection: con1
+connection con1;
+# Reaping SELECT 1
+1
+1
+HANDLER t1 CLOSE;
+# connection: con2
+connection con2;
+# Reaping SELECT 1
+1
+1
+# connection: default
+connection default;
+DROP TABLE t1, t2;
+SET DEBUG_SYNC= 'RESET';
+disconnect con1;
+disconnect con2;
+disconnect con3;
+SET @@global.general_log= @old_general_log;
+SET @@global.log_output= @old_log_output;
+SET @@session.sql_log_off= @old_sql_log_off;
+#
+# Additional coverage for bug #50913 "Deadlock between
+# open_and_lock_tables_derived and MDL". The main test
+# case is in lock_multi.test
+#
+drop table if exists t1;
+set debug_sync= 'RESET';
+connect  con50913_1,localhost,root;
+connect  con50913_2,localhost,root;
+connection default;
+create table t1 (i int) engine=InnoDB;
+connection con50913_1;
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go';
+# Sending:
+alter table t1 add column j int, ALGORITHM=COPY;
+connection default;
+# Wait until ALTER TABLE gets blocked on a sync point after
+# acquiring thr_lock.c lock.
+set debug_sync= 'now WAIT_FOR parked';
+# The below statement should wait on MDL lock and not deadlock on
+# thr_lock.c lock.
+# Sending:
+truncate table t1;
+connection con50913_2;
+# Wait until TRUNCATE TABLE is blocked on MDL lock.
+# Unblock ALTER TABLE.
+set debug_sync= 'now SIGNAL go';
+connection con50913_1;
+# Reaping ALTER TABLE.
+connection default;
+# Reaping TRUNCATE TABLE.
+disconnect con50913_1;
+disconnect con50913_2;
+set debug_sync= 'RESET';
+drop table t1;
+#
+# Test for bug #50998 "Deadlock in MDL code during test
+#                      rqg_mdl_stability".
+# Also provides coverage for the case when addition of
+# waiting statement adds several loops in the waiters
+# graph and therefore several searches for deadlock
+# should be performed.
+drop table if exists t1;
+set debug_sync= 'RESET';
+connect  con1,localhost,root;
+connect  con2,localhost,root;
+connect  con3,localhost,root;
+connection default;
+create table t1 (i int);
+connection con1;
+begin;
+select * from t1;
+i
+connection con2;
+begin;
+select * from t1;
+i
+connection default;
+# Start ALTER TABLE which will acquire SNW lock and
+# table lock and get blocked on sync point.
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go';
+# Sending:
+alter table t1 add column j int;
+connection con1;
+# Wait until ALTER TABLE gets blocked on a sync point.
+set debug_sync= 'now WAIT_FOR parked';
+# Sending:
+insert into t1 values (1);
+connection con2;
+# Sending:
+insert into t1 values (1);
+connection con3;
+# Wait until both 'con1' and 'con2' are blocked trying to acquire
+# SW lock on the table.
+# Unblock ALTER TABLE. Since it will try to upgrade SNW to X lock
+# deadlock with two loops in waiting graph will occur. Both loops
+# should be found and DML statements in both 'con1' and 'con2'
+# should be aborted with ER_LOCK_DEADLOCK errors.
+set debug_sync= 'now SIGNAL go';
+connection con1;
+# Reaping INSERT. It should end with ER_LOCK_DEADLOCK error and
+# not wait indefinitely (as it happened before the bugfix).
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+commit;
+connection con2;
+# Reaping INSERT.
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+commit;
+connection default;
+# Reap ALTER TABLE.
+disconnect con1;
+disconnect con2;
+disconnect con3;
+connection default;
+set debug_sync= 'RESET';
+drop table t1;
+#
+# Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
+#
+# Ensure that a acquired lock is not given up due to a conflict.
+#
+connect  con1,localhost,root,,test,,;
+connect  con2,localhost,root,,test,,;
+connect  con3,localhost,root,,test,,;
+connection default;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3);
+connection con1;
+LOCK TABLES t1 WRITE;
+SET debug_sync='upgrade_lock_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate';
+TRUNCATE TABLE t1;
+connection default;
+SET debug_sync='now WAIT_FOR parked_truncate';
+connection con2;
+SET debug_sync='after_open_table_ignore_flush SIGNAL parked_show WAIT_FOR go_show';
+SHOW FIELDS FROM t1;
+connection default;
+SET debug_sync='now WAIT_FOR parked_show';
+connection con3;
+SET debug_sync='after_flush_unlock SIGNAL parked_flush WAIT_FOR go_flush';
+FLUSH TABLES t1;
+connection default;
+SET debug_sync='now WAIT_FOR parked_flush';
+SET debug_sync='now SIGNAL go_truncate';
+# Ensure that truncate waits for a exclusive lock
+SET debug_sync= 'now SIGNAL go_show';
+connection con1;
+# Reaping...
+UNLOCK TABLES;
+connection con2;
+# Reaping...
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+connection default;
+SET debug_sync= 'now SIGNAL go_flush';
+connection con3;
+# Reaping...
+disconnect con1;
+disconnect con2;
+disconnect con3;
+connection default;
+SET debug_sync= 'RESET';
+DROP TABLE t1;
+#
+# Bug#52856 concurrent show columns or show full columns causes a crash!!!
+#
+CREATE TABLE t1(a CHAR(255));
+connect con1, localhost, root;
+SET DEBUG_SYNC= "get_schema_column SIGNAL waiting WAIT_FOR completed";
+SHOW FULL COLUMNS FROM t1;
+connection default;
+SET DEBUG_SYNC= "now WAIT_FOR waiting";
+SHOW FULL COLUMNS FROM t1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+a	char(255)	latin1_swedish_ci	YES		NULL		#	
+SET DEBUG_SYNC= "now SIGNAL completed";
+connection con1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+a	char(255)	latin1_swedish_ci	YES		NULL		#	
+connection default;
+DROP TABLE t1;
+disconnect con1;
+#
+# Tests for schema-scope locks
+#
+DROP DATABASE IF EXISTS db1;
+DROP DATABASE IF EXISTS db2;
+connect  con2, localhost, root;
+connect  con3, localhost, root;
+# Test 1: 
+# CREATE DATABASE blocks database DDL on the same database, but
+# not database DDL on different databases. Tests X vs X lock.
+#
+connection default;
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+CREATE DATABASE db1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+CREATE DATABASE db1;
+connection con3;
+CREATE DATABASE db2;
+ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
+DROP DATABASE db2;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: CREATE DATABASE db1
+connection con2;
+# Reaping: CREATE DATABASE db1
+ERROR HY000: Can't create database 'db1'; database exists
+# Test 2:
+# ALTER DATABASE blocks database DDL on the same database, but
+# not database DDL on different databases. Tests X vs X lock.
+#
+connection default;
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
+connection con3;
+CREATE DATABASE db2;
+ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
+DROP DATABASE db2;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
+connection con2;
+# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
+connection default;
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+DROP DATABASE db1;
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
+connection con2;
+# Reaping: DROP DATABASE db1
+CREATE DATABASE db1;
+# Test 3:
+# Two ALTER..UPGRADE of the same database are mutually exclusive, but
+# two ALTER..UPGRADE of different databases are not. Tests X vs X lock.
+#
+connection default;
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
+connection con3;
+ALTER DATABASE `#mysql50#a-b-c-d` UPGRADE DATA DIRECTORY NAME;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME
+connection con2;
+# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME
+ERROR 42000: Unknown database '#mysql50#a-b-c'
+DROP DATABASE `a-b-c`;
+DROP DATABASE `a-b-c-d`;
+# Test 4:
+# DROP DATABASE blocks database DDL on the same database, but
+# not database DDL on different databases. Tests X vs X lock.
+#
+connection default;
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+DROP DATABASE db1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+DROP DATABASE db1;
+connection con3;
+CREATE DATABASE db2;
+ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
+DROP DATABASE db2;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: DROP DATABASE db1
+connection con2;
+# Reaping: DROP DATABASE db1
+ERROR HY000: Can't drop database 'db1'; database doesn't exist
+connection default;
+CREATE DATABASE db1;
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+DROP DATABASE db1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: DROP DATABASE db1
+connection con2;
+# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
+Got one of the listed errors
+# Test 5:
+# Locked database name prevents CREATE of tables in that database.
+# Tests X vs IX lock.
+#
+connection default;
+CREATE DATABASE db1;
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+DROP DATABASE db1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+CREATE TABLE db1.t1 (a INT);
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: DROP DATABASE db1
+connection con2;
+# Reaping: CREATE TABLE db1.t1 (a INT)
+ERROR 42000: Unknown database 'db1'
+# Test 6:
+# Locked database name prevents RENAME of tables to/from that database.
+# Tests X vs IX lock.
+#
+connection default;
+CREATE DATABASE db1;
+CREATE TABLE db1.t1 (a INT);
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+DROP DATABASE db1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+RENAME TABLE db1.t1 TO test.t1;
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: DROP DATABASE db1
+connection con2;
+# Reaping: RENAME TABLE db1.t1 TO test.t1
+ERROR 42S02: Table 'db1.t1' doesn't exist
+connection default;
+CREATE DATABASE db1;
+CREATE TABLE test.t2 (a INT);
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+DROP DATABASE db1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+RENAME TABLE test.t2 TO db1.t2;
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: DROP DATABASE db1
+connection con2;
+# Reaping: RENAME TABLE test.t2 TO db1.t2
+Got one of the listed errors
+DROP TABLE test.t2;
+# Test 7:
+# Locked database name prevents DROP of tables in that database.
+# Tests X vs IX lock.
+#
+connection default;
+CREATE DATABASE db1;
+CREATE TABLE db1.t1 (a INT);
+SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
+# Sending:
+DROP DATABASE db1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# Sending:
+DROP TABLE db1.t1;
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL blocked';
+connection default;
+# Reaping: DROP DATABASE db1
+connection con2;
+# Reaping: DROP TABLE db1.t1
+ERROR 42S02: Unknown table 'db1.t1'
+connection default;
+disconnect con2;
+disconnect con3;
+SET DEBUG_SYNC= 'RESET';
+#
+# End of tests for schema-scope locks
+#
+#
+# Tests of granted global S lock (FLUSH TABLE WITH READ LOCK)
+#
+CREATE DATABASE db1;
+CREATE TABLE db1.t1(a INT);
+connect con2, localhost, root;
+connect con3, localhost, root;
+connection default;
+FLUSH TABLE WITH READ LOCK;
+connection con2;
+CREATE TABLE db1.t2(a INT);
+connection default;
+UNLOCK TABLES;
+connection con2;
+# Reaping CREATE TABLE db1.t2(a INT)
+connection default;
+FLUSH TABLE WITH READ LOCK;
+connection con2;
+ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
+connection default;
+UNLOCK TABLES;
+connection con2;
+# Reaping ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
+connection default;
+FLUSH TABLE WITH READ LOCK;
+connection con2;
+FLUSH TABLE WITH READ LOCK;
+UNLOCK TABLES;
+connection default;
+UNLOCK TABLES;
+DROP DATABASE db1;
+disconnect con2;
+disconnect con3;
+#
+# Bug#56292 Deadlock with ALTER TABLE and MERGE tables
+#
+DROP TABLE IF EXISTS t1, t2, m1;
+CREATE TABLE t1(a INT) engine=MyISAM;
+CREATE TABLE t2(a INT) engine=MyISAM;
+CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (3), (4);
+connect con1, localhost, root;
+connect con2, localhost, root;
+connect con3, localhost, root;
+connection con1;
+# We need EXECUTE 2 since ALTER TABLE does SU => SNW => X and we want
+# to stop at the second upgrade.
+SET DEBUG_SYNC= 'mdl_upgrade_lock SIGNAL upgrade WAIT_FOR continue EXECUTE 2';
+# Sending:
+ALTER TABLE m1 engine=MERGE UNION=(t2, t1);
+connection con2;
+# Waiting for ALTER TABLE to try lock upgrade
+SET DEBUG_SYNC= 'now WAIT_FOR upgrade';
+SET DEBUG_SYNC= 'now SIGNAL continue';
+SET DEBUG_SYNC= 'now WAIT_FOR upgrade';
+# Sending:
+DELETE FROM t2 WHERE a = 3;
+connection con3;
+# Check that DELETE is waiting on a metadata lock and not a table lock.
+# Now that DELETE blocks on a metadata lock, we should be able to do
+# SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table
+# lock request.
+SELECT * FROM m1 WHERE a < 3;
+connection default;
+# Resuming ALTER TABLE
+SET DEBUG_SYNC= 'now SIGNAL continue';
+connection con1;
+# Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1)
+connection con2;
+# Reaping: DELETE FROM t2 WHERE a = 3
+connection con3;
+# Reaping: SELECT * FROM m1 WHERE a < 3
+a
+1
+2
+connection default;
+DROP TABLE m1, t1, t2;
+SET DEBUG_SYNC= 'RESET';
+disconnect con1;
+disconnect con2;
+disconnect con3;
+#
+# MDEV-12620 - set lock_wait_timeout = 1;flush tables with read lock;
+#              lock not released after timeout
+#
+CREATE TABLE t1(a INT) ENGINE=InnoDB;
+SET debug_sync='open_tables_after_open_and_process_table SIGNAL ready WAIT_FOR go';
+INSERT INTO t1 values (1);
+connect  con1,localhost,root,,;
+SET debug_sync='now WAIT_FOR ready';
+SET lock_wait_timeout=1;
+FLUSH TABLES WITH READ LOCK;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET debug_sync='now SIGNAL go';
+connection default;
+# After MDEV-5536, SELECT will not block FLUSH TABLES
+SET debug_sync='RESET';
+SET debug_sync='open_tables_after_open_and_process_table SIGNAL ready WAIT_FOR go';
+SELECT * FROM t1;
+connection con1;
+SET debug_sync='now WAIT_FOR ready';
+SET lock_wait_timeout=1;
+FLUSH TABLES WITH READ LOCK;
+SET debug_sync='now SIGNAL go';
+connection default;
+a
+1
+connection con1;
+unlock tables;
+connection default;
+SET debug_sync='RESET';
+DROP TABLE t1;
+disconnect con1;