The solution to this is to either do a mysql_ping() on the connection if there has been a long time since the last query (this is what Connector\/ODBC does) or set wait_timeout on the mysqld server so high that it in practice never times out.<\/pre>\n <\/p>\n
\u90a3\u5c31\u6309\u7167\u5b98\u65b9\u7ed9\u51fa\u7684\u65b9\u6cd5\u5b9a\u65f6ping()\u4e00\u4e0b\u597d\u4e86\uff0c\u5199\u4e86\u4e00\u4e2a\u66f4\u65b0\u5b9a\u65f6\u5668\u7684\u51fd\u6570\uff0c\u5728\u7a0b\u5e8f\u542f\u52a8\u7684\u65f6\u5019\u6267\u884c\u4e00\u4e0b\uff0c\u5728\u6709\u65b0\u7684\u67e5\u8be2\u64cd\u4f5c\u4e5f\u4f1a\u6267\u884c\u4e00\u4e0b<\/p>\n
void DbConnection::UpdatePingTimer() {\r\n if (timer_ping_ != nullptr) {\r\n timer_ping_->Cancel();\r\n timer_ping_ = nullptr;\r\n }\r\n\r\n timer_ping_ = cron_timer_mgr_.AddDelayTimer(\r\n 3600 * 1000,\r\n [this]() {\r\n auto ping_ret = mysql_ping(mysql_);\r\n\r\n std::ostringstream oss;\r\n oss << std::this_thread::get_id();\r\n std::string thread_id = oss.str();\r\n LOG_WARN(\"timer mysql_ping:{}, this_thread_id:{}\", ping_ret, thread_id);\r\n\r\n if (ping_ret != 0) {\r\n pair_error_.first = mysql_errno(mysql_);\r\n pair_error_.second = mysql_error(mysql_);\r\n LOG_ERROR(\"mysql_errno:{}, mysql_error:{}\", pair_error_.first, pair_error_.second);\r\n }\r\n },\r\n cron_timer::TimerMgr::RUN_FOREVER);\r\n}\r\n<\/pre>\n <\/p>\n
\u8fd9\u6837\u4e5f\u4e0d\u4f1a\u56e0\u4e3amysql_ping()\u7684\u64cd\u4f5c\u5f71\u54cd\u5230\u67e5\u8be2\u7ed3\u679c\u3002<\/p>\n
<\/p>\n
\u6628\u5929\u60f3\u91cd\u73b0\u4e00\u4e0bbug\uff0c\u7136\u540e\u786e\u8ba4\u4e0b\u662f\u5426\u5b8c\u7f8e\u89e3\u51b3<\/p>\n
\u4e8e\u662f\u627e\u4e86\u4e00\u53f0mariadb\u6570\u636e\u5e93\uff08\u624b\u8fb9\u6ca1\u6709mysql\uff09\uff0c\u767b\u9646\u4e0a\u53bb\u67e5\u8be2\u4e86\u4e0b\u53c2\u6570\uff1a<\/p>\n
MariaDB [(none)]> show variables like '%timeout%';\r\n+---------------------------------------+----------+\r\n| Variable_name | Value |\r\n+---------------------------------------+----------+\r\n| connect_timeout | 10 |\r\n| deadlock_timeout_long | 50000000 |\r\n| deadlock_timeout_short | 10000 |\r\n| delayed_insert_timeout | 300 |\r\n| idle_readonly_transaction_timeout | 0 |\r\n| idle_transaction_timeout | 0 |\r\n| idle_write_transaction_timeout | 0 |\r\n| innodb_flush_log_at_timeout | 1 |\r\n| innodb_lock_wait_timeout | 50 |\r\n| innodb_rollback_on_timeout | OFF |\r\n| interactive_timeout | 28800 |\r\n| lock_wait_timeout | 86400 |\r\n| net_read_timeout | 30 |\r\n| net_write_timeout | 60 |\r\n| rpl_semi_sync_master_timeout | 10000 |\r\n| rpl_semi_sync_slave_kill_conn_timeout | 5 |\r\n| slave_net_timeout | 60 |\r\n| thread_pool_idle_timeout | 60 |\r\n| wait_timeout | 28800 |\r\n+---------------------------------------+----------+\r\n19 rows in set (0.002 sec)\r\n<\/pre>\n <\/p>\n
\u5176\u4e2d\u6709\u4e24\u4e2a\u53c2\u6570interactive_timeout\u548cwait_timeout\uff0c\u8fd9\u4e24\u4e2a\u53c2\u6570\u7528\u6765\u63a7\u5236\u8d85\u65f6\u65f6\u95f4\u7684\uff0c\u9ed8\u8ba4\u662f8\u5c0f\u65f6\uff0828800\u79d2\uff09<\/p>\n
\u52a8\u624b\u4fee\u6539<\/p>\n
cd \/etc\/mysql\/mariadb.conf.d\r\nvim 50-server.cnf\r\n\r\n\u589e\u52a0\u4e0b\u9762\u4e24\u9879\uff0c\u7f3a\u7701\u6ca1\u6709\u7684\r\nwait_timeout=60\r\ninteractive_timeout=60\r\n\r\n:wq\r\n\r\n\u91cd\u542fmysql\u670d\u52a1\r\nsudo systemctl restart mysqld\r\n<\/pre>\n <\/p>\n
\u7136\u540e\u786e\u8ba4\u4e0b\u5df2\u7ecf\u751f\u6548\uff1a<\/p>\n
MariaDB [mysql]> show variables like '%timeout%';\r\n+---------------------------------------+----------+\r\n| Variable_name | Value |\r\n+---------------------------------------+----------+\r\n| connect_timeout | 10 |\r\n| deadlock_timeout_long | 50000000 |\r\n| deadlock_timeout_short | 10000 |\r\n| delayed_insert_timeout | 300 |\r\n| idle_readonly_transaction_timeout | 0 |\r\n| idle_transaction_timeout | 0 |\r\n| idle_write_transaction_timeout | 0 |\r\n| innodb_flush_log_at_timeout | 1 |\r\n| innodb_lock_wait_timeout | 50 |\r\n| innodb_rollback_on_timeout | OFF |\r\n| interactive_timeout | 60 |\r\n| lock_wait_timeout | 86400 |\r\n| net_read_timeout | 30 |\r\n| net_write_timeout | 60 |\r\n| rpl_semi_sync_master_timeout | 10000 |\r\n| rpl_semi_sync_slave_kill_conn_timeout | 5 |\r\n| slave_net_timeout | 60 |\r\n| thread_pool_idle_timeout | 60 |\r\n| wait_timeout | 60 |\r\n+---------------------------------------+----------+\r\n19 rows in set (0.002 sec)<\/pre>\n <\/p>\n
\u7136\u540e\u5f00\u59cb\u90e8\u7f72\u5e94\u7528\u4e0a\u53bb\u9a8c\u8bc1\uff1a<\/p>\n
\u9996\u5148\u6765\u4e00\u4e2a\u6ca1\u6709\u8bbe\u7f6e\u91cd\u8fde\u9009\u9879\u7684\uff0c90\u79d2\u949f\u4e4b\u540e\u6267\u884cmysql_ping()\uff0c\u679c\u7136\u51fa\u73b0\u4e86\u8fde\u63a5\u65ad\u5f00\u7684\u60c5\u51b5<\/p>\n
[2021-06-11 10:44:01.987][warning] [mysql_db.cpp:95 operator()] timer mysql_ping:-1, this_thread_id:139682938480384\r\n[2021-06-11 10:44:01.987][error] [mysql_db.cpp:100 operator()] mysql_errno:2006, mysql_error:MySQL server has gone away<\/pre>\n\u4e0d\u8fc7\u8fd9\u6b21\u5374\u662f2006\u7684\u9519\u8bef\uff0c\u5e76\u4e0d\u662f2013\u3002\u7acb\u523b\u60f3\u5230\u4e0d\u662f\u8d85\u65f6\u65ad\u5f00\u7684\u95ee\u9898\uff0c\u8054\u7cfb\u817e\u8baf\u4e91\u7684\u8fd0\u7ef4\uff0c\u4ed6\u4eec\u5f88\u723d\u5feb\u7684\u63a5\u4e86\u8fd9\u4e2abug\uff0c\u5e76\u8bf4\u6700\u8fd1\u51e0\u5929\u8fd9\u4e0d\u662f\u4e2a\u6848\uff0c\u4ed6\u9700\u8981\u4e00\u70b9\u65f6\u95f4\u8c03\u4e0b\u53c2\u6570\u3002\u8c8c\u4f3c\u8fd9\u95ee\u9898\u89e3\u51b3\u4e86\u3002\u4e0d\u8fc7\u6211\u8fd8\u60f3\u770b\u4e0bmysql\u7684\u91cd\u8fde\u903b\u8f91\uff0c\u4e8e\u662f\u7ee7\u7eed\u3002<\/p>\n
<\/p>\n
\u5982\u679c\u4e0d\u8bbe\u7f6e\u91cd\u8fde\u9009\u9879\uff0c\u572860\u79d2\u5185\u4e0d\u65ad\u7684\u8c03\u7528mysql_ping()<\/p>\n
char opt_val = 1; \r\nmysql_options(mysql_, MYSQL_OPT_RECONNECT, &opt_val);<\/pre>\n\u5b9e\u6d4b\u80fd\u591f\u4fdd\u6301\u8fde\u63a5\uff0c\u5f53\u7136\u572890\u79d2\u949f\u7684\u65f6\u5019\u8c03\u7528\u5c31\u4f1a\u53d1\u73b0\u8fde\u63a5\u65ad\u4e86\u3002<\/p>\n
<\/p>\n
\u5982\u679c\u8bbe\u7f6e\u4e86\u91cd\u8fde\u9009\u9879\uff0cmysql_ping()\u4e0d\u7ba1\u662f60\u79d2\u4ee5\u5185\u6267\u884c\uff0c\u8fd8\u662f90\u79d2\u610f\u5916\u6267\u884c\u90fd\u662f\u6ca1\u95ee\u9898\u7684\uff08\u81ea\u52a8\u91cd\u8fde\u529f\u80fd\u5df2\u7ecf\u751f\u6548\uff09<\/p>\n
\u5b9e\u6d4b\u4e0d\u8c03\u7528mysql_ping()\uff0c\u8c03\u7528mysql_real_query()\u4e5f\u80fd\u6210\u529f\u91cd\u8fde\u4e0a\u3002<\/p>\n
\u6240\u4ee5\uff0c\u5982\u679c\u4f60\u60f3\u83b7\u5f97\u91cd\u8fde\u529f\u80fd\uff0c\u8bbe\u7f6e\u597d\u91cd\u8fde\u9009\u9879\u5c31\u597d\u4e86\uff08\u53ef\u4ee5\u4e0d\u8c03\u7528mysql_ping()\uff09\uff0c\u81f3\u6b64\u95ee\u9898\u5b8c\u7f8e\u89e3\u51b3\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u9996\u5148\u662f\u5ba2\u6237\u90a3\u8fb9\u5728\u817e\u8baf\u4e91\u4e0a\u7684\u670d\u52a1\u62a5\u544a\u4e86\u201cm<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"colormag_page_container_layout":"default_layout","colormag_page_sidebar_layout":"default_layout","footnotes":""},"categories":[],"tags":[118],"class_list":["post-5318","post","type-post","status-publish","format-standard","hentry","tag-02-"],"_links":{"self":[{"href":"https:\/\/i007.cc\/wordpress\/wp-json\/wp\/v2\/posts\/5318","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/i007.cc\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/i007.cc\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/i007.cc\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/i007.cc\/wordpress\/wp-json\/wp\/v2\/comments?post=5318"}],"version-history":[{"count":0,"href":"https:\/\/i007.cc\/wordpress\/wp-json\/wp\/v2\/posts\/5318\/revisions"}],"wp:attachment":[{"href":"https:\/\/i007.cc\/wordpress\/wp-json\/wp\/v2\/media?parent=5318"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/i007.cc\/wordpress\/wp-json\/wp\/v2\/categories?post=5318"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/i007.cc\/wordpress\/wp-json\/wp\/v2\/tags?post=5318"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}