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.

[AndyXheli]

@ChristophWurst Still an issue on NC 21.0.2 Mail APP 1.9.5

{"reqId":"mjemKf9aaaorG9KJ8bHU","level":3,"time":"2021-06-02T14:41:08+00:00","remoteAddr":"","user":"--","app":"core","method":"","url":"--","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":917,"function":"execute","class":"OC\DB\QueryBuilder\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/mail/lib/Service/CleanupService.php","line":60,"function":"deleteOrphans","class":"OCA\Mail\Db\MessageMapper","type":"->"},{"file":"/var/www/nextcloud/apps/mail/lib/Command/CleanUp.php","line":51,"function":"cleanUp","class":"OCA\Mail\Service\CleanupService","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Command/Command.php","line":255,"function":"execute","class":"OCA\Mail\Command\CleanUp","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Application.php","line":1009,"function":"run","class":"Symfony\Component\Console\Command\Command","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Application.php","line":273,"function":"doRunCommand","class":"Symfony\Component\Console\Application","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Application.php","line":149,"function":"doRun","class":"Symfony\Component\Console\Application","type":"->"},{"file":"/var/www/nextcloud/lib/private/Console/Application.php","line":215,"function":"run","class":"Symfony\Component\Console\Application","type":"->"},{"file":"/var/www/nextcloud/console.php","line":100,"function":"run","class":"OC\Console\Application","type":"->"},{"file":"/var/www/nextcloud/occ","line":11,"args":["/var/www/nextcloud/console.php"],"function":"require_once"}],"File":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","Line":268,"CustomMessage":"More than 1000 expressions in a list are not allowed on Oracle."},"userAgent":"--","version":"21.0.2.1","id":"60b799e1395ad"}

[ChristophWurst]

I know. The fix wasn't backported yet.

[AndyXheli]

I know. The fix wasn't backported yet.

Just wanted to make sure! Sorry to bother you! 🙂

[ilsawa]

NC 21.0.3
I don't know what this problem is related to. But the first line of the error message is the same as in this thread.
A couple of months ago I set up a large hard drive and created group folders where I downloaded a lot of files. It seems to me that after that this error appeared:

Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle
./var/www/nextcloud/lib/private/Comments/Manager.php - line 656:
OC\DB\QueryBuilder\QueryBuilder->execute()
/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php - line 89:
OC\Comments\Manager->getNumberOfUnreadCommentsForObjects()
/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php - line 117:
OCA\DAV\Connector\Sabre\CommentPropertiesPlugin->cacheDirectory()
/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php - line 89:
OCA\DAV\Connector\Sabre\CommentPropertiesPlugin->handleGetProperties()
/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php - line 1052:
Sabre\DAV\Server->emit()
/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php - line 984:
Sabre\DAV\Server->getPropertiesByNode()
/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php - line 1661:
Sabre\DAV\Server->getPropertiesIteratorForPath()
/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php - line 1646:
Sabre\DAV\Server->writeMultiStatus()
/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php - line 346:
Sabre\DAV\Server->generateMultiStatus()
/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php - line 89:
Sabre\DAV\CorePlugin->httpPropFind()
/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php - line 472:
Sabre\DAV\Server->emit()
/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php - line 253:
Sabre\DAV\Server->invokeMethod()
/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php - line 321:
Sabre\DAV\Server->start()
/var/www/nextcloud/apps/dav/lib/Server.php - line 332:
Sabre\DAV\Server->exec()
/var/www/nextcloud/apps/dav/appinfo/v2/remote.php - line 35:
OCA\DAV\Server->exec()
/var/www/nextcloud/remote.php - line 167:
require_once("/var/www/ne ... p")

/var/www/nextcloud/data/__groupfolders# ls
1 2 3 4 trash versions
/var/www/nextcloud/data/__groupfolders# for f in *; do [ -d ./"$f" ] && find ./"$f" -exec echo ; | wc -l && echo $f; done
40575
1
28274
2
4451
3
8200
4
5553
trash
17
versions
/var/www/nextcloud/data/__groupfolders# df -h |grep sdf
/dev/sdf1 3,6T 596G 2,9T 18% /var/www/nextcloud/data

[ChristophWurst]

Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle
./var/www/nextcloud/lib/private/Comments/Manager.php - line 656:

nextcloud/server#27203