Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Qt Mysql [Mysql Server has gone away] and Silent failures after query exec()
Forum Update on Tuesday, May 27th 2025

Qt Mysql [Mysql Server has gone away] and Silent failures after query exec()

Scheduled Pinned Locked Moved Unsolved General and Desktop
qt5.15.2mysqlcppqt c++mysql server
10 Posts 3 Posters 156 Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M Offline
    M Offline
    mvsri
    wrote last edited by
    #1

    Hello everyone,

    I'm working with Qt 5.15.2 (MinGW 64-bit) and MySQL 8.0 on Windows 10, and I’ve implemented a custom DatabaseConnector class to manage database interactions. Below is a simplified version of the class:

    #include "databaseconnector.h"
    
    DatabaseConnector::DatabaseConnector(QString databaseName,
                                         QString userName,
                                         QString password,
                                         QString connectionName,
                                         QObject *parent)
        : QObject{parent}
    {
        if(QSqlDatabase::contains(connectionName)) {
            QSqlDatabase::removeDatabase(connectionName);
        }
        db2 = QSqlDatabase::addDatabase("QMYSQL", connectionName);
        db2.setHostName("localhost");
        db2.setDatabaseName(databaseName);
        db2.setUserName(userName);
        db2.setPassword(password);
        db2.setConnectOptions("MYSQL_OPT_RECONNECT=1"); // reconnect mysql if its get disconnected
        if(!db2.open())
            qWarning() << "Database Connection Opening Error " << db2.lastError().text();
    }
    
    DatabaseConnector::~DatabaseConnector()
    {
        //qDebug() << "==> Database Connector Destructor " << db2.connectionName();
        QString connection;
        if(db2.isOpen())
        {
            connection = db2.connectionName();
            db2.close();
            db2 = QSqlDatabase();
            QSqlDatabase::removeDatabase(connection);
        }
    }
    
    bool DatabaseConnector::databaseOpen()
    {
        return db2.open();
    }
    
    QSqlQuery DatabaseConnector::executeQuery(const QString &query)
    {
        if(!ensureOpen()) {
            return QSqlQuery();
        }
    
        QSqlQuery queryExec(db2);
    
        if(!queryExec.exec(query)) {
            QString err = queryExec.lastError().text();
            qWarning() << "[executeQuery] Query failed:" << err;
            if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                err.contains("lost connection", Qt::CaseInsensitive)) {
                qWarning() << "[executeQuery] Lost connection. Attempting reconnect...";
                db2.close();
    
                if (db2.open()) {
                    qInfo() << "[executeQuery] Reconnected to DB. Retrying query...";
    
                    QSqlQuery retryQuery(db2);
                    if (!retryQuery.exec(query)) {
                        qWarning() << "[executeQuery] Retry failed:" << retryQuery.lastError().text();
                    }
                    return retryQuery;
                } else {
                    qWarning() << "[executeQuery] Reconnect failed:" << db2.lastError().text();
                    return QSqlQuery();
                }
            }
        }
    
        return queryExec;
    }
    
    bool DatabaseConnector::executeSingleQuery(const QString &query)
    {
        if(!ensureOpen()) {
            return false;
        }
    
        QSqlQuery queryExec(db2);
    
        if(!queryExec.exec(query)) {
            qWarning() << "Failed to execute query: " << query << ", Error: " << queryExec.lastError().text();
            QString err = queryExec.lastError().text();
            if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                err.contains("lost connection", Qt::CaseInsensitive)) {
                db2.close();
                if(!db2.open()){
                    qWarning() << "Database Connection Opening Error - 2" << db2.lastError().text();
                }
    
                // retry logic
                QSqlQuery retryQuery(db2);
                if (!retryQuery.exec(query)) {
                    qWarning() << "Retry failed: " << retryQuery.lastError().text();
                    return false;
                }
                qDebug() << "==> Database is Gone and reoppend";
                return true;
            }
            return false;
        }
        return true;
    }
    
    bool DatabaseConnector::ensureOpen()
    {
        if(!db2.isOpen()) {
            if(!db2.open()) {
                qWarning() << "==> Failed to re-open db: " << db2.lastError().text();
                return false;
            }
        }
        return true;
    }
    
    

    This class is working as expected without any errors and all, but what i have noticed is when the application is running for long hours like 10+ hours any statements like update or insert doesn't work.
    when i checked the log file of the application i got this

    Lost connection to MySQL server during query QMYSQL: Unable to execute query" (:0, )
    

    and after that for subsequent statements i got this:

    MySQL server has gone away QMYSQL: Unable to execute query" (:0, )
    

    After observing these messages in the logs:

    Lost connection to MySQL server during query
    
    MySQL server has gone away
    

    I added reconnection logic in the executeQuery and executeSingleQuery functions to handle such scenarios. Sample code below

    QString err = queryExec.lastError().text();
            if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                err.contains("lost connection", Qt::CaseInsensitive)) {
                db2.close();
                if(!db2.open()){
                    qWarning() << "Database Connection Opening Error - 2" << db2.lastError().text();
                }
    

    Interestingly, another class in the same application—used for audit logs—continues to work without issues. It uses a separate connection name and writes to MySQL via QtConcurrent::run.

    #include "auditlogger.h"
    
    
    AuditLogger &AuditLogger::instance()
    {
        static AuditLogger instance;
        return instance;
    }
    
    void AuditLogger::log(const QString &username, int userLevel, const QString &reason)
    {
        QtConcurrent::run(this, &AuditLogger::peformLog, username, userLevel, reason);
    }
    
    AuditLogger::AuditLogger()
    {
        db = QSqlDatabase::addDatabase("QMYSQL", connectionName);
        db.setHostName("localhost");
        db.setDatabaseName(databasename);
        db.setUserName(username);
        db.setPassword(password);
    
        if(!db.open()) {
            qCritical() << "==> Failed to connect to database in Audit Log: " << db.lastError().text();
        }
    }
    
    AuditLogger::~AuditLogger()
    {
        db.close();
        QSqlDatabase::removeDatabase(connectionName);
    }
    
    void AuditLogger::peformLog(const QString &username, int userLevel, const QString &reason)
    {
        QMutexLocker locker(&mutex); 
    
        //qDebug() << "==> User Type: " << QThread::currentThreadId();
        QSqlQuery query(db);
        query.prepare("insert into AuditTrails(userName, userLevel, reason) values(:username, :userlvl, :reason)");
        query.bindValue(":username", username);
        query.bindValue(":userlvl", userLevel);
        query.bindValue(":reason", reason);
    
        if(!query.exec()) {
            qCritical() << "Failed to insert data in audit trail log: " << query.lastError().text();
        }
    }
    
    

    I’m using three DatabaseConnector instances, each with a different connection name (one per UI screen). Could that be contributing to instability? What could be the cause of Mysql Server gone away?

    Apart from that, is it possible that a QSqlQuery::exec() call returns true but does not actually affect the database in mysql (e.g., an INSERT appears to work, but nothing is written)?

    Any advice or shared experience would be appreciated. Thank you!

    Note: Does antivirus or Group Admin/Network Policies in windows 10 affect this type of connections?
    wait_timeout in my.ini is also changed to 2 days.

    jsulmJ JonBJ 3 Replies Last reply
    0
    • M mvsri

      Hello everyone,

      I'm working with Qt 5.15.2 (MinGW 64-bit) and MySQL 8.0 on Windows 10, and I’ve implemented a custom DatabaseConnector class to manage database interactions. Below is a simplified version of the class:

      #include "databaseconnector.h"
      
      DatabaseConnector::DatabaseConnector(QString databaseName,
                                           QString userName,
                                           QString password,
                                           QString connectionName,
                                           QObject *parent)
          : QObject{parent}
      {
          if(QSqlDatabase::contains(connectionName)) {
              QSqlDatabase::removeDatabase(connectionName);
          }
          db2 = QSqlDatabase::addDatabase("QMYSQL", connectionName);
          db2.setHostName("localhost");
          db2.setDatabaseName(databaseName);
          db2.setUserName(userName);
          db2.setPassword(password);
          db2.setConnectOptions("MYSQL_OPT_RECONNECT=1"); // reconnect mysql if its get disconnected
          if(!db2.open())
              qWarning() << "Database Connection Opening Error " << db2.lastError().text();
      }
      
      DatabaseConnector::~DatabaseConnector()
      {
          //qDebug() << "==> Database Connector Destructor " << db2.connectionName();
          QString connection;
          if(db2.isOpen())
          {
              connection = db2.connectionName();
              db2.close();
              db2 = QSqlDatabase();
              QSqlDatabase::removeDatabase(connection);
          }
      }
      
      bool DatabaseConnector::databaseOpen()
      {
          return db2.open();
      }
      
      QSqlQuery DatabaseConnector::executeQuery(const QString &query)
      {
          if(!ensureOpen()) {
              return QSqlQuery();
          }
      
          QSqlQuery queryExec(db2);
      
          if(!queryExec.exec(query)) {
              QString err = queryExec.lastError().text();
              qWarning() << "[executeQuery] Query failed:" << err;
              if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                  err.contains("lost connection", Qt::CaseInsensitive)) {
                  qWarning() << "[executeQuery] Lost connection. Attempting reconnect...";
                  db2.close();
      
                  if (db2.open()) {
                      qInfo() << "[executeQuery] Reconnected to DB. Retrying query...";
      
                      QSqlQuery retryQuery(db2);
                      if (!retryQuery.exec(query)) {
                          qWarning() << "[executeQuery] Retry failed:" << retryQuery.lastError().text();
                      }
                      return retryQuery;
                  } else {
                      qWarning() << "[executeQuery] Reconnect failed:" << db2.lastError().text();
                      return QSqlQuery();
                  }
              }
          }
      
          return queryExec;
      }
      
      bool DatabaseConnector::executeSingleQuery(const QString &query)
      {
          if(!ensureOpen()) {
              return false;
          }
      
          QSqlQuery queryExec(db2);
      
          if(!queryExec.exec(query)) {
              qWarning() << "Failed to execute query: " << query << ", Error: " << queryExec.lastError().text();
              QString err = queryExec.lastError().text();
              if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                  err.contains("lost connection", Qt::CaseInsensitive)) {
                  db2.close();
                  if(!db2.open()){
                      qWarning() << "Database Connection Opening Error - 2" << db2.lastError().text();
                  }
      
                  // retry logic
                  QSqlQuery retryQuery(db2);
                  if (!retryQuery.exec(query)) {
                      qWarning() << "Retry failed: " << retryQuery.lastError().text();
                      return false;
                  }
                  qDebug() << "==> Database is Gone and reoppend";
                  return true;
              }
              return false;
          }
          return true;
      }
      
      bool DatabaseConnector::ensureOpen()
      {
          if(!db2.isOpen()) {
              if(!db2.open()) {
                  qWarning() << "==> Failed to re-open db: " << db2.lastError().text();
                  return false;
              }
          }
          return true;
      }
      
      

      This class is working as expected without any errors and all, but what i have noticed is when the application is running for long hours like 10+ hours any statements like update or insert doesn't work.
      when i checked the log file of the application i got this

      Lost connection to MySQL server during query QMYSQL: Unable to execute query" (:0, )
      

      and after that for subsequent statements i got this:

      MySQL server has gone away QMYSQL: Unable to execute query" (:0, )
      

      After observing these messages in the logs:

      Lost connection to MySQL server during query
      
      MySQL server has gone away
      

      I added reconnection logic in the executeQuery and executeSingleQuery functions to handle such scenarios. Sample code below

      QString err = queryExec.lastError().text();
              if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                  err.contains("lost connection", Qt::CaseInsensitive)) {
                  db2.close();
                  if(!db2.open()){
                      qWarning() << "Database Connection Opening Error - 2" << db2.lastError().text();
                  }
      

      Interestingly, another class in the same application—used for audit logs—continues to work without issues. It uses a separate connection name and writes to MySQL via QtConcurrent::run.

      #include "auditlogger.h"
      
      
      AuditLogger &AuditLogger::instance()
      {
          static AuditLogger instance;
          return instance;
      }
      
      void AuditLogger::log(const QString &username, int userLevel, const QString &reason)
      {
          QtConcurrent::run(this, &AuditLogger::peformLog, username, userLevel, reason);
      }
      
      AuditLogger::AuditLogger()
      {
          db = QSqlDatabase::addDatabase("QMYSQL", connectionName);
          db.setHostName("localhost");
          db.setDatabaseName(databasename);
          db.setUserName(username);
          db.setPassword(password);
      
          if(!db.open()) {
              qCritical() << "==> Failed to connect to database in Audit Log: " << db.lastError().text();
          }
      }
      
      AuditLogger::~AuditLogger()
      {
          db.close();
          QSqlDatabase::removeDatabase(connectionName);
      }
      
      void AuditLogger::peformLog(const QString &username, int userLevel, const QString &reason)
      {
          QMutexLocker locker(&mutex); 
      
          //qDebug() << "==> User Type: " << QThread::currentThreadId();
          QSqlQuery query(db);
          query.prepare("insert into AuditTrails(userName, userLevel, reason) values(:username, :userlvl, :reason)");
          query.bindValue(":username", username);
          query.bindValue(":userlvl", userLevel);
          query.bindValue(":reason", reason);
      
          if(!query.exec()) {
              qCritical() << "Failed to insert data in audit trail log: " << query.lastError().text();
          }
      }
      
      

      I’m using three DatabaseConnector instances, each with a different connection name (one per UI screen). Could that be contributing to instability? What could be the cause of Mysql Server gone away?

      Apart from that, is it possible that a QSqlQuery::exec() call returns true but does not actually affect the database in mysql (e.g., an INSERT appears to work, but nothing is written)?

      Any advice or shared experience would be appreciated. Thank you!

      Note: Does antivirus or Group Admin/Network Policies in windows 10 affect this type of connections?
      wait_timeout in my.ini is also changed to 2 days.

      jsulmJ Offline
      jsulmJ Offline
      jsulm
      Lifetime Qt Champion
      wrote last edited by
      #2

      @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

      What could be the cause of Mysql Server gone away?

      Did you check MySQL server logs?

      https://forum.qt.io/topic/113070/qt-code-of-conduct

      M 1 Reply Last reply
      0
      • jsulmJ jsulm

        @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

        What could be the cause of Mysql Server gone away?

        Did you check MySQL server logs?

        M Offline
        M Offline
        mvsri
        wrote last edited by
        #3

        @jsulm said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

        Did you check MySQL server logs?

        I haven't checked it yet, checking it now.

        Apart from that, do you find any issues with the code shared above?

        jsulmJ 1 Reply Last reply
        0
        • M mvsri

          @jsulm said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

          Did you check MySQL server logs?

          I haven't checked it yet, checking it now.

          Apart from that, do you find any issues with the code shared above?

          jsulmJ Offline
          jsulmJ Offline
          jsulm
          Lifetime Qt Champion
          wrote last edited by
          #4

          @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

          Apart from that, do you find any issues with the code shared above?

          I'm just wondering why you're spawning a thread everytime AuditLogger::log? QtConcurrent::run uses a thread pool, but I still would use a different approach: let a log thread run as long as the application runs and send log messages to it via signals/slots or invokeMethod.

          It is also better to use QSqlError::type() instead of searching for a string in the error message (which could change in later Qt versions). You probably get QSqlError::ConnectionError if the connection disappeared.

          https://forum.qt.io/topic/113070/qt-code-of-conduct

          M 1 Reply Last reply
          1
          • M mvsri

            Hello everyone,

            I'm working with Qt 5.15.2 (MinGW 64-bit) and MySQL 8.0 on Windows 10, and I’ve implemented a custom DatabaseConnector class to manage database interactions. Below is a simplified version of the class:

            #include "databaseconnector.h"
            
            DatabaseConnector::DatabaseConnector(QString databaseName,
                                                 QString userName,
                                                 QString password,
                                                 QString connectionName,
                                                 QObject *parent)
                : QObject{parent}
            {
                if(QSqlDatabase::contains(connectionName)) {
                    QSqlDatabase::removeDatabase(connectionName);
                }
                db2 = QSqlDatabase::addDatabase("QMYSQL", connectionName);
                db2.setHostName("localhost");
                db2.setDatabaseName(databaseName);
                db2.setUserName(userName);
                db2.setPassword(password);
                db2.setConnectOptions("MYSQL_OPT_RECONNECT=1"); // reconnect mysql if its get disconnected
                if(!db2.open())
                    qWarning() << "Database Connection Opening Error " << db2.lastError().text();
            }
            
            DatabaseConnector::~DatabaseConnector()
            {
                //qDebug() << "==> Database Connector Destructor " << db2.connectionName();
                QString connection;
                if(db2.isOpen())
                {
                    connection = db2.connectionName();
                    db2.close();
                    db2 = QSqlDatabase();
                    QSqlDatabase::removeDatabase(connection);
                }
            }
            
            bool DatabaseConnector::databaseOpen()
            {
                return db2.open();
            }
            
            QSqlQuery DatabaseConnector::executeQuery(const QString &query)
            {
                if(!ensureOpen()) {
                    return QSqlQuery();
                }
            
                QSqlQuery queryExec(db2);
            
                if(!queryExec.exec(query)) {
                    QString err = queryExec.lastError().text();
                    qWarning() << "[executeQuery] Query failed:" << err;
                    if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                        err.contains("lost connection", Qt::CaseInsensitive)) {
                        qWarning() << "[executeQuery] Lost connection. Attempting reconnect...";
                        db2.close();
            
                        if (db2.open()) {
                            qInfo() << "[executeQuery] Reconnected to DB. Retrying query...";
            
                            QSqlQuery retryQuery(db2);
                            if (!retryQuery.exec(query)) {
                                qWarning() << "[executeQuery] Retry failed:" << retryQuery.lastError().text();
                            }
                            return retryQuery;
                        } else {
                            qWarning() << "[executeQuery] Reconnect failed:" << db2.lastError().text();
                            return QSqlQuery();
                        }
                    }
                }
            
                return queryExec;
            }
            
            bool DatabaseConnector::executeSingleQuery(const QString &query)
            {
                if(!ensureOpen()) {
                    return false;
                }
            
                QSqlQuery queryExec(db2);
            
                if(!queryExec.exec(query)) {
                    qWarning() << "Failed to execute query: " << query << ", Error: " << queryExec.lastError().text();
                    QString err = queryExec.lastError().text();
                    if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                        err.contains("lost connection", Qt::CaseInsensitive)) {
                        db2.close();
                        if(!db2.open()){
                            qWarning() << "Database Connection Opening Error - 2" << db2.lastError().text();
                        }
            
                        // retry logic
                        QSqlQuery retryQuery(db2);
                        if (!retryQuery.exec(query)) {
                            qWarning() << "Retry failed: " << retryQuery.lastError().text();
                            return false;
                        }
                        qDebug() << "==> Database is Gone and reoppend";
                        return true;
                    }
                    return false;
                }
                return true;
            }
            
            bool DatabaseConnector::ensureOpen()
            {
                if(!db2.isOpen()) {
                    if(!db2.open()) {
                        qWarning() << "==> Failed to re-open db: " << db2.lastError().text();
                        return false;
                    }
                }
                return true;
            }
            
            

            This class is working as expected without any errors and all, but what i have noticed is when the application is running for long hours like 10+ hours any statements like update or insert doesn't work.
            when i checked the log file of the application i got this

            Lost connection to MySQL server during query QMYSQL: Unable to execute query" (:0, )
            

            and after that for subsequent statements i got this:

            MySQL server has gone away QMYSQL: Unable to execute query" (:0, )
            

            After observing these messages in the logs:

            Lost connection to MySQL server during query
            
            MySQL server has gone away
            

            I added reconnection logic in the executeQuery and executeSingleQuery functions to handle such scenarios. Sample code below

            QString err = queryExec.lastError().text();
                    if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                        err.contains("lost connection", Qt::CaseInsensitive)) {
                        db2.close();
                        if(!db2.open()){
                            qWarning() << "Database Connection Opening Error - 2" << db2.lastError().text();
                        }
            

            Interestingly, another class in the same application—used for audit logs—continues to work without issues. It uses a separate connection name and writes to MySQL via QtConcurrent::run.

            #include "auditlogger.h"
            
            
            AuditLogger &AuditLogger::instance()
            {
                static AuditLogger instance;
                return instance;
            }
            
            void AuditLogger::log(const QString &username, int userLevel, const QString &reason)
            {
                QtConcurrent::run(this, &AuditLogger::peformLog, username, userLevel, reason);
            }
            
            AuditLogger::AuditLogger()
            {
                db = QSqlDatabase::addDatabase("QMYSQL", connectionName);
                db.setHostName("localhost");
                db.setDatabaseName(databasename);
                db.setUserName(username);
                db.setPassword(password);
            
                if(!db.open()) {
                    qCritical() << "==> Failed to connect to database in Audit Log: " << db.lastError().text();
                }
            }
            
            AuditLogger::~AuditLogger()
            {
                db.close();
                QSqlDatabase::removeDatabase(connectionName);
            }
            
            void AuditLogger::peformLog(const QString &username, int userLevel, const QString &reason)
            {
                QMutexLocker locker(&mutex); 
            
                //qDebug() << "==> User Type: " << QThread::currentThreadId();
                QSqlQuery query(db);
                query.prepare("insert into AuditTrails(userName, userLevel, reason) values(:username, :userlvl, :reason)");
                query.bindValue(":username", username);
                query.bindValue(":userlvl", userLevel);
                query.bindValue(":reason", reason);
            
                if(!query.exec()) {
                    qCritical() << "Failed to insert data in audit trail log: " << query.lastError().text();
                }
            }
            
            

            I’m using three DatabaseConnector instances, each with a different connection name (one per UI screen). Could that be contributing to instability? What could be the cause of Mysql Server gone away?

            Apart from that, is it possible that a QSqlQuery::exec() call returns true but does not actually affect the database in mysql (e.g., an INSERT appears to work, but nothing is written)?

            Any advice or shared experience would be appreciated. Thank you!

            Note: Does antivirus or Group Admin/Network Policies in windows 10 affect this type of connections?
            wait_timeout in my.ini is also changed to 2 days.

            JonBJ Offline
            JonBJ Offline
            JonB
            wrote last edited by
            #5

            @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

            db2 = QSqlDatabase::addDatabase("QMYSQL", connectionName);
            

            It looks like this db2, and where you use it in other methods, is a member variable of your DatabaseConnector class? You really are not supposed to do this, and we always point this out. It may not be related to your problem of disconnection over time, but you should still alter your code to comply.

            https://doc.qt.io/qt-6/qsqldatabase.html#details

            Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior.

            M 1 Reply Last reply
            2
            • jsulmJ jsulm

              @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

              Apart from that, do you find any issues with the code shared above?

              I'm just wondering why you're spawning a thread everytime AuditLogger::log? QtConcurrent::run uses a thread pool, but I still would use a different approach: let a log thread run as long as the application runs and send log messages to it via signals/slots or invokeMethod.

              It is also better to use QSqlError::type() instead of searching for a string in the error message (which could change in later Qt versions). You probably get QSqlError::ConnectionError if the connection disappeared.

              M Offline
              M Offline
              mvsri
              wrote last edited by
              #6

              @jsulm said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

              @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

              Apart from that, do you find any issues with the code shared above?

              I'm just wondering why you're spawning a thread everytime AuditLogger::log? QtConcurrent::run uses a thread pool, but I still would use a different approach: let a log thread run as long as the application runs and send log messages to it via signals/slots or invokeMethod.

              It is also better to use QSqlError::type() instead of searching for a string in the error message (which could change in later Qt versions). You probably get QSqlError::ConnectionError if the connection disappeared.

              Thanks for the response, I can use your suggestion on invokemethod for auditlogger. apart from that instead of searching string i updated it using QSqlError::type().

              1 Reply Last reply
              0
              • JonBJ JonB

                @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

                db2 = QSqlDatabase::addDatabase("QMYSQL", connectionName);
                

                It looks like this db2, and where you use it in other methods, is a member variable of your DatabaseConnector class? You really are not supposed to do this, and we always point this out. It may not be related to your problem of disconnection over time, but you should still alter your code to comply.

                https://doc.qt.io/qt-6/qsqldatabase.html#details

                Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior.

                M Offline
                M Offline
                mvsri
                wrote last edited by
                #7

                @JonB said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

                @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

                db2 = QSqlDatabase::addDatabase("QMYSQL", connectionName);
                

                It looks like this db2, and where you use it in other methods, is a member variable of your DatabaseConnector class? You really are not supposed to do this, and we always point this out. It may not be related to your problem of disconnection over time, but you should still alter your code to comply.

                https://doc.qt.io/qt-6/qsqldatabase.html#details

                Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior.

                db2 is a member variable, and after going through your suggestions and document i modified the code like this,

                DatabaseConnector::DatabaseConnector(QString databaseName,
                                                     QString userName,
                                                     QString password,
                                                     QString connectionName,
                                                     QObject *parent)
                    : QObject{parent}
                {
                    if(QSqlDatabase::contains(connectionName)) {
                        QSqlDatabase::removeDatabase(connectionName);
                    }
                    ConnectionName = connectionName;
                    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", connectionName);
                    db.setHostName("localhost");
                    db.setDatabaseName(databaseName);
                    db.setUserName(userName);
                    db.setPassword(password);
                    db.setConnectOptions("MYSQL_OPT_RECONNECT=1"); // reconnect mysql if its get disconnected
                    if(!db.open())
                        qWarning() << "Database Connection Opening Error " << db.lastError().text();
                }
                
                DatabaseConnector::~DatabaseConnector()
                {
                    //qDebug() << "==> Database Connector Destructor " << db2.connectionName();
                
                    if (QSqlDatabase::contains(ConnectionName)) {
                        {
                            QSqlDatabase db = QSqlDatabase::database(ConnectionName);
                            if (db.isOpen()) {
                                db.close();
                            }
                        }
                        QSqlDatabase::removeDatabase(ConnectionName);  
                    }
                }
                
                bool DatabaseConnector::executeSingleQuery(const QString &query)
                {
                    if(!ensureOpen()) {
                        return false;
                    }
                
                    QSqlDatabase db = QSqlDatabase::database(ConnectionName);
                    QSqlQuery queryExec(db);
                
                    if(!queryExec.exec(query)) {
                        qWarning() << "Failed to execute query: " << query << ", Error: " << queryExec.lastError().text();
                        // added the retry logic from QSqlError::type()
                    }
                    if(queryExec.numRowsAffected() == 0) {
                        qWarning() << "[executeSingleQuery] Query executed, but affected 0 rows: " << query;
                    }
                    return true;
                }
                
                bool DatabaseConnector::ensureOpen()
                {
                    if (!QSqlDatabase::contains(ConnectionName)) {
                        qWarning() << "Database connection name not registered.";
                        return false;
                    }
                    QSqlDatabase db = QSqlDatabase::database(ConnectionName);
                    if (!db.isOpen()) {
                        if (!db.open()) {
                            qWarning() << "==> Failed to re-open db: " << db.lastError().text();
                            return false;
                        }
                    }
                    return true;
                }
                

                Is this the correct way?
                also i just have a query, is this line working

                db.setConnectOptions("MYSQL_OPT_RECONNECT=1");
                
                JonBJ 1 Reply Last reply
                1
                • M mvsri

                  @JonB said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

                  @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

                  db2 = QSqlDatabase::addDatabase("QMYSQL", connectionName);
                  

                  It looks like this db2, and where you use it in other methods, is a member variable of your DatabaseConnector class? You really are not supposed to do this, and we always point this out. It may not be related to your problem of disconnection over time, but you should still alter your code to comply.

                  https://doc.qt.io/qt-6/qsqldatabase.html#details

                  Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior.

                  db2 is a member variable, and after going through your suggestions and document i modified the code like this,

                  DatabaseConnector::DatabaseConnector(QString databaseName,
                                                       QString userName,
                                                       QString password,
                                                       QString connectionName,
                                                       QObject *parent)
                      : QObject{parent}
                  {
                      if(QSqlDatabase::contains(connectionName)) {
                          QSqlDatabase::removeDatabase(connectionName);
                      }
                      ConnectionName = connectionName;
                      QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", connectionName);
                      db.setHostName("localhost");
                      db.setDatabaseName(databaseName);
                      db.setUserName(userName);
                      db.setPassword(password);
                      db.setConnectOptions("MYSQL_OPT_RECONNECT=1"); // reconnect mysql if its get disconnected
                      if(!db.open())
                          qWarning() << "Database Connection Opening Error " << db.lastError().text();
                  }
                  
                  DatabaseConnector::~DatabaseConnector()
                  {
                      //qDebug() << "==> Database Connector Destructor " << db2.connectionName();
                  
                      if (QSqlDatabase::contains(ConnectionName)) {
                          {
                              QSqlDatabase db = QSqlDatabase::database(ConnectionName);
                              if (db.isOpen()) {
                                  db.close();
                              }
                          }
                          QSqlDatabase::removeDatabase(ConnectionName);  
                      }
                  }
                  
                  bool DatabaseConnector::executeSingleQuery(const QString &query)
                  {
                      if(!ensureOpen()) {
                          return false;
                      }
                  
                      QSqlDatabase db = QSqlDatabase::database(ConnectionName);
                      QSqlQuery queryExec(db);
                  
                      if(!queryExec.exec(query)) {
                          qWarning() << "Failed to execute query: " << query << ", Error: " << queryExec.lastError().text();
                          // added the retry logic from QSqlError::type()
                      }
                      if(queryExec.numRowsAffected() == 0) {
                          qWarning() << "[executeSingleQuery] Query executed, but affected 0 rows: " << query;
                      }
                      return true;
                  }
                  
                  bool DatabaseConnector::ensureOpen()
                  {
                      if (!QSqlDatabase::contains(ConnectionName)) {
                          qWarning() << "Database connection name not registered.";
                          return false;
                      }
                      QSqlDatabase db = QSqlDatabase::database(ConnectionName);
                      if (!db.isOpen()) {
                          if (!db.open()) {
                              qWarning() << "==> Failed to re-open db: " << db.lastError().text();
                              return false;
                          }
                      }
                      return true;
                  }
                  

                  Is this the correct way?
                  also i just have a query, is this line working

                  db.setConnectOptions("MYSQL_OPT_RECONNECT=1");
                  
                  JonBJ Offline
                  JonBJ Offline
                  JonB
                  wrote last edited by
                  #8

                  @mvsri
                  Yes, so far as I can see this all looks a lot better. And you have even got the "extra scoping" in ~DatabaseConnector() right so that the removeDatabase() should work correctly.

                  Yes, your db.setConnectOptions("MYSQL_OPT_RECONNECT=1"); should be working. You can retrieve the current options at any later time via connectionOptions() to verify.

                  However, whether that option is even doing anything for you I am unsure. I advise you to Google for MYSQL_OPT_RECONNECT. It has been deprecated in MySQL 8.x. I believe this goes back at least till MySQL 8.0.34. I am unclear whether they are saying that it already "does nothing" from that version onwards. If that is the case it is a possible explanation of your problem.

                  Did anything turn up when you checked MySQL logs?

                  1 Reply Last reply
                  0
                  • M mvsri

                    Hello everyone,

                    I'm working with Qt 5.15.2 (MinGW 64-bit) and MySQL 8.0 on Windows 10, and I’ve implemented a custom DatabaseConnector class to manage database interactions. Below is a simplified version of the class:

                    #include "databaseconnector.h"
                    
                    DatabaseConnector::DatabaseConnector(QString databaseName,
                                                         QString userName,
                                                         QString password,
                                                         QString connectionName,
                                                         QObject *parent)
                        : QObject{parent}
                    {
                        if(QSqlDatabase::contains(connectionName)) {
                            QSqlDatabase::removeDatabase(connectionName);
                        }
                        db2 = QSqlDatabase::addDatabase("QMYSQL", connectionName);
                        db2.setHostName("localhost");
                        db2.setDatabaseName(databaseName);
                        db2.setUserName(userName);
                        db2.setPassword(password);
                        db2.setConnectOptions("MYSQL_OPT_RECONNECT=1"); // reconnect mysql if its get disconnected
                        if(!db2.open())
                            qWarning() << "Database Connection Opening Error " << db2.lastError().text();
                    }
                    
                    DatabaseConnector::~DatabaseConnector()
                    {
                        //qDebug() << "==> Database Connector Destructor " << db2.connectionName();
                        QString connection;
                        if(db2.isOpen())
                        {
                            connection = db2.connectionName();
                            db2.close();
                            db2 = QSqlDatabase();
                            QSqlDatabase::removeDatabase(connection);
                        }
                    }
                    
                    bool DatabaseConnector::databaseOpen()
                    {
                        return db2.open();
                    }
                    
                    QSqlQuery DatabaseConnector::executeQuery(const QString &query)
                    {
                        if(!ensureOpen()) {
                            return QSqlQuery();
                        }
                    
                        QSqlQuery queryExec(db2);
                    
                        if(!queryExec.exec(query)) {
                            QString err = queryExec.lastError().text();
                            qWarning() << "[executeQuery] Query failed:" << err;
                            if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                                err.contains("lost connection", Qt::CaseInsensitive)) {
                                qWarning() << "[executeQuery] Lost connection. Attempting reconnect...";
                                db2.close();
                    
                                if (db2.open()) {
                                    qInfo() << "[executeQuery] Reconnected to DB. Retrying query...";
                    
                                    QSqlQuery retryQuery(db2);
                                    if (!retryQuery.exec(query)) {
                                        qWarning() << "[executeQuery] Retry failed:" << retryQuery.lastError().text();
                                    }
                                    return retryQuery;
                                } else {
                                    qWarning() << "[executeQuery] Reconnect failed:" << db2.lastError().text();
                                    return QSqlQuery();
                                }
                            }
                        }
                    
                        return queryExec;
                    }
                    
                    bool DatabaseConnector::executeSingleQuery(const QString &query)
                    {
                        if(!ensureOpen()) {
                            return false;
                        }
                    
                        QSqlQuery queryExec(db2);
                    
                        if(!queryExec.exec(query)) {
                            qWarning() << "Failed to execute query: " << query << ", Error: " << queryExec.lastError().text();
                            QString err = queryExec.lastError().text();
                            if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                                err.contains("lost connection", Qt::CaseInsensitive)) {
                                db2.close();
                                if(!db2.open()){
                                    qWarning() << "Database Connection Opening Error - 2" << db2.lastError().text();
                                }
                    
                                // retry logic
                                QSqlQuery retryQuery(db2);
                                if (!retryQuery.exec(query)) {
                                    qWarning() << "Retry failed: " << retryQuery.lastError().text();
                                    return false;
                                }
                                qDebug() << "==> Database is Gone and reoppend";
                                return true;
                            }
                            return false;
                        }
                        return true;
                    }
                    
                    bool DatabaseConnector::ensureOpen()
                    {
                        if(!db2.isOpen()) {
                            if(!db2.open()) {
                                qWarning() << "==> Failed to re-open db: " << db2.lastError().text();
                                return false;
                            }
                        }
                        return true;
                    }
                    
                    

                    This class is working as expected without any errors and all, but what i have noticed is when the application is running for long hours like 10+ hours any statements like update or insert doesn't work.
                    when i checked the log file of the application i got this

                    Lost connection to MySQL server during query QMYSQL: Unable to execute query" (:0, )
                    

                    and after that for subsequent statements i got this:

                    MySQL server has gone away QMYSQL: Unable to execute query" (:0, )
                    

                    After observing these messages in the logs:

                    Lost connection to MySQL server during query
                    
                    MySQL server has gone away
                    

                    I added reconnection logic in the executeQuery and executeSingleQuery functions to handle such scenarios. Sample code below

                    QString err = queryExec.lastError().text();
                            if (err.contains("server has gone away", Qt::CaseInsensitive) ||
                                err.contains("lost connection", Qt::CaseInsensitive)) {
                                db2.close();
                                if(!db2.open()){
                                    qWarning() << "Database Connection Opening Error - 2" << db2.lastError().text();
                                }
                    

                    Interestingly, another class in the same application—used for audit logs—continues to work without issues. It uses a separate connection name and writes to MySQL via QtConcurrent::run.

                    #include "auditlogger.h"
                    
                    
                    AuditLogger &AuditLogger::instance()
                    {
                        static AuditLogger instance;
                        return instance;
                    }
                    
                    void AuditLogger::log(const QString &username, int userLevel, const QString &reason)
                    {
                        QtConcurrent::run(this, &AuditLogger::peformLog, username, userLevel, reason);
                    }
                    
                    AuditLogger::AuditLogger()
                    {
                        db = QSqlDatabase::addDatabase("QMYSQL", connectionName);
                        db.setHostName("localhost");
                        db.setDatabaseName(databasename);
                        db.setUserName(username);
                        db.setPassword(password);
                    
                        if(!db.open()) {
                            qCritical() << "==> Failed to connect to database in Audit Log: " << db.lastError().text();
                        }
                    }
                    
                    AuditLogger::~AuditLogger()
                    {
                        db.close();
                        QSqlDatabase::removeDatabase(connectionName);
                    }
                    
                    void AuditLogger::peformLog(const QString &username, int userLevel, const QString &reason)
                    {
                        QMutexLocker locker(&mutex); 
                    
                        //qDebug() << "==> User Type: " << QThread::currentThreadId();
                        QSqlQuery query(db);
                        query.prepare("insert into AuditTrails(userName, userLevel, reason) values(:username, :userlvl, :reason)");
                        query.bindValue(":username", username);
                        query.bindValue(":userlvl", userLevel);
                        query.bindValue(":reason", reason);
                    
                        if(!query.exec()) {
                            qCritical() << "Failed to insert data in audit trail log: " << query.lastError().text();
                        }
                    }
                    
                    

                    I’m using three DatabaseConnector instances, each with a different connection name (one per UI screen). Could that be contributing to instability? What could be the cause of Mysql Server gone away?

                    Apart from that, is it possible that a QSqlQuery::exec() call returns true but does not actually affect the database in mysql (e.g., an INSERT appears to work, but nothing is written)?

                    Any advice or shared experience would be appreciated. Thank you!

                    Note: Does antivirus or Group Admin/Network Policies in windows 10 affect this type of connections?
                    wait_timeout in my.ini is also changed to 2 days.

                    JonBJ Offline
                    JonBJ Offline
                    JonB
                    wrote last edited by JonB
                    #9

                    @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

                    like 10+ hours any statements like update or insert doesn't work.

                    This may be significant. From your INSERT example you are using prepare() and you are binding variables. Please read https://forum.qt.io/topic/107690/lost-connection-to-mysql-server-during-query-qmysql3-unable-to-reset-statement carefully. There the suggestion is that this may fail to reconnect even with the reconnection option. If your problem is reproducible can you, at least temporarily/as a test change to make the query a string with whatever in it and abandon preparation and variable substitution? Does that make the problem go away? Furthermore, you check the result of exec() but when you do use prepare() you do not check the result it returns. You should do so. That may reveal an error at that stage.

                    Also, given that abandonment of MYSQL_OPT_RECONNECT has either happened or is about to happen, you might remove this from your code and do whatever you have to manage reconnection yourself, to see whether that improves the situation.

                    I do not know why your other audit log connection appears to work while your main one fails. Even more complex is your "writes to MySQL via QtConcurrent::run". I was under the impression that Qt requires you to write to a SQL connection from the same thread as where it was created/currently lives, won't QtConcurrent::run() break precisely that rule? Yet that one works. Who knows?!

                    M 1 Reply Last reply
                    1
                    • JonBJ JonB

                      @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

                      like 10+ hours any statements like update or insert doesn't work.

                      This may be significant. From your INSERT example you are using prepare() and you are binding variables. Please read https://forum.qt.io/topic/107690/lost-connection-to-mysql-server-during-query-qmysql3-unable-to-reset-statement carefully. There the suggestion is that this may fail to reconnect even with the reconnection option. If your problem is reproducible can you, at least temporarily/as a test change to make the query a string with whatever in it and abandon preparation and variable substitution? Does that make the problem go away? Furthermore, you check the result of exec() but when you do use prepare() you do not check the result it returns. You should do so. That may reveal an error at that stage.

                      Also, given that abandonment of MYSQL_OPT_RECONNECT has either happened or is about to happen, you might remove this from your code and do whatever you have to manage reconnection yourself, to see whether that improves the situation.

                      I do not know why your other audit log connection appears to work while your main one fails. Even more complex is your "writes to MySQL via QtConcurrent::run". I was under the impression that Qt requires you to write to a SQL connection from the same thread as where it was created/currently lives, won't QtConcurrent::run() break precisely that rule? Yet that one works. Who knows?!

                      M Offline
                      M Offline
                      mvsri
                      wrote last edited by
                      #10

                      @JonB said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

                      @mvsri said in Qt Mysql [Mysql Server has gone away] and Silent failures after query exec():

                      like 10+ hours any statements like update or insert doesn't work.

                      This may be significant. From your INSERT example you are using prepare() and you are binding variables. Please read https://forum.qt.io/topic/107690/lost-connection-to-mysql-server-during-query-qmysql3-unable-to-reset-statement carefully. There the suggestion is that this may fail to reconnect even with the reconnection option. If your problem is reproducible can you, at least temporarily/as a test change to make the query a string with whatever in it and abandon preparation and variable substitution? Does that make the problem go away? Furthermore, you check the result of exec() but when you do use prepare() you do not check the result it returns. You should do so. That may reveal an error at that stage.

                      Also, given that abandonment of MYSQL_OPT_RECONNECT has either happened or is about to happen, you might remove this from your code and do whatever you have to manage reconnection yourself, to see whether that improves the situation.

                      I do not know why your other audit log connection appears to work while your main one fails. Even more complex is your "writes to MySQL via QtConcurrent::run". I was under the impression that Qt requires you to write to a SQL connection from the same thread as where it was created/currently lives, won't QtConcurrent::run() break precisely that rule? Yet that one works. Who knows?!

                      I have gone through this thread: https://forum.qt.io/topic/107690/lost-connection-to-mysql-server-during-query-qmysql3-unable-to-reset-statement/6.
                      In my case, I’ve been using QString to build the query and pass it to the functions in databaseconnector class and passing it to QSqlQuery.
                      To recreate the issue, I restarted the MySQL server from Services (windows), and I received the same error:

                      Lost connection to MySQL server during query QMYSQL: Unable to execute query" (:0, )
                      

                      After this, I implemented reconnection logic that attempts to reconnect if QSqlQuery::exec() fails and give connection error. When I recreated the same scenario (restarting the MySQL server), the reconnection worked as expected.

                      Based on suggestions and further reading of the documentation, I’ve also modified my code to avoid holding the QSqlDatabase instance as a class variable and instead use it as a function-local variable.

                      Regarding the AuditLogger class, I’ve completely reimplemented it and now use QMetaObject::invokeMethod for logging operations.

                      While reviewing the documentation and googling for MYSQL_OPT_RECONNECT, it appears this option may no longer work as expected with recent versions. So, I’m considering removing it from the code.

                      One pending task is to check the MySQL server logs—I haven’t received them yet, but once I do, I’ll analyze them for further insight.

                      One doubt I have is:
                      The system where the application is running is part of a domain network, governed by Group IT policies, and has antivirus installed.
                      Could this environment be a potential cause for the dropped connection? (I understand this might be a naive question, but I’d like to rule out any possible cause.)

                      Thank you again for your inputs—they’ve helped clarify several concepts for me.

                      1 Reply Last reply
                      0

                      • Login

                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • Users
                      • Groups
                      • Search
                      • Get Qt Extensions
                      • Unsolved