First sync of large mailbox - after 'fixing' mem limit problem, now have 'more than 1000 expressions are not allowed in Oracle'

[Kyzarvs]

Expected behavior

Initial sync of a largeish (~150k) mailbox via IMAP

Actual behavior

Continuous "loading messages" on inbox. Sometimes, some of the smaller folders show contents, but the largest folder (inbox) never seems to complete.

Log has numerous entries of this:
{"reqId":"KAWb80AdIZasTLVikgG1","level":3,"time":"2021-03-19T12:39:19+00:00","remoteAddr":"192.168.1.254","user":"Joe McNulty","app":"core","method":"POST","url":"/index.php/apps/mail/api/mailboxes/12/sync","message":{"Exception":"Doctrine\\DBAL\\Query\\QueryException","Message":"More than 1000 expressions in a list are not allowed on Oracle.","Code":0,"Trace":[{"file":"/var/www/nextcloud/apps/mail/lib/Db/MessageMapper.php","line":780,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/nextcloud/apps/mail/lib/Db/MessageMapper.php","line":758,"function":"findRecipients","class":"OCA\\Mail\\Db\\MessageMapper","type":"->","args":[[{"id":61657,"__class__":"OCA\\Mail\\Db\\Message"},{"id":61656,"__class__":"OCA\\Mail\\Db\\Message"},{"id":61655,"__class__":"OCA\\Mail\\Db\\Message"},{"id":61654,"__class__":"OCA\\Mail\\Db\\Message"},{"id":61653,"__class__":"OCA\\Mail\\Db\\Message"},"And 1041 more entries, set log level to debug to see all entries"]]},{"file":"/var/www/nextcloud/apps/mail/lib/Service/Sync/SyncService.php","line":158,"function":"findByIds","class":"OCA\\Mail\\Db\\MessageMapper","type":"->","args":[[60612,60613,60614,60615,60616,"And 1041 more entries, set log level to debug to see all entries"]]},{"file":"/var/www/nextcloud/apps/mail/lib/Service/Sync/SyncService.php","line":126,"function":"getDatabaseSyncChanges","class":"OCA\\Mail\\Service\\Sync\\SyncService","type":"->","args":[{"__class__":"OCA\\Mail\\Account"},{"id":12,"__class__":"OCA\\Mail\\Db\\Mailbox"},[],null]},{"file":"/var/www/nextcloud/apps/mail/lib/Controller/MailboxesController.php","line":161,"function":"syncMailbox","class":"OCA\\Mail\\Service\\Sync\\SyncService","type":"->","args":[{"__class__":"OCA\\Mail\\Account"},{"id":12,"__class__":"OCA\\Mail\\Db\\Mailbox"},42,[],false,null]},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":218,"function":"sync","class":"OCA\\Mail\\Controller\\MailboxesController","type":"->","args":[12,[],true,null]},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":127,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->","args":[{"__class__":"OCA\\Mail\\Controller\\MailboxesController"},"sync"]},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":157,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->","args":[{"__class__":"OCA\\Mail\\Controller\\MailboxesController"},"sync"]},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":302,"function":"main","class":"OC\\AppFramework\\App","type":"::","args":["OCA\\Mail\\Controller\\MailboxesController","sync",{"__class__":"OC\\AppFramework\\DependencyInjection\\DIContainer"},{"id":"12","_route":"mail.mailboxes.sync"}]},{"file":"/var/www/nextcloud/lib/base.php","line":993,"function":"match","class":"OC\\Route\\Router","type":"->","args":["/apps/mail/api/mailboxes/12/sync"]},{"file":"/var/www/nextcloud/index.php","line":37,"function":"handleRequest","class":"OC","type":"::","args":[]}],"File":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","Line":267,"CustomMessage":"More than 1000 expressions in a list are not allowed on Oracle."},"userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:86.0) Gecko/20100101 Firefox/86.0","version":"21.0.0.18"}

Mail app

Mail app version: 1.9.3

Mailserver or service: Dovecot

Server configuration

Operating system: Debian Buster

Web server: Apache2

Database: MariaDB

PHP version: 7.3

Nextcloud Version: 21.0.0

Client configuration

Browser: e.g. Firefox 86

Operating system: W10Pro

New Nextcloud build - after a VM trial, I configured a dedicated machine (32GB RAM, Ryzen 3600, 1TB NVME + 2TB backup spinner) for my team to live-trial before hopefully taking NC company-wide. I realise my mailbox goes back a few years, but was hoping that I could get it synced before I onboard the rest of my team. Currently I'm the only user, I've checked CLI php.ini has -1 max ram and I've upped apache2 php.ini from 768M (where it failed on RAM usage) to 8192M while I get this sync sorted.

Mail server is our own Postfix / Dovecot setup with approximately 2500 users. This mailbox has no isse in Roundcube, Outlook or Thunderbird.

[mat-m]

Yes, Lists in SQL queries for Oracle are limited to 1000 entries. It hurted us before...

[Kyzarvs]

So I take it it is a known bug in the mail app, but there's no fix? Pity, it was handy to have everything in one place for the remote team setup I'm doing, I'll break out mail and keep an eye out for a fix somewhere down the line.

[ChristophWurst]

Duplicate of #4584

So I take it it is a known bug in the mail app, but there's no fix?

We can't fix every reported bug immediately. Fixing bugs takes time. If you want to see things fixed quicker, consider paying someone to do the work for you.