Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

How to properly share in-memory SQLite database among threads?



  • Hi!

    I'm developing an application that shall visualize log files of a specific binary format. In order to do so it dumps parsed entries into an in-memory SQLite database and then uses a QTableView to display a QSqlTableModel of the database.

    As parsing of the entries can be quite complex (and the logs can get very large) I have distributed that task to multiple threads.

    This worked fine but starting with Qt 5.11 I got this warning:

    QSqlDatabasePrivate::database: requested database does not belong to the calling thread.
    

    I eventually found out that sharing a QSqlDatabase connection object between multiple threads is not supported anymore and that I would have to create a separate connection for each thread (e.g. like here).

    My naive approach to this ended up in having distinct in-memory databases for each thread, but after reading this topic I got that fixed (using QSQLITE_OPEN_URI, QSQLITE_ENABLE_SHARED_CACHE and file::memory: as database name).

    Unfortunately I am now back where I started, getting the aforementioned warning. So I am wondering: how do you properly share an in-memory database? Is this even supported?

    For reference, here's how I create the database within the constructor of my QSqlTableModel subclass (simplified):

    QSqlDatabase db = database();
    db.setConnectOptions("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE;QSQLITE_ENABLE_REGEXP");
    db.setDatabaseName("file::memory:");
    if (!db.open())
    {
        qFatal("Failed to create database");
    }
    
    QSqlQuery query("CREATE TABLE entries (id INTEGER PRIMARY KEY, message TEXT);", db);
    if (!query.isActive())
    {
        qCritical() << "Failed to create entry table:"
                    << query.lastError().text();
        exit(EXIT_FAILURE);
    }
    

    And this is how each thread creates its own connection:

    const QString connName = QString::number((quintptr)QThread::currentThreadId());
    QSqlDatabase db = QSqlDatabase::database(connName);
    if (!db.isOpen() || !db.isValid()) {
       db = QSqlDatabase::cloneDatabase(database(), connName);
       if (!db.open()) {
          qCritical() << "Failed to open db connection" + connName;
       }
    }
    

    Thanks in advance for any help!


  • Lifetime Qt Champion

    Hi and welcome to devnet,

    Interesting use case.

    What if you do not clone the database connection but create a new one ?


  • Qt Champions 2019

    @akoch said in How to properly share in-memory SQLite database among threads?:

    getting the aforementioned warning

    As always when you want to find out where a qt warning comes from - install a custom qt message handler, put a breakpoint there and see where it comes from



  • @SGaist said in How to properly share in-memory SQLite database among threads?:

    What if you do not clone the database connection but create a new one ?

    I've tried that actually before I found out about ::cloneDatabase(), same result :(



  • I got it fixed, thanks to @Christian-Ehrlicher!

    I almost did not try your suggestion because I thought I knew very well where the warning comes from.

    But it turned out that I had missed a setQuery() call that was lacking its second parameter (the database connection to use).