QMYSQLResult::cleanup: unable to free statement handle

Question

I'm trying to use a thread to bring data from a MySQL database, but after a call to:

QThread* thread = new QThread;
Beacon *beacon = new Beacon(m_db, begin, end);
beacon->moveToThread(thread);
connect(beacon, &Beacon::values, this, &Tm::insertRow);
connect(beacon, &Beacon::finished, thread, &QThread::quit);
connect(beacon, &Beacon::finished, beacon, &Beacon::deleteLater);
connect(thread, &QThread::finished, thread, &QThread::deleteLater);
connect(thread, &QThread::started, beacon, &Beacon::executeQuerySmooth);
connect(beacon, &Beacon::finished, this, &Tm::finished);
thread->start();

I get:

QMYSQLResult::cleanup: unable to free statement handle

And the next time a try to use the connection I get:

QSqlError("2006", "QMYSQL: Unable to execute query", "MySQL server has gone away")

What might be happening?

Answer 1

You can only use the connection from a thread that created it (reference), or in general from one thread only.

There are two approaches you can take:

1. Maintain the database connection in a dedicated thread, and use it from there. The use of shortlived threads is a premature pessimization anyway, so it was a bad approach to begin with.

2. Move the database connection between worker threads from a thread pool. Use QtConcurrent::run to run a functor on a thread from the thread pool.

Note: Beacon::Beacon(...) constructor can't be using the database, it can only store a reference/pointer to it!

See this answer for more about postCall.

template <typename T>
void postCall(QThread * thread, T && functor) {
   QObject source;
   QObject::connect(&source, &QObject::destroyed,
                    QEventDispatcher::instance(thread), std::forward(functor));
}

Workers From The Thread Pool

class Tm : public QObject {
  Q_OBJECT
  QMutex m_dbMutex;
  QSqlDatabase m_db;
  Q_SIGNAL void openFailed();
  Q_SIGNAL void openSucceeded();
public:
  void openConnection() {
    QMutexLocker lock(&m_dbMutex);
    m_db.addDatabase("QSQLITE");
    ... // set database's properties
    m_db.moveToThread(0); // we don't know what thread it will be used in
    lock.unlock();
    QtConcurrent::run([this]{
      QMutexLocker lock(&m_dbMutex);
      m_db.moveToThread(QThread::currentThread());
      bool rc = m_db.open();
      if (rc) {
        m_db.moveToThread(0);
        emit openSucceeded();
      } else {
        m_db.moveToThread(this->thread());
        emit openFailed();
      }
    });
  }
  void beaconate() {
    ...
    QSharedPointer<Beacon> beacon(new Beacon(&m_db, begin, end));
    connect(beacon, &Beacon::values, this, &Tm::insertRow);
    connect(beacon, &Beacon::finished, this, &Tm::finished);
    beacon->setMoveToThread(0);
    QtConcurrent::run([beacon]{
      beacon->moveToThread(QThread::currentThread());
      QMutexLocker lock(&m_dbMutex);
      m_db.moveToThread(QThread::currentThread());
      QEventLoop loop; // only if Beacon needs one
      connect(beacon, &Beacon::finished, &loop, &QEventLoop::quit);
      beacon->executeQuerySmooth();
      loop.exec();
      m_db.moveToThread(0);
    });
  }
  ~Tm() {
    QMutexLocker lock(&m_dbMutex);
    m_db.moveToThread(thread());
  }
  ...
};

Dedicated Thread

class Thread : public QThread
{ using QThread::run; public: ~Thread() { quit(); wait(); } };

class Tm : public QObject {
  Q_OBJECT
  QSqlDatabase m_db;
  Thread m_dbThread; // must be declared after m_db, so that it's destructed prior to m_db
  Q_SIGNAL void openFailed();
  Q_SIGNAL void openSucceeded();
public:
  Tm(QObject * parent = 0) : QObject(parent) {
    m_dbThread.start();
  }
  void openConnection() {
    m_db.addDatabase("QSQLITE");
    ... // set database's properties
    m_db.moveToThread(&m_dbThread);
    postCall(&m_dbThread, [this]{
      if (! m_db.open()) {
        m_db.moveToThread(this->thread());
        emit openFailed();
      } else
        emit openSucceeded();
    });
  }
  void beaconate() {
    ...
    auto beacon = new Beacon(&m_db, begin, end);
    beacon.moveToThread(&m_dbThread);
    connect(beacon, &Beacon::values, this, &Tm::insertRow);
    connect(beacon, &Beacon::finished, beacon, &Beacon::deleteLater);
    connect(beacon, &Beacon::finished, this, &Tm::finished);
    postCall(&m_dbThread, [beacon]{ beacon->executeQuerySmooth(); });
  }
  ~Tm() {
    // Destructing objects living in other threads is undefined behavior
    postCall(&m_dbThread, [this]{ 
      if (m_db.thread() == QThread::currentThread())         
        m_db.moveToThread(thread());
    });
  }
  ...
};