Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Qt SQLite Unable to Open Database after many successful inserts/updates on Mac
QtWS25 Last Chance

Qt SQLite Unable to Open Database after many successful inserts/updates on Mac

Scheduled Pinned Locked Moved General and Desktop
1 Posts 1 Posters 937 Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • A Offline
    A Offline
    adammhaile
    wrote on last edited by
    #1

    I'm trying to use SQLite with Qt 5.3 and access a central DB from 3 separate threads. Each thread has its own connection to the DB as per many suggestions I've seen here. Inserts are happening on a file scanner thread and updates are happening on another. It works perfectly fine on Windows and on Mac when running a debug build. But when I have a release (build for profiling) build it fails with the errors seen below. At least on my machine, it always fails after insert 272.. though I can push that out a few by adding in some delays after each insert.

    @ Success: insert into MediaItems (Type, UUID, Path, Width, Height, Parents) values (:Type, :UUID, :Path, :Width, :Height, :Parents)
    1 rows affected
    New ROWID: 270

    Success: insert into MediaItems (Type, UUID, Path, Width, Height, Parents) values (:Type, :UUID, :Path, :Width, :Height, :Parents)
    1 rows affected
    New ROWID: 271
    
    Success: insert into MediaItems (Type, UUID, Path, Width, Height, Parents) values (:Type, :UUID, :Path, :Width, :Height, :Parents)
    1 rows affected
    New ROWID: 272
    
    DB Error on thread: Scanner for /Users/adamh/PicTest
    Error: insert into MediaItems (Type, UUID, Path, Width, Height, Parents) values (:Type, :UUID, :Path, :Width, :Height, :Parents)
    unable to open database file
    Unable to fetch row
    
    DB Error on thread: DB_MAIN
    Error: update MediaItems set UUID = :UUID, Path = :Path, Children = :Children, Parents = :Parents where ROWID = 194
    unable to open database file
    Unable to fetch row@
    

    Example of my insert/update code:

    @ execQueryWrapper(QSqlQuery q)
    {
    bool result = false;

       if (!q.exec())
       {
          AS_LOG(q.lastQuery() + QS("\n"));
          QSqlError err = q.lastError();
          AS_LOG(err.databaseText() + QS("\n"));
          AS_LOG(err.driverText() + QS("\n"));
       }
       else
       {
          AS_LOG(q.lastQuery() + QS("\n"));
          AS_LOG(QS("%1 rows affected\n").arg(q.numRowsAffected()));
          result = true;
       }
    
       return result;
    }
    
    insertQuery(QString table, QStringList cols, QVariantList vals, uint * rowID)
    {
       AS_ASSERT(cols.count() == vals.count());
    
       QSqlQuery q = getQuery();
    
       QString statement = QS("insert into %1 (%2) values (%3)").arg(table).arg(cols.join(QS(", "))).arg(QS(":") + cols.join(QS(", :")));
       q.prepare(statement);
    
       for (int i = 0; i < cols.count(); i++)
       {
          q.bindValue(QS(":") + cols[i], vals[i]);
       }
    
       bool result = execQueryWrapper(q);
       if (result)
       {
          *rowID = q.lastInsertId().toInt();
       }
    
       return result;
    }
    
    updateQuery(QString table, QStringList cols, QVariantList vals, QString conditionCol, QVariant conditionVal)
    {
       AS_ASSERT(cols.count() == vals.count());
    
       QSqlQuery q = getQuery();
    
       QStringList combined;
       foreach(QString col, cols)
       {
          combined.append(col + QS(" = :") + col);
       }
    
       QString statement = QS("update %1 set %2 where %3 = %4").arg(table).arg(combined.join(QS(", "))).arg(conditionCol).arg(conditionVal.toString());
       q.prepare(statement);
    
       for (int i = 0; i < cols.count(); i++)
       {
          q.bindValue(QS(":") + cols[i], vals[i]);
       }
    
       return execQueryWrapper(q);
    }
    

    @
    For the most part, this works... it just eventually fails. I thought at first it was a thread concurrency thing but as far as I know I'm doing everything I can do prevent that. I know I could batch the inserts but for a variety of reasons that's not really possible at the moment... I need single atomic transactions for each new item mainly because the insert requests are coming in as the scanner finds things.

    The actual DB is a file in the user directory on all systems. Again, this ONLY happens on Mac in release builds.
    I'm building with XCode and CMake (also Visual Studio on Windows... but it works there).

    What could I be doing wrong?

    1 Reply Last reply
    0

    • Login

    • Login or register to search.
    • First post
      Last post
    0
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Get Qt Extensions
    • Unsolved