MDEV-29676 Add query to set lock wait timeout when getting sts crdbb-11.1-mdev-29676

Set the lock wait timeout to 1 beforehand, and reset it afterwards, to
avoid lock conflict caused by opening the same table twice in case of
self-reference.

9 files changed, 188 insertions, 3 deletions

diff --git a/storage/spider/mysql-test/spider/bugfix/disabled.def b/storage/spider/mysql-test/spider/bugfix/disabled.def
index e19ea07b76b..2314e88ea0c 100644
--- a/storage/spider/mysql-test/spider/bugfix/disabled.def
+++ b/storage/spider/mysql-test/spider/bugfix/disabled.def
@@ -1 +1,2 @@
 wait_timeout : MDEV-26045
+mdev_29676 : MDEV-31138
diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_29676.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29676.result
new file mode 100644
index 00000000000..72c624c926b
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29676.result
@@ -0,0 +1,47 @@
+#
+# MDEV-29676 Dual thread hang in 'closing tables' and 'Waiting for table metadata lock' on Spider CREATE OR REPLACE TABLE
+#
+for master_1
+for child2
+for child3
+CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');
+
+# length-0 self-reference
+
+CREATE TABLE t (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t"';
+CREATE OR REPLACE TABLE t (c INT);
+Warnings:
+Error	1205	Lock wait timeout exceeded; try restarting transaction
+Error	12722	Table test.t open lock wait timeout. Please check for self-reference.
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `c` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t;
+
+# length-2 self-reference
+
+CREATE TABLE t2 (c int);
+CREATE TABLE t1 (c int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"';
+CREATE TABLE t0 (c int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"';
+ALTER TABLE t2 ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t0"';
+CREATE OR REPLACE TABLE t0 (c int);
+Warnings:
+Error	1205	Lock wait timeout exceeded; try restarting transaction
+Error	12722	Table test.t1 open lock wait timeout. Please check for self-reference.
+SHOW CREATE TABLE t0;
+Table	Create Table
+t0	CREATE TABLE `t0` (
+  `c` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE OR REPLACE TABLE t1 (c int);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop TABLE t0, t1, t2;
+for master_1
+for child2
+for child3
diff --git a/storage/spider/mysql-test/spider/bugfix/r/slave_trx_isolation.result b/storage/spider/mysql-test/spider/bugfix/r/slave_trx_isolation.result
index e84d42bbc8a..ffccf2d5ef1 100644
--- a/storage/spider/mysql-test/spider/bugfix/r/slave_trx_isolation.result
+++ b/storage/spider/mysql-test/spider/bugfix/r/slave_trx_isolation.result
@@ -50,6 +50,10 @@ SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argum
 argument
 set session time_zone = '+00:00';set @`spider_lc_./auto_test_remote/tbl_a` = '-xxxxxxxxxxxx-xxxxx-./auto_test_local/tbl_a-'
 SET NAMES utf8mb3
+set @old_lock_wait_timeout=@@session.lock_wait_timeout;set session lock_wait_timeout=1
+set session lock_wait_timeout=@old_lock_wait_timeout
+set @old_lock_wait_timeout=@@session.lock_wait_timeout;set session lock_wait_timeout=1
+set session lock_wait_timeout=@old_lock_wait_timeout
 set session transaction isolation level read committed;set session autocommit = 1;set session wait_timeout = 604800;set session sql_mode = 'strict_trans_tables,error_for_division_by_zero,no_auto_create_user,no_engine_substitution';start transaction
 SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%set %'
 SELECT pkey FROM tbl_a ORDER BY pkey;
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29676.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29676.test
new file mode 100644
index 00000000000..565d95dfaac
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29676.test
@@ -0,0 +1,41 @@
+--echo #
+--echo # MDEV-29676 Dual thread hang in 'closing tables' and 'Waiting for table metadata lock' on Spider CREATE OR REPLACE TABLE
+--echo #
+
+--disable_query_log
+--disable_result_log
+--source ../../t/test_init.inc
+--enable_result_log
+--enable_query_log
+
+--replace_regex /SOCKET ".*"/SOCKET "$MASTER_1_MYSOCK"/
+eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');
+
+--echo
+--echo # length-0 self-reference
+--echo
+CREATE TABLE t (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t"';
+CREATE OR REPLACE TABLE t (c INT);
+SHOW CREATE TABLE t;
+DROP TABLE t;
+
+--echo
+--echo # length-2 self-reference
+--echo
+CREATE TABLE t2 (c int);
+CREATE TABLE t1 (c int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"';
+CREATE TABLE t0 (c int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"';
+ALTER TABLE t2 ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t0"';
+# warnings
+CREATE OR REPLACE TABLE t0 (c int);
+SHOW CREATE TABLE t0;
+# no warnings
+CREATE OR REPLACE TABLE t1 (c int);
+SHOW CREATE TABLE t1;
+drop TABLE t0, t1, t2;
+
+--disable_query_log
+--disable_result_log
+--source ../../t/test_deinit.inc
+--enable_result_log
+--enable_query_log
diff --git a/storage/spider/mysql-test/spider/r/slave_trx_isolation.result b/storage/spider/mysql-test/spider/r/slave_trx_isolation.result
index 28aaf74fa3d..c48d68758bf 100644
--- a/storage/spider/mysql-test/spider/r/slave_trx_isolation.result
+++ b/storage/spider/mysql-test/spider/r/slave_trx_isolation.result
@@ -53,6 +53,10 @@ SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argum
 argument
 set session time_zone = '+00:00';set @`spider_lc_./auto_test_remote/tbl_a` = '-xxxxxxxxxxxx-xxxxx-./auto_test_local/tbl_a-'
 SET NAMES utf8mb3
+set @old_lock_wait_timeout=@@session.lock_wait_timeout;set session lock_wait_timeout=1
+set session lock_wait_timeout=@old_lock_wait_timeout
+set @old_lock_wait_timeout=@@session.lock_wait_timeout;set session lock_wait_timeout=1
+set session lock_wait_timeout=@old_lock_wait_timeout
 set session transaction isolation level read committed;set session autocommit = 1;set session wait_timeout = 604800;set session sql_mode = 'strict_trans_tables,error_for_division_by_zero,no_auto_create_user,no_engine_substitution';start transaction
 SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%set %'
 SELECT pkey FROM tbl_a ORDER BY pkey;
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
index 53d48041239..6f93aaea492 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -13399,6 +13399,56 @@ int spider_mbase_handler::sts_mode_exchange(
   DBUG_RETURN(sts_mode);
 }
 
+/** Set the session lock wait time out */
+int spider_db_mbase::set_lock_wait_timeout(uint timeout)
+{
+  String query(0);
+  int error_num;
+  DBUG_ENTER("spider_db_set_lock_wait_timeout");
+  query.append(STRING_WITH_LEN(
+                 "set @old_lock_wait_timeout=@@session.lock_wait_timeout;"
+                 "set session lock_wait_timeout="));
+  query.append_ulonglong(timeout);
+  query.append(STRING_WITH_LEN(";"));
+  if ((error_num = exec_query(query.c_ptr(), query.length(), -1)))
+    DBUG_RETURN(error_num);
+  spider_db_result *result;
+  do {
+    st_spider_db_request_key request_key= {1, 1, NULL, 1, NULL};
+    if ((result = conn->db_conn->store_result(NULL, &request_key,
+                                              &error_num)))
+    {
+      result->free_result();
+      delete result;
+    } else if ((error_num = conn->db_conn->get_errno()))
+      break;
+  } while (!(error_num = conn->db_conn->next_result()));
+  DBUG_RETURN(0);
+}
+
+/** Reset the session lock wait time out */
+int spider_db_mbase::reset_lock_wait_timeout()
+{
+  const LEX_CSTRING query = {STRING_WITH_LEN(
+      "set session lock_wait_timeout=@old_lock_wait_timeout;")};
+  int error_num;
+  DBUG_ENTER("spider_db_set_lock_wait_timeout");
+  if ((error_num = exec_query(query.str, query.length, -1)))
+    DBUG_RETURN(error_num);
+  spider_db_result *result;
+  do {
+    st_spider_db_request_key request_key= {1, 1, NULL, 1, NULL};
+    if ((result = conn->db_conn->store_result(NULL, &request_key,
+                                              &error_num)))
+    {
+      result->free_result();
+      delete result;
+    } else if ((error_num = conn->db_conn->get_errno()))
+      break;
+  } while (!(error_num= conn->db_conn->next_result()));
+  DBUG_RETURN(0);
+}
+
 /** FIXME: refactor more functions to use spider_setup_for_query() and
 spider_teardown_after_query(). */
 void spider_setup_for_query(ha_spider *spider, SPIDER_CONN *conn, int link_idx)
@@ -13450,6 +13500,8 @@ int spider_mbase_handler::show_table_status(
   spider_conn_set_timeout_from_share(
     conn, link_idx, spider->wide_handler->trx->thd, share);
   if ((error_num = spider_db_set_names(spider, conn, link_idx)) ||
+      (error_num =
+       ((spider_db_mbase *) conn->db_conn)->set_lock_wait_timeout(1)) ||
       /* Executes the `show table status` query */
       (spider_db_query(
         conn,
@@ -13470,6 +13522,9 @@ int spider_mbase_handler::show_table_status(
       spider_conn_set_timeout_from_share(conn, link_idx,
                                          spider->wide_handler->trx->thd,
                                          share);
+      if ((error_num =
+           ((spider_db_mbase *) conn->db_conn)->set_lock_wait_timeout(1)))
+        DBUG_RETURN(spider_teardown_after_query(conn, error_num, true));
       if (spider_db_query(
             conn,
             mysql_share->show_table_status[pos].ptr(),
@@ -13546,8 +13601,21 @@ int spider_mbase_handler::show_table_status(
   }
   if ((error_num = ((spider_db_mbase *) conn->db_conn)->print_warnings(NULL)))
   {
+    ((spider_db_mbase *) conn->db_conn)->reset_lock_wait_timeout();
+    if (error_num == ER_LOCK_WAIT_TIMEOUT)
+    {
+      error_num = ER_SPIDER_TABLE_OPEN_LOCK_WAIT_TIMEOUT_NUM;
+      my_printf_error(
+        ER_SPIDER_TABLE_OPEN_LOCK_WAIT_TIMEOUT_NUM,
+        ER_SPIDER_TABLE_OPEN_LOCK_WAIT_TIMEOUT_STR, MYF(0),
+        mysql_share->db_names_str[spider->conn_link_idx[link_idx]].ptr(),
+        mysql_share->table_names_str[spider->conn_link_idx[link_idx]].ptr());
+    }
     DBUG_RETURN(error_num);
   }
+  if ((error_num =
+       ((spider_db_mbase *) conn->db_conn)->reset_lock_wait_timeout()))
+    DBUG_RETURN(error_num);
   if (share->static_records_for_status != -1)
     share->stat.records = (ha_rows) share->static_records_for_status;
   if (share->static_mean_rec_length != -1)
@@ -13588,6 +13656,8 @@ int spider_mbase_handler::show_index(
   spider_conn_set_timeout_from_share(conn, link_idx,
                                      spider->wide_handler->trx->thd, share);
   if ((error_num = spider_db_set_names(spider, conn, link_idx)) ||
+      (error_num =
+       ((spider_db_mbase *) conn->db_conn)->set_lock_wait_timeout(1)) ||
       (spider_db_query(
         conn,
         mysql_share->show_index[pos].ptr(),
@@ -13607,6 +13677,9 @@ int spider_mbase_handler::show_index(
       spider_conn_set_timeout_from_share(conn, link_idx,
                                          spider->wide_handler->trx->thd,
                                          share);
+      if ((error_num =
+           ((spider_db_mbase *) conn->db_conn)->set_lock_wait_timeout(1)))
+        DBUG_RETURN(spider_teardown_after_query(conn, error_num, true));
       if (spider_db_query(
             conn,
             mysql_share->show_index[pos].ptr(),
@@ -13667,6 +13740,10 @@ int spider_mbase_handler::show_index(
   default:
     break;
   }
+  if (!error_num)
+    error_num = ((spider_db_mbase *) conn->db_conn)->reset_lock_wait_timeout();
+  else
+    ((spider_db_mbase *) conn->db_conn)->reset_lock_wait_timeout();
   DBUG_RETURN(error_num);
 }
 
diff --git a/storage/spider/spd_db_mysql.h b/storage/spider/spd_db_mysql.h
index aa70d28e0ad..8bd3f561249 100644
--- a/storage/spider/spd_db_mysql.h
+++ b/storage/spider/spd_db_mysql.h
@@ -515,6 +515,12 @@ public:
     int wait_timeout,
     int *need_mon
   );
+
+  /** Set the global lock wait time out */
+  int set_lock_wait_timeout(uint timeout);
+  /** Reset the global lock wait time out */
+  int reset_lock_wait_timeout();
+
   bool set_sql_mode_in_bulk_sql();
   int set_sql_mode(
     sql_mode_t sql_mode,
diff --git a/storage/spider/spd_err.h b/storage/spider/spd_err.h
index e9a4a41946e..e2098d4e751 100644
--- a/storage/spider/spd_err.h
+++ b/storage/spider/spd_err.h
@@ -134,6 +134,8 @@
 #define ER_SPIDER_SAME_SERVER_LINK_STR2 "Host:%s and Port:%ld aim self server. Please change spider_same_server_link parameter if this link is required."
 #define ER_SPIDER_CANT_NUM 12721
 #define ER_SPIDER_CANT_STR1 "Can't %s%d"
+#define ER_SPIDER_TABLE_OPEN_LOCK_WAIT_TIMEOUT_NUM 12722
+#define ER_SPIDER_TABLE_OPEN_LOCK_WAIT_TIMEOUT_STR "Table %s.%s open lock wait timeout. Please check for self-reference."
 #define ER_SPIDER_COND_SKIP_NUM 12801
 
 #define ER_SPIDER_UNKNOWN_NUM 12500
diff --git a/storage/spider/spd_table.cc b/storage/spider/spd_table.cc
index a38733f5482..153bb210472 100644
--- a/storage/spider/spd_table.cc
+++ b/storage/spider/spd_table.cc
@@ -5002,8 +5002,10 @@ bool spider_share_get_sts_crd(
         (*error_num = spider_get_sts(share, spider->search_link_idx, tmp_time,
                                      spider, sts_interval, sts_mode, sts_sync,
                                      1, HA_STATUS_VARIABLE | HA_STATUS_CONST | HA_STATUS_AUTO))
-    ) {
-      if (*error_num != ER_SPIDER_SYS_TABLE_VERSION_NUM)
+    )
+    {
+      if (*error_num != ER_SPIDER_SYS_TABLE_VERSION_NUM &&
+          *error_num != ER_SPIDER_TABLE_OPEN_LOCK_WAIT_TIMEOUT_NUM)
         thd->clear_error();
       else
       {
@@ -5020,7 +5022,8 @@ bool spider_share_get_sts_crd(
                                      crd_sync,
                                      1)))
     {
-      if (*error_num != ER_SPIDER_SYS_TABLE_VERSION_NUM)
+      if (*error_num != ER_SPIDER_SYS_TABLE_VERSION_NUM &&
+          *error_num != ER_SPIDER_TABLE_OPEN_LOCK_WAIT_TIMEOUT_NUM)
         thd->clear_error();
       else
       {