Fixing things Akonadi doesn't with some SQL-fu

[Martin Steigerwald]

Hello Jerome.

Jerome Yuzyk - 20.03.18, 20:23:
> So while I wait for Akonadi to reliaby do whatever magic was promised us
> long ago it still left me with a buggered database that makes every new
> mail pick- up an adventure, including this folder. Hopefully there are at
> least plans to make akonadictl fsck actually fix things some day so it's
> easier to wait.

Which version of Akonadi + KMail do you use?

> Every time KMail gags on a "Retrieving..." and I shut it down and have to
> eventually kill a lingering kmail process and then cleanup after akonadictl
> stop crashes I run akonadictl fsck and it gives me the usual list of
> 
> Item "122837" has no RID.
> Item "138602" has no RID.
> Item "170068" has no RID.
> Item "201887" has no RID.
> Item "201888" has no RID.
> Item "201889" has no RID.
> Item "201892" has no RID.
> 
> which has been growing.
> 
> I've gone into akonadiconsole and even though it crashes too when trying to
> view one of those no-RID Items (all KMail messages) I can see that in fact
> the RID column is empty for the Items that were flagged.

Does it only crash when accessing items without RID? Probably this is just a 
co-incidence.

> So how could I manually clear out those Items, either with akonadiconsole or
> the SQL cli, or even PHPMyAdmin? I've read through
> https://techbase.kde.org/
> KDE_PIM/Akonadi/Development_Tools#Access_to_the_Server_Database but before
> I dive in on my own has anyone else ever done any Akonadi DB surgery using
> any of these methods? I have some SQL DB experience, and have hand-edited
> MythTV SQL tables using PHPMyAdmin before, and it's worth learning more so
> I can keep using KMail.
> 
> Has anyone attempted such a thing?

I suggest you read through my second post in the thread:

Re: Review of database aspect of Akonadi, Akonadi concepts and a master plan
https://marc.info/?l=kde-pim&m=152137731726228&w=2

It gives more details about the meaning of these "Itam nnn has no RID" 
messages. And the major change in Akonadi that will fix the issue: Server-side 
change recording.

Just removing those entries from the database may result in data loss as item 
without RID are items that only exist in the Akonadi database cause for some 
reason the Akonadi resource was not able to play back the (changed) item to 
the backend storage like for example an IMAP account.

If you really want to mess with the database, I suggest you also read through:

https://community.kde.org/KDE_PIM/Akonadi/Architecture

Dan summarizes there the major concepts of Akonadi. There are some bits still 
missing, but as is it should already give a good overview on how Akonadi 
works. At least it helped me a lot to understand it a bit better.

Also you may review the summary Pablo gave about the current state of the 
database analysis in:

Re: Review of database aspect of Akonadi, Akonadi concepts and a master plan
https://marc.info/?l=kde-pim&m=152155153509020&w=2

Together with the writing of Dan, it should give you good understanding of the 
underlying architecture.

In any case: The item without RID messages may not be the cause of the issues 
you describe. So I suggest you´d rather not delete them until you are sure 
that they are the issue, or you have made a backup of the Akonadi database 
before.

Thanks,
-- 
Martin