Bug 452958 - akonadi's mysql locked with an extraneous SELECT query after resuming from sleep
Summary: akonadi's mysql locked with an extraneous SELECT query after resuming from sleep
Status: REPORTED
Alias: None
Product: Akonadi
Classification: Frameworks and Libraries
Component: server (show other bugs)
Version: 5.20.0
Platform: Slackware Linux
: NOR normal with 40 votes (vote)
Target Milestone: ---
Assignee: kdepim bugs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-04-24 20:45 UTC by Ricardo J. Barberis
Modified: 2022-09-26 16:20 UTC (History)
2 users (show)

See Also:
Latest Commit:
Version Fixed In:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Ricardo J. Barberis 2022-04-24 20:45:35 UTC
SUMMARY

Hello all,

A few seconds after resuming from sleep I see an SQL query running in akonadi's mysql that locks any other operation, e.g.: kmail won't show emails, I can't backup akonadi's DB with mysqldump, etc.
This happens everyday after waking my laptop from sleep.

If I stop akonadi the mysql server eventually stops accepting connections but a `ps faxu | grep -v grep | grep akonadi` shows it's still running and the only way to stop it is to `kill -9` said process.

After that akonadi can be started and everything is OK untill the next sleep/resume cycle.

In the mysql log I see lines like these when the stop fails because of this query:

2022-04-23 12:03:46 0 [Note] /usr/libexec/mysqld (initiated by: unknown): Normal shutdown
2022-04-23 12:04:06 0 [Warning] /usr/libexec/mysqld: Thread 6 (user : '<USERNAME>') did not exit

BTW, if instead of stoping akonadi I issue a shutdown to mysql directly (e.g. mysqladmin --socket=/run/user/$(id -u)/akonadi/mysql.socket shutdown), the mysql server still has to be forcibly killed, so I'm not sure if this is akonadi's fault for sending that SELECT query or a mariadb bug.

BTW 2, I don't have the exact SQL query right now and ATM I can't suspend my laptop but I'll update this bug report as soon as I can (later today o tomorrow morning).

STEPS TO REPRODUCE
1. With akonadi running, put the laptop to sleep
2. Wake up the laptop and monitor akonadi's mysql processes list (e.g. mysqladmin --socket=/run/user/$(id -u)/akonadi/mysql.socket processlist)

OBSERVED RESULT
There's a SELECT query which never ends and is unkillable

EXPECTED RESULT
No extraneous SELECT and akonadi/kmail working properly

SOFTWARE/OS VERSIONS
Operating System: Slackware 15.0+ (-current)
KDE Plasma Version: 5.24.4
KDE Frameworks Version: 5.93.0
Qt Version: 5.15.3 (qt5-5.15.3_20220407_9b1efa0e-x86_64-1)
Kernel Version: 5.17.4 (64-bit)
Graphics Platform: X11
Processors: 8 × Intel® Core™ i5-1035G1 CPU @ 1.00GHz
Memory: 15.2 GiB of RAM
Graphics Processor: Mesa Intel® UHD Graphics

akonadictl -v: akonadictl 5.20.0 (22.04.0)
mysql --version: mysql  Ver 15.1 Distrib 10.6.7-MariaDB, for Linux (x86_64) using readline 5.1

ADDITIONAL INFORMATION
Similar bug involving an akonadi crash. Not my case but possibly related?
https://bugs.kde.org/show_bug.cgi?id=422309
Comment 1 Ricardo J. Barberis 2022-04-25 12:52:59 UTC
Process list after resume from sleep:

$ mysqladmin --socket=/run/user/1000/akonadi/mysql.socket processlist -v
+----+-----------+-----------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User      | Host      | db      | Command | Time | State        | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | Progress |
+----+-----------+-----------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 5  | <MY_USER> | localhost | akonadi | Sleep   | 1207 |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | 0.000    |
| 6  | <MY_USER> | localhost | akonadi | Execute | 290  | Sending data | SELECT PartTable.id, PartTable.pimItemId, PartTable.partTypeId, PartTable.data, PartTable.datasize, PartTable.version, PartTable.storage FROM PartTable INNER JOIN PimItemTable ON ( pimItemId = PimItemTable.id ) INNER JOIN PartTypeTable ON ( PartTable.partTypeId = PartTypeTable.id ) WHERE ( PimItemTable.collectionId = ? AND PimItemTable.atime < ? AND PartTable.data IS NOT NULL AND PartTypeTable.ns = ? AND PimItemTable.dirty = ? AND PartTypeTable.name <> ? ) | 0.000    |
| 7  | <MY_USER> | localhost | akonadi | Sleep   | 1204 |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | 0.000    |
| 8  | <MY_USER> | localhost | akonadi | Sleep   | 1204 |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | 0.000    |
| 62 | root      | localhost |         | Query   | 0    | starting     | show full processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                        | 0.000    |
+----+-----------+-----------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
Comment 2 Ricardo J. Barberis 2022-04-25 12:57:10 UTC
Trying to stop akonadi:

$ akonadictl stop

$ akonadictl status
Akonadi Control: running
Akonadi Server: running
Akonadi Server Search Support: available (Remote Search, Akonadi Search Plugin)
Available Agent Types: akonadi_akonotes_resource, akonadi_archivemail_agent, akonadi_birthdays_resource, akonadi_contacts_resource, akonadi_davgroupware_resource, akonadi_ews_resource, akonadi_ewsmta_resource, akonadi_followupreminder_agent, akonadi_google_resource, akonadi_ical_resource, akonadi_icaldir_resource, akonadi_imap_resource, akonadi_indexing_agent, akonadi_knut_resource, akonadi_maildir_resource, akonadi_maildispatcher_agent, akonadi_mailfilter_agent, akonadi_mailmerge_agent, akonadi_mbox_resource, akonadi_migration_agent, akonadi_mixedmaildir_resource, akonadi_newmailnotifier_agent, akonadi_notes_agent, akonadi_notes_resource, akonadi_openxchange_resource, akonadi_pop3_resource, akonadi_sendlater_agent, akonadi_tomboynotes_resource, akonadi_unifiedmailbox_agent, akonadi_vcard_resource, akonadi_vcarddir_resource

$ akonadictl status
Akonadi Control: stopped
Akonadi Server: stopped
Akonadi Server Search Support: available (Remote Search, Akonadi Search Plugin)
Available Agent Types: akonadi_akonotes_resource, akonadi_archivemail_agent, akonadi_birthdays_resource, akonadi_contacts_resource, akonadi_davgroupware_resource, akonadi_ews_resource, akonadi_ewsmta_resource, akonadi_followupreminder_agent, akonadi_google_resource, akonadi_ical_resource, akonadi_icaldir_resource, akonadi_imap_resource, akonadi_indexing_agent, akonadi_knut_resource, akonadi_maildir_resource, akonadi_maildispatcher_agent, akonadi_mailfilter_agent, akonadi_mailmerge_agent, akonadi_mbox_resource, akonadi_migration_agent, akonadi_mixedmaildir_resource, akonadi_newmailnotifier_agent, akonadi_notes_agent, akonadi_notes_resource, akonadi_openxchange_resource, akonadi_pop3_resource, akonadi_sendlater_agent, akonadi_tomboynotes_resource, akonadi_unifiedmailbox_agent, akonadi_vcard_resource, akonadi_vcarddir_resource

$ mysqladmin --socket=/run/user/1000/akonadi/mysql.socket processlist -v
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local server through socket '/run/user/1000/akonadi/mysql.socket' (2)'
Check that mariadbd is running and that the socket: '/run/user/1000/akonadi/mysql.socket' exists!

$ ps faxu | grep -v grep | grep akonadi
<MY_USER>    2518  0.9  1.3 4192940 217156 ?      Sl   abr24  10:07 /usr/libexec/mysqld --defaults-file=/home/<MY_USER>/.local/share/akonadi/mysql.conf --datadir=/home/<MY_USER>/.local/share/akonadi/db_data/ --socket=/run/user/1000/akonadi/mysql.socket --pid-file=/run/user/1000/akonadi/mysql.pid

$ kill -9 2518
Comment 3 Ricardo J. Barberis 2022-05-01 17:13:45 UTC
Workaroud: right after resuming, restart akonadi.

I tried that fot several days and it seems to work so far.
Maybe it could be scripted?
Comment 4 Daniel Roschka 2022-06-02 05:36:26 UTC
I've got the same problem using Debian/sid with Akonadi 21.12.3 and MariaDB 10.6.8.
Comment 5 Martin van Es 2022-08-17 05:59:46 UTC
For what it's worth: I'm seeing the same symptoms, although the problem does not seem as reproducable as with the reporter, but it does happen and always after resume from suspend. The mysqld-akonadi process is unkillable (except for -9) and the worst part is, it doesn't really show in kmail so sometimes I notice real late. I don't have a mysql processlist to show (yet).

For me akonadi-server is 4:22.04.3-0ubuntu1~ubuntu22.04~ppa1 (Kubuntu 22.04 with backports enabled).
Comment 6 Martin van Es 2022-08-18 07:46:24 UTC
So, my akonadi was unresponse this morning after resuming from sleep and I see these unresponsive processes/queries in mysqld:

UPDATE PimItemTable SET rev = ?, remoteId = ?, remoteRevision = ?, gid = ?, collectionId = ?, mimeTypeId = ?, datetime = ?, atime = ?, dirty = ?, size = ? WHERE ( id = ? )

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 ( collectionId = ? AND ( ( remoteId = ? ) ) ) FOR UPDATE

UPDATE PimItemTable SET remoteId = ? WHERE ( ( PimItemTable.id = ? OR PimItemTable.id = ? OR PimItemTable.id = ? OR PimItemTable.id = ? OR PimItemTable.id = ? ) )

UPDATE PimItemTable SET atime = ? WHERE ( ( ( PimItemTable.id >= ? AND PimItemTable.id <= ? ) OR PimItemTable.id = ? ) )

On a sidenote: I disabled apparmor, so it's not likely to interfere in my setup.
Comment 7 Ricardo J. Barberis 2022-08-21 18:46:26 UTC
Still happening :(

Operating System: Slackware64 15.0 (-current)
KDE Plasma Version: 5.25.4
KDE Frameworks Version: 5.97.0
Qt Version: 5.15.5
Kernel Version: 5.19.2 (64-bit)
Graphics Platform: X11
Processors: 8 × Intel® Core™ i5-1035G1 CPU @ 1.00GHz
Memory: 15.2 GiB of RAM
Graphics Processor: Mesa Intel® UHD Graphics

mysql  Ver 15.1 Distrib 10.6.9-MariaDB, for Linux (x86_64) using readline 5.1
Comment 8 Ricardo J. Barberis 2022-08-21 18:48:36 UTC
Forgot to add:

$ akonadictl -v
akonadictl 5.21.0 (22.08.0)
Comment 9 Ricardo J. Barberis 2022-09-20 19:21:56 UTC
A couple days ago I received an update to mariadb version:

$ mysql -V
mysql  Ver 15.1 Distrib 10.6.10-MariaDB, for Linux (x86_64) using readline 5.1

So far I haven't noticed any more stuck queries fomr akonadi (cross my fingers, knock on wood, etc).

akonadi was also updated e few days erlier but I think the maruadb upgrade is what fixed this:

$ akonadictl -v
akonadictl 5.21.1 (22.08.1)

I'll monitor it for a few more days but let's hope this issue has been resolved.
Comment 10 Martin van Es 2022-09-26 16:20:39 UTC
I moved my configuration to a Postgres based one and have yet to see the problem.