Bug 495675

Summary:	database error on akonadi startup: too long list passed to SQL engine
Product:	[Frameworks and Libraries] Akonadi	Reporter:	Erik Quaeghebeur <bugs.kde.org>
Component:	general	Assignee:	kdepim bugs <kdepim-bugs>
Status:	REPORTED ---
Severity:	grave	CC:	carl
Priority:	NOR
Version:	6.2.1
Target Milestone:	---
Platform:	Gentoo Packages
OS:	Linux
See Also:	https://bugs.kde.org/show_bug.cgi?id=451620
Latest Commit:		Version Fixed In:
Sentry Crash Report:

Description Erik Quaeghebeur 2024-11-01 07:24:25 UTC

SUMMARY
When akonadi gets started after a boot, I get the following database error reported on in my log:

---
akonadiserver[2437]: org.kde.pim.akonadiserver: DATABASE ERROR while PREPARING QUERY:
akonadiserver[2437]: org.kde.pim.akonadiserver:   Error code: "1"
akonadiserver[2437]: org.kde.pim.akonadiserver:   DB error:  "too many SQL variables"
akonadiserver[2437]: org.kde.pim.akonadiserver:   Error text: "too many SQL variables Kon statement niet uitvoeren"
akonadiserver[2437]: org.kde.pim.akonadiserver:   Query: "SELECT DISTINCT ResourceTable.name FROM PimItemTable LEFT JOIN CollectionTable ON ( PimItemTable.collectionId = CollectionTable.id ) LEFT JOIN ResourceTable ON ( CollectionTable.resourceId = ResourceTable.id ) WHERE ( PimItemTable.id IN ( :0, :1, :2, […], :65065, :65066 ) )"
---
I elided the list of PimItemTable.id values; all intermediate values are present. (“Kon statement niet uitvoeren” is Dutch for “Could not execute statement”.)

When I restart akonadi (akonadictl restart), the same happens (I see it reported on the console), but it does not seem to be logged.

The issue is clear: a list is passed that is too long to be processed. The code should never pass a list that is too long to be processed to the SQL engine. All occurrences of lists passed to SQL queries in the code should be sanity checked. Likely all these occurrences can be fixed by rewriting the SQL queries in such a way that the list being passed is implicit in them, as is the case for another occurrence of this phenomenon that I reported in Bug 451620. 

STEPS TO REPRODUCE
1. (Re)start akonadi

OBSERVED RESULT
Database error

EXPECTED RESULT
No database error

SOFTWARE/OS VERSIONS
KDE Plasma Version: 6.1.5
KDE Frameworks Version: 6.6.0 
Qt Version: 6.7.2

ADDITIONAL INFORMATION
I am using the sqlite backend, sqlite version 3.46.1.

If the plan to use sqlite as the default backend continues, this (type of) issue will appear again and again unless the underlying code is fixed to not pass too long lists (silly long, TBH, 65k! elements).