1

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?

user3853774
  • 11
  • 4
  • Why don't you check the return code of mysql_ping()? If you set reconnect via mysql_options() api function, mysql_ping() will reconnect automatically. – Georg Richter Sep 04 '19 at 18:53
  • What is the value of `autocommit`? Are there "transactions"? – Rick James Sep 04 '19 at 21:09
  • @GeorgRichter my initial connection method sets up MYSQL_OPT_RECONNECT with the value of 1, so I think mysql_ping() should try to reconnect. I'm logging now the return code of it. – user3853774 Sep 06 '19 at 11:47
  • @RickJames Autocommit is on. My application is uses SELECT, INSERT, UPDATE statements, nothing fancy. – user3853774 Sep 06 '19 at 11:58
  • Do you use MySQL or MariaDB Connector/C? There is a difference, since MySQL Connector/C only tries to reconnect if error code was CR_SERVER_LOST, while MariaDB Connector/C always tries to reconnect in case of an connection error (e.g. if error code was CR_SERVER_GONE_ERROR). – Georg Richter Sep 07 '19 at 07:44
  • I use MariaDB Connector/C. Now I have logs from mysql_ping: `msg: 'Commands out of sync; you can't run this command now' error:(-1)` – user3853774 Sep 07 '19 at 09:50

0 Answers0