Uploaded image for project: 'MariaDB Connector/C'
  1. MariaDB Connector/C
  2. CONC-589

First query after connection was lost fails when auto-reconnect enabled

Details

    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

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. CONC-760 valid named pipe connection is closed, if reconnect is set

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            georg Georg Richter added a comment -

            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?

            georg Georg Richter added a comment - Not throwing an error might lead to inconsistencies. Consider the following cases: Non transactional: 1 create table t1(a int); 2. set @a:=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 > ? 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?
            FredyH Frederik Haselmeier added a comment - - edited

            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.

            FredyH Frederik Haselmeier added a comment - - edited 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.

            People

              georg Georg Richter
              FredyH Frederik Haselmeier
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.