MDEV-7894 - ALTER TABLE ... IMPORT/DISCARD TABLESPACE under LOCK TABLES doesn'tbb-mdev7894

            acquire X metadata lock

Due to coding mistake thr_lock.c lock was necessary to isolate ALTER TABLE
IMPORT/DISCARD TABLESPACE under LOCK TABLES from concurrent I_S queries/
open HANDLERs.

Change ALTER TABLE IMPORT/DISCARD TABLESPACE code to acquire X
lock on table being imported/discarded even under lock tables.

3 files changed, 351 insertions, 0 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 544e64e6373..9bbb525cc2e 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -2007,3 +2007,129 @@ INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
 INSERT INTO t1 SELECT a.* FROM t1 a, t1 b, t1 c, t1 d, t1 e;
 ALTER TABLE t1 MODIFY i FLOAT;
 DROP TABLE t1;
+#
+# Coverage for changes to ALTER TABLE ... IMPORT/DISCARD TABLESPACE
+# code introduced by WL#6671 "Improve scalability by not using
+# thr_lock.c locks for InnoDB tables".
+#
+# Check that ALTER TABLE ... IMPORT/DISCARD TABLESPACE acquires X
+# metadata lock on the table even when executed under LOCK TABLES.
+#
+# Suppress error messages which will be generated by IMPORT TABLESPACE
+call mtr.add_suppression("InnoDB: Trying to import a tablespace, but could not open the tablespace file");
+call mtr.add_suppression("InnoDB: Operating system error number 2 in a file operation.");
+call mtr.add_suppression("InnoDB: The error means the system cannot find the path specified.");
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+#
+# 1) Check that ALTER TABLE ... IMPORT/DISCARD TABLESPACE on
+#    base table acquire X lock (when not under LOCK TABLES).
+#
+connect con1, localhost, root;
+# Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+connect con2, localhost, root;
+# Sending:
+ALTER TABLE t1 DISCARD TABLESPACE;
+connection default;
+# Wait until ALTER TABLE is blocked since it tries to acquire X lock.
+connection con1;
+# Unblock ALTER TABLE DISCARD
+HANDLER t1 CLOSE;
+connection con2;
+# Reaping ALTER TABLE DISCARD
+connection con1;
+# Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+connection con2;
+# Sending:
+ALTER TABLE t1 IMPORT TABLESPACE;
+connection default;
+# Wait until ALTER TABLE is blocked since it tries to acquire X lock.
+connection con1;
+# Unblock ALTER TABLE IMPORT
+HANDLER t1 CLOSE;
+connection con2;
+# Reaping ALTER TABLE IMPORT
+# It should fail as no tablespace was copied after DISCARD.
+ERROR 42S02: Table 'test.t1' doesn't exist in engine
+connection default;
+DROP TABLE t1;
+#
+# 2) ALTER TABLE ... IMPORT/DISCARD TABLESPACE on temporary table
+#    should not try to acquire X metadata lock on base table.
+#
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+connection con1;
+# Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+connection con2;
+CREATE TEMPORARY TABLE t1 (j INT) ENGINE=InnoDB;
+# Both DISCARD and IMPORT on temporary table should fail without
+# acquiring any locks and blocking.
+# NOTE: they don't fail in MariaDB yet, still we test for locks.
+ALTER TABLE t1 DISCARD TABLESPACE;
+ALTER TABLE t1 IMPORT TABLESPACE;
+ERROR 42S02: Table 'test.t1' doesn't exist in engine
+DROP TEMPORARY TABLE t1;
+#
+# 3) Check that ALTER TABLE ... IMPORT/DISCARD TABLESPACE on
+#    base table under LOCK TABLES acquire X lock.
+#
+connection con2;
+LOCK TABLES t1 WRITE;
+# Sending:
+ALTER TABLE t1 DISCARD TABLESPACE;
+connection default;
+# Wait until ALTER TABLE is blocked since it tries to acquire X lock.
+connection con1;
+# Unblock ALTER TABLE DISCARD
+HANDLER t1 CLOSE;
+connection con2;
+# Reaping ALTER TABLE DISCARD
+connection con1;
+# Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+connection con2;
+# Sending:
+ALTER TABLE t1 IMPORT TABLESPACE;
+connection default;
+# Wait until ALTER TABLE is blocked since it tries to acquire X lock.
+connection con1;
+# Unblock ALTER TABLE IMPORT
+HANDLER t1 CLOSE;
+connection con2;
+# Reaping ALTER TABLE IMPORT
+# It should fail as no tablespace was copied after DISCARD.
+ERROR 42S02: Table 'test.t1' doesn't exist in engine
+UNLOCK TABLES;
+connection default;
+DROP TABLE t1;
+#
+# 4) Under LOCK TABLES, ALTER TABLE ... IMPORT/DISCARD TABLESPACE on
+#    temporary table should not try to acquire X metadata lock on base
+#    table.
+#
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+connection con1;
+# Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+connection con2;
+CREATE TEMPORARY TABLE t1 (j INT) ENGINE=InnoDB;
+LOCK TABLES t1 WRITE;
+# Both DISCARD and IMPORT on temporary table should fail without
+# acquiring any locks and blocking.
+# NOTE: they don't fail in MariaDB yet, still we test for locks.
+ALTER TABLE t1 DISCARD TABLESPACE;
+ALTER TABLE t1 IMPORT TABLESPACE;
+ERROR 42S02: Table 'test.t1' doesn't exist in engine
+UNLOCK TABLES;
+DROP TEMPORARY TABLE t1;
+connection con1;
+HANDLER t1 CLOSE;
+#
+# Clean-up.
+#
+disconnect con1;
+disconnect con2;
+connection default;
+DROP TABLE t1;
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index ab1fb4e4c54..e540fbd843c 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1697,3 +1697,203 @@ INSERT INTO t1 SELECT a.* FROM t1 a, t1 b, t1 c, t1 d, t1 e;
 ALTER TABLE t1 MODIFY i FLOAT;
 DROP TABLE t1;
 
+
+
+--echo #
+--echo # Coverage for changes to ALTER TABLE ... IMPORT/DISCARD TABLESPACE
+--echo # code introduced by WL#6671 "Improve scalability by not using
+--echo # thr_lock.c locks for InnoDB tables".
+--echo #
+--echo # Check that ALTER TABLE ... IMPORT/DISCARD TABLESPACE acquires X
+--echo # metadata lock on the table even when executed under LOCK TABLES.
+--echo #
+
+--enable_connect_log
+--echo # Suppress error messages which will be generated by IMPORT TABLESPACE
+call mtr.add_suppression("InnoDB: Trying to import a tablespace, but could not open the tablespace file");
+call mtr.add_suppression("InnoDB: Operating system error number 2 in a file operation.");
+call mtr.add_suppression("InnoDB: The error means the system cannot find the path specified.");
+
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+
+--echo #
+--echo # 1) Check that ALTER TABLE ... IMPORT/DISCARD TABLESPACE on
+--echo #    base table acquire X lock (when not under LOCK TABLES).
+--echo #
+
+connect(con1, localhost, root);
+--echo # Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+
+connect(con2, localhost, root);
+--echo # Sending:
+--send ALTER TABLE t1 DISCARD TABLESPACE
+
+connection default;
+--echo # Wait until ALTER TABLE is blocked since it tries to acquire X lock.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock" AND
+        info = "ALTER TABLE t1 DISCARD TABLESPACE";
+--source include/wait_condition.inc
+
+connection con1;
+--echo # Unblock ALTER TABLE DISCARD
+HANDLER t1 CLOSE;
+
+connection con2;
+--echo # Reaping ALTER TABLE DISCARD
+--reap
+
+connection con1;
+--echo # Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+
+connection con2;
+--echo # Sending:
+--send ALTER TABLE t1 IMPORT TABLESPACE
+
+connection default;
+--echo # Wait until ALTER TABLE is blocked since it tries to acquire X lock.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock" AND
+        info = "ALTER TABLE t1 IMPORT TABLESPACE";
+--source include/wait_condition.inc
+
+connection con1;
+--echo # Unblock ALTER TABLE IMPORT
+HANDLER t1 CLOSE;
+
+connection con2;
+--echo # Reaping ALTER TABLE IMPORT
+--echo # It should fail as no tablespace was copied after DISCARD.
+--error ER_NO_SUCH_TABLE_IN_ENGINE
+--reap
+
+connection default;
+DROP TABLE t1;
+
+--echo #
+--echo # 2) ALTER TABLE ... IMPORT/DISCARD TABLESPACE on temporary table
+--echo #    should not try to acquire X metadata lock on base table.
+--echo #
+
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+
+connection con1;
+--echo # Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+
+connection con2;
+CREATE TEMPORARY TABLE t1 (j INT) ENGINE=InnoDB;
+
+--echo # Both DISCARD and IMPORT on temporary table should fail without
+--echo # acquiring any locks and blocking.
+--echo # NOTE: they don't fail in MariaDB yet, still we test for locks.
+#--error ER_CANNOT_DISCARD_TEMPORARY_TABLE
+ALTER TABLE t1 DISCARD TABLESPACE;
+#--error ER_CANNOT_DISCARD_TEMPORARY_TABLE
+--error ER_NO_SUCH_TABLE_IN_ENGINE
+ALTER TABLE t1 IMPORT TABLESPACE;
+
+DROP TEMPORARY TABLE t1;
+
+--echo #
+--echo # 3) Check that ALTER TABLE ... IMPORT/DISCARD TABLESPACE on
+--echo #    base table under LOCK TABLES acquire X lock.
+--echo #
+
+connection con2;
+LOCK TABLES t1 WRITE;
+--echo # Sending:
+--send ALTER TABLE t1 DISCARD TABLESPACE
+
+connection default;
+--echo # Wait until ALTER TABLE is blocked since it tries to acquire X lock.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock" AND
+        info = "ALTER TABLE t1 DISCARD TABLESPACE";
+--source include/wait_condition.inc
+
+connection con1;
+--echo # Unblock ALTER TABLE DISCARD
+HANDLER t1 CLOSE;
+
+connection con2;
+--echo # Reaping ALTER TABLE DISCARD
+--reap
+
+connection con1;
+--echo # Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+
+connection con2;
+--echo # Sending:
+--send ALTER TABLE t1 IMPORT TABLESPACE
+
+connection default;
+--echo # Wait until ALTER TABLE is blocked since it tries to acquire X lock.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock" AND
+        info = "ALTER TABLE t1 IMPORT TABLESPACE";
+--source include/wait_condition.inc
+
+connection con1;
+--echo # Unblock ALTER TABLE IMPORT
+HANDLER t1 CLOSE;
+
+connection con2;
+--echo # Reaping ALTER TABLE IMPORT
+--echo # It should fail as no tablespace was copied after DISCARD.
+--error ER_NO_SUCH_TABLE_IN_ENGINE
+--reap
+
+UNLOCK TABLES;
+
+connection default;
+DROP TABLE t1;
+
+--echo #
+--echo # 4) Under LOCK TABLES, ALTER TABLE ... IMPORT/DISCARD TABLESPACE on
+--echo #    temporary table should not try to acquire X metadata lock on base
+--echo #    table.
+--echo #
+
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+
+connection con1;
+--echo # Acquire S lock on table 't1'.
+HANDLER t1 OPEN;
+
+connection con2;
+CREATE TEMPORARY TABLE t1 (j INT) ENGINE=InnoDB;
+LOCK TABLES t1 WRITE;
+
+--echo # Both DISCARD and IMPORT on temporary table should fail without
+--echo # acquiring any locks and blocking.
+--echo # NOTE: they don't fail in MariaDB yet, still we test for locks.
+#--error ER_CANNOT_DISCARD_TEMPORARY_TABLE
+ALTER TABLE t1 DISCARD TABLESPACE;
+#--error ER_CANNOT_DISCARD_TEMPORARY_TABLE
+--error ER_NO_SUCH_TABLE_IN_ENGINE
+ALTER TABLE t1 IMPORT TABLESPACE;
+
+UNLOCK TABLES;
+DROP TEMPORARY TABLE t1;
+
+connection con1;
+HANDLER t1 CLOSE;
+
+--echo #
+--echo # Clean-up.
+--echo #
+disconnect con1;
+--source include/wait_until_disconnected.inc
+disconnect con2;
+--source include/wait_until_disconnected.inc
+connection default;
+--disable_connect_log
+DROP TABLE t1;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index c4b07ad2035..b4cbc936fa1 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -5561,6 +5561,24 @@ int mysql_discard_or_import_tablespace(THD *thd,
     DBUG_RETURN(-1);
   }
 
+  /*
+    Under LOCK TABLES we need to upgrade SNRW metadata lock to X lock
+    before doing discard or import of tablespace.
+
+    Skip this step for temporary tables as metadata locks are not
+    applicable for them.
+  */
+  if (table_list->table->s->tmp_table == NO_TMP_TABLE &&
+      (thd->locked_tables_mode == LTM_LOCK_TABLES ||
+       thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES) &&
+      thd->mdl_context.upgrade_shared_lock(table_list->table->mdl_ticket,
+                                           MDL_EXCLUSIVE,
+                                           thd->variables.lock_wait_timeout))
+  {
+    thd->tablespace_op= FALSE;
+    DBUG_RETURN(-1);
+  }
+
   error= table_list->table->file->ha_discard_or_import_tablespace(discard);
 
   THD_STAGE_INFO(thd, stage_end);
@@ -5583,6 +5601,13 @@ int mysql_discard_or_import_tablespace(THD *thd,
   error= write_bin_log(thd, FALSE, thd->query(), thd->query_length());
 
 err:
+  if (table_list->table->s->tmp_table == NO_TMP_TABLE &&
+      (thd->locked_tables_mode == LTM_LOCK_TABLES ||
+       thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES))
+  {
+    table_list->table->mdl_ticket->downgrade_lock(MDL_SHARED_NO_READ_WRITE);
+  }
+
   thd->tablespace_op=FALSE;
 
   if (error == 0)