[CONC-589] First query after connection was lost fails when auto-reconnect enabled Created: 2022-04-19  Updated: 2022-04-20

Status: Open
Project: MariaDB Connector/C
Component/s: Other
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Frederik Haselmeier Assignee: Georg Richter
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The reconnect feature that can be enabled by setting MYSQL_OPT_RECONNECT to true seems to always fail the first query after the connection was lost with the error "Lost connection to server during query".
The second query that runs after the connection succeeds and the connection is re-established.

The expected behaviour (which is also the behaviour of libmysql) would be for the connection to be re-established without any query failing.

The MySQL documentation specifically mentions that any queries that are sent after the connection was lost will be retried if auto-reconnect is enabled but I could not find any documentation like this for the MariaDB C Connector.

Here is a small piece of code that can be used to replicate the issue. It requires the MySQL/MariaDB server to be restarted within the 10 seconds waiting period to demonstrate the issue.

#include <iostream>
 
#include <thread>
 
#include <chrono>
 
 
 
#include <mysql/mysql.h>
 
 
 
using namespace std::chrono_literals;
 
 
 
void runQuery(MYSQL *sql) {
 
    std::string queryStr = "SELECT 1";
 
    MYSQL_RES *resultSet;
 
    if (mysql_real_query(sql, queryStr.c_str(), queryStr.size()) ||
 
        (resultSet = mysql_store_result(sql)) == nullptr) {
 
        std::cout << "Query failed with error: " << mysql_error(sql) << std::endl;
 
    } else {
 
        mysql_free_result(resultSet);
 
        std::cout << "Query finished successfully" << std::endl;
 
    }
 
}
 
 
 
int main() {
 
    my_bool myAutoReconnectBool = '1';
 
 
 
    auto sql = mysql_init(nullptr);
 
    mysql_options(sql, MYSQL_OPT_RECONNECT, &myAutoReconnectBool);
 
    if (!mysql_real_connect(sql, "127.0.0.1", "root", "", "mysql", 3306, nullptr, 0)) {
 
        std::cout << mysql_error(sql) << std::endl;
 
        exit(1);
 
    }
 
    std::cout << std::endl << "Connected successfully" << std::endl;
 
 
 
    std::cout << std::endl << "Running first query" << std::endl;
 
    runQuery(sql);
 
 
 
    //Restart your mysql/mariadb server after the first query finished successfully
 
    std::cout << std::endl << "Waiting 10 seconds..." << std::endl;
 
    std::this_thread::sleep_for(10s);
 
 
 
    std::cout << std::endl << "Running second query" << std::endl;
 
    runQuery(sql);
 
 
 
    std::cout << std::endl << "Running third query" << std::endl;
 
    runQuery(sql);
 
 
 
    mysql_close(sql);
 
    return 0;
 
}

Output using libmysql:

Connected successfully
 
 
 
Running first query
 
Query finished successfully
 
 
 
Waiting 10 seconds...
 
 
 
Running second query
 
Query finished successfully
 
 
 
Running third query
 
Query finished successfully

Output using libmariadb:

Connected successfully
 
 
 
Running first query
 
Query finished successfully
 
 
 
Waiting 10 seconds...
 
 
 
Running second query
 
Query failed with error: Lost connection to server during query
 
 
 
Running third query
 
Query finished successfully



 Comments   
Comment by Georg Richter [ 2022-04-20 ]

Not throwing an error might lead to inconsistencies.

Consider the following cases:

Non transactional:
1 create table t1(a int);
2. set @a:=1

  1. drop connection here
    3. insert into t1 values (@a);

Do you really want to insert a NULL value?

Transactional:
1. BEGIN
2. UPDATE t1 SET newval=1, value=? where DATE > ?

  1. drop connection here
    3. DELETE FROM t1 where newval=0
    4. END

Does it make sense to delete rows with newval=0 when previous statements were rolled back?

Comment by Frederik Haselmeier [ 2022-04-20 ]

While your example will break when using auto-reconnect, this is also explicitly stated by the MySQL documentation.
Auto-reconnect is almost certainly only useful in cases where one does not have stateful queries, such as in the example I posted above. This is also why the option is disabled by default.

If auto-reconnect fails the first query, then this feature is almost entirely useless. After running any networked command I will have to manually check if the connection was lost anyways and then retry if applicable, in which case I could also just add the one additional line of running mariadb_reconnect.

I just want to point out again that libmysql (at least the version I tested, 6.1.11) does not fail the first query, which is the behaviour I would expect from the MySQL documentation. Unfortunately there is seemingly no documentation for what MariaDB does when an auto-reconnect happens so I assumed it would work the same as with MySQL.

Generated at Thu Feb 08 03:06:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.