iOS 18 IMAP requests causing mailbox unavailability

[jered]

I see a few recent threads here about "mailbox locked" errors and have recently started encountering the problem myself. To shed some more light on the matter, I believe this is due to a problematic interaction between iOS 18 Mail and Zimbra. I haven't been able to trace the IMAP requests at fault yet, but I've noticed that requests from User Agent "com.apple.email.maild/3826.200.121.2.3" generate mysql slow queries that have run times in the tens of minutes, and are queries that are many tens of thousands of lines long.

These queries lock the mailbox against other requests for minutes-to-hours and cause the errors we've been seeing.

In my experience, it's not possible to get Apple to fix (or even acknowledge) errors, so I suspect this is going to come down to Zimbra to determine a workaround. As an example, these are the sort of absurd queries being generated inside Zimbra due to Apple's IMAP requests:

SELECT mi.id, mi.index_id, mi.type FROM mboxgroup27.mail_item AS mi WHERE mi.mailbox_id = 27 AND ((((mi.type = 5 OR mi.type = 6 OR mi.type = 16)) AND (((mi.id >= 411 AND mi.id <= 414)) OR ((mi.id >= 416 AND mi.id <= 420)) OR ((mi.id >= 422 AND mi.id <= 430)) OR ((mi.id >= 432 AND mi.id <= 456)) OR ((mi.id >= 460 AND mi.id <= 463)) OR ((mi.id >= 465 AND mi.id <= 471)) OR ((mi.id >= 473 AND mi.id <= 483)) OR ((mi.id >= 485 AND mi.id <= 488)) OR ((mi.id >= 490 AND mi.id <= 491)) OR ((mi.id >= 493 AND mi.id <= 494)) OR ((mi.id >= 496 AND mi.id <= 499)) OR ((mi.id >= 501 AND mi.id <= 508)) OR ((mi.id >= 510 AND mi.id <= 514)) OR ((mi.id >= 520 AND mi.id <= 533)) OR ((mi.id >= 535 AND mi.id <= 539)) OR ((mi.id >= 541 AND mi.id <= 545)) OR ((mi.id >= 547 AND mi.id <= 548)) OR ((mi.id >= 550 AND mi.id <= 556)) OR ((mi.id >= 558 AND mi.id <= 566)) OR ((mi.id >= 568 AND mi.id <= 569)) OR ((mi.id >= 573 AND mi.id <= 576)) OR ((mi.id >= 578 AND mi.id <= 580)) OR ((mi.id >= 584 AND mi.id <= 586)) OR ((mi.id >= 588 AND mi.id <= 592)) OR ((mi.id >= 594 AND mi.id <= 598)) OR ((mi.id >= 600 AND mi.id <= 626)) OR ((mi.id >= 628 AND mi.id <= 635)) OR ((mi.id >= 637 AND mi.id <= 640)) OR ((mi.id >= 642 AND mi.id <= 643)) OR ((mi.id >= 645 AND mi.id <= 650)) OR ((mi.id >= 652 AND mi.id <= 668)) OR ((mi.id >= 670 AND mi.id <= 677)) OR ((mi.id >= 679 AND mi.id <= 683)) OR ((mi.id >= 685 AND mi.id <= 689)) OR ((mi.id >= 691 AND mi.id <= 706)) OR ((mi.id >= 708 AND mi.id <= 720)) OR ((mi.id >= 722 AND mi.id <= 724)) OR ((mi.id >= 726 AND mi.id <= 733)) OR ((mi.id >= 735 AND mi.id <= 743)) OR ((mi.id >= 745 AND mi.id <= 752)) OR ((mi.id >= 754 AND mi.id <= 774)) OR ((mi.id >= 776 AND mi.id <= 797)) OR ((mi.id >= 799 AND mi.id <= 800)) OR ((mi.id >= 802 AND mi.id <= 803)) OR ((mi.id >= 805 AND mi.id <= 809)) OR ((mi.id >= 811 AND mi.id <= 814)) OR ((mi.id >= 816 AND mi.id <= 817)) OR ((mi.id >= 819 AND mi.id <= 821)) OR ((mi.id >= 823 AND mi.id <= 836)) OR ((mi.id >= 838 AND mi.id <= 842)) OR ((mi.id >= 846 AND mi.id <= 857)) OR ((mi.id >= 859 AND mi.id <= 862)) OR ((mi.id >= 866 AND mi.id <= 871)) OR ((mi.id >= 873 AND mi.id <= 876)) OR ((mi.id >= 878 AND mi.id <= 891)) OR ((mi.id >= 893 AND mi.id <= 900)) OR ((mi.id >= 902 AND mi.id <= 904)) OR ((mi.id >= 906 AND mi.id <= 907)) OR ((mi.id >= 909 AND mi.id <= 912)) OR ((mi.id >= 914 AND mi.id <= 917)) OR ((mi.id >= 919 AND mi.id <= 921)) OR ((mi.id >= 923 AND mi.id <= 925)) OR ((mi.id >= 927 AND mi.id <= 930)) OR ((mi.id >= 932 AND mi.id <= 942)) OR ((mi.id >= 944 AND mi.id <= 945)) OR ((mi.id >= 947 AND mi.id <= 950)) OR ((mi.id >= 954 AND mi.id <= 955)) OR ((mi.id >= 957 AND mi.id <= 959)) OR ((mi.id >= 963 AND mi.id <= 966)) OR ((mi.id >= 968 AND mi.id <= 972)) OR ((mi.id >= 974 AND mi.id <= 980)) OR ((mi.id >= 982 AND mi.id <= 983)) OR ((mi.id >= 985 AND mi.id <= 988)) OR ((mi.id >= 990 AND mi.id <= 992)) OR ((mi.id >= 994 AND mi.id <= 997)) OR ((mi.id >= 999 AND mi.id <= 1006)) OR ((mi.id >= 1008 AND mi.id <= 1011)) OR ((mi.id >= 1013 AND mi.id <= 1014)) OR ((mi.id >= 1016 AND mi.id <= 1022)) OR ((mi.id >= 1024 AND mi.id <= 1026)) OR ((mi.id >= 1028 AND mi.id <= 1029)) OR ((mi.id >= 1031 AND mi.id <= 1035)) OR ((mi.id >= 1037 AND mi.id <= 1039)) OR ((mi.id >= 1041 AND mi.id <= 1044)) OR ((mi.id >= 1046 AND mi.id <= 1050)) OR ((mi.id >= 1052 AND mi.id <= 1065)) OR ((mi.id >= 1067 AND mi.id <= 1069)) OR ((mi.id >= 1071 AND mi.id <= 1072)) OR ((mi.id >= 1074 AND mi.id <= 1079)) OR ((mi.id >= 1081 AND mi.id <= 1086)) OR ((mi.id >= 1090 AND mi.id <= 1091)) OR ((mi.id >= 1093 AND mi.id <= 1098)) OR ((mi.id >= 1100 AND mi.id <= 1111)) OR ((mi.id >= 1113 AND mi.id <= 1115)) ...

These go on for pages and pages of logs (so much that I've never managed to actually scroll through an entire query!) and are surely the source of our problems.

[jered]

Digging into the code a bit, I think this is likely Apple Mail sending IMAP SEARCH requests with a fundamentally unreasonable number of clauses.

[jered]

Looking into zm-mailbox/../ImapRequest.java:readSearchClause() there's currently a mechanism to limit requests to a maximum nesting depth (default 100):
https://github.com/Zimbra/zm-mailbox/bl ... java#L1055

I think the quickest fix would be for Zimbra to also count and limit the number of clauses in readSearch() so that these many-tens-of-thousands of clause queries also generate an ImapSearchTooComplexException and fail the SEARCH request rather than hang up the server.

[oetiker]

Any news ? I have the same problem ...
I open up a Case: 01735144
for that.

Release 10.1.2.GA.4633.UBUNTU20_64 NETWORK edition.

[jered]

Any news ? I have the same problem ...
I open up a Case: 01735144

Since I am running FOSS I don't have access to support, but you may want to point them at this thread for my analysis (although they have smart people and have probably reached similar conclusions).

[oetiker]

Since I am running FOSS I don't have access to support, but you may want to point them at this thread for my analysis (although they have smart people and have probably reached similar conclusions).

yes, I will do that. But first I have to answer the standard first round questions ......

Updates on the Case:

• 2024-11-01: Escalated this issue to the development team. Raised the severity of this case to 2

[nuramon]

Hi,
we have the exact same problem. We can reproduce it by starting a search in the iOS mail app. Then it triggers these ultra-long queries, which show up as "Sending data" in the MySQL processlist. This results in deferred mails and issues accessing IMAP for this account, due to the locked mailbox.
We're using Zimbra 9 FOSS.

Thanks @oetiker, glad to see that the DevTeam takes a look at it.

[oetiker]

Zimbra made it offical:

• Bug Number: ZBUG-4531

• Status: Open

• Summary: Performance issues due to Long MySQL "OR" queries for IMAP requests on Apple mobile clients (iOS 18).

• Last Updated At: Tue, 12 Nov 2024 05:28:45 GMT

• Version Fixed: NA

• Product Management's comments on the bug: We acknowledge the impact of the issue. Our engineering team is analyzing the issue.

Recommendation of the Supporter:

Meanwhile, I would suggest you to use ActiveSync on iPhones

update: 2023-11-21, polled support for information about the bug. The answer is: please wait, we have no more information about this. (no date, no update, nothing)

[hisfran]

We are on 8.8.15 NE and are also experiencing this issue.
Zimbra support won't help as this version of Zimbra is EOL.
Please update if Zimbra provides a workaround.

Thank you.
Fran

[oetiker]

Version Fixed: 10.1.4

• Paying customer have to wait about 3 weeks to get the fix.

• FOSS users are more lucky and get the (FIX)https://github.com/Zimbra/zm-mailbox/co ... 2c1ea8021b today.