Forum: >>> Magnum BBS <<<

KDEPIM with SQLite3

From Martin Steigerwald@21:1/5 to All on Wed Dec 18 18:50:01 2024

Hi!

As KDEPIM 24.12 comes to unstable, I thought I'd write something up
about this:

As I told before about KDEPIM 24.08 in experimental already: Using
SQLite3 as database engine I faced a great deal of improved performance.
The improvement has been impressive. And that compared to the already
probably faster than MariaDB PostgreSQL engine I was using.

I have been migrating the hard way. But there is a migrator available meanwhile:

https://kontact.kde.org/blog/2024/2024-03-01-kde-pim-january-february-2024/

As far as I know this migrator is stable. However… I did not use it.

As SQLite3 is the new recommended engine, it might be good to migrate
at some point in time. But right now I can mostly only tell about my
own experience… so if someone feels adventurous, give it a try and please report back. You help to get a clearer picture on how well it works in
general. If you rather like to keep your current engine… sure I fully sympathize. Never change (and possibly break) a running system :)

I am at least happy not having to upgrade PostgreSQL clusters again.
SQLite3 should be pretty maintenance free. Digikam is using it. Firefox
is using it for a ton of things and so on…

In case you give it a try: *Make a backup first*! Ideally of your complete
home directory while Akonadi is not running. And probably also with
the import/export tool I mentioned before:

KDE PIM data exporter

https://lists.debian.org/debian-kde/2024/12/msg00065.html

--
Martin - please no carbon copy to me

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Martin Steigerwald@21:1/5 to All on Wed Dec 18 19:00:01 2024

Martin Steigerwald - 18.12.24, 18:45:54 MEZ:

As SQLite3 is the new recommended engine, it might be good to migrate
at some point in time. But right now I can mostly only tell about my
own experience… so if someone feels adventurous, give it a try and
please report back. You help to get a clearer picture on how well it
works in general. If you rather like to keep your current engine… sure
I fully sympathize. Never change (and possibly break) a running system
🙂

One little addition to that:

I recommend first upgrading to KDEPIM 24.12 and use it for a while.

Then in case you choose to, migrate to SQLite 3.

If you do both changes at once or very quickly one after another it would
be more difficult to tell for any possible issues where they come from.

Change one thing at a time :).

Best,
--
Martin

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Eike Lantzsch ZP5CGE / KY4PZ@21:1/5 to All on Wed Dec 18 22:40:02 2024

On Wednesday, 18 December 2024 14:55:50 -03 Martin Steigerwald wrote:

Martin Steigerwald - 18.12.24, 18:45:54 MEZ:

As SQLite3 is the new recommended engine, it might be good to
migrate
at some point in time. But right now I can mostly only tell about my
own experience… so if someone feels adventurous, give it a try and
please report back. You help to get a clearer picture on how well it
works in general. If you rather like to keep your current engine…
sure I fully sympathize. Never change (and possibly break) a
running system 🙂

One little addition to that:

I recommend first upgrading to KDEPIM 24.12 and use it for a while.

Then in case you choose to, migrate to SQLite 3.

If you do both changes at once or very quickly one after another it
would be more difficult to tell for any possible issues where they
come from.

Change one thing at a time :).

Best,

Thanks,
so I reckon that I can go on using PostgreSQL and if I like can migrate
to SQLite3 later - is that right?
all the best
--
Eike Lantzsch KY4PZ / ZP5CGE

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Martin Steigerwald@21:1/5 to All on Thu Dec 19 09:30:02 2024

Hi Eike, hi.

Eike Lantzsch ZP5CGE / KY4PZ - 18.12.24, 22:35:27 MEZ:

Thanks,
so I reckon that I can go on using PostgreSQL and if I like can migrate
to SQLite3 later - is that right?

Exactly.

I'd recommend that even. Do one step at a time. Test KDEPIM 24.12 for some weeks and then, if you like, do the migration.

My latest setup with PostgreSQL was PostgreSQL 15. I was happy to migrate
to SQLite 3, cause I got fed up having to upgrade PostgreSQL cluster every
now and then. I do not like to be a database administrator just to use a
mail program.

I was surprised by the immense improvement of the performance. I did not
expect that. I have more than one and a half million of local mails and
for the first time ever akonadi_indexing_agent actually completed the job
to full text index them without bogging down even a very powerful ThinkPad
T14 AMD Gen 5 laptop with Samsung 990 Pro SSD to an extent I gave up by stopping the indexing and chmod'ing akonadi indexing agent to 000. Also
during daily use it is a difference as night and day.

Akonadi SQLite3 database is currently 2,4G, Xapian based search database
is 12 GiB.

Best,
--
Martin

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Martin Steigerwald@21:1/5 to All on Sat Dec 21 19:10:01 2024

Hi Frank, hi.

Frank Mehnert - 21.12.24, 18:02:36 MEZ:

I tried the migration from MySQL to Sqlite3 and failed:

I upgraded everything to current unstable.

Did you restart the session or the computer afterwards?

I started `akonadi-db-migrator --newengine sqlite`.
The assistant started and executed all steps, then tried to finish and aborted with a core dump. MySQL is still running.

[…]

Any hints?

Unfortunately no as I did not use the migrator. A core dump is unexpected
to say the least.

Except for asking upstream about it on kdepim-users or reporting a bug on
with upstream¹ *if* none about this is reported already. As KDEPIM should 24.12 on your system, I think it would be okay to report directly to
upstream. Of course for that it is good you have debug packages set up so
the core dump would be useful.

In case you find anything, please let us now.

[1] https://bugs.kde.org

Best,
--
Martin

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Hefee@21:1/5 to All on Sat Dec 21 19:53:03 2024

Hi,

I expect that you have akonadi-backend-sqlite installed.

Get logs why the migrator is failing:

QT_LOGGING_RULES="*.*=true" akonadi-db-migrator --newengine sqlite

A last recommend, maybe appmor profile is not uptodate, so put it in complain mode:

aa-complain usr.bin.akonadiserver mariadbd_akonadi

(to enforce it afterwards use aa-enforce)

Regards,

hefee

--

On Samstag, 21. Dezember 2024 19:00:45 MEZ Martin Steigerwald wrote:

Hi Frank, hi.

Frank Mehnert - 21.12.24, 18:02:36 MEZ:

I tried the migration from MySQL to Sqlite3 and failed:

I upgraded everything to current unstable.

Did you restart the session or the computer afterwards?

I started `akonadi-db-migrator --newengine sqlite`.
The assistant started and executed all steps, then tried to finish and aborted with a core dump. MySQL is still running.

[…]

Any hints?

Unfortunately no as I did not use the migrator. A core dump is unexpected
to say the least.

Except for asking upstream about it on kdepim-users or reporting a bug on with upstream¹ *if* none about this is reported already. As KDEPIM should 24.12 on your system, I think it would be okay to report directly to upstream. Of course for that it is good you have debug packages set up so
the core dump would be useful.

In case you find anything, please let us now.

[1] https://bugs.kde.org

Best,

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEOewRoCAWtykmSRoG462wCFBgVjYFAmdnDpAACgkQ462wCFBg VjYURg//b6SasPNByJBSqS3h9Oof53RuXOvx5wwzoevRuqWQ4POt5pdIOzsFzwJz J6Qd4G0yBTnwgwRU2QSqE7FQfY/eDsDwyEftl9uourU2QerTDghi3jObhYr0SBSZ ok5FQR11266VxQOxmub3+xN6HwDFMaZQXthMWE65qAYFcCym0Ng+jVhEor382Oy/ iEIAdd+9UdOoU4/6ENZQ4r5uNMIsw61aJo6lQSgEXX9CY+4IUBxxi54OUpBHYX0V VE5lGShd9LKF/vfskrdKsoRQbvOw6W7w8CDj4GvYPCQ07ZKgLkpGgDIOJ/kF+km8 Fg1Vvqu22pSr87nrHcv+D1aNDrd8H0ZMuHg8xssVXZJKgRqkNiG6LzcPkSJzf95B CZPbV8Plcq43xfQfNCcUGRdFRg2+HadsTzu0W954RT8+/SVvYRkjzocxl4IIR9od p41C7nU9uwMr09Fk5j34YsTnRBl051riFemSEZn39uO/kSC6l/71/ENsMS1WGU0D Awf+wGzCHGSv2HgKoHpUtMfdWvmwMfX4zzGAfxNboKqS3L6llPhW/FjM9NdUY8JM 5irwTwOlZUX8x6WitYWSnbfBNSzomf7Wmt06TQpdY7ALQI67oZRtDvj46xqRu8qK YWGtCYQgJzXqenj4HZpUqkoIwvEm7ePZJ9QcdXm1mrXbDN6y4ag=
=+yRn
-----END PGP SIGNATURE-----

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Dietz =?UTF-8?B?UHLDtnBwZXI=?=@21:1/5 to All on Sat Dec 21 22:59:05 2024

Howdy,

Frank Mehnert:

I tried the migration from MySQL to Sqlite3 and failed:

I upgraded everything to current unstable.
I started `akonadi-db-migrator --newengine sqlite`.
The assistant started and executed all steps, then tried to finish and aborted with a core dump. MySQL is still running.

Same here. As a side note, I also installed akonadi-backend-sqlite, but still
a core dump.

Regards,
Dietz

-----BEGIN PGP SIGNATURE-----

iHUEABEKAB0WIQRXLjFJrGUqrtIn2lCong32mc4SfAUCZ2c6KQAKCRCong32mc4S fCQ5AP9kJd75sPZHZrljEcIhyJT+uNCzgH8iLL19q3O5C7smmgEAg/f3MeyFo8kC m5TgztqFAJbyxrJfMTasSfuvYsszKYk=
=/ZyV
-----END PGP SIGNATURE-----

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Martin Steigerwald@21:1/5 to All on Mon Dec 23 10:50:01 2024

Hi.

Frank Mehnert - 22.12.24, 09:47:41 MEZ:

Thanks, will try further. I have to admit that during the first
migration attempt I did not have the akonadi-backend-sqlite installed.

I will try the migration again but will wait for some days, and next
time with logging enabled (thanks Hefee!)

No hurries.

Of course it would be good to figure things out for upcoming stable Debian
12 => 13 upgrades, but there is still some time for that.

There is a way to change the database without migration. Which is what I
did as I was not aware of the migrator at the time. But… it is not the recommended way and in worst case it is not completely lossless. So I will
not describe it here at the moment unless someone specifically asks for
it. But there is a way which also takes care of local filter rules which otherwise can be broken. But there is still some manual configuration
adaption work so using the DB migrator would be much more convenient.

Another way could be exporting and importing with the PIM data exporter,
but not sure whether that works across database changes. Ideally it would. Whether that is completely lossless? No idea. From what I have seen I
think it could be.

However… for upcoming stable upgrades it would be much better to see about getting that database migrator to work just fine. Then there could be something in the release notes and if it is just a short note with a link
to a Debian wiki article about it.

Best,
--
Martin - please no carbon copy to me

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From MERLIN Philippe@21:1/5 to All on Mon Dec 23 10:30:01 2024

This is a multi-part message in MIME format.

Le dimanche 22 décembre 2024, 09:47:41 UTC+1 Frank Mehnert a écrit :

Hi Martin,

On Samstag, 21. Dezember 2024 19:00:45 MEZ Martin Steigerwald wrote:

Frank Mehnert - 21.12.24, 18:02:36 MEZ:

I tried the migration from MySQL to Sqlite3 and failed:

I upgraded everything to current unstable.

Did you restart the session or the computer afterwards?

Yes, I also rebooted.

I started `akonadi-db-migrator --newengine sqlite`.
The assistant started and executed all steps, then tried to finish and aborted with a core dump. MySQL is still running.

[…]

Any hints?

Unfortunately no as I did not use the migrator. A core dump is unexpected to say the least.

Except for asking upstream about it on kdepim-users or reporting a bug on with upstream¹ *if* none about this is reported already. As KDEPIM should 24.12 on your system, I think it would be okay to report directly to upstream. Of course for that it is good you have debug packages set up so the core dump would be useful.

In case you find anything, please let us now.

[1] https://bugs.kde.org

Thanks, will try further. I have to admit that during the first migration attempt I did not have the akonadi-backend-sqlite installed.

I will try the migration again but will wait for some days, and next time with logging enabled (thanks Hefee!)

Unrelated topic: Since kmail was upgraded, the fonts in the message view
look "rough". See attached part of a screenshot (sorry for the attachment). Doesn't matter if I use the fixed font (Hack) or the normal one (Noto Sans). In the font setting dialog both fonts look smooth!

Thanks!

Frank

+ 1
Philippe MERLIN

<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>Le dimanche 22 décembre 2024, 09:47:41 UTC+1 Frank Mehnert a écrit :
> Hi Martin,
> > On Samstag, 21. Dezember 2024 19:00:45 MEZ Martin Steigerwald wrote:
> > Frank Mehnert - 21.12.24, 18:02:36 MEZ:
> > > I tried the migration from MySQL to Sqlite3 and failed:
> > > 
> > > I upgraded everything to current unstable.
> > 
> > Did you restart the session or the computer afterwards?
> > Yes, I also rebooted.
> > > > I started `akonadi-db-migrator --newengine sqlite`.
> > > The assistant started and executed all steps, then tried to finish and
> > > aborted with a core dump. MySQL is still running.
> > 
> > […]
> > 
> > > Any hints?
> > 
> > Unfortunately no as I did not use the migrator. A core dump is unexpected
> > to say the least.
> > 
> > Except for asking upstream about it on kdepim-users or reporting a bug on
> > with upstream¹ *if* none about this is reported already. As KDEPIM should
> > 24.12 on your system, I think it would be okay to report directly to
> > upstream. Of course for that it is good you have debug packages set up so
> > the core dump would be useful.
> > 
> > In case you find anything, please let us now.
> > 
> > [1] https://bugs.kde.org
> > Thanks, will try further. I have to admit that during the first migration
> attempt I did not have the akonadi-backend-sqlite installed.
> > I will try the migration again but will wait for some days, and next time
> with logging enabled (thanks Hefee!)
> > Unrelated topic: Since kmail was upgraded, the fonts in the message view
> look "rough". See attached part of a screenshot (sorry for the attachment).
> Doesn't matter if I use the fixed font (Hack) or the normal one (Noto Sans).
> In the font setting dialog both fonts look smooth!
> > Thanks!
> > Frank
+ 1 Philippe MERLIN
 </body>
</html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From yaros@21:1/5 to All on Mon Jan 13 11:00:02 2025

Dnia sobota, 21 grudnia 2024 22:59:05 CET Dietz Pröpper pisze:
Howdy,

Frank Mehnert:

I tried the migration from MySQL to Sqlite3 and failed:

I upgraded everything to current unstable.
I started `akonadi-db-migrator --newengine sqlite`.
The assistant started and executed all steps, then tried to finish and aborted with a core dump. MySQL is still running.

Same here. As a side note, I also installed akonadi-backend-sqlite, but still a core dump.

Regards,
Dietz

Tried to migrate (after new akonadi migration to testing), but as the rest - core dump and still on mysql backend...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From yaros@21:1/5 to All on Fri Feb 28 09:00:01 2025

Dnia sobota, 21 grudnia 2024 22:59:05 CET Dietz Pröpper pisze:
Howdy,

Frank Mehnert:

I tried the migration from MySQL to Sqlite3 and failed:

I upgraded everything to current unstable.
I started `akonadi-db-migrator --newengine sqlite`.
The assistant started and executed all steps, then tried to finish and aborted with a core dump. MySQL is still running.

Same here. As a side note, I also installed akonadi-backend-sqlite, but still a core dump.

Regards,
Dietz

New akonadi installed today (24.12.2-1), but still no luck with db migration:

yaros@DebYan:~$ akonadi-db-migrator --newengine sqlite
Stopping Akonadi service...
Backup file /home/yaros/.config/akonadi/akonadiserverrc.bkp already exists. Overwrite? [(y)es/(N)o] y
Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
ERROR 1049 (42000): Unknown database 'mysql'
FATAL ERROR: Upgrade failed
org.kde.pim.akonadiserver: Running DB initializer
org.kde.pim.akonadiserver: DB initializer done
org.kde.pim.akonadiserver: Running DB initializer
Naruszenie ochrony pamięci (zrzut pamięci)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Martin Steigerwald@21:1/5 to All on Fri Feb 28 10:20:02 2025

Hi.

yaros - 28.02.25, 08:50:31 MEZ:

Dnia sobota, 21 grudnia 2024 22:59:05 CET Dietz Pröpper pisze:
Howdy,

[…]

New akonadi installed today (24.12.2-1), but still no luck with db
migration:

Thanks for reporting.

I saw that Merlin reported a Debian issue about the migrator crashing:

akonadi-server: Segmentation fault when using akonadi-db-migrator

https://bugs.debian.org/1098891

https://bugs.debian.org/1098894

Hmmm, duplicate? First one has a strace, so maybe merge the later with the first one?

I wonder whether

Bug#1034561: akonadi-server: Switch default Akonadi backend to SQLite

https://bugs.debian.org/1034561

would be good to consider for Trixie.

This could save a lot of new Debian users who start with Trixie from
trying to migrate later. While the migrator appears to crash currently, I
am using SQLite3 engine on several systems with very light Akonadi load
and one system with heavy duty Akonadi load (more than a million of
mails).

It works.

Best,
--
Martin

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Martin Steigerwald@21:1/5 to All on Fri Feb 28 10:30:01 2025

Martin Steigerwald - 28.02.25, 10:13:54 MEZ:

I saw that Merlin reported a Debian issue about the migrator crashing:

akonadi-server: Segmentation fault when using akonadi-db-migrator

https://bugs.debian.org/1098891

https://bugs.debian.org/1098894

Hmmm, duplicate? First one has a strace, so maybe merge the later with
the first one?

Another thing: On quick search I did not find an upstream¹ report about
this issue.

Would someone of you consider to look a bit more thoroughly whether there
is one and if not report one and paste the link here? I think an upstream report is okay to do since 24.12.2 is really the most recent stable
version of KDEPIM.

That would help.

It might be good to run the migrator in a gdb session for the report.

On a segmentation fault there usually is a kernel log entry. That may also help to pinpoint roughly in which file the segmentation fault happened.

[1] https://bugs.kde.org

Thank you,
--
Martin

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Martin Steigerwald@21:1/5 to All on Sat Mar 1 11:00:01 2025

Martin Steigerwald - 28.02.25, 10:13:54 MEZ:

I wonder whether

Bug#1034561: akonadi-server: Switch default Akonadi backend to SQLite

https://bugs.debian.org/1034561

would be good to consider for Trixie.

Sandro already made SQLite default since package version 4:24.05.2-1.

Thank you.

--
Martin - please no carbon copy to me

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Who's Online
Recent Visitors
- Centurion
  Sat Jun 7 21:52:22 2025
  from Berea, Ohio via Telnet
- Tnmoc
  Sat Jun 7 13:44:20 2025
  from Milton Keynes via Telnet
- Tnmoc
  Sat Jun 7 13:40:01 2025
  from Milton Keynes via Telnet
- Plume
  Sat Jun 7 11:13:29 2025
  from Uk via SSH
- Gwylbert
  Sat Jun 7 08:57:45 2025
  from Sydney, Nsw via Telnet
- Centurion
  Sat Jun 7 04:30:40 2025
  from Berea, Ohio via Telnet
- Plume
  Fri Jun 6 22:32:36 2025
  from Uk via Telnet
- Adam Fancher
  Fri Jun 6 18:28:55 2025
  from Winsted, Ct via Telnet

System Info

Sysop:	Keyop
Location:	Huddersfield, West Yorkshire, UK
Users:	486
Nodes:	16 (3 / 13)
Uptime:	140:54:02
Calls:	9,658
Calls today:	6
Files:	13,708
Messages:	6,167,412

KDEPIM with SQLite3

Who's Online

Recent Visitors

System Info