summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYuchen Pei <yuchen.pei@mariadb.com>2023-04-27 09:51:34 +1000
committerYuchen Pei <yuchen.pei@mariadb.com>2023-04-28 10:32:51 +1000
commit6abafdbb7cb79aec4dda159f177948e1ea77b7aa (patch)
treec0f79ae833c1cdd542bcb904cd61250531014c5b
parent779307dd5b5de8a6eea9d5ce5db574e5d6a2f70b (diff)
downloadmariadb-git-bb-11.0-mdev-29676.tar.gz
MDEV-29676 Add query to set lock wait timeout when getting sts crdbb-11.0-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.
-rw-r--r--storage/spider/mysql-test/spider/bugfix/disabled.def1
-rw-r--r--storage/spider/mysql-test/spider/bugfix/r/mdev_29676.result47
-rw-r--r--storage/spider/mysql-test/spider/bugfix/r/slave_trx_isolation.result4
-rw-r--r--storage/spider/mysql-test/spider/bugfix/t/mdev_29676.test41
-rw-r--r--storage/spider/mysql-test/spider/r/slave_trx_isolation.result4
-rw-r--r--storage/spider/spd_db_mysql.cc77
-rw-r--r--storage/spider/spd_db_mysql.h6
-rw-r--r--storage/spider/spd_err.h2
-rw-r--r--storage/spider/spd_table.cc9
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 f376ae3037f..555ab33d777 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -13443,6 +13443,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)
@@ -13494,6 +13544,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,
@@ -13514,6 +13566,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(),
@@ -13590,8 +13645,21 @@ int spider_mbase_handler::show_table_status(
}
if ((error_num = ((spider_db_mbase *) conn->db_conn)->fetch_and_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)
@@ -13632,6 +13700,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(),
@@ -13651,6 +13721,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(),
@@ -13711,6 +13784,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 b1f30c22c00..35f4f8392d9 100644
--- a/storage/spider/spd_db_mysql.h
+++ b/storage/spider/spd_db_mysql.h
@@ -513,6 +513,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 66042b36a66..943d781a171 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
{