MariaDB server times out client connection after 600 seconds

villapx picture villapx · Jul 24, 2018 · Viewed 10.3k times · Source

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.

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)
...

Looking at a tcpdump capture, I can also see the server sending the client a TCP FIN packet, which closes the connection.

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    |
+-------------------------------------+----------+

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...

BTW I'm using MariaDB 10.0 and libmariadb 2.0 (from the Ubuntu Xenial Universe repo)


Edit: 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). pcap opened in wireshark

Answer

Rick James picture Rick James · Jul 25, 2018

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");

Are you connected as 'root' or not?

More connector details:

See: https://dev.mysql.com/doc/refman/5.5/en/mysql-options.html

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 Connector/C++ 1.1.5)

It is also possible to get and set the statement execution-time limit using the MySQL_Statement::getQueryTimeout() and MySQL_Statement::setQueryTimeout() methods.

There may also be a TCP/IP timeout.