Lost connection to MySQL server during query QMYSQL3: Unable to reset statement

Chrisw01 · 12 Oct 2019, 08:43

Hi, I am experiencing a problem I'm not sure how to trace. I receive the above error about 500 records into an import from my Qt: App. I've downloaded the latest Qt for Windows. Used several different versions of mysql server on linux on several different machines with the same outcome. I've found references to timeouts on the internet but usually its after hours. I can make this fail in seconds.

OS Windows 10. Qt 5.12.5. Mysql Server both 5.x series and 8.x series. Latest MariaDB server. doesn't matter same thing after bout 500-600 records.

Any ideas on where to point me to?

Christian Ehrlicher · wrote on 12 Oct 2019, 06:18

Can you show use some code?

JonB · wrote on 12 Oct 2019, 08:43

@Chrisw01
Unfortunately, https://forum.qt.io/topic/13442/qmysql3-unable-to-reset-statement reports the same from 2012 and received no replies :(
https://kde-pim.kde.narkive.com/b6Avv5nE/long-running-app-loses-mysql-connection reports similar-ish, claimed resolution:

using setConnectOption("MYSQL_OPT_RECONNECT=1") should automatically restore the connection if it's lost.

Source code of error:
https://code.woboq.org/qt5/qtbase/src/plugins/sqldrivers/mysql/qsql_mysql.cpp.html, line #1012.

Chrisw01 · wrote on 14 Oct 2019, 18:23

Thanks for your input.

  I've changed the way I handle the database entries getting away from QSqlQuery::prepare() by building a string and calling QSqlQuery::exec() with the string. I get to about 1200 records before the time out however, after a moment or two the program continues to add entries into the database.

@JonB Yea, I found that as well as a couple of other references on the net but no outcomes or fixes mentioned.

@Christian-Ehrlicher I will post some code when I get back to my office later on.

Thanks again ...

Chrisw01 · wrote on 24 Oct 2019, 15:59

Not quite sure if this is a bug or not, but I've changed the offending code to call QSqlQuery::exec() with a pre-built string instead of using QSqlQuery::prepare(). I've wrapped ::exec in a timed while loop until either timeout or positive completion. It will timeout but after a few moments it continues to process just fine. It seams ::prepare() simply gives up and throws the reset error.

Any other Ideas?

Chrisw01 · wrote on 26 Oct 2019, 14:55

The above method introduced another issue. Apparently bindValue escapes the special characters. I've looked for a function or example on how to do this manually without luck. Does anyone know if such function exists ?