diff --git a/mysql-test/r/schema.result b/mysql-test/r/schema.result index f6b59863368..82210546fab 100644 --- a/mysql-test/r/schema.result +++ b/mysql-test/r/schema.result @@ -261,6 +261,7 @@ DROP DATABASE db1; SET DEBUG_SYNC= 'now SIGNAL continue_insert'; SET DEBUG_SYNC= 'now SIGNAL continue_alter'; # Reaping connection con1 +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction # Reaping connection con2 # Reaping connection con3 # @@ -353,3 +354,16 @@ SHOW CREATE DATABASE test1; Database Create Database test1 CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ DROP DATABASE test1; +# +# Bug#xxxx: +# +create database if not exists db1; +create table db1.t1(a int); +[connection1] +begin; +insert into db1.t1 values(2); +[connection2] +create database if not exists db1; +[connection1] +commit; +drop database db1; diff --git a/mysql-test/suite/perfschema/r/mdl_func.result b/mysql-test/suite/perfschema/r/mdl_func.result index 85b33c282db..6d65ec21cab 100644 --- a/mysql-test/suite/perfschema/r/mdl_func.result +++ b/mysql-test/suite/perfschema/r/mdl_func.result @@ -352,14 +352,6 @@ LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER -OBJECT_TYPE SCHEMA -OBJECT_SCHEMA test -OBJECT_NAME NULL -COLUMN_NAME NULL -LOCK_TYPE INTENTION_EXCLUSIVE -LOCK_DURATION TRANSACTION -LOCK_STATUS GRANTED -OWNER_THREAD_ID USER1 OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks @@ -419,14 +411,6 @@ COLUMN_NAME NULL LOCK_TYPE INTENTION_EXCLUSIVE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED -OWNER_THREAD_ID USER1 -OBJECT_TYPE SCHEMA -OBJECT_SCHEMA test -OBJECT_NAME NULL -COLUMN_NAME NULL -LOCK_TYPE INTENTION_EXCLUSIVE -LOCK_DURATION TRANSACTION -LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema diff --git a/mysql-test/t/bug_wait_schema_mdl_lock.test b/mysql-test/t/bug_wait_schema_mdl_lock.test new file mode 100644 index 00000000000..3d9d4cff25d --- /dev/null +++ b/mysql-test/t/bug_wait_schema_mdl_lock.test @@ -0,0 +1,22 @@ +create database if not exists db1; +create table db1.t1(a int); + +--connect(conn1, localhost, root) +--echo [connection1] +begin; +insert into db1.t1 values(2); + +--connect(conn2, localhost, root) +--echo [connection2] +--disable_warnings +create database if not exists db1; +--enable_warnings + +--connection conn1 +--echo [connection1] +commit; + +--disconnect conn1 +--disconnect conn2 +--connection default +drop database db1; diff --git a/mysql-test/t/drop.test b/mysql-test/t/drop.test index 4ba57e07075..45239421c3a 100644 --- a/mysql-test/t/drop.test +++ b/mysql-test/t/drop.test @@ -275,7 +275,7 @@ FLUSH TABLES t1 FOR EXPORT; --echo # Wait until DROP DATABASE becomes blocked on table metadata lock let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist - WHERE state = "Waiting for schema metadata lock" AND + WHERE state = "Waiting for table metadata lock" AND info = "DROP DATABASE bug19573998"; --source include/wait_condition.inc UNLOCK TABLES; diff --git a/mysql-test/t/schema.test b/mysql-test/t/schema.test index a5cd359950f..39615e6b3cd 100644 --- a/mysql-test/t/schema.test +++ b/mysql-test/t/schema.test @@ -11,12 +11,7 @@ let $MYSQLD_DATADIR= `SELECT @@datadir`; -# MDL blocking concurrent drop schema is different with --ps-protocol -let $drop_schema_target_state='Waiting for schema metadata lock'; -if (`SELECT $PS_PROTOCOL = 1`) -{ - let $drop_schema_target_state='Waiting for table metadata lock'; -} +let $drop_schema_target_state='Waiting for table metadata lock'; --disable_warnings drop database if exists mysqltest1; @@ -614,6 +609,7 @@ SET DEBUG_SYNC= 'now SIGNAL continue_alter'; --echo # Reaping connection con1 --connection con1 +--error 1213 --reap --echo # Reaping connection con2 @@ -739,3 +735,30 @@ DROP SCHEMA s1; CREATE DATABASE test1 COLLATE utf8mb4_bin CHARACTER SET utf8mb4; SHOW CREATE DATABASE test1; DROP DATABASE test1; + +--echo # +--echo # Bug#119451: The schema's MDL lock is held for too long after opening the table +--echo # + +create database if not exists db1; +create table db1.t1(a int); + +--connect(conn1, localhost, root) +--echo [connection1] +begin; +insert into db1.t1 values(2); + +--connect(conn2, localhost, root) +--echo [connection2] +--disable_warnings +create database if not exists db1; +--enable_warnings + +--connection conn1 +--echo [connection1] +commit; + +--disconnect conn1 +--disconnect conn2 +--connection default +drop database db1; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 13b7a0454d1..f62220ffb3e 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -684,6 +684,8 @@ TABLE_SHARE *get_table_share(THD *thd, const char *db, const char *table_name, bool open_secondary) { TABLE_SHARE *share; bool open_table_err = false; + bool need_unlock_schema = false; + MDL_ticket* schema_mdl_ticket; DBUG_TRACE; /* Make sure we own LOCK_open */ @@ -711,19 +713,26 @@ TABLE_SHARE *get_table_share(THD *thd, const char *db, const char *table_name, } mysql_mutex_unlock(&LOCK_open); - if (dd::mdl_lock_schema(thd, db, MDL_TRANSACTION)) { + if (dd::mdl_lock_schema(thd, db, MDL_EXPLICIT, &schema_mdl_ticket)) { // Lock LOCK_open again to preserve function contract mysql_mutex_lock(&LOCK_open); return nullptr; } + need_unlock_schema = true; mysql_mutex_lock(&LOCK_open); // Need to re-try the find after getting the mutex again continue; } share = it->second.get(); - if (!share->m_open_in_progress) + if (!share->m_open_in_progress) { + if (need_unlock_schema) { + mysql_mutex_unlock(&LOCK_open); + thd->mdl_context.release_lock(schema_mdl_ticket); + mysql_mutex_lock(&LOCK_open); + } return process_found_table_share(thd, share, open_view); + } DEBUG_SYNC(thd, "get_share_before_COND_open_wait"); mysql_cond_wait(&COND_open, &LOCK_open); @@ -736,6 +745,11 @@ TABLE_SHARE *get_table_share(THD *thd, const char *db, const char *table_name, */ if (!(share = alloc_table_share(db, table_name, key, key_length, open_secondary))) { + if (need_unlock_schema) { + mysql_mutex_unlock(&LOCK_open); + thd->mdl_context.release_lock(schema_mdl_ticket); + mysql_mutex_lock(&LOCK_open); + } return nullptr; } @@ -834,6 +848,10 @@ TABLE_SHARE *get_table_share(THD *thd, const char *db, const char *table_name, } } + if (need_unlock_schema) { + thd->mdl_context.release_lock(schema_mdl_ticket); + } + /* Get back LOCK_open before continuing. Notify all waiters that the opening is finished, even if there was a failure while opening.