MariaDB 服务器在 600 秒后超时客户端连接

2022-01-15 00:00:00 sql database mariadb mysql libmysql

我的 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...
";

    // sleep for 11 minutes
    sleep(660);

    // run the query again
    if(! mysql_query(connection, "select 5"))
    {
        std::cout << "Second query succeeded after " << seconds << " seconds
";
        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
";
        }
    }

    // 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 很棘手.从 same 连接做

wait_timeout is tricky. From the same connection do

SHOW SESSION VARIABLES LIKE '%timeout%';
SHOW SESSION VARIABLES WHERE VALUE BETWEEN 500 AND 700;

您应该能够通过执行来解决此问题

You should be able to workaround the issue by executing

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/en/news-1-1-5.html(MySQL 连接器/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.

相关文章