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

[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.

[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?

[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?

[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.

[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.

[mvsri]

@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().

[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");

[JonB]

@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?

[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?!

[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():

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.