Bug 402229

Summary:	Sqlite backend error with schema update 36 & 37 (5.10.0)
Product:	[Frameworks and Libraries] Akonadi	Reporter:	Maël Kerbiriou <piezo.wdimd>
Component:	server	Assignee:	kdepim bugs <kdepim-bugs>
Status:	REPORTED ---
Severity:	major	CC:	asturm, elbin.p, hannibal, reuben_p, stupor_scurvy343
Priority:	NOR
Version:	GIT (master)
Target Milestone:	---
Platform:	Arch Linux
OS:	Linux
Latest Commit:		Version Fixed In:
Sentry Crash Report:
Attachments:	sqlite_master.sql akonadiserver.err* fix_PartTable.sql akonadiserverrc Patch witch PRAGMA changes suggested by Maël

Description Maël Kerbiriou 2018-12-17 08:42:00 UTC

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

Comment 1 Maël Kerbiriou 2018-12-17 11:23:38 UTC

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/

Comment 2 Konrad Rzepecki 2019-02-04 14:06:14 UTC

I have same problem in Slackware-current with alien KDE5/Plasma packages.

@Maël:
Are this PRAGMA changes are possible in compiled akonadi?

Comment 3 Konrad Rzepecki 2019-02-05 10:27:29 UTC

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.

Comment 4 Christophe Marin 2019-02-05 14:20:06 UTC

Please submit your patch using phabricator.kde.org.

You can find information here: https://community.kde.org/Infrastructure/Phabricator

Comment 5 Daniel Vrátil 2019-02-06 17:14:57 UTC

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