[kdepim-users] akonadi fsck fails

[J. Roeleveld]

On Tuesday, August 25, 2015 01:17:11 PM Daniel Vrátil wrote:
> On Tuesday, August 25, 2015 11:16:15 AM CEST J. Roeleveld wrote:
> > On Monday, August 24, 2015 04:39:37 PM Daniel Vrátil wrote:
> > > On Friday, August 21, 2015 9:16:51 AM CEST J. Roeleveld wrote:
> > > > Hi all,
> > > > 
> > > > Not sure if this is the correct list to post this.
> > > > 
> > > > When I run "akonadictl fsck", I notice an error in the
> > > > "akonadiserver.error" logfile showing a SQL-error.
> > > > 
> > > > The SQL that is used is over 100,000 characters long, with over 54,000
> > > > variables that need to be loaded in during the execute-phase.
> > > > 
> > > > Is there a way to clean up the "PimItemFlagRelation" table manually to
> > > > ensure this error stops?
> > > 
> > > Hi,
> > > 
> > > that's a known problem, the query is simply too large and MySQL refuses
> > > to
> > > execute it (same with PostgreSQL). I wouldn'd worry too much though - it
> > > does not mean that the database is corrupted or anything.  And since we
> > > use
> > > database constraints the PimItemFlagRelation table should not contain
> > > any
> > > orphaned records (this was added mostly for SQLite). It also does not
> > > affect the rest of fsck process, other checks are executed.
> > > 
> > > I should probably look at making the query saner, but that's not the top
> > > priority right now :)
> > > 
> > > Cheers,
> > > Dan
> > 
> > Hi Dan,
> > 
> > Thank you for this reply.
> > If you can point to where in the code this query is created, I could look
> > into this issue.
> > I, unfortunately, don't, currently, have the time to go through the entire
> > codebase.
> 
> Sure. It's in akonadi.git, depending on if you are using 1.13 branch or
> Applications/15.08, the location is
> 
> 1.13: server/src/storagejanitor.cpp
> Applications/15.08, master: src/server/storagejanitor.cpp
> 
> the check that fails is in StorageJanitor::findOrphanedPimItemFlags()
> 
> Feel free to ask on kde-pim at kde.org or #akonadi if you have any questions or
> need some help.

Dan,

I am using 1.13 at the moment.

I had some trouble understanding the way the queries are built, but I think I 
have the process now.

Currently, it does:

select pimitem_id              
from pimitemflagrelation
left join
pimitemtable
on pimitemflagrelation.pimitem_id = pimitemtable.id;

The result-set is then copied into " QVector<ImapSet::Id> imapIds; "
and the amount of records counted.

If the count >0, the ImapIds QVector is then used as input for the delete-
statement creation.

(On my system, the above query returns 753,634 records)

I see 2 possible solutions:
1) Create a DELETE-statement which includes the above SELECT statement
=== Either of these 2 would do the trick ===
delete from pimitemflagrelation 
where not exists 
   (select 1 from pimitemtable where pimitemtable.id = 
pimitemflagrelation.pimitem_id);

delete from pimitemflagrelation 
   where pimitemflagrelation.pimitem_id in (
         select pimitem_id              
         from pimitemflagrelation
         left join
         pimitemtable
         on pimitemflagrelation.pimitem_id = pimitemtable.id);
===

2) Split the ImapIds over multiple smaller sets and process those in the same 
way the current code works.


I think option 2 would stay the closest to the current coding methods and has 
the benefit that if it fails somewhere, part of the orphans could already be 
cleared, providing a better chance to clear all orphans on the next run.


Please let me know which you would prefer.

Kind regards,

Joost
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: This is a digitally signed message part.
URL: <http://mail.kde.org/pipermail/kdepim-users/attachments/20150825/3e670407/attachment.sig>
-------------- next part --------------
_______________________________________________
KDE PIM users mailing list
Subscription management: https://mail.kde.org/mailman/listinfo/kdepim-users