Why 'Lost connection to MySQL server during query' errors occur time-to-time (timeouts raised, pinging before query)

581 Views Asked by At

I'm creating an experiment, in which the server side is a libuv based C/C++ application that runs queries on a mysql server (it's on localhost).

In most cases it just works, but sometimes I get 'Lost connection to MySQL server during query' in various places in the process.

I have tried raising all timeouts. But I think this is unrelated, if the server gets bombarded with requests (like every second) the same error gets thrown.

+-------------------------------------+----------+
| Variable_name                       | Value    |
+-------------------------------------+----------+
| connect_timeout                     | 31536000 |
| 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                 | 31536000 |
| lock_wait_timeout                   | 31536000 |
| net_read_timeout                    | 31536000 |
| net_write_timeout                   | 31536000 |
| slave_net_timeout                   | 31536000 |
| thread_pool_idle_timeout            | 60       |
| wait_timeout                        | 31536000 |
+-------------------------------------+----------+

I'm pinging the server before doing queries.

    // this->con was set up somewhere before, just like down below in the retry section

    char query[] = "SELECT * FROM data WHERE id = 12;";

    mysql_ping(this->con);
    if(!mysql_query(this->con, query)) {
        // OK
        return 0;
    } else {
        // reconnect usually helps
        // here I get the error message
        mysql_close(this->con);
        this->con = mysql_init(NULL);
        if(mysql_real_connect(this->con, this->host.c_str(), this->user.c_str(), this->password.c_str(), this->db.c_str(), 0, NULL, 0) == NULL) {
            // no DB, goodbye
            exit(6);
        }
        // retry
        if(!mysql_query(this->con, query)) {
            return 0;
        } else {
            // DB fail
            return 1;
        }
    }

I have tried the reconnect option, the problem is the same.

In my understanding this flow should be possible with single-threaded libuv and mysql:

1. set up db connection
2. run event loop
    -> make queries based on IO events, get results
3. event loop ends
4. db close

What do I miss?

0

There are 0 best solutions below