[Akonadi] [Bug 402229] New: Sqlite backend error with schema update 36 & 37 (5.10.0)

[Maël Kerbiriou]

https://bugs.kde.org/show_bug.cgi?id=402229

            Bug ID: 402229
           Summary: Sqlite backend error with schema update 36 & 37
                    (5.10.0)
           Product: Akonadi
           Version: GIT (master)
          Platform: Archlinux Packages
                OS: Linux
            Status: REPORTED
          Severity: major
          Priority: NOR
         Component: server
          Assignee: kdepim-bugs at kde.org
          Reporter: piezo.wdimd at gmail.com
  Target Milestone: ---

Created attachment 116958
  --> https://bugs.kde.org/attachment.cgi?id=116958&action=edit
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

-- 
You are receiving this mail because:
You are the assignee for the bug.