From 5a5d10cafc03a1739aaad948ea76906da8210d5a Mon Sep 17 00:00:00 2001 From: Yuchen Pei Date: Thu, 27 Apr 2023 09:51:34 +1000 Subject: MDEV-29676 Add query to set lock wait timeout when getting sts crd 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. --- .../mysql-test/spider/bugfix/r/mdev_29676.result | 47 +++++++++++++ .../spider/bugfix/r/slave_trx_isolation.result | 4 ++ .../mysql-test/spider/bugfix/t/mdev_29676.test | 41 ++++++++++++ .../mysql-test/spider/r/slave_trx_isolation.result | 4 ++ storage/spider/spd_db_mysql.cc | 77 ++++++++++++++++++++++ storage/spider/spd_db_mysql.h | 6 ++ storage/spider/spd_err.h | 2 + storage/spider/spd_table.cc | 9 ++- 8 files changed, 187 insertions(+), 3 deletions(-) create mode 100644 storage/spider/mysql-test/spider/bugfix/r/mdev_29676.result create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_29676.test 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 a036e34f024..4923d2ef03b 100644 --- a/storage/spider/spd_db_mysql.cc +++ b/storage/spider/spd_db_mysql.cc @@ -13456,6 +13456,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) @@ -13507,6 +13557,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, @@ -13527,6 +13579,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(), @@ -13604,8 +13659,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) @@ -13646,6 +13714,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(), @@ -13665,6 +13735,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(), @@ -13725,6 +13798,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 5cfd3e693d5..262c4f29082 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 143214134d1..3e6e5d74b75 100644 --- a/storage/spider/spd_table.cc +++ b/storage/spider/spd_table.cc @@ -5003,8 +5003,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 { @@ -5021,7 +5023,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 { -- cgit v1.2.1