[Qt 5.14.1] [Win32][MySQL 5.6] QSqlQuery::lastError() empty, transaction error?

[QNewbie]

Hello all, I've been dealing with a problem lately.
The program I'm dealing with has a serie of modules that are widgets displayed in a central area.
Each one of them was using the default connection to a database (whose tables are all innodb-engined).
All the data requests were in the same main GUI thread. However, as theres a QSqlQuery that makes a "connected" test, another one who checks the insertions from a remote pay provider, and others that check if the database has gotten any new index in notifications table, I decided to ease out erroring I would use a new connection for stock/invoice updating/adding, so I could rollback() the entire operation.

This proved to be harder than expected, since at some places at some times transaction() worked, and some others it would return error, and lastError()(be it driverText, databaseText or anything) would be empty, even though querying the qsqlquery would say its connection is open, so the qsqldatabase.

To remove the problem from the board, after asking to the brim of oblivion this in gemini and chatgpt, they both suggested this might be a driver issue, to which I ended up ::exec("START TRANSACTION") and ::exec("ROLLBACK") and ::exec("COMMIT") manually, however this is working, is not what is intended, so I wanted to ask you guys where the problem might be lying.

The executable is distributed with both libmysql from the installation and libssl/libcrypto, and otherwise work, but that is irking me.

Kind Regards

[d_stranz]

It does sound like this could be a concurrency issue with the driver or the underlying DB engine. If you are issuing all calls to SQL through the main GUI thread, then there is no multithreading on your side, but if you have separated your START TRANSACTION and COMMIT calls into different slots, it is possible that other slots that also issue SQL calls could be executing in between. If that is the case, put breakpoints in those slots to find out if they are being hit in the correct sequence. I realize this could be really tedious if it is an intermittent problem. Alternatively, you could create a member variable flag that is set in the START TRANSACTION slot and cleared in the COMMIT slot. In other slots you could add an assert() that checks the flag and stops execution if it is not the expected value. This way, the only time the program would halt is when a transaction error happens.

Maybe someone else has a better answer.

[QNewbie]

Yes, not only is it intermitent, but it does not happens in all the hosts with the same binary.
Maybe it would be better to try and move up into a newer qt release?

As we speak, I've moved to the point where I got no connection to any slots, nor do I have encapsulated nor added code in a new db manager class or such.

The code to add a new invoice is all encapsulated in a function, who in turn might use some static functions coming from an utility unit which also take in the db connection (so that, for example, I can choose if I save the invoice even though I could not register the stock differences, or a table that is for inspection that gets added a new row everytime something in the stock of products changes), but I do not connect to any signal/slot from QSqlQuery, nor from QSqlDatabase

[ajinaniyan]

Hello all, I've been dealing with a problem lately.
The program I'm dealing with has a serie of modules that are widgets displayed in a central area.
Each one of them was using the default connection to a database (whose tables are all innodb-engined).
All the data requests were in the same main GUI thread. However, as theres a QSqlQuery that makes a "connected" test, another one who checks the insertions from a remote pay provider, and others that check if the database has gotten any new index in notifications table, I decided to ease out erroring I would use a new connection for stock/invoice updating/adding, so I could rollback() the entire operation.

This proved to be harder than expected, since at some places at some times transaction() worked, and some others it would return error, and lastError()(be it driverText, databaseText or anything) would be empty, even though querying the qsqlquery would say its connection is open, so the qsqldatabase.

To remove the problem from the board, after asking to the brim of oblivion this in gemini and chatgpt, they both suggested this might be a driver issue, to which I ended up ::exec("START TRANSACTION") and ::exec("ROLLBACK") and ::exec("COMMIT") manually, however this is working, is not what is intended, so I wanted to ask you guys where the problem might be lying.

The executable is distributed with both libmysql from the installation and libssl/libcrypto, and otherwise work, but that is irking me.

Kind Regards

This usually isn’t InnoDB itself, but how Qt’s SQL layer manages connections and transactions. In Qt, transactions are per connection, not per thread or per query, and mixing multiple QSqlDatabase connections in the same GUI thread can easily lead to undefined behavior if objects outlive their connection or if a connection is implicitly reused. A very common pitfall is reusing the default connection name or letting a QSqlQuery exist after its connection was replaced or closed. When that happens, transaction() may silently fail and lastError() stays empty because the driver thinks the connection is valid, but it’s no longer in a clean transactional state.

The fact that START TRANSACTION / COMMIT / ROLLBACK works via exec() strongly points to a Qt MySQL driver issue or misuse, not a MySQL server problem. Make sure every connection has a unique connection name, is opened once, lives as long as its queries, and that no query from another module is accidentally using the same connection while a transaction is active. Also verify autocommit is disabled when you expect transaction() to work, and don’t mix implicit and explicit transactions on the same connection.

If this setup has already caused inconsistent data or partial commits during failed rollbacks, it’s worth validating table integrity. Native tools like CHECK TABLE help, but if corruption or orphaned rows show up, Stellar Repair for MySQL can be useful to recover data from damaged InnoDB tables that Qt/MySQL tooling can’t safely dump.