Doctrine\DBAL\Query\QueryException: More than 1000 expressions "lib/private/Files/Cache"

[AndyXheli]

This is different then https://github.com/nextcloud/mail/issues/4584 https://github.com/nextcloud/server/issues/26493

NC 21.0.2

Nextcloud log (data/nextcloud.log)

Nextcloud log

``` {"reqId":"uwz3Tr8HXljsLL1rvUSy","level":3,"time":"2021-06-04T02:16:40+00:00","remoteAddr":"10.241.202.218","user":"admin","app":"core","method":"GET","url":"/ocs/v2.php/apps/serverinfo/api/v1/info?format=json","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/lib/private/Files/Cache/Cache.php","line":606,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Cache.php","line":550,"function":"removeChildren","class":"OC\\Files\\Cache\\Cache","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Wrapper/CacheWrapper.php","line":180,"function":"remove","class":"OC\\Files\\Cache\\Cache","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Updater.php","line":160,"function":"remove","class":"OC\\Files\\Cache\\Wrapper\\CacheWrapper","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/View.php","line":330,"function":"remove","class":"OC\\Files\\Cache\\Updater","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/View.php","line":1180,"function":"removeUpdate","class":"OC\\Files\\View","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/View.php","line":351,"function":"basicOperation","class":"OC\\Files\\View","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Node/Folder.php","line":468,"function":"rmdir","class":"OC\\Files\\View","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/SimpleFS/SimpleFolder.php","line":68,"function":"delete","class":"OC\\Files\\Node\\Folder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Preview/Watcher.php","line":66,"function":"delete","class":"OC\\Files\\SimpleFS\\SimpleFolder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Preview/Watcher.php","line":55,"function":"deleteNode","class":"OC\\Preview\\Watcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Preview/WatcherConnector.php","line":64,"function":"postWrite","class":"OC\\Preview\\Watcher","type":"->"},{"function":"OC\\Preview\\{closure}","class":"OC\\Preview\\WatcherConnector","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/nextcloud/lib/private/Hooks/EmitterTrait.php","line":107,"function":"call_user_func_array"},{"file":"/var/www/nextcloud/lib/private/Hooks/PublicEmitter.php","line":41,"function":"emit","class":"OC\\Hooks\\BasicEmitter","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Node/Root.php","line":139,"function":"emit","class":"OC\\Hooks\\PublicEmitter","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Node/Node.php","line":117,"function":"emit","class":"OC\\Files\\Node\\Root","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Node/File.php","line":76,"function":"sendHooks","class":"OC\\Files\\Node\\Node","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/SimpleFS/SimpleFile.php","line":109,"function":"putContent","class":"OC\\Files\\Node\\File","type":"->"},{"file":"/var/www/nextcloud/lib/private/App/AppStore/Fetcher/Fetcher.php","line":199,"function":"putContent","class":"OC\\Files\\SimpleFS\\SimpleFile","type":"->"},{"file":"/var/www/nextcloud/lib/private/Installer.php","line":444,"function":"get","class":"OC\\App\\AppStore\\Fetcher\\Fetcher","type":"->"},{"file":"/var/www/nextcloud/apps/serverinfo/lib/SystemStatistics.php","line":177,"function":"isUpdateAvailable","class":"OC\\Installer","type":"->"},{"file":"/var/www/nextcloud/apps/serverinfo/lib/SystemStatistics.php","line":86,"function":"getAppsInfo","class":"OCA\\ServerInfo\\SystemStatistics","type":"->"},{"file":"/var/www/nextcloud/apps/serverinfo/lib/Controller/ApiController.php","line":99,"function":"getSystemStatistics","class":"OCA\\ServerInfo\\SystemStatistics","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":218,"function":"info","class":"OCA\\ServerInfo\\Controller\\ApiController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":127,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":157,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":302,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/ocs/v1.php","line":63,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/ocs/v2.php","line":24,"args":["/var/www/nextcloud/ocs/v1.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":"NextStats/4 CFNetwork/1240.0.4 Darwin/20.5.0","version":"21.0.2.1","id":"60ba6b4b4d11c"} ```

[AndyXheli]

@Simounet dose this look the same to you ?

[Simounet]

Nope. Can you explain to me the steps to reproduce it please?

[AndyXheli]

I wish I know how this error came up I'm not sure how to replicate this.

[AndyXheli]

@Simounet i was trying to delete my photos folder i hit delete and just got an error cant delete folder then in a bit i got this error

{"reqId":"JDDnTTPj7uREr2viteje","level":3,"time":"2021-06-23T16:18:44+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/lib/private/Files/Cache/Cache.php","line":606,"function":"execute","class":"OC\DB\QueryBuilder\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Cache.php","line":550,"function":"removeChildren","class":"OC\Files\Cache\Cache","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Scanner.php","line":274,"function":"remove","class":"OC\Files\Cache\Cache","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Scanner.php","line":465,"function":"removeFromCache","class":"OC\Files\Cache\Scanner","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Scanner.php","line":389,"function":"handleChildren","class":"OC\Files\Cache\Scanner","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Scanner.php","line":392,"function":"scanChildren","class":"OC\Files\Cache\Scanner","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Scanner.php","line":392,"function":"scanChildren","class":"OC\Files\Cache\Scanner","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Scanner.php","line":392,"function":"scanChildren","class":"OC\Files\Cache\Scanner","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Scanner.php","line":341,"function":"scanChildren","class":"OC\Files\Cache\Scanner","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Utils/Scanner.php","line":260,"function":"scan","class":"OC\Files\Cache\Scanner","type":"->"},{"file":"/var/www/nextcloud/apps/files/lib/Command/Scan.php","line":158,"function":"scan","class":"OC\Files\Utils\Scanner","type":"->"},{"file":"/var/www/nextcloud/apps/files/lib/Command/Scan.php","line":214,"function":"scanFiles","class":"OCA\Files\Command\Scan","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Command/Command.php","line":255,"function":"execute","class":"OCA\Files\Command\Scan","type":"->"},{"file":"/var/www/nextcloud/core/Command/Base.php","line":169,"function":"run","class":"Symfony\Component\Console\Command\Command","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Application.php","line":1009,"function":"run","class":"OC\Core\Command\Base","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":"60d35ee68185f"}

[alx-tuilmenau]

I got the same error in a different context (cleaning up the trashcan via cronjob), NC 20.0.11:

[core] Error: Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle. at <<closure>>

 0. /data/nextcloud_19/lib/private/Files/Cache/Cache.php line 576
    OC\DB\QueryBuilder\QueryBuilder->execute()
 1. /data/nextcloud_19/lib/private/Files/Cache/Cache.php line 537
    OC\Files\Cache\Cache->removeChildren(OC\Files\Cache\CacheEntry {})
 2. /data/nextcloud_19/lib/private/Files/Cache/Updater.php line 160
    OC\Files\Cache\Cache->remove("files_trashbin/files/LAG.d1616661183")
 3. /data/nextcloud_19/lib/private/Files/View.php line 329
    OC\Files\Cache\Updater->remove("files_trashbin/files/LAG.d1616661183")
 4. /data/nextcloud_19/lib/private/Files/View.php line 1179
    OC\Files\View->removeUpdate(OCA\Files_Trashb ... }}, "files_trashbin/files/LAG.d1616661183")
 5. /data/nextcloud_19/lib/private/Files/View.php line 350
    OC\Files\View->basicOperation("rmdir", "/***username removed***/files ... 3", ["delete"])
 6. /data/nextcloud_19/lib/private/Files/Node/Folder.php line 468
    OC\Files\View->rmdir("/***username removed***/files ... 3")
 7. /data/nextcloud_19/apps/files_trashbin/lib/Trashbin.php line 684
    OC\Files\Node\Folder->delete()
 8. /data/nextcloud_19/apps/files_trashbin/lib/Trashbin.php line 872
    OCA\Files_Trashbin\Trashbin::delete("LAG", "***username removed***", 1616661183)
 9. /data/nextcloud_19/apps/files_trashbin/lib/Trashbin.php line 838
    OCA\Files_Trashbin\Trashbin::deleteFiles([OC\Files\FileIn ... "], "***username removed***", -10854831203.5)
10. /data/nextcloud_19/apps/files_trashbin/lib/Command/Expire.php line 58
    OCA\Files_Trashbin\Trashbin::expire("***username removed***")
11. /data/nextcloud_19/lib/private/Command/CommandJob.php line 35
    OCA\Files_Trashbin\Command\Expire->handle()
12. /data/nextcloud_19/lib/private/BackgroundJob/Job.php line 52
    OC\Command\CommandJob->run("O:33:\"OCA\\Fil ... }")
13. /data/nextcloud_19/lib/private/BackgroundJob/QueuedJob.php line 46
    OC\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})
14. /data/nextcloud_19/cron.php line 127
    OC\BackgroundJob\QueuedJob->execute(OC\BackgroundJob\JobList {}, OC\Log {})

at 2021-07-09T08:21:37+00:00

This patch will solve this (but only my issue with deleting the entries (similar to https://github.com/nextcloud/mail/pull/4906)):

--- lib/private/Files/Cache/Cache.php.orig      2021-07-09 11:46:45.693774314 +0200
+++ lib/private/Files/Cache/Cache.php.new   2021-07-09 11:49:06.101459883 +0200
@@ -572,8 +572,12 @@

                        $query = $this->getQueryBuilder();
                        $query->delete('filecache_extended')
-                               ->where($query->expr()->in('fileid', $query->createNamedParameter($childIds, IQueryBuilder::PARAM_INT_ARRAY)));
-                       $query->execute();
+                               ->where($query->expr()->in('fileid', $query->createParameter('childIds', IQueryBuilder::PARAM_INT_ARRAY)));
+                       $chunks = array_chunk($childIds, 1000);
+                       foreach($chunks as $chunk) {
+                               $query->setParameter('childIds', $chunk, IQueryBuilder::PARAM_INT_ARRAY);
+                               $query->execute();
+                       }

                        /** @var ICacheEntry[] $childFolders */
                        $childFolders = array_filter($children, function ($child) {

[szaimen]

Is this Issue still valid in NC21.0.4? If not, please close this issue. Thanks! :)

[danielfl]

Yes it is still present on v21.0.4 For me happens when a folder has more than 1k files/photos

[danielfl]

[core] Error: Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle. at <<closure>>

 0. /var/www/nextcloud/lib/private/Comments/Manager.php line 656
    OC\DB\QueryBuilder\QueryBuilder->execute()
 1. /var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php line 89
    OC\Comments\Manager->getNumberOfUnreadCommentsForObjects("files", ["1784915","1784 ... "], OC\User\User {})
 2. /var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php line 117
    OCA\DAV\Connector\Sabre\CommentPropertiesPlugin->cacheDirectory(OCA\DAV\Connector\Sabre\Directory {})
 3. /var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php line 89
    OCA\DAV\Connector\Sabre\CommentPropertiesPlugin->handleGetProperties(Sabre\DAV\PropFind {}, OCA\DAV\Connector\Sabre\Directory {})
 4. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php line 1052
    Sabre\DAV\Server->emit("propFind", [Sabre\DAV\PropF ... }])
 5. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php line 984
    Sabre\DAV\Server->getPropertiesByNode(Sabre\DAV\PropFind {}, OCA\DAV\Connector\Sabre\Directory {})
 6. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php line 1661
    Sabre\DAV\Server->getPropertiesIteratorForPath("files/danielfl/ ... I", ["{DAV:}getlastm ... "], 1)
 7. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php line 1646
    Sabre\DAV\Server->writeMultiStatus(Sabre\Xml\Writer ... ]}, Generator {}, false)
 8. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php line 346
    Sabre\DAV\Server->generateMultiStatus(Generator {}, false)
 9. /var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php line 89
    Sabre\DAV\CorePlugin->httpPropFind(Sabre\HTTP\Request {}, Sabre\HTTP\Response {})
10. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php line 472
    Sabre\DAV\Server->emit("method:PROPFIND", [Sabre\HTTP\Requ ... }])
11. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php line 253
    Sabre\DAV\Server->invokeMethod(Sabre\HTTP\Request {}, Sabre\HTTP\Response {})
12. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php line 321
    Sabre\DAV\Server->start()
13. /var/www/nextcloud/apps/dav/lib/Server.php line 332
    Sabre\DAV\Server->exec()
14. /var/www/nextcloud/apps/dav/appinfo/v2/remote.php line 35
    OCA\DAV\Server->exec()
15. /var/www/nextcloud/remote.php line 167
    require_once("/var/www/nextcl ... p")

PROPFIND /remote.php/dav/files/dani/Photos/Ca%20-%20fotos/Weni%20%26%20Ca_Part%20I/
from 192.168.178.20 by danl at 2021-08-17T07:30:10+00:00

[szaimen]

Duplicate of https://github.com/nextcloud/server/issues/27932#issuecomment-880984335