Detect a database error before executing a query

Mark81

On Ubuntu 22.04 with Qt 6.4.0 I open a database connection on the local machine:

Orders::Orders(QObject *parent) : QObject{parent}
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", "orders");
    db.setHostName("localhost");
    db.setDatabaseName("database");
    db.setUserName("user");
    db.setPassword("password");
    db.open();

    // QSqlTableModel *_model; <-- declared as private member
    _model = new QSqlTableModel(this, db);
    _model->setTable("orders");
    _model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    _model->select();
}

It happens that after some time (hours... not sure how many exactly) when I execute a query like this:

void Orders::appendEvent(QString machine, EventType event)
{
    QSqlDatabase db = QSqlDatabase::database("orders");
    QString sql = QString("INSERT INTO orders (datetime, machine, an_events_id) VALUES (current_timestamp(), :machine, :event);");
    QSqlQuery query(db);
    query.prepare(sql);
    query.bindValue(":machine", machine);
    query.bindValue(":event", static_cast<int>(event));
    query.exec();
    if (query.lastError().type() != QSqlError::NoError) qWarning() << query.lastError();
}

I get the following error:

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

The error message seems misleading to me (I'm not a native English speaker, though). Does it really say the connection was lost during the execution of the query? Or it means that during the execution of the query it detected the connection was lost?

In any case, I'm trying to catch the disconnection before I need it. Otherwise, if I detect an error during a query execution I need to save the data, put inside a queue and retry later.

Looking at the docs I don't find any signal (like "disconnected" or "errorChanged"). What is the recommended way? Have I to constantly polling QSqlDatabase::lastError() ? I'm not sure if it can detect an error if I don't execute a query, though...

Christian Ehrlicher

Use QSqlDatabase::isOpen() and also look at the mysql option MYSQL_OPT_RECONNECT.

Mark81

@Christian-Ehrlicher so this bug is completely resolved? Can I reliably rely on isOpen() then?

Christian Ehrlicher

@Mark81 said in Detect a database error before executing a query:

Can I reliably rely on isOpen() then?

You should use also MYSQL_OPT_RECONNECT since we can only do what the client library gives to us: https://dev.mysql.com/doc/c-api/8.0/en/mysql-info.html - no real connection check.
mysql_ping() will only work with MYSQL_OPT_RECONNECT so no need to add this to isOpen().

Mark81

@Christian-Ehrlicher it surely needs more testing, but it seems that adding the MYSQL_OPT_RECONNECT prevented the above error.

Mark81

@Christian-Ehrlicher,
I have to change my assertion. After about 48h it still loses the connection with the database:

QSqlError("2013", "QMYSQL: Unable to reset statement", "Lost connection to MySQL server during query")

The function is:

bool Orders::appendOrder(QString machine, int size, QSqlRecord newOrder)
{
    OrderKey key = OdbcSql::getOrderKey(newOrder);
    int length = newOrder.field("mo_quant").value().toInt();

    _model->setFilter("");
    QSqlRecord record = _model->record();
    record.setValue("id", OdbcSql::encodeOrderKey(key));
    record.setValue("machine", machine);
    record.setValue("reference", newOrder.value("td_riferim"));
    record.setValue("product", newOrder.value("mo_codart"));
    record.setValue("length", length / size);
    record.setValue("date", QDateTime::currentDateTime());
    record.setValue("states_id", static_cast<int>(DbOrderStates::Queued));

    for (int i = 0; i < record.count(); i++) record.setGenerated(i, true);
    bool ret = true;
    ret &= _model->insertRecord(-1, record);
    ret &= _model->submitAll();
    if (!ret) qWarning() << _model->lastError();
    return ret;
}

Here how initialize the stuff now:

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", "orders");
db.setHostName("localhost");
db.setDatabaseName("database");
db.setUserName("user");
db.setPassword("password");
db.setConnectOptions("MYSQL_OPT_RECONNECT=TRUE;");
db.open();

// QSqlTableModel *_model; <-- declared as private member
_model = new QSqlTableModel(this, db);
_model->setTable("orders");
_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
_model->select();

What does actually mean "Unable to reset statement"?

Why does the MYSQL_OPT_RECONNECT seem not to reconnect as expected?

JonB

@Mark81
This may not have anything to do with Qt and you need to investigate at the MySQL side? Having said that consider trying as per https://forum.qt.io/topic/107690/lost-connection-to-mysql-server-during-query-qmysql3-unable-to-reset-statement/5.

Mark81

@JonB I saw that thread, but here I'm not using an explicit query. I'm using the QSqlTableModel - not sure if I can change something here (i.e. call QSqlQuery::exec() with a pre-built string instead of using QSqlQuery::prepare()).

How to investigate on MySQL side? I checked /var/log/mysql/error.log but there are no entries.

How to exclude it does not depend on my Qt code?