Quote Originally Posted by QNewbie View Post
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.