Bug 936102

Summary:	kde-apps/akonadi: Switch default database backend (from QMYSQL) to QSQLITE (IUSE => "mysql +sqlite")
Product:	Gentoo Linux	Reporter:	Andreas Sturmlechner <asturm>
Component:	Current packages	Assignee:	Gentoo KDE team <kde>
Status:	CONFIRMED ---
Severity:	normal	CC:	arsen, gentoo, josef64, sam, soap
Priority:	Normal	Keywords:	PullRequest
Version:	unspecified
Hardware:	All
OS:	Linux
See Also:	https://bugs.gentoo.org/show_bug.cgi?id=441596 https://github.com/gentoo/kde/pull/1007 https://bugs.kde.org/show_bug.cgi?id=451620 https://bugs.kde.org/show_bug.cgi?id=495675
Whiteboard:
Package list:		Runtime testing required:	---
Bug Depends on:	681658
Bug Blocks:	935416

Description Andreas Sturmlechner gentoo-dev

2024-07-15 10:20:18 UTC

What happened so far:
https://kontact.kde.org/blog/2023/2023-05-03-kde-pim-march-april-2023/#akonadi
https://kontact.kde.org/blog/2024/2024-01-01-kde-pim-november-december-2024/#akonadi


Consequently, a year ago we dropped our warning about QSQLITE backend:

    kde-apps/akonadi: Drop obsolete QSQLITE3 deps, args, ewarn
    
    tl;dr: Upstream QSQLITE driver is considered stable, more up to date,
    old (and by now outdated) QSQLITE3 fork was removed.
    
    Upstream commit e70a7a5521b4d8fe0bb36a5b3392c547851507b0


Missing, so far, was 

1) an upstream migration utility to switch backends without starting from scratch, but it is part of 24.05:

    https://discuss.kde.org/t/migrate-akonadi-to-sqlite-from-postgres/7094
    https://kontact.kde.org/blog/2024/2024-03-01-kde-pim-january-february-2024/#akonadi

2) a clear "Go" by upstream that this backend should be preferred. While QMYSQL is still default ("for the time being"), the wording has changed: 

    https://invent.kde.org/pim/akonadi/-/commit/064b9f38

    DATABASE_BACKEND (Default: MYSQL, available: MYSQL, POSTGRES, SQLITE):
    Define which database driver to use by default. MYSQL is the default 
    right now, SQLITE is recommended for most users.


FreeBSD have already switched:
https://euroquis.nl//freebsd/2023/04/24/akonadi.html

What we need to do:
a) search for remaining known issues with QSQLITE (bugs.kde.org, FreeBSD)
b) test the upstream migration tool
c) add instructions for users about the migration path (postinst, news item?)
d) don't break existing users' setups
   - leave IUSE="+mysql" enabled for some time(?)
   - detect "new" from "upgrade" for setting up default "DRIVER=" config
e) do it (with 25.04.2 already, or wait for 24.08?)

Comment 1 Andreas Sturmlechner gentoo-dev

2024-10-10 23:55:38 UTC

Migration on my newly upgraded old laptop worked rather simple, and fine, coming from postgresql:13.

> $ akonadi-db-migrator --newengine sqlite
> Stopping Akonadi service...
> org.kde.pim.akonadiserver: Running DB initializer
> org.kde.pim.akonadiserver: DB initializer done
> org.kde.pim.akonadiserver: Running DB initializer
> org.kde.pim.akonadiserver: DB initializer done
> Running fsck on the source database
> Database fsck completed
> Migrating table SchemaVersionTable (1/19)...
> 100%...
> Optimizing table SchemaVersionTable...
> Migrating table ResourceTable (2/19)...
> 50%...
> 100%...
> Optimizing table ResourceTable...
> Migrating table CollectionTable (3/19)...
> 50%...
> 100%...
> Optimizing table CollectionTable...
> Migrating table MimeTypeTable (4/19)...
> 30%...
> 100%...
> Optimizing table MimeTypeTable...
> Migrating table PimItemTable (5/19)...
> 0%...
> 10%...
> 20%...
> 30%...
> 40%...
> 50%...
> 60%...
> 70%...
> 80%...
> 90%...
> 100%...
> Optimizing table PimItemTable...
> Migrating table FlagTable (6/19)...
> 50%...
> 100%...
> Optimizing table FlagTable...
> Migrating table PartTypeTable (7/19)...
> 10%...
> 20%...
> 30%...
> 40%...
> 50%...
> 60%...
> 70%...
> 80%...
> 90%...
> 100%...
> Optimizing table PartTypeTable...
> Migrating table PartTable (8/19)...
> 0%...
> 10%...
> 20%...
> 30%...
> 40%...
> 50%...
> 60%...
> 70%...
> 80%...
> 90%...
> 100%...
> Optimizing table PartTable...
> Migrating table CollectionAttributeTable (9/19)...
> 30%...
> 50%...
> 80%...
> 100%...
> Optimizing table CollectionAttributeTable...
> Migrating table TagTypeTable (10/19)...
> 100%...
> Optimizing table TagTypeTable...
> Migrating table TagTable (11/19)...
> Optimizing table TagTable...
> Migrating table TagAttributeTable (12/19)...
> Optimizing table TagAttributeTable...
> Migrating table TagRemoteIdResourceRelationTable (13/19)...
> Optimizing table TagRemoteIdResourceRelationTable...
> Migrating table RelationTypeTable (14/19)...
> 100%...
> Optimizing table RelationTypeTable...
> Migrating table RelationTable (15/19)...
> Optimizing table RelationTable...
> Migrating table PimItemFlagRelation (16/19)...
> 0%...
> 10%...
> 20%...
> 30%...
> 40%...
> 50%...
> 60%...
> 70%...
> 80%...
> 90%...
> 100%...
> Optimizing table PimItemFlagRelation...
> Migrating table PimItemTagRelation (17/19)...
> Optimizing table PimItemTagRelation...
> Migrating table CollectionMimeTypeRelation (18/19)...
> 20%...
> 50%...
> 70%...
> 100%...
> Optimizing table CollectionMimeTypeRelation...
> Migrating table CollectionPimItemRelation (19/19)...
> Optimizing table CollectionPimItemRelation...
> waiting for server to shut down.... done
> server stopped
> Backing up original database...
> Starting Akonadi service...
> Migration completed successfully.

After that, I found ~/.config/akonadi/akonadiserverrc to have been switched Driver=QSQLITE by the tool as well, together with an added [QSQLITE] section pointing to the resulting akonadi.db.

So it seems like any migration guide will be rather short.

Comment 2 Andreas Sturmlechner gentoo-dev

2024-10-11 00:02:52 UTC

(In reply to Andreas Sturmlechner from comment #1)
> After that, I found ~/.config/akonadi/akonadiserverrc to have been switched
> Driver=QSQLITE by the tool as well, together with an added [QSQLITE] section
> pointing to the resulting akonadi.db.
... with a copy of the orig file named akonadiserverrc.bkp in the same dir.