Solved Qt 5.9.1 SQLite bug
-
hello I have a small app in Qt C++ / SQLite was working nicely on Qt 5.6.2 and I did upgrade to Qt 5.9.1 and the problem is i get runtime errors when i try to VACUUM my db note that all queries works except vacuum :( and it works without error on Qt 5.6.2
relevant code
QString msql::sqlExec(QString sqlCmd) { query->clear(); QString value; if (!query->exec(sqlCmd)) { mDebug() << sqlCmd << query->lastError().text(); } else if (!query->first()) { //mDebug() << "not valid query: " << sqlCmd << query->isSelect(); mDebug() << query->lastError().text(); return ""; } else { value = query->value(0).toString(); } query->finish(); return value; }
and
bool msql::vacuum() { QStringList tables = db.tables(QSql::AllTables); int x = 1; int y = tables.count(); foreach (QString table, tables) { sqlExec(QString("VACUUM `%1`").arg(table)); emit progress(QString("Optimizing table %1/%2:%3").arg(x).arg(y).arg(table)); if (killLoopFlag_) return false; x++; } return true; }
I get this errors
Debug:[2017-07-17 02:51 AM] "VACUUM `visits`" "unknown database `visits` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `investigations`" "unknown database `investigations` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `credentials`" "unknown database `credentials` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `siblings`" "unknown database `siblings` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `surgicalNotes`" "unknown database `surgicalNotes` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `metadata`" "unknown database `metadata` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `drugs`" "unknown database `drugs` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `deceased`" "unknown database `deceased` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `visitPrices`" "unknown database `visitPrices` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `conditions`" "unknown database `conditions` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `conditions_patients`" "unknown database `conditions_patients` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `perinatal`" "unknown database `perinatal` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `development`" "unknown database `development` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `obgyn`" "unknown database `obgyn` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `patients`" "unknown database `patients` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `agendaView`" "unknown database `agendaView` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `registerView`" "unknown database `registerView` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `sqlite_master`" "unknown database `sqlite_master` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `SERVICES`" "unknown database `SERVICES` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `defaultDoses`" "unknown database `defaultDoses` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `allergies`" "unknown database `allergies` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `complaints`" "unknown database `complaints` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `diagnoses`" "unknown database `diagnoses` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `dictionary`" "unknown database `dictionary` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `doses`" "unknown database `doses` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `surgeries`" "unknown database `surgeries` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `investigations`" "unknown database `investigations` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `jobs`" "unknown database `jobs` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `places`" "unknown database `places` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `placeOfBirth`" "unknown database `placeOfBirth` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `expander`" "unknown database `expander` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `drugs`" "unknown database `drugs` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `diet`" "unknown database `diet` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `sqlite_master`" "unknown database `sqlite_master` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `drugsIndex`" "unknown database `drugsIndex` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `metadata`" "unknown database `metadata` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString) Debug:[2017-07-17 02:51 AM] "VACUUM `sqlite_master`" "unknown database `sqlite_master` Unable to execute statement" (..\qtemr\msql.cpp:16) class QString __thiscall msql::sqlExec(class QString)
-
@dr3mro Why don't you just vacuum the whole database instead of calling vacuum for each table in the same database? It would be only one call.
-
Hi,
To add to @jsulm and from an educated guess, the SQLite library has likely been updated. Following the vacuum documentation, the parameter passed to the command used to be silently ignored which seems to not be the case anymore hence the errors you get.
-
Thanks all
But is there a way to show progress of Vacuum? -
Do you mean show a progress bar while it's running or having a direct feedback from the command ?
-
yes , I mean when it compacts a table it shows its results and may be cleaned percentage !
-
AFAIK, that information is not provided by SQLite.