`Doctrine\DBAL\Query\QueryException`: More than 1000 expressions in a list are not allowed on Oracle

[ghost]

Getting the following error on Nextcloud 21.

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

{"reqId":"LaqWXzUVqo7eg2AoX34h","level":3,"time":"2021-04-09T17:39:07+00:00","remoteAddr":","user":"","app":"core","method":"PROPFIND","url":"/remote.php/dav/files/axheli/Photos/","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/Comments/Manager.php","line":657,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":89,"function":"getNumberOfUnreadCommentsForObjects","class":"OC\\Comments\\Manager","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":117,"function":"cacheDirectory","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"handleGetProperties","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1052,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":984,"function":"getPropertiesByNode","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1661,"function":"getPropertiesIteratorForPath","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1646,"function":"writeMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php","line":346,"function":"generateMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"httpPropFind","class":"Sabre\\DAV\\CorePlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":472,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":253,"function":"invokeMethod","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":321,"function":"start","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Server.php","line":332,"function":"exec","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/appinfo/v2/remote.php","line":35,"function":"exec","class":"OCA\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/remote.php","line":167,"args":["/var/www/nextcloud/apps/dav/appinfo/v2/remote.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":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36","version":"21.0.1.1","id":"6070916208dba"}

[mweinelt]

Happens to me as well while browsing a folder via the Android app. The spinner continues spinning, but backend already errored out.

I'm running NextCloud 21.0.1 with PostgreSQL as db backend.

The issue sounds similar to https://github.com/nextcloud/mail/issues/4584.

{
  "reqId": "fTqBodwF2OeG8BPVzsa8",
  "level": 3,
  "time": "2021-04-10T14:45:09+00:00",
  "remoteAddr": "192.168.178.16",
  "user": "test",
  "app": "core",
  "method": "PROPFIND",
  "url": "/remote.php/webdav/SofortUpload/WhatsApp%20Images/",
  "message": "{\"Exception\":\"Doctrine\\\\DBAL\\\\Query\\\\QueryException\",\"Message\":\"More than 1000 expressions in a list are not allowed on Oracle.\",\"Code\":0,\"Trace\":[{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/lib/private/Comments/Manager.php\",\"line\":657,\"function\":\"execute\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php\",\"line\":89,\"function\":\"getNumberOfUnreadCommentsForObjects\",\"class\":\"OC\\\\Comments\\\\Manager\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php\",\"line\":117,\"function\":\"cacheDirectory\",\"class\":\"OCA\\\\DAV\\\\Connector\\\\Sabre\\\\CommentPropertiesPlugin\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"handleGetProperties\",\"class\":\"OCA\\\\DAV\\\\Connector\\\\Sabre\\\\CommentPropertiesPlugin\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1052,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":984,\"function\":\"getPropertiesByNode\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1661,\"function\":\"getPropertiesIteratorForPath\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1646,\"function\":\"writeMultiStatus\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/dav/lib/DAV/CorePlugin.php\",\"line\":346,\"function\":\"generateMultiStatus\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"httpPropFind\",\"class\":\"Sabre\\\\DAV\\\\CorePlugin\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":472,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":253,\"function\":\"invokeMethod\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":321,\"function\":\"start\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/apps/dav/appinfo/v1/webdav.php\",\"line\":84,\"function\":\"exec\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/remote.php\",\"line\":167,\"args\":[\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/apps/dav/appinfo/v1/webdav.php\"],\"function\":\"require_once\"}],\"File\":\"/nix/store/kk42k7xk0mc4d42z2j0hy2c3s035kgf7-nextcloud-21.0.1/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"Line\":268,\"CustomMessage\":\"More than 1000 expressions in a list are not allowed on Oracle.\"}",
  "userAgent": "Mozilla/5.0 (Android) Nextcloud-android/3.15.1",
  "version": "21.0.1.1"
}

[ghost]

@mweinelt yes but that error looks more with the mail app

[mweinelt]

Yes, but ultimatly runs into the same exception

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

[ghost]

Hopefully it’ll be fixed! I restored my server back to 20 too many issues with 21

[FaySmash]

This probably also breaks the "External Storage" Extension (no uploads possible because of "no Space left")

I get the following error right after the DBAL one:

Exception: Could not json_encode due to invalid non UTF-8 characters in the array: array ( 'status' => 'success', 'data' => array ( 'uploadMaxFilesize' => INF, 'maxHumanFilesize' => 'Hochladen (max. INF PB)', 'freeSpace' => -2, 'quota' => -3, 'used' => 0, 'usedSpacePercent' => 0, 'owner' => 'Administrator', 'ownerDisplayName' => 'Administrator', 'mountType' => 'external', 'mountPoint' => 'Benutzer', ), )
/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 172:

OCP\AppFramework\Http\JSONResponse->render()

/var/www/nextcloud/lib/private/AppFramework/App.php - line 157:

OC\AppFramework\Http\Dispatcher->dispatch()

/var/www/nextcloud/lib/private/Route/Router.php - line 302:

OC\AppFramework\App::main()

/var/www/nextcloud/lib/base.php - line 993:

OC\Route\Router->match()

/var/www/nextcloud/index.php - line 37:

OC::handleRequest()

[ghost]

@ChristophWurst I'm sorry to bother you any way you can have a quick look at this and any suggestions. Please and thank you!!

I know you had some dealings with a similar error caused by the mail app so maybe you can shine some light on this?

{"reqId":"ffyNOSC3cePhlUypVYbn","level":3,"time":"2021-04-20T14:25:40+00:00","remoteAddr":"66.158.36.34","user":"axheli","app":"core","method":"PROPFIND","url":"/remote.php/dav/files/axheli/Photos","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/Comments/Manager.php","line":657,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":89,"function":"getNumberOfUnreadCommentsForObjects","class":"OC\\Comments\\Manager","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":117,"function":"cacheDirectory","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"handleGetProperties","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1052,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":984,"function":"getPropertiesByNode","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1661,"function":"getPropertiesIteratorForPath","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1646,"function":"writeMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php","line":346,"function":"generateMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"httpPropFind","class":"Sabre\\DAV\\CorePlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":472,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":253,"function":"invokeMethod","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":321,"function":"start","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Server.php","line":332,"function":"exec","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/appinfo/v2/remote.php","line":35,"function":"exec","class":"OCA\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/remote.php","line":167,"args":["/var/www/nextcloud/apps/dav/appinfo/v2/remote.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":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.128 Safari/537.36","version":"21.0.1.1","id":"607ee561d007c"}

[unaipuelles]

Same for me. I attach here my information and case:

Expected behavior

Not throwing this exception

Actual behavior

When accessing folders with more or less 1GB of data, throws this exception:

{
  "reqId": "y0Ghs7XZz0SSc4sGNxR6",
  "level": 3,
  "time": "2021-04-21T15:05:44+02:00",
  "remoteAddr": "172.20.0.15",
  "user": "upuelles",
  "app": "core",
  "method": "PROPFIND",
  "url": "/remote.php/dav/files/upuelles/Imagenes/Wha_Mi_Mix_01_04_2020",
  "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/Comments/Manager.php",
        "line": 657,
        "function": "execute",
        "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php",
        "line": 89,
        "function": "getNumberOfUnreadCommentsForObjects",
        "class": "OC\\Comments\\Manager",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php",
        "line": 117,
        "function": "cacheDirectory",
        "class": "OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
        "line": 89,
        "function": "handleGetProperties",
        "class": "OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 1052,
        "function": "emit",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 984,
        "function": "getPropertiesByNode",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 1661,
        "function": "getPropertiesIteratorForPath",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 1646,
        "function": "writeMultiStatus",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php",
        "line": 346,
        "function": "generateMultiStatus",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
        "line": 89,
        "function": "httpPropFind",
        "class": "Sabre\\DAV\\CorePlugin",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 472,
        "function": "emit",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 253,
        "function": "invokeMethod",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 321,
        "function": "start",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
        "line": 332,
        "function": "exec",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
        "line": 35,
        "function": "exec",
        "class": "OCA\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/remote.php",
        "line": 167,
        "args": [
          "/var/www/nextcloud/apps/dav/appinfo/v2/remote.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": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:88.0) Gecko/20100101 Firefox/88.0",
  "version": "21.0.1.1"
}

Mail app

Mail app version: 1.9.5

Mailserver or service: Gmail

Server configuration

Motherboard: Raspberry Pi 4 - 4GB

Operating system: Linux 5.10.17-v7l+ armv7l (Raspberri PI OS)

Web server: Nginx 1.14.2

Database: MariaDB -> 10.3.27-MariaDB-0+deb10u1 Raspbian 10

PHP version: 7.4.16

Nextcloud Version: 21.0.1

Client configuration

Browser: Firefox 88.0

Operating system: Windows 10 Pro 20H2

[ghost]

So i logged into the nextcloud via browser on my phone went under file photos and started to scroll down and then after you start to scroll down looking at the files and i have alot of files under photos is just crashes and says A problem repeatedly occurred and if i go into the log i see this error in this issue just wanted to give some more info.

[orex]

I've had exactly the same problem. The WA is to disable comment app, which is a source of the error. I don't need comments in my NC server, so it is solved the problem for me. Probably the issue should be tagged as comment app issue.

[ghost]

Disabling the comment app may take the error out but dose not fix the issue when you open nextcloud via mobile browser and start to scroll down fast you get the below affect like in the video i can confirm this happens on Nextcloud 21.0.1 then if you go into your error log you will see the error in this ticket.

https://user-images.githubusercontent.com/59488153/115739933-f9977d80-a353-11eb-9d22-265551d8d1c3.mp4

[ghost]

Would anything from this link help https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is

[Simounet]

It seems like a serious issue encountered by a lot of people (including myself) when you have a lot of content (files, images…) in one directory since 21. Any feedback would be really appreciated.

[ghost]

@Simounet I would not tag people based on my expense you get blocked. Plus they have to much work that i sure they hate getting stuff that's its not related to them. I want to apologies for the people that I have tagged I was just trying to help and report the bugs to help the community. My sincere apologies..

[Simounet]

FYI, I temporarily removed the exception from https://github.com/nextcloud/server/blob/master/lib/private/DB/QueryBuilder/QueryBuilder.php#L267 (Line 267 to 275) and everything is back to normal. I assume this code has been introduced for a good reason by #25036 but in the mean time, my NC instance is working correctly again.

[Simounet]

@Simounet I would not tag people based on my expense you get blocked.

You're right. I removed my mentions. Sorry about that.

[ChristophWurst]

https://github.com/nextcloud/server/blob/abb5b5d983be485b7e006085643788e5e007d942/lib/private/Comments/Manager.php#L646 is where some additional logic has be added.

If anyone is interested in a fix you can look into https://github.com/nextcloud/mail/pull/4947 because there we have lots of good examples of how to transform the various types of queries.

[Simounet]

Thanks for the hint @ChristophWurst . I'll look into this if I can.

[ghost]

@Simounet where you able to look into this ?

[whoamiUNIX]

Same problem NC21.0.2 with iOS app.

[Simounet]

@Simounet where you able to look into this ?

I'm sorry but no. I just removed my comment to get the error back but I couldn't reproduce the issue I encountered a month ago with a large file folder. I'll try to go deeper.

[Simounet]

Ok so I get the error back browsing files through the Android App. I fixed it and made a PR that you can check at #27187 . 🤞

[ghost]

@Simounet Thank you so very much I applied the patch so far so good. wooho :)

[Simounet]

@andyxheli Hi, you should update the code with the one on #27187.

[ghost]

@andyxheli Hi, you should update the code with the one on #27187.

@Simounet thank you for taking your time to fix this and help the community! 🙏🏻

[Simounet]

My pleasure. I'm always happy to contribute to this amazing project!

[timowevel1]

Not fixed in v21

[ChristophWurst]

Not fixed in v21

Not helpful. This could be triggered by any app. If you don't post traces of the exception then we can't help you. Please dig deeper and open tickets in the respective app(s). Thanks.

[magikmw]

Pretty sure this fix hasn't been backported to v21 yet. I see that 21.0.3 milestone is around a week away, would be great if this and some other fixes got in. For now my instance is fairly unusable with any of the big media folders.

[ghost]

https://github.com/nextcloud/server/pull/27187 should fix this and i believe it's back ported

[magikmw]

27187 should fix this and i believe it's back ported

Not yet - backport PR https://github.com/nextcloud/server/pull/27203

[mrAceT]

I updated to version 21.0.3, from 21.0.2, the problem persists:

Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle. /var/www/.../public_html/lib/public/AppFramework/Db/QBMapper.php - line 332:

OC\DB\QueryBuilder\QueryBuilder->execute()

/var/www/.../public_html/apps/mail/lib/Db/MessageMapper.php - line 592:

OCP\AppFramework\Db\QBMapper->findEntities()

/var/www/.../public_html/apps/mail/lib/Service/Sync/SyncService.php - line 156:

OCA\Mail\Db\MessageMapper->findIdsByQuery()

/var/www/.../public_html/apps/mail/lib/Service/Sync/SyncService.php - line 126:

OCA\Mail\Service\Sync\SyncService->getDatabaseSyncChanges()

/var/www/.../public_html/apps/mail/lib/Controller/MailboxesController.php - line 161:

OCA\Mail\Service\Sync\SyncService->syncMailbox()

/var/www/.../public_html/lib/private/AppFramework/Http/Dispatcher.php - line 218:

OCA\Mail\Controller\MailboxesController->sync()

/var/www/.../public_html/lib/private/AppFramework/Http/Dispatcher.php - line 127:

OC\AppFramework\Http\Dispatcher->executeController()

/var/www/.../public_html/lib/private/AppFramework/App.php - line 157:

OC\AppFramework\Http\Dispatcher->dispatch()

/var/www/.../public_html/lib/private/Route/Router.php - line 302:

OC\AppFramework\App::main()

/var/www/.../public_html/lib/base.php - line 993:

OC\Route\Router->match()

/var/www/.../public_html/index.php - line 37:

OC::handleRequest()

[ChristophWurst]

@mrAceT see the previous comments. This hasn't been integrated yet.

[toriva]

Good day! Please help. I can't find a solution here, but all links lead here. After NC auto-update via snap to version 22, an error appeared and synchronization works intermittently. Please tell me what to do? Thank you in advance.

Logs

Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle. /var/snap/nextcloud/28586/nextcloud/extra-apps/groupfolders/lib/ACL/RuleManager.php - line 113: OC\DB\QueryBuilder\QueryBuilder->execute() /var/snap/nextcloud/28586/nextcloud/extra-apps/groupfolders/lib/ACL/ACLManager.php - line 77: OCA\GroupFolders\ACL\RuleManager->getRulesForFilesByPath() /var/snap/nextcloud/28586/nextcloud/extra-apps/groupfolders/lib/ACL/ACLManager.php - line 118: OCA\GroupFolders\ACL\ACLManager->getRules() /var/snap/nextcloud/28586/nextcloud/extra-apps/groupfolders/lib/ACL/ACLCacheWrapper.php - line 98: OCA\GroupFolders\ACL\ACLManager->preloadPaths() /var/snap/nextcloud/28586/nextcloud/extra-apps/groupfolders/lib/ACL/ACLCacheWrapper.php - line 68: OCA\GroupFolders\ACL\ACLCacheWrapper->preloadEntries() /snap/nextcloud/28586/htdocs/lib/private/Files/Cache/Wrapper/CacheWrapper.php - line 103: OCA\GroupFolders\ACL\ACLCacheWrapper->getFolderContentsById() /snap/nextcloud/28586/htdocs/lib/private/Files/Cache/Wrapper/CacheWrapper.php - line 103: OC\Files\Cache\Wrapper\CacheWrapper->getFolderContentsById() /snap/nextcloud/28586/htdocs/lib/private/Files/Cache/Wrapper/CacheWrapper.php - line 103: OC\Files\Cache\Wrapper\CacheWrapper->getFolderContentsById() /snap/nextcloud/28586/htdocs/lib/private/Files/Cache/Wrapper/CacheWrapper.php - line 103: OC\Files\Cache\Wrapper\CacheWrapper->getFolderContentsById() /snap/nextcloud/28586/htdocs/lib/private/Files/View.php - line 1461: OC\Files\Cache\Wrapper\CacheWrapper->getFolderContentsById() /snap/nextcloud/28586/htdocs/apps/dav/lib/Connector/Sabre/Directory.php - line 264: OC\Files\View->getDirectoryContent() /snap/nextcloud/28586/htdocs/3rdparty/sabre/dav/lib/DAV/Tree.php - line 200: OCA\DAV\Connector\Sabre\Directory->getChildren() /snap/nextcloud/28586/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php - line 900: Sabre\DAV\Tree->getChildren() /snap/nextcloud/28586/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php - line 982: Sabre\DAV\Server->generatePathNodes() /snap/nextcloud/28586/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php - line 1661: Sabre\DAV\Server->getPropertiesIteratorForPath() /snap/nextcloud/28586/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php - line 1646: Sabre\DAV\Server->writeMultiStatus() /snap/nextcloud/28586/htdocs/3rdparty/sabre/dav/lib/DAV/CorePlugin.php - line 346: Sabre\DAV\Server->generateMultiStatus() /snap/nextcloud/28586/htdocs/3rdparty/sabre/event/lib/WildcardEmitterTrait.php - line 89: Sabre\DAV\CorePlugin->httpPropFind() /snap/nextcloud/28586/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php - line 472: Sabre\DAV\Server->emit() /snap/nextcloud/28586/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php - line 253: Sabre\DAV\Server->invokeMethod() /snap/nextcloud/28586/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php - line 321: Sabre\DAV\Server->start() /snap/nextcloud/28586/htdocs/apps/dav/lib/Server.php - line 333: Sabre\DAV\Server->exec() /snap/nextcloud/28586/htdocs/apps/dav/appinfo/v2/remote.php - line 35: OCA\DAV\Server->exec() /snap/nextcloud/28586/htdocs/remote.php - line 166: require_once("/snap/nextc ... p")

[ChristophWurst]

@toriva report this at https://github.com/nextcloud/groupfolders/issues.

[spicemint]

Running into this issue already for half a year, relates to contact app. One user has +8000 contacts and it keeps throwing error below. Had also posted here https://github.com/nextcloud/contacts/issues/677 but no updates ATM on nextcloud 22.2.3, contact app 4.0.6, arch linux 5.14.16, php 8.0.12, mysql 10.6.4, nginx 1.20.1

[core] Error: More than 1000 expressions in a list are not allowed on Oracle.

at 2021-11-28T20:30:03+00:00

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

/usr/share/webapps/nextcloud/apps/dav/lib/CardDAV/CardDavBackend.php - line 1132:

OC\DB\QueryBuilder\QueryBuilder->execute()

/usr/share/webapps/nextcloud/apps/dav/lib/CardDAV/CardDavBackend.php - line 1030:

OCA\DAV\CardDAV\CardDavBackend->searchByAddressBookIds()

/usr/share/webapps/nextcloud/apps/dav/lib/CardDAV/AddressBookImpl.php - line 119:

OCA\DAV\CardDAV\CardDavBackend->search()

/var/lib/nextcloud/apps/contacts/lib/Service/SocialApiService.php - line 395:

OCA\DAV\CardDAV\AddressBookImpl->search()

/var/lib/nextcloud/apps/contacts/lib/Cron/SocialUpdate.php - line 49:

OCA\Contacts\Service\SocialApiService->updateAddressbooks("*** sensiti ... *")

/usr/share/webapps/nextcloud/lib/private/BackgroundJob/Job.php - line 51:

OCA\Contacts\Cron\SocialUpdate->run()

/usr/share/webapps/nextcloud/lib/private/BackgroundJob/QueuedJob.php - line 45:

OC\BackgroundJob\Job->execute()

/usr/share/webapps/nextcloud/cron.php - line 127:

OC\BackgroundJob\QueuedJob->execute()

[spicemint]

in the mean time on nextcloud 23.0.3, still same errors:

Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle./usr/share/webapps/nextcloud/apps/dav/lib/CardDAV/CardDavBackend.php - line 1138: OC\DB\QueryBuilder\QueryBuilder->execute()/usr/share/webapps/nextcloud/apps/dav/lib/CardDAV/CardDavBackend.php - line 1032: OCA\DAV\CardDAV\CardDavBackend->searchByAddressBookIds()/usr/share/webapps/nextcloud/apps/dav/lib/CardDAV/AddressBookImpl.php - line 121: OCA\DAV\CardDAV\CardDavBackend->search()/var/lib/nextcloud/apps/contacts/lib/Service/SocialApiService.php - line 395: OCA\DAV\CardDAV\AddressBookImpl->search()/var/lib/nextcloud/apps/contacts/lib/Cron/SocialUpdate.php - line 49: OCA\Contacts\Service\SocialApiService->updateAddressbooks("** sensiti ... ")/usr/share/webapps/nextcloud/lib/private/BackgroundJob/Job.php - line 51: OCA\Contacts\Cron\SocialUpdate->run()/usr/share/webapps/nextcloud/lib/private/BackgroundJob/QueuedJob.php - line 45: OC\BackgroundJob\Job->execute()/usr/share/webapps/nextcloud/cron.php - line 128: OC\BackgroundJob\QueuedJob->execute()

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

/usr/share/webapps/nextcloud/apps/dav/lib/CardDAV/CardDavBackend.php - line 1138:

OC\DB\QueryBuilder\QueryBuilder->execute()

/usr/share/webapps/nextcloud/apps/dav/lib/CardDAV/CardDavBackend.php - line 1032:

OCA\DAV\CardDAV\CardDavBackend->searchByAddressBookIds()

/usr/share/webapps/nextcloud/apps/dav/lib/CardDAV/AddressBookImpl.php - line 121:

OCA\DAV\CardDAV\CardDavBackend->search()

/usr/share/webapps/nextcloud/lib/private/ContactsManager.php - line 73:

OCA\DAV\CardDAV\AddressBookImpl->search()

/var/lib/nextcloud/apps/maps/lib/Controller/ContactsController.php - line 58:

OC\ContactsManager->search()

/usr/share/webapps/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 217:

OCA\Maps\Controller\ContactsController->getContacts()

/usr/share/webapps/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 126:

OC\AppFramework\Http\Dispatcher->executeController()

/usr/share/webapps/nextcloud/lib/private/AppFramework/App.php - line 157:

OC\AppFramework\Http\Dispatcher->dispatch()

/usr/share/webapps/nextcloud/lib/private/Route/Router.php - line 302:

OC\AppFramework\App::main()

/usr/share/webapps/nextcloud/lib/base.php - line 1006:

OC\Route\Router->match()

/usr/share/webapps/nextcloud/index.php - line 36:

OC::handleRequest()

why is this issue closed btw, is it resolved?

[p5n]

ArchLinux: nextcloud 25.0.4.1, postgresql 15.2-1 the same issue while accessing /remote.php/dav/calendars/USER/inbox/ with method REPORT

[p5n]

I believe in my case it is because of And 1023 more entries, set log level to debug to see all entries in getAllStoragesForUser(). My instance has more than 1000 groups.

Probably it is fixed by adding:

$groupIds = array_slice($groupIds, 0, 500);

Also I am adding array_slice to getUserGroups and getUserCircles

[Geexirooz]

@ChristophWurst Hello Christoph, I hope you are well. Please be aware that, I got the same issue since I upgraded my server to 25.0.13 version on 30th of October. But the issue seems a bit odd to me. So the cronjob sudo -u www-data php -f /local/nextcloud/cron.php runs every 15 minutes in my server and it runs fine during the day but from 7-7:30pm it generates this error. Of course mentioning the exact time shouldn't be important to you but the point is it happens in a specific period everyday. Does that ring a bell for you or it means something is going wrong in my network during that time? BTW, My machine is Ubuntu 22.04. Here is the error log:

{"reqId":"pjvrzIh0ADQFdQ1KSyfk","level":3,"time":"2023-11-05T19:30:27+00:00","remoteAddr":"","user":"--","app":"core","method":"","url":"--","message":"More than 1000 expressions in a list are not allowed on Oracle.","userAgent":"--","version":"25.0.13.2","exception":{"Exception":"Doctrine\\DBAL\\Query\\QueryException","Message":"More than 1000 expressions in a list are not allowed on Oracle.","Code":0,"Trace":[{"file":"/local/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":327,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/local/nextcloud/apps/activity/lib/Data.php","line":513,"function":"executeStatement","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/local/nextcloud/apps/activity/lib/Data.php","line":380,"function":"deleteActivitiesForMySQL","class":"OCA\\Activity\\Data","type":"->"},{"file":"/local/nextcloud/apps/activity/lib/Data.php","line":365,"function":"deleteActivities","class":"OCA\\Activity\\Dat
 a","type":"->"},{"file":"/local/nextcloud/apps/activity/lib/BackgroundJob/ExpireActivities.php","line":54,"function":"expire","class":"OCA\\Activity\\Data","type":"->"},{"file":"/local/nextcloud/lib/public/BackgroundJob/Job.php","line":78,"function":"run","class":"OCA\\Activity\\BackgroundJob\\ExpireActivities","type":"->"},{"file":"/local/nextcloud/lib/public/BackgroundJob/TimedJob.php","line":103,"function":"start","class":"OCP\\BackgroundJob\\Job","type":"->"},{"file":"/local/nextcloud/lib/public/BackgroundJob/TimedJob.php","line":93,"function":"start","class":"OCP\\BackgroundJob\\TimedJob","type":"->"},{"file":"/local/nextcloud/cron.php","line":152,"function":"execute","class":"OCP\\BackgroundJob\\TimedJob","type":"->"}],"File":"/local/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","Line":264,"message":"More than 1000 expressions in a list are not allowed on Oracle.","query":"DELETE FROM `*PREFIX*activity` WHERE `activity_id` IN (:ids)","exception":{},"CustomMessage":"M
 ore than 1000 expressions in a list are not allowed on Oracle."}}

[ChristophWurst]

I got the same issue

It's not. Yours is for the activity baackground job to expire activities. See https://github.com/nextcloud/activity/issues/1384.