Long running SQL queries blocking mailboxes

Hi,

we have a problem with (mostly) one mailbox, that the user cannot retrieve mails/attachments and is unable to send mails or login via webinterface.
It took some time investigating, but apparently it comes down to this:

Every time when this happens, we see the mysql-process is using 100% of one CPU core and we see something like this query in the mysql processlist:

#> SHOW FULL PROCESSLIST;
Id      User    Host    db      Command Time    State   Info    Progress
6504    zimbra  localhost:44998 zimbra  Sleep   0               NULL    0.000
7479    zimbra  localhost:46622 zimbra  Sleep   2               NULL    0.000
28951   zimbra  localhost:41804 zimbra  Sleep   16              NULL    0.000
38392   zimbra  localhost:48888 zimbra  Query   1127    Sending data    SELECT COUNT(*) FROM mboxgroup20.mail_item AS mi WHERE mi.mailbox_id = 20 AND ((((mi.type = 0x05 OR mi.type = 0x06 OR mi.type = 0x10)) AND ((mi.id = 1013345) OR (mi.id = 1013349) OR (mi.id = 1013358) 
OR (mi.id = 1013375) OR (mi.id = 1013402) OR (mi.id = 1013412) OR (mi.id = 1013422) OR (mi.id = 1013438) OR (mi.id = 1013441) OR (mi.id = 1013444) OR (mi.id = 1013448) OR (mi.id = 1013455) OR (mi.id = 1013464) OR (mi.id = 1013466) OR (mi.id = 1013496) OR (mi.id = 1013512) OR (mi.id = 1013525) OR (mi.id = 1013531) OR (mi.id = 1013537) OR (mi.id = 1013555) OR (mi.id = 1013563) OR (mi.id = 1013565) OR (mi.id = 1013580) OR (mi.id = 1013585) OR (mi.id = 1013615) OR (mi.id = 1013624) OR (mi.id = 1013632) OR (mi.id = 1013638) OR (mi.id = 1013647) OR (mi.id = 1013651) OR (mi.id = 1013664) OR (mi.id = 1013677) OR (mi.id = 1013685) OR (mi.id = 1013687) OR (mi.id = 1013690) OR (mi.id = 1013725) OR (mi.id = 1013740) OR (mi.id = 1013750) OR (mi.id = 1013753) OR (mi.id = 1013760) OR (mi.id = 1013769) OR (mi.id = 1013799) OR (mi.id = 1013805) OR (mi.id = 1013812) OR (mi.id = 10138 [...]

Does anyone know, what this process does and why it is stuck in "sending data"? The query is huge, I cut it off in the excerpt above.

When I kill this process, everything seems to work fine after that.
The mailbox.log logfile then shows this error "imap - SEARCH failed":

2024-11-06 11:36:45,418 WARN  [ImapServer-77] [name=@.com;mid=20;ip=X.X.X.X;oip=Y.Y.Y.Y;via=X.X.X.X(nginx/1.24.0);ua=com.apple.email.maild/3818.100.11.2.5;cid=310619;] imap - SEARCH failed

This is rather frustating, I cannnot run around and kill mysql processes in order to work. The user is using an iPhone, but we already ditched that and set the mailaccount up on a new iPhone, it still shows up with this problem. So we can rule that out, it must have something to do with the server.

We are using Zimbra Release 9.0.0_ZEXTRAS_20240927.
The issue started over month ago. All other mailboxes seem to run fine.

Hope you can help me with this.