Make sure that number of query parameters doesn't exceed limitations

[fschrempf]

As #1702 shows, we have queries that have the potential of hitting the Oracle limitation of 1000 parameters per query. We should check all queries and fix any potential issues by rewriting or chunking the queries as done for example in nextcloud/mail#4947, nextcloud/server#27187 or #1804.

[CarlSchwan]

Current usage of in():

• Folder\FolderManager::getFoldersForGroups: This hits the limitation when a user is in more than 1000 groups. Very unlikely.
• Trash\TrashManager::listTrashForFolders: This hits the limitation when a folder has more than 1000 items in the trash. More likely.

And another usage of in() was removed here: #1846

[CarlSchwan]

Found another one in a customer log

"version":"23.0.0.10","exception":{"Exception":"Doctrine\\DBAL\\Query\\QueryException","Message":"More than 1000 expressions in a list are not allowed on Oracle.","Code":0,"Trace":[{"file":"/var/www/html/custom_apps/groupfolders/lib/ACL/RuleManager.php","line":113,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/custom_apps/groupfolders/lib/ACL/ACLManager.php","line":75,"function":"getRulesForFilesByPath","class":"OCA\\GroupFolders\\ACL\\RuleManager","type":"->","args":

[fedisto]

@CarlSchwan I have the same error
{ "method": "PROPFIND", "url": "/remote.php/dav/files/28E6D3C5-CA63-4584-956D-CC595FBDA00F/2-DATA-REPORTINGS/7-Auto/%20-%20Reportings/Auto_Jour/_grise", "message": "More than 1000 expressions in a list are not allowed on Oracle.", "userAgent": "Mozilla/5.0 (Windows) mirall/3.4.1stable-Win64 (build 20211221) (Nextcloud, windows-10.0.19044 ClientArchitecture: x86_64 OsArchitecture: x86_64)", "version": "22.2.3.0", "exception": { "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/groupfolders/lib/ACL/RuleManager.php", "line": 113, "function": "execute", "class": "OC\\DB\\QueryBuilder\\QueryBuilder", "type": "->", "args": [] }, { "file": "/var/www/nextcloud/apps/groupfolders/lib/ACL/ACLManager.php", "line": 75, "function": "getRulesForFilesByPath", "class": "OCA\\GroupFolders\\ACL\\RuleManager", "type": "->", "args": [ { "__class__": "OC\\User\\User" }, 1, [ "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180903.csv", "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180904.csv", "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180905.csv", "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180906.csv", "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180907.csv", "And 1213 more entries, set log level to debug to see all entries" ] ] }, { "file": "/var/www/nextcloud/apps/groupfolders/lib/ACL/ACLManager.php", "line": 109, "function": "getRules", "class": "OCA\\GroupFolders\\ACL\\ACLManager", "type": "->", "args": [ [ "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180903.csv", "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise", "__groupfolders/4/7-Auto/Reportings/Auto_Jour", "__groupfolders/4/7-Auto/Reportings", "__groupfolders/4/7-Auto", "And 1220 more entries, set log level to debug to see all entries" ] ] }, { "file": "/var/www/nextcloud/apps/groupfolders/lib/ACL/ACLCacheWrapper.php", "line": 98, "function": "preloadPaths", "class": "OCA\\GroupFolders\\ACL\\ACLManager", "type": "->", "args": [ [ "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180903.csv", "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180904.csv", "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180905.csv", "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180906.csv", "__groupfolders/4/7-Auto/Reportings/Auto_Jour/_grise/_grise_20180907.csv", "And 1213 more entries, set log level to debug to see all entries" ] ]

it seems that the bug is triggered when there are more than 1000 files in the folder

Nextcloud version : 22.2.3
Group Folders : 10.0.2

[mschoeffmann]

+1 ... exactly same situation (logs) as mentioned by @fedisto ...
nc version 22.2.3
gf version 10.0.2
using the official docker image from https://hub.docker.com/_/nextcloud with tag 22

[CarlSchwan]

If someone want to test it, I created #1891

[mschoeffmann]

@CarlSchwan I manually applied patch #1891 to RuleManager.php file of a NC 22.2.4 GF 10.0.2 installation and the issue seems gone ...
a backport would be great then :-)

[CarlSchwan]

@CarlSchwan I manually applied patch #1891 to RuleManager.php file of a NC 22.2.4 GF 10.0.2 installation and the issue seems gone ... a backport would be great then :-)

Thanks for testing 👍 I will look into backporting it tomorrow

[fedisto]

@CarlSchwan, I also applied manually the patch and it works.
Thanks

[royalroot]

Current usage of in():

* `Folder\FolderManager::getFoldersForGroups`: This hits the limitation when a user is in more than 1000 groups. Very unlikely.

* `Trash\TrashManager::listTrashForFolders`: This hits the limitation when a folder has more than 1000 items in the trash. More likely.

And another usage of in() was removed here: #1846

@CarlSchwan, For my instance the limitation is hit in MountProvider.php -> getMountsForUser. So there are more then 1000 $mountPoints the error is thrown in $this->findConflictsForUser($user, $mountPoints)

This in() throws the error:

// apps/groupfolders/lib/MOunt/MountProvider.php
// function findConflictsForUser
$query->select('path')
    ->from('filecache')
    ->where($query->expr()->eq('storage', $query->createNamedParameter($userHome->getNumericStorageId(), IQueryBuilder::PARAM_INT)))
    ->andWhere($query->expr()->in('path_hash', $query->createNamedParameter($pathHashes, IQueryBuilder::PARAM_STR_ARRAY)));

We use a combination of groupfolders and circles with many groups. So maybe only the Admin-User is affected. But the log is bloated with this error, that it is hard to see other errors.

{
  "reqId": "9lTOgTZASDUAI2bbdm0n",
  "level": 3,
  "time": "2022-04-27T09:50:03+00:00",
  "remoteAddr": "127.0.0.1",
  "user": "Administrator",
  "app": "core",
  "method": "POST",
  "url": "/apps/text/session/sync",
  "message": "More than 1000 expressions in a list are not allowed on Oracle.",
  "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:99.0) Gecko/20100101 Firefox/99.0",
  "version": "22.2.7.1",
  "exception": {
    "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/groupfolders/lib/Mount/MountProvider.php",
        "line": 254,
        "function": "execute",
        "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
        "type": "->",
        "args": []
      },
      {
        "file": "/var/www/nextcloud/apps/groupfolders/lib/Mount/MountProvider.php",
        "line": 110,
        "function": "findConflictsForUser",
        "class": "OCA\\GroupFolders\\Mount\\MountProvider",
        "type": "->",
        "args": [
          {
            "__class__": "OC\\User\\User"
          },
          [
             // removed for anonymization
          ]
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Config/MountProviderCollection.php",
        "line": 108,
        "function": "getMountsForUser",
        "class": "OCA\\GroupFolders\\Mount\\MountProvider",
        "type": "->",
        "args": [
          {
            "__class__": "OC\\User\\User"
          },
          {
            "__class__": "OC\\Files\\Storage\\StorageFactory"
          }
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Filesystem.php",
        "line": 424,
        "function": "addMountForUser",
        "class": "OC\\Files\\Config\\MountProviderCollection",
        "type": "->",
        "args": [
          {
            "__class__": "OC\\User\\User"
          },
          {
            "__class__": "OC\\Files\\Mount\\Manager"
          }
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Node/Root.php",
        "line": 378,
        "function": "initMountPoints",
        "class": "OC\\Files\\Filesystem",
        "type": "::",
        "args": [
          "Administrator"
        ]
      },
      {
        "function": "getUserFolder",
        "class": "OC\\Files\\Node\\Root",
        "type": "->",
        "args": [
          "Administrator"
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Node/LazyFolder.php",
        "line": 65,
        "function": "call_user_func_array",
        "args": [
          [
            {
              "__class__": "OC\\Files\\Node\\Root"
            },
            "getUserFolder"
          ],
          [
            "Administrator"
          ]
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Node/LazyRoot.php",
        "line": 40,
        "function": "__call",
        "class": "OC\\Files\\Node\\LazyFolder",
        "type": "->",
        "args": [
          "getUserFolder",
          [
            "Administrator"
          ]
        ]
      },
      {
        "file": "/var/www/nextcloud/apps/text/lib/Service/DocumentService.php",
        "line": 401,
        "function": "getUserFolder",
        "class": "OC\\Files\\Node\\LazyRoot",
        "type": "->",
        "args": [
          "Administrator"
        ]
      },
      {
        "file": "/var/www/nextcloud/apps/text/lib/Service/DocumentService.php",
        "line": 378,
        "function": "getFileById",
        "class": "OCA\\Text\\Service\\DocumentService",
        "type": "->",
        "args": [
          23870,
          "Administrator"
        ]
      },
      {
        "file": "/var/www/nextcloud/apps/text/lib/Service/ApiService.php",
        "line": 167,
        "function": "getFileForSession",
        "class": "OCA\\Text\\Service\\DocumentService",
        "type": "->",
        "args": [
          {
            "id": 220,
            "__class__": "OCA\\Text\\Db\\Session"
          },
          null
        ]
      },
      {
        "file": "/var/www/nextcloud/apps/text/lib/Controller/SessionController.php",
        "line": 82,
        "function": "sync",
        "class": "OCA\\Text\\Service\\ApiService",
        "type": "->",
        "args": [
          23870,
          220,
          "57dSL5vh9nJk7CRvCrooT9x4UT2ppjvIDUATQ9xaH3zLrdGUKS+pJkRUC7emTcUW",
          0,
          null,
          false,
          false
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
        "line": 217,
        "function": "sync",
        "class": "OCA\\Text\\Controller\\SessionController",
        "type": "->",
        "args": [
          23870,
          220,
          "57dSL5vh9nJk7CRvCrooT9x4UT2ppjvIDUATQ9xaH3zLrdGUKS+pJkRUC7emTcUW",
          0,
          null,
          false,
          false
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
        "line": 126,
        "function": "executeController",
        "class": "OC\\AppFramework\\Http\\Dispatcher",
        "type": "->",
        "args": [
          {
            "__class__": "OCA\\Text\\Controller\\SessionController"
          },
          "sync"
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/AppFramework/App.php",
        "line": 156,
        "function": "dispatch",
        "class": "OC\\AppFramework\\Http\\Dispatcher",
        "type": "->",
        "args": [
          {
            "__class__": "OCA\\Text\\Controller\\SessionController"
          },
          "sync"
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Route/Router.php",
        "line": 302,
        "function": "main",
        "class": "OC\\AppFramework\\App",
        "type": "::",
        "args": [
          "OCA\\Text\\Controller\\SessionController",
          "sync",
          {
            "__class__": "OC\\AppFramework\\DependencyInjection\\DIContainer"
          },
          {
            "_route": "text.Session.sync"
          }
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/base.php",
        "line": 1008,
        "function": "match",
        "class": "OC\\Route\\Router",
        "type": "->",
        "args": [
          "/apps/text/session/sync"
        ]
      },
      {
        "file": "/var/www/nextcloud/index.php",
        "line": 36,
        "function": "handleRequest",
        "class": "OC",
        "type": "::",
        "args": []
      }
    ],
    "File": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
    "Line": 270,
    "CustomMessage": "More than 1000 expressions in a list are not allowed on Oracle."
  }
}

Groupfolders: 10.0.2
Nextcloud: 22.2.7