Bug 406856

I did a little bit of work with akonadi database and realized that all items with remoteId null are in pimitemtable with references to parttable and pimitemflagrelation ,
I got to akonadi database via sokket then issued the commands :

select "akonadi database status of NULL RemoteId  before clean up ";
 select count(*) from akonadi.pimitemtable where remoteId is NULL ;
 select count(*) from akonadi.parttable where pimItemId in ( select id from pimitemtable where remoteId is null ) ;
 select count(*) from akonadi.pimitemflagrelation where PimItem_id  in ( select id from pimitemtable where remoteId is null ) ;

 --  
 delete from akonadi.pimitemflagrelation where PimItem_id  in ( select id from pimitemtable where remoteId is null ) ;
 delete from akonadi.parttable where pimItemId in ( select id from pimitemtable where remoteId is null ) ;
 delete from akonadi.pimitemtable where remoteId is NULL ;
 --
 select "akonadi database status of NULL RemoteId  after clean up ";
 select count(*) from akonadi.pimitemtable where remoteId is NULL ;
 select count(*) from akonadi.parttable where pimItemId in ( select id from pimitemtable where remoteId is null ) ;
 select count(*) from akonadi.pimitemflagrelation where PimItem_id  in ( select id from pimitemtable where remoteId is null ) ;

After that the akonadictl fsck appears clean .

Nick, thank you for your report. I can confirm that this is happening.

You may clear up the fsck messages about "item has no RID" with the queries you stated, however… as far as I understand Akonadi assigns a RID once it stored the item in remote storage. For IMAP this would be the IMAP account and for Maildir resource it would be the Maildir. And right now Akonadi's change recorder has no means to retry this failed operation. So items may never end up in the remote storage! Additionally for Maildir I do not get how this operation to store into (the quite local) remote storage could ever fail, as long as the filesystem has enough space.

So it could be that those items without RID have not yet been stored to the remote storage and that by deleting them from the database (and if payload stored externally from 'file_db_data') you loose those items (mails, events, contacts, …). So there is a real potential for data loss there.

However it also could be that Akonadi just messes up big time like in storing the items into remote storage, but somehow failing to store the remote ID or whatever. I just recently switched from MariaDB to PostgreSQL and already got

"Found 43 items without RID."

as well as

"Moved 6882 unreferenced files to lost+found." (will do a separate bug report about that, since it actually did not even move those files), after just a few weeks of usage. This is with POP3 filling maildir.

One thing: On delays during background jobs I sometimes just did "akonadictl stop" and then as PostgreSQL is not stopped completely on my system (yet another bug report), also stop the PostgreSQL process, so this may contribute to the situation. Did you do something similar at times?

Hello Martin, 
Thank you for your email and your comments .
I agree that some emails may be lost . I took the time to try making/finding some correlations with what the user sees .
-----
First of all I converted to IMAP as per ip provider's tech-support .
So the database contains records from the "old" [maildir? ] system and the new "IMAP" system .  Also I had to move the database from my old machine to a new one [ in fact entire /home  from old was transfered to the new machine ]
So the same userid had acceess to the emails immediately after opensuse leap 15.0 was installed on the new machine ].
I do not know if these changes have any impact [ the hostname was chaged ] 
-----

Based on the way akonadictl presents its resulst it(akonadictl) is NOT a user friendly tool . On the contrary the actual reporting scares the user with those   messages "no RID" or "dirty" or "records removed from data base and moved  in lost-and-found" without at least to ask the user if [s]he wants/agrees with that move .

I looked closely to pimitemtable and it seems that the column remoteId is nullable [ i.e. accepts an undefinned/unidentified/unknowm value ]. 

I believe that the hub of all these issues is not with the database server but with akonadi because switching between MariaDB and Postgresql does not stop the isses from happening. These problems in kmail-database are inherited from the time when kmail was using sqlite .

In my humble opinion, the database server does what is told/comanded by the interface between the user and the database---that is akonadi---.  

It is strange that a such important item as remoteId was translated in a datamodel that accepts it as being nullable . 
That allows that any  event that is not controlled by the code inserts/updates a record as having remoteId  NULL, and from this all users' complaints .

This assumption does not match the reality . I would like to know how it comes that receiving an email---which has a sender and a receiver--- is recorded in the database with a remoteId of NULL value ?

I provide below some info collected from my machine before c;eaning up the records with RID NULl in pimitemtable .
You van notice that that record had a valid collectionId  but has invalid remoteId .  I checked that many other emails with valid remoteId pointer to the same collection Id .

I tend to agree with you that ..... 
``
However it also could be that Akonadi just messes up big time like in storing the items into remote storage, but somehow failing to store the remote ID or whatever. 
`` 

I believe that KDE development should have a look at kmail-database-datamodel if it matches technical-requirements  and/or ifv akonadi handles the email requests properly . 

Also it is very dificult for the user to find any item that is wrong using kmail 
and not to dig in databse tables .  That's the role of akonadictl and/or what ever any USER ORIENTED tools . I believe that akonadikconsole is too powerful for the casual user .  At the same time akonadi&kmail seem to keep their cards close to the vest ..... making easy for developers to point to user errors .

The users have had it for too long asking and asking and asking and nothing to be done .  

Thank you,
Nick
========================= info from my machine =========================
--------------
describe pimitemtable
--------------

Field	Type	Null	Key	Default	Extra
id	bigint(20)	NO	PRI	NULL	auto_increment
rev	int(11)	NO		0	
remoteId	varbinary(255)	YES	MUL	NULL	
remoteRevision	varbinary(255)	YES		NULL	
gid	varbinary(255)	YES	MUL	NULL	
collectionId	bigint(20)	YES	MUL	NULL	
mimeTypeId	bigint(20)	YES	MUL	NULL	
datetime	timestamp	NO		current_timestamp()	
atime	timestamp	NO		current_timestamp()	
dirty	tinyint(1)	YES		NULL	
size	bigint(20)	NO		0	
--------------
select count(*) from pimitemtable
--------------

count(*)
8691

Record without RID as reported by akonadictl fsck [ 38264 item ... RID ] 
38264	5	NULL	NULL	NULL	123	4	2019-04-05 01:12:16	2019-04-05 01:12:16	1	81206

collectionId 123 is one of directories under "Local Folders 



--------------
describe pimitemflagrelation
--------------

Field	Type	Null	Key	Default	Extra
PimItem_id	bigint(20)	NO	PRI	NULL	
Flag_id	bigint(20)	NO	PRI	NULL	
--------------
select count(*) from pimitemflagrelation
--------------

count(*)
13859

Records associated with id 38264 [ from pimitemtable ]
38264	1
38264	5
38264	12
38264	13

--------------
describe parttable
--------------

Field	Type	Null	Key	Default	Extra
id	bigint(20)	NO	PRI	NULL	auto_increment
pimItemId	bigint(20)	NO	MUL	NULL	
partTypeId	bigint(20)	NO	MUL	NULL	
data	longblob	YES		NULL	
datasize	bigint(20)	NO		NULL	
version	int(11)	YES		0	
storage	tinyint(4)	YES		0	
--------------
select count(*) from parttable
--------------

count(*)
30569


Records associated with id 38264 [ from pimitemtable ]

131077	38264	5	131077_r2	80258	1	1
131078	38264	6	X-Virus-Flag: no\nX-Virus-Flag: no\nFrom: ......\nTo: .......\nSubject: 
Fwd: Your ...... .	497	1	0
131079	38264	7	\0\0\0\0\0....	451	2	0
131080	38264	8	\0\0\0*\0n\0d\0o\0r\0d....	104	0	0
131081	38264	9	immediately	11	0	0
131082	38264	10	\0\0\0˰\0\0\0˰\0\0\0\0˰\0\0\0˰\02\0\0\0˰\0\0\0\0\0\0\0�X	32	0	0
131083	38264	11	moveTo5	7	0	0
131084	38264	12	97585928	8	0	0

Nick, which version are you testing on? Do you use KDEPIM 18.12 or later by chance? If so could you please update the version number to the output of 'akonadictl --version'?

Martin, 
akonadictl version is 5.7.3 
kdepim is at level 17.12.3
The levels of all akonadi/kmail/etc installed software  and 
the reports od akonadictl fsck| vacuum are provided as attachments.

After the "remoteId is NULL" cleanup I noticed that all those annoyng messages "wait until ..." are gone . 
I checked my email at the interner provider server and  there are no emails there . 
The IP tech support told me that with IMAP  after an email is received by the client[laptop] it is deleted from the server . 
So I did not understand why the kmail client was checking remote server .
Now it appears that kmail/akonadi tries to solve that NULL remoteId . 

akonadictl fsck's message ' Item "28761" has no RID.' is wrong . 
That item with id=28761 HAS  an RID but its value is set to NULL .
It is very strange/misleading message and the culprit seems to be akonadictl and/or kmail because they do not interpret correctly database/table/record's contents .

Thanks,
Nick

Created attachment 119695 [details]
akonadi_kmail_kontact_mariadb_installed_software_levels.txt

Created attachment 119696 [details]
akonadictl_fsck_vacuum_after_NULL-RID_cleanup.txt

Martin,
After the "remoteId is null" clean I tried to import some records/messages that had "remoteId" issues [  based on pimitemtable I found out their associated collectionid [ i.e. a dir under Local Folders ]. 
The import was done into a new folder called "Import_from_archive" .
It went fine and it created the entire folder stuctures existing under "Inbox" .
Then I MOVED the contents of the imported emails into the old folder .
After that everything under the folder "Import_from_archive" was deleted .
Stop kmail , start kmail the issue akonadictl fsck ....
Gues what ?   The "Item has not RID" was back .

Cleaned the null RIDs again and repeat the import .....
The only diffrence was to COPY from imported structure in the old structure .

Stop kmail , start kmail the issue akonadictl fsck ....
Gues what ?    No "Item has not RID"  !!!

That means thet the move is a physical move of data  whereas copy involves 
some logical processing that recreaates the messages/records .

With this procedure I am very confident that only a few emails were lost .

I do not know who's doing the move and who's doing the copy .

Thanks,
Nick