MariaDB服务器在600秒后使客户端连接超时 [英] MariaDB server times out client connection after 600 seconds
问题描述
我的MariaDB服务器在不活动600秒(10分钟)后正在使C ++客户端(使用libmariadb)超时，我不确定为什么，因为我找不到任何配置的超时来指定该数字.
My MariaDB server is timing out my C++ client (using libmariadb) after 600 seconds (10 minutes) of inactivity, and I'm not sure why, because I can't find any configured timeouts that specify that number.
这是我的代码，在这里我执行一个简单的SELECT查询，等待11分钟，然后再次运行相同的查询，并收到服务器不可用"错误:
Here's my code, where I execute a simple SELECT query, wait 11 minutes, then run that same query again and get a "server gone" error:
#include <iostream>
#include <unistd.h>
#include <errmsg.h>
#include <mysql.h>
int main(int, char**)
{
// connect to the database
MYSQL* connection = mysql_init(NULL);
my_bool reconnect = 0;
mysql_options(connection, MYSQL_OPT_RECONNECT, &reconnect); // don't implicitly reconnect
mysql_real_connect(connection, "127.0.0.1", "testuser", "password",
"my_test_db", 3306, NULL, 0);
// run a simple query
mysql_query(connection, "select 5");
mysql_free_result(mysql_store_result(connection));
std::cout << "First query done...\n";
// sleep for 11 minutes
sleep(660);
// run the query again
if(! mysql_query(connection, "select 5"))
{
std::cout << "Second query succeeded after " << seconds << " seconds\n";
mysql_free_result(mysql_store_result(connection));
}
else
{
if(mysql_errno(connection) == CR_SERVER_GONE_ERROR)
{
// **** this happens every time ****
std::cout << "Server went away after " << seconds << " seconds\n";
}
}
// close the connection
mysql_close(connection);
connection = nullptr;
return 0;
}
服务器进程的标准输出报告说我的连接超时:
The stdout of the server process reports that it timed out my connection:
$ sudo journalctl -u mariadb
...
Jul 24 17:58:31 myhost mysqld[407]: 2018-07-24 17:58:31 139667452651264 [Warning] Aborted connection 222 to db: 'my_test_db' user: 'testuser' host: 'localhost' (Got timeout reading communication packets)
...
看看tcpdump捕获，我还可以看到服务器向客户端发送了一个TCP FIN数据包，该数据包关闭了连接.
Looking at a tcpdump capture, I can also see the server sending the client a TCP FIN packet, which closes the connection.
我感到难过的原因是因为我没有更改任何默认超时值，甚至没有600秒的默认超时值.
The reason I'm stumped is because I haven't changed any of the default timeout values, none of which are even 600 seconds:
MariaDB [(none)]> show variables like '%timeout%';
+-------------------------------------+----------+
| Variable_name | Value |
+-------------------------------------+----------+
| connect_timeout | 10 |
| deadlock_timeout_long | 50000000 |
| deadlock_timeout_short | 10000 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_print_lock_wait_timeout_info | OFF |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 28800 |
+-------------------------------------+----------+
那么服务器为什么超时了我的连接?根据文档，我本来以为是因为 wait_timeout 服务器变量，但是默认保留了8个小时...
So why is the server timing out my connection? Based on the documentation, I would have thought it would have been because of the wait_timeout server variable, but it's left at the default of 8 hours...
顺便说一句，我正在使用MariaDB 10.0和libmariadb 2.0(来自Ubuntu Xenial Universe存储库)
BTW I'm using MariaDB 10.0 and libmariadb 2.0 (from the Ubuntu Xenial Universe repo)
这是捕获断开连接的tcpdump捕获的图像.我的Wireshark过滤器是 tcp.port == 55916 ，所以我正在查看往返于此客户端连接的流量.服务器发送的FIN数据包是数据包1199，恰好在前一个数据包(884)之后600秒.
here's an image of a tcpdump capture catching the disconnect. My Wireshark filter is tcp.port == 55916, so I'm looking at traffic to/from this one client connection. The FIN packet that the server sends is packet 1199, exactly 600 seconds after the previous packet (884).
推荐答案
wait_timeout 很棘手.通过相同连接执行
wait_timeout is tricky. From the same connection do
SHOW SESSION VARIABLES LIKE '%timeout%';
SHOW SESSION VARIABLES WHERE VALUE BETWEEN 500 AND 700;
您应该能够通过执行
mysql_query("SET @@wait_timeout = 22222");
您是否以"root"身份连接?
Are you connected as 'root' or not?
更多连接器详细信息:
请参见: https://dev.mysql.com/doc/refman/5.5/en/mysql-options.html
CLIENT_INTERACTIVE:在关闭连接之前，允许Interactive_timeout秒处于非活动状态(而不是wait_timeout秒).客户端的会话wait_timeout变量设置为会话Interactive_timeout变量的值.
CLIENT_INTERACTIVE: Permit interactive_timeout seconds of inactivity (rather than wait_timeout seconds) before closing the connection. The client's session wait_timeout variable is set to the value of the session interactive_timeout variable.
https://dev.mysql.com/doc/relnotes/connector-cpp/zh-CN/news-1-1-5.html (MySQL Connector/C ++ 1.1.5)
https://dev.mysql.com/doc/relnotes/connector-cpp/en/news-1-1-5.html (MySQL Connector/C++ 1.1.5)
还可以使用MySQL_Statement :: getQueryTimeout()和MySQL_Statement :: setQueryTimeout()方法获取并设置语句的执行时间限制.
It is also possible to get and set the statement execution-time limit using the MySQL_Statement::getQueryTimeout() and MySQL_Statement::setQueryTimeout() methods.
可能还会有TCP/IP超时.
There may also be a TCP/IP timeout.
这篇关于MariaDB服务器在600秒后使客户端连接超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
