Created attachment 116958 [details] sqlite_master.sql akonadiserver.err* fix_PartTable.sql akonadiserverrc SUMMARY With QSQLITE backend: - update 36 fails because of an extraneous column 'external' in 'PartTable', - After fixing PartTable update 37 seems to pass ok, - An invalid PRAGMA is sent to SQL when the back-end try to fix some invalid foreign constraints. STEPS TO REPRODUCE On Arch Linux using the QSQLITE backend, after upgrading akonadi from 18.08.3-2 to 18.12.0-1, akonadiserver fails to start with a migration error: $ akonadiserver --start-without-control --verbose > Updating foreign keys in table "PartTable" > [...] Error: table PartTable_new has 7 columns but 8 values were supplied Unable to execute statement Full log in the atatched "akonadiserver.err1" log file. The initial schema is attached as "sqlite_master.sql". Comparing schemas with those produced by akonaditest, i found an extraneous column 'external' in PartTable. I tried to remove it (with "fix_PartTable.sql"). Now akonadiserver fails on a malformed PRAGMA: > Starting database update to version 36 > [...] (Update 36 seems to complete now) > DbUpdater: update to version: 37 mandatory: true > Found existing foreign constraint that doesn't match the schema: "CollectionTableparentId_CollectionTable_oldid_fk" "parentId" "CollectionTable_old" "id" > Found existing foreign constraint that doesn't match the schema: "TagTableparentId_TagTable_oldid_fk" "parentId" "TagTable_old" "id" > Updating indexes > [...] > Removing invalid foreign key constraints > "PRAGMA foreign_key_check=OFF" > Updating index failed: > Sql error: no such table: OFF Unable to execute statement Full log in "akonadiserver.err2". If i'm not mistaken, the correct pragma should be either "PRAGMA foreign_keys=OFF" or "PRAGMA foreign_key_check". Subsequent runs skip all the updates (including update 37) but the invalid foreign constraint warnings are still emitted before the pragma error. (Full log in "akonadiserver.err3") SOFTWARE/OS VERSIONS Arch Linux/KDE Plasma: KDE Plasma Version: 5.14.4-2 KDE Frameworks Version: 5.53.0-1 Qt Version: 5.12.0-1 akonadiserver 5.10.0 sqlite Version: 3.26.0-1
This change in the code make it working for me: s/PRAGMA foreign_key_check=OFF/PRAGMA defer_foreign_keys=ON/ s/PRAGMA foreign_key_check=ON/PRAGMA defer_foreign_keys=OFF/
I have same problem in Slackware-current with alien KDE5/Plasma packages. @Maël: Are this PRAGMA changes are possible in compiled akonadi?
Created attachment 117837 [details] Patch witch PRAGMA changes suggested by Maël I've attached patch with PRAGMA changes suggested by Maël. "PRAGMA foreign_key_check=OFF" is definitely wrong syntax according SQLite documentation. I have no idea if "PRAGMA defer_foreign_keys=ON" is a correct replacement, but as Maël wrote, it works.
Please submit your patch using phabricator.kde.org. You can find information here: https://community.kde.org/Infrastructure/Phabricator
Git commit 42d6c38c4dc619f23ca00ad42d9c538fc9ca2f78 by Daniel Vrátil, on behalf of Konrad Rzepecki. Committed on 06/02/2019 at 17:14. Pushed by dvratil into branch 'Applications/18.12'. Fix SQLite backend foreign key PRAGMAs Summary: SQLite backend contain "PRAGMA foreign_key_check=OFF/ON" which ins't correct SQLite syntax. Author intention was probably disengage key checking. In SQLlite this can be achieved be temporary deffer key check. Correct syntax is in this case "PRAGMA defer_foreign_keys=ON/OFF". This partial resolves bug 402229. Reviewers: dvratil Reviewed By: dvratil Subscribers: lbeltrame, anthonyfieroni, kde-pim Tags: #kde_pim Differential Revision: https://phabricator.kde.org/D18781 M +46 -46 autotests/server/dbtest_data/dbinit_sqlite M +50 -50 autotests/server/dbtest_data/dbinit_sqlite_incremental M +2 -2 src/server/storage/dbinitializer_p.cpp https://commits.kde.org/akonadi/42d6c38c4dc619f23ca00ad42d9c538fc9ca2f78