Bug 480543

SUMMARY
This is not a crash report, but this behavior of akonadi and especially akonadis sqlite backend, renders the usability of kdepim with sqlite to useless and leads to many errors users are not expecting. In fact kdepim "feels" very laggy and slow to use.


STEPS TO REPRODUCE
1.  Use sqlite backend:
akonadiserverrc:
[%General]
Driver=QSQLITE

Try to delete more than one thousand mails or prune your trash folder with several thousand mails. Or open a mailbox with several thousand mails.


OBSERVED RESULT
Mails do not get deleted. You get an error that "deleting mails failed" (with no further information about what failed).
Mailfolder do not open in an acceptable period of time, sometimes you get a timeout or nothing happens. You have to restart akonadi to get back to a usable state in that case.
Kontact feels unresponsive and extremely laggy.

EXPECTED RESULT
Mails do get purged (i.e. trash) and mailfolders should load and show in an acceptable amount of time.
Kontact/kmail should feel responsive

SOFTWARE/OS VERSIONS
Linux/KDE Plasma: 
Kontact Version: 5.24.4 (23.08.4)
KDE Plasma Version: 5.27.10
KDE Frameworks Version: 5.113.0
Qt Version: 5.15.12 (kompiliert gegen 5.15.11)

ADDITIONAL INFORMATION
I use kontact with four mail accounts (2xIMAP, GMail and EWS plus a local mailfolder for trash) and three Calendar Sources. The overall amount of Mails is significant, but only one folder exceeds 10k Mails (the local trash folder), the other folders are quite small.


LOGGING INFORMATION
Every now and then while opening a mailfolder with many mails (but none of my folders carries 99999 Mails!):
org.kde.pim.akonadiserver: DATABASE ERROR while PREPARING QUERY:
org.kde.pim.akonadiserver:   Error code: "1"
org.kde.pim.akonadiserver:   DB error:  "too many SQL variables"
org.kde.pim.akonadiserver:   Error text: "too many SQL variables Der Befehl konnte nicht ausgeführt werden"
org.kde.pim.akonadiserver:   Query: "SELECT PimItemTable.id, PimItemTable.rev, PimItemTable.remoteId, PimItemTable.remoteRevision, PimItemTable.gid, PimItemTable.collectionId, PimItemTable.mimeTypeId, PimItemTable.datetime, PimItemTable.atime, PimItemTable.dirty, PimItemTable.size FROM PimItemTable WHERE ( PimItemTable.id IN ( :0, :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, :75, :76, :77, :78, :79, :80, :81, :82, :83, :84, :85, :86, :87, :88, :89, :90, :91, :92, :93, :94, :95, :96, :97, :98, :99, :100, :101, :102, :103, :104, :105, :106, :107, :108, :109, :110, :111, :112, :113, :114, :115, :116, :117, :118, :119, :120, :121, :122, :123, :124, :125, :126, :127, :128, :129, :130, :131, :132, :133, :134, :135, :136, :137, :138, :139, :140, :141, :142, :143, :144, :145, :146, :147, :148, :149, :150, :151, :152, :153, :154, :155, :156,
=== (snip) ====
:99938, :99939, :99940, :99941, :99942, :99943, :99944, :99945, :99946, :99947, :99948, :99949, :99950, :99951, :99952, :99953, :99954, :99955, :99956, :99957, :99958, :99959, :99960, :99961, :99962, :99963, :99964, :99965, :99966, :99967, :99968, :99969, :99970, :99971, :99972, :99973, :99974, :99975, :99976, :99977, :99978, :99979, :99980, :99981, :99982, :99983, :99984, :99985, :99986, :99987, :99988, :99989, :99990, :99991, :99992, :99993, :99994, :99995, :99996, :99997, :99998, :99999 ) )"
This error comes quite often, maybe it's not related to opening a folder? I don't know.


Trying to delete more than a thousand mails or expunge trash:
org.kde.pim.akonadiserver: DATABASE ERROR while PREPARING QUERY:
org.kde.pim.akonadiserver:   Error code: "1"
org.kde.pim.akonadiserver:   DB error:  "Expression tree is too large (maximum depth 1000)"
org.kde.pim.akonadiserver:   Error text: "Expression tree is too large (maximum depth 1000) Der Befehl konnte nicht ausgeführt werden"
org.kde.pim.akonadiserver:   Query: "SELECT PimItemTable.id, PimItemTable.rev, PimItemTable.remoteId, PimItemTable.remoteRevision, PimItemTable.gid, PimItemTable.collectionId, PimItemTable.mimeTypeId, PimItemTable.datetime, PimItemTable.atime, PimItemTable.dirty, PimItemTable.size FROM PimItemTable WHERE ( ( ( PimItemTable.id >= :0 AND PimItemTable.id <= :1 ) OR PimItemTable.id = :2 OR PimItemTable.id = :3 OR PimItemTable.id = :4 OR PimItemTable.id = :5 OR PimItemTable.id = :6 OR PimItemTable.id = :7 OR ( PimItemTable.id >= :8 AND PimItemTable.id <= :9 ) OR PimItemTable.id = :10 OR PimItemTable.id = :11 OR PimItemTable.id = :12 OR ( PimItemTable.id >= :13 AND PimItemTable.id <= :14 ) OR PimItemTable.id = :15 OR PimItemTable.id = :16 OR PimItemTable.id = :17 OR PimItemTable.id = :18 OR PimItemTable.id = :19 OR PimItemTable.id = :20 OR PimItemTable.id = :21 OR PimItemTable.id = :22 OR PimItemTable.id = :23 OR PimItemTable.id = :24 OR PimItemTable.id = :25 OR PimItemTable.id = :26 OR PimItemTable.id = :27 OR PimItemTable.id = :28 OR PimItemTable.id = :29 OR PimItemTable.id = :30 OR PimItemTable.id = :31 OR PimItemTable.id = :32 OR PimItemTable.id = :33 OR PimItemTable.id = :34 OR PimItemTable.id = :35 OR PimItemTable.id = :36 OR PimItemTable.id = :37 OR PimItemTable.id = :38 OR PimItemTable.id = :39 OR PimItemTable.id = :40 OR PimItemTable.id = :41 OR PimItemTable.id = :42 OR PimItemTable.id = :43 OR ( PimItemTable.id >= :44 AND PimItemTable.id <= :45 ) OR PimItemTable.id = :46 OR PimItemTable.id = :47 OR PimItemTable.id = :48 OR PimItemTable.id = :49 OR PimItemTable.id = :50 OR
=== (snip) ====
( PimItemTable.id >= :2765 AND PimItemTable.id <= :2766 ) OR ( PimItemTable.id >= :2767 AND PimItemTable.id <= :2768 ) OR PimItemTable.id = :2769 OR PimItemTable.id = :2770 OR ( PimItemTable.id >= :2771 AND PimItemTable.id <= :2772 ) OR PimItemTable.id = :2773 OR PimItemTable.id = :2774 OR PimItemTable.id = :2775 OR PimItemTable.id = :2776 OR PimItemTable.id = :2777 OR ( PimItemTable.id >= :2778 AND PimItemTable.id <= :2779 ) OR PimItemTable.id = :2780 OR PimItemTable.id = :2781 OR PimItemTable.id = :2782 OR PimItemTable.id = :2783 OR ( PimItemTable.id >= :2784 AND PimItemTable.id <= :2785 ) OR ( PimItemTable.id >= :2786 AND PimItemTable.id <= :2787 ) OR ( PimItemTable.id >= :2788 AND PimItemTable.id <= :2789 ) OR ( PimItemTable.id >= :2790 AND PimItemTable.id <= :2791 ) OR ( PimItemTable.id >= :2792 AND PimItemTable.id <= :2793 ) ) )"

And these occur from time to time
org.kde.pim.akonadiserver: Database error: DataStore::beginTransaction (SQLITE)
org.kde.pim.akonadiserver:   Last driver error: "Der Datensatz konnte nicht abgeholt werden"
org.kde.pim.akonadiserver:   Last database error: "database is locked"

I think this bug is related: #403034