NC21, postgres, Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle.

[joergmschulz]

Actual behavior

Using mail on a dovecot mailbox with > 1000 messages.
The app tends to timeout and make the browser think it stopped.
The log displays:
[core] Error: Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle. at <>

0. /www/nextcloud/custom-apps/mail/lib/Db/MessageMapper.php line 764
   OC\DB\QueryBuilder\QueryBuilder->execute()
1. /www/nextcloud/custom-apps/mail/lib/Db/MessageMapper.php line 742
   OCA\Mail\Db\MessageMapper->findRecipients([OCA\Mail\Db\Mes ... "])
2. /www/nextcloud/custom-apps/mail/lib/Service/Sync/SyncService.php line 158
   OCA\Mail\Db\MessageMapper->findByIds([55535,44431,444 ... "])
3. /www/nextcloud/custom-apps/mail/lib/Service/Sync/SyncService.php line 126
   OCA\Mail\Service\Sync\SyncService->getDatabaseSyncChanges(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox {id: 167}, [61398,61397,613 ... "], OCA\Mail\Service\Search\SearchQuery {})
4. /www/nextcloud/custom-apps/mail/lib/Controller/MailboxesController.php line 161
   OCA\Mail\Service\Sync\SyncService->syncMailbox(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox {id: 167}, 42, [61398,61397,613 ... "], true, "is:pi-other")
5. /www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 218
   OCA\Mail\Controller\MailboxesController->sync(167, [61398,61397,613 ... "], false, "is:pi-other")
6. /www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 127
   OC\AppFramework\Http\Dispatcher->executeController(OCA\Mail\Control ... {}, "sync")
7. /www/nextcloud/lib/private/AppFramework/App.php line 157
   OC\AppFramework\Http\Dispatcher->dispatch(OCA\Mail\Control ... {}, "sync")
8. /www/nextcloud/lib/private/Route/Router.php line 302
   OC\AppFramework\App::main("OCA\Mail\Cont ... r", "sync", OC\AppFramework\ ... {}, {id: "167",_route: "mail.mailboxes.sync"})
9. /www/nextcloud/lib/base.php line 993
   OC\Route\Router->match("/apps/mail/api/mailboxes/167/sync")
10. /www/nextcloud/index.php line 37
    OC::handleRequest()

POST /index.php/api/mailboxes/167/sync
f

Mail app

Mail app version:
1.8.3

Mailserver or service:
dovecot imap

Server configuration

Operating system:
Alpine, dockerized

Web server:
nginx

Database: (e.g. MariaDB, SQLite or PostgreSQL)
postgresql

PHP version: (e.g. 7.0)
7.4
Nextcloud Version: (see admin page, e.g. 13.0.2)
21.0.0

Client configuration

Browser: (e.g. Firefox 48)
current firefox

[kesselb]

Introduced by nextcloud/server#25036

[efelon]

This also happens with the files app NC21
(32bit Raspberry PI OS - buster, nginx, mariadb):

app":"core","method":"PROPFIND","url":"/remote.php/dav/files/user/InstantUpload/Camera","message":{"Exception":"D
octrine\\DBAL\\Query\\QueryException","Message":"More than 1000 expressions in a list are not allowed on Oracle.","Code"
:0,"Trace":[{"file":"/var/www/nextcloud/lib/private/Comments/Manager.php","line":657,"function":"execute","class":"OC\\D
B\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPr
opertiesPlugin.php","line":89,"function":"getNumberOfUnreadCommentsForObjects","class":"OC\\Comments\\Manager","type":"-
>","args":["files",["6062","6214","6663","6950","7336","And 2562 more entries, set log level to debug to see all entries

The effect is, that the newest files are not visible. Not in the web UI and not in any client. All users with big folders report the same problem.

For both it is the instant upload folder and mostly images:
User1: 3677 files
User2: 2566 files

Update

This Error appears in the log files on a 64bit installation on a Debian Server as well. And the same for internal or external storage. But the error with not seeing the files immediately is not visible.

[jlinford]

+1. NC21 with 101658 folders and 306737 files (according to occ files:scan). This error appears in the log when manipulating the largest folders. Folders with about 1GB or less of data are OK, but anything larger trips this error.