[Akonadi] [Bug 480543] New: Akonadis sqlite backend does not honor sql limits

Tue Jan 30 15:26:00 GMT 2024

https://bugs.kde.org/show_bug.cgi?id=480543

            Bug ID: 480543
           Summary: Akonadis sqlite backend does not honor sql limits
    Classification: Frameworks and Libraries
           Product: Akonadi
           Version: unspecified
          Platform: Fedora RPMs
                OS: Linux
            Status: REPORTED
          Severity: major
          Priority: NOR
         Component: server
          Assignee: kdepim-bugs at kde.org
          Reporter: scheiter at gonicus.de
                CC: carl at carlschwan.eu
  Target Milestone: ---

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

-- 
You are receiving this mail because:
You are the assignee for the bug.