Bug 305543 - "Paramter count mismatch" during akonadi start-up due to broken SQL query
Summary: "Paramter count mismatch" during akonadi start-up due to broken SQL query
Status: RESOLVED FIXED
Alias: None
Product: Akonadi
Classification: Unclassified
Component: general (show other bugs)
Version: 4.8
Platform: unspecified Linux
: NOR normal (vote)
Target Milestone: ---
Assignee: kdepim bugs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-08-21 10:40 UTC by Matthias Nagel
Modified: 2013-11-28 14:39 UTC (History)
3 users (show)

See Also:
Latest Commit:
Version Fixed In: 1.11


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Matthias Nagel 2012-08-21 10:40:04 UTC
If I (re-)start akonadi from the shell with "akonadictl restart" I get the following message a few times

Error during executing query "UPDATE PimItemTable SET atime = :0 WHERE ( CollectionPimItemRelation.Collection_id = :1 AND ( CollectionPimItemRelation.PimItem_id = PimItemTable.id ) )" :  " Parameter count mismatch"
Unable to update item access time 

The reason becomes obvious if one tries to execute this query in the db console of the akonadi console.

A table "CollectionPimItemRelation" does not exist.

Unfortunately I have no glue which akonadi component (which agent) is responsible for this malformed query.

"akonadictl --version" gives 1.8.0
"kontact --version" gives
Qt: 4.8.2
KDE: 4.8.3 (4.8.3)
Kontact: 4.8.3

Reproducible: Always
Comment 1 Jan Hendrik Nielsen 2012-11-11 19:41:42 UTC
I can confirm this behavior using 
KDE SC 4.9.3
Akonadi 1.8.1
Qt: 4.8.2
KDE: 4.9.3
Kontact: 4.9.3
on gentoo linux
Comment 2 Maciej Mrozowski 2013-11-26 02:59:06 UTC
Still happening with akonadi-1.10.3. Sqlite-backend specific bug perhaps?
Comment 3 Daniel Vrátil 2013-11-26 12:22:01 UTC
Table CollectionPimItemRelation exists. If it does not exists on your setup, your database is probably corrupted.

If you are on SQLite, run this query to create the table, then restart Akonadi and see whether it fixes the problem.

CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL,
                                        PimItem_id BIGINT NOT NULL,
                                        PRIMARY KEY (Collection_id, PimItem_id))

(for other backends, more queries are needed).
Comment 4 Maciej Mrozowski 2013-11-27 00:37:11 UTC
Relation CollectionPimItemRelation exists in my case, still query in question fails.

UPDATE PimItemTable SET atime = :0 WHERE ( CollectionPimItemRelation.Collection_id = :1 AND ( CollectionPimItemRelation.PimItem_id = PimItemTable.id ) )

When I actually run in directly on my SQLite akonadi db, it fails differently:
"Query Error: no such column: CollectionPimItemRelation.Collection_id Unable to execute statement"

Is this some MySQL shortcut? (bug reporter doesn't seem to have that problem)
I admit I haven't written SQL in a while but that SQL statement updates PimItemTable but in WHERE clause it references foreign table CollectionPimItemRelation, which would otherwise require separate lookup.
More portable query would look like:

UPDATE PimItemTable SET atime = :0 WHERE id = (SELECT PimItem_id FROM CollectionPimItemRelation WHERE Collection_id = :1 AND PimItem_id = PimItemTable.id )

Still it won't work when directly executed on database propably because of :0 and :1, which are likely stored procedure or prepared statement params (hence value weren't printed in akonadi log).

Anway.. if "parameter mismatch" is the only error here, maybe stored procedure/prepared statement parameter quoting is broken somewhere there?
Comment 5 Maciej Mrozowski 2013-11-27 00:59:51 UTC
Btw, it seems related code is below:

void FetchHelper::updateItemAccessTime()
{
  Transaction transaction( mConnection->storageBackend() );
  QueryBuilder qb( PimItem::tableName(), QueryBuilder::Update );
  qb.setColumnValue( PimItem::atimeColumn(), QDateTime::currentDateTime() );
  ItemQueryHelper::scopeToQuery( mScope, mConnection, qb );

  if ( !qb.exec() )
    qWarning() << "Unable to update item access time";
  else
    transaction.commit();
}

From quick look it's fine. But sorry for silly question, but how is it possible that it generates "WHERE ( CollectionPimItemRelation.Collection_id = :1 AND ( CollectionPimItemRelation.PimItem_id = PimItemTable.id ) )" ?
Thanks to "scopeToQuery"?
(if so, way to go.. no wonder nobody is brave enough to write proper no-SQL backend for akonadi cache yet if it's so difficult to follow what exactly is going on now...)
Comment 6 Daniel Vrátil 2013-11-27 15:20:46 UTC
AAh, right. I completely missed that we are not joining in the CollectionPimItemRelation table, I'll look into it.

The WHERE condition indeed comes from scopeToQuery(). We don't want to support no-SQL (I don't think it makes sense for us), so using methods to generate parts of queries in SQL makes the code easier to maintain and more bullet-proof.
Comment 7 Daniel Vrátil 2013-11-27 20:53:31 UTC
Git commit 6fe8b071f183d64dd934663c8cd1c5e4d587d93d by Dan Vrátil.
Committed on 27/11/2013 at 20:44.
Pushed by dvratil into branch '1.11'.

SQLite: Handle joins in UPDATE queries

Since SQLite does not directly support JOIN expression in UPDATE or UPDATE on
multiple tables at once, we have to convert the JOIN into a subquery in WHERE
condition.

The conversion is far from perfect, but it passes the unit-tests based on the
query that does this (I think there's only one such query in Akonadi that does
this, see FetchHelper::updateItemAccessTime())

This fixes updating atime on items from virtual collections when using SQLite
backend.
FIXED-IN: 1.11

A  +88   -0    server/src/search/searchcollector.cpp     [License: LGPL (v2+)]
A  +98   -0    server/src/search/searchcollector.h     [License: LGPL (v2+)]
A  +62   -0    server/src/search/searchinstance.cpp     [License: LGPL (v2+)]
A  +47   -0    server/src/search/searchinstance.h     [License: LGPL (v2+)]
A  +82   -0    server/src/search/searchresultsretriever.cpp     [License: LGPL (v2+)]
A  +57   -0    server/src/search/searchresultsretriever.h     [License: LGPL (v2+)]
M  +52   -6    server/src/storage/querybuilder.cpp
M  +7    -0    server/src/storage/querybuilder.h
M  +18   -2    server/tests/unittest/querybuildertest.cpp

http://commits.kde.org/akonadi/6fe8b071f183d64dd934663c8cd1c5e4d587d93d
Comment 8 Maciej Mrozowski 2013-11-27 21:25:29 UTC
Wow, so many changes. Was updating item access time actually worth it?
Comment 9 Daniel Vrátil 2013-11-28 14:39:26 UTC
I committed the files in /server/src/search stuff accidentally, the real fix is the +52 in querybuilder.cpp :-)