Re: akonadi fsck fails

Daniel Vrátil <[email protected]> Wed, 26 Aug 2015 13:52:22 +0200
Newsgroups	gmane.comp.kde.users.pim
Message-ID	<2069584.YdrBN8U1iI@thor>
On Tuesday, August 25, 2015 3:17:16 PM CEST J. Roeleveld wrote:
> 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 [email protected] 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);

I personally prefer this (specifically the first version) for it's simplicity. 
We don't really care about performance in Janitor, so it does not matter how 
long it takes, as long as it's a reasonable timeframe.

However the QueryBuilder currently does not support nested queries like this 
and implementing that, I'm afraid, would be quite a lot of work - you are more 
than welcome to do it (we might make use of it on one or two more places in 
Akonadi), but for now I think it's not worth the effort :)

> ===
> 
> 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.

Yep, this one will be way easier to implement. The problem is to determine 
what is the maximum size of a "smaller chunk" that the database can still deal 
with. But again, since we don't care about top performance here, spliting to 
say 1000 IDs per chunk should work just fine.

IMO the entire loop should happen in a transaction, so that it's either all or 
nothing. Doing something partially can be dangerous.


Cheers,
Dan

> 
> Please let me know which you would prefer.
> 
> Kind regards,
> 
> Joost

-- 
Daniel Vrátil
Email: [email protected]
Jabber: [email protected]
IRC: dvratil on Freenode (#kde, #kontact, #akonadi)

_______________________________________________
KDE PIM users mailing list
Subscription management: https://mail.kde.org/mailman/listinfo/kdepim-users
signature.asc (application/pgp-signature, 819 B)