[Bug]: FilesMetadataService fails with large file ID arrays in MetadataRequestService

⚠️ This issue respects the following points: ⚠️

• This is not a troubleshooting question, general support matter, or webserver/proxy problem, but likely a bug (if unsure, ask the Community Help Forum).
• This issue is not already reported on Github OR solved at the Community Help Forum (I've searched!).
• I'm using a maintained major version of Nextcloud Server and tested against the latest patch level. (Supported major versions and current patch levels).
• I agree to follow Nextcloud's Code of Conduct.
• I've tried my best to provide clear reproduction steps that someone unfamiliar with this bug could use to reproduce it.

Bug description

When scanning large SMB external storages, Nextcloud can fail with database query exceptions caused by very large IN() clauses in FilesMetadata.

Affected file:
lib/private/FilesMetadata/Service/MetadataRequestService.php

The errors observed are:
More than 1000 expressions in a list are not allowed on Oracle.
and:
The number of parameters must not exceed 65535. Restriction by PostgreSQL.

The database backend in our environment is MariaDB. The exception is generated by Nextcloud's QueryBuilder before the query reaches the database.

The issue was reproduced during WebDAV PROPFIND operations on large SMB external storage directories.

The affected methods are:

• dropMetadataForFiles()
• getMetadataFromFileIds()

In dropMetadataForFiles(), the method creates chunks of 1000 IDs but the query uses the original $fileIds array instead of the current $chunk.

In getMetadataFromFileIds(), the input array is not split into chunks before creating the IN() expression.

Processing file IDs in chunks of 1000 fixes the issue.

Steps to reproduce

1. Configure Nextcloud with a large SMB external storage.
2. Have a directory containing a large number of files.
3. Access the directory through WebDAV or the web interface.
4. Trigger metadata cleanup/update operations affecting many file IDs.
5. The request fails with a QueryException caused by the size of the generated IN() clause.

Expected behavior

Large directories and metadata operations should complete successfully regardless of the number of affected file IDs.

Nextcloud should handle large file ID lists internally (for example by chunking queries) instead of generating SQL statements that exceed database/query builder limits.

Nextcloud Server version

34

Operating system

RHEL/CentOS

PHP engine version

PHP 8.2

Web server

Apache (supported)

Database engine version

MariaDB

Is this bug present after an update or on a fresh install?

Updated from a MINOR version (ex. 32.0.1 to 32.0.2)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

{
    "system": {
        "config_is_read_only": false,
        "default_quota": "1GB",
        "default_phone_region": "ES",
        "maintenance_window_start": 1,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud.ihm.local",
            "cloud.ihm.local:443",
            "192.168.11.61",
            "chat.ihm.local",
            "192.168.15.11",
            "192.168.15.12",
            "192.168.15.13",
            "ihm.local",
            "colabora.ihm.local"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "34.0.3.2",
        "overwrite.cli.url": "https:\/\/cloud.ihm.local",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "ldapProviderFactory": "OCA\\User_LDAP\\LDAPProviderFactory",
        "maintenance": false,
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "mail_smtpauth": 1,
        "mail_smtptimeout": 30,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "loglevel": 2,
        "forbidden_filename_basenames": [
            "con",
            "prn",
            "aux",
            "nul",
            "com0",
            "com1",
            "com2",
            "com3",
            "com4",
            "com5",
            "com6",
            "com7",
            "com8",
            "com9",
            "com\u00b9",
            "com\u00b2",
            "com\u00b3",
            "lpt0",
            "lpt1",
            "lpt2",
            "lpt3",
            "lpt4",
            "lpt5",
            "lpt6",
            "lpt7",
            "lpt8",
            "lpt9",
            "lpt\u00b9",
            "lpt\u00b2",
            "lpt\u00b3"
        ],
        "forbidden_filename_characters": [
            "<",
            ">",
            ":",
            "\"",
            "|",
            "?",
            "*",
            "\\",
            "\/"
        ],
        "forbidden_filename_extensions": [
            " ",
            ".",
            ".filepart",
            ".part"
        ],
        "allow_local_remote_servers": true,
        "mail_smtpsecure": "ssl",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379,
            "timeout": 0
        },
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "updater.release.channel": "stable",
        "appstoreenabled": true,
        "appstoreurl": "https:\/\/apps.nextcloud.com\/api\/v1",
        "logtimezone": "Europe\/Madrid",
        "app_install_overwrite": [
            "yumisign_nextcloud",
            "duplicatefinder",
            "electronicsignatures",
            "login_notes",
            "organization_folders",
            "files_bpm"
        ],
        "libresign.skip_system_fingerprint": true,
        "filesystem_check_changes": 1,
        "theme": "",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***"
    }
}

List of activated Apps

Enabled:
  - admin_audit: 1.24.0
  - analytics: 6.7.2
  - announcementcenter: 7.5.0
  - approval: 3.3.1
  - appstore: 1.0.0
  - assistant: 3.5.0
  - autofirma: 0.1.7
  - bruteforcesettings: 7.0.0
  - calendar: 6.5.3
  - circles: 34.0.0
  - cloud_federation_api: 1.18.0
  - comments: 1.24.0
  - contacts: 8.7.6
  - contactsinteraction: 1.15.0
  - dashboard: 7.14.0
  - dav: 1.40.0
  - deck: 1.18.3
  - drawio: 4.3.8
  - encryption: 2.22.0
  - federatedfilesharing: 1.24.0
  - federation: 1.24.0
  - files: 2.6.0
  - files_accesscontrol: 5.0.0
  - files_antivirus: 6.4.0
  - files_automatedtagging: 5.0.0
  - files_confidential: 5.1.0
  - files_downloadlimit: 5.2.0
  - files_external: 1.26.0
  - files_lock: 34.0.1
  - files_pdfviewer: 7.0.0-dev.0
  - files_reminders: 1.7.0
  - files_retention: 5.0.0
  - files_sharing: 1.26.0
  - files_trashbin: 1.24.0
  - files_versions: 1.27.0
  - files_versions_s3: 1.3.2
  - firstrunwizard: 7.0.0-dev.0
  - forms: 5.3.5
  - groupfolders: 22.0.6
  - integration_itop: 1.4.2
  - integration_mattermost: 3.2.0
  - integration_openproject: 3.1.1
  - integration_paperless: 1.0.13
  - logreader: 7.0.0
  - lookup_server_connector: 1.22.0
  - mail: 5.10.13
  - nextcloud_announcements: 6.0.0
  - notes: 6.0.2
  - notifications: 7.0.0-dev.1
  - notify_push: 1.4.0
  - oauth2: 1.22.0
  - office: 1.0.0
  - password_policy: 6.0.0-dev.0
  - passwords: 2026.7.10
  - photos: 7.0.0
  - privacy: 6.0.0-dev.1
  - profile: 1.3.0
  - provisioning_api: 1.24.0
  - quota_warning: 1.24.0
  - recommendations: 7.0.0
  - related_resources: 5.0.0-dev.0
  - richdocuments: 11.1.0
  - richdocumentscode: 26.4.104
  - serverinfo: 6.0.0
  - settings: 1.17.0
  - sharebymail: 1.24.0
  - spreed: 24.0.4
  - support: 6.0.0
  - survey_client: 6.0.0-dev.0
  - suspicious_login: 12.0.0-dev.0
  - systemtags: 1.24.0
  - tables: 2.2.2
  - text: 8.0.0
  - text_templates: 1.5.0
  - theming: 2.9.0
  - twofactor_backupcodes: 1.23.0
  - twofactor_nextcloud_notification: 8.0.0
  - twofactor_totp: 16.0.0
  - updatenotification: 1.24.0
  - user_ldap: 1.25.0
  - user_status: 1.14.0
  - viewer: 7.0.0-dev.0
  - webhook_listeners: 1.6.0
  - workflow_script: 5.0.0
  - workflowengine: 2.16.0
Disabled:
  - activity: 7.0.0 (installed 7.0.0)
  - app_api: 34.0.0 (installed 32.0.0)
  - certificate24: 0.4.0 (installed 0.4.0)
  - duplicatefinder: 1.7.3 (installed 1.7.3)
  - electronicsignatures: 3.0.5 (installed 3.0.5)
  - files_archive: 1.2.8 (installed 1.2.8)
  - files_autorename: 2.4.0 (installed 2.4.0)
  - files_bpm: 1.7.1 (installed 1.7.1)
  - integration_docusign: 4.2.0 (installed 4.2.0)
  - libresign: 14.1.0 (installed 14.1.0)
  - login_notes: 1.6.1 (installed 1.6.1)
  - metadata: 0.24.0 (installed 0.23.0)
  - organization_folders: 1.1.1 (installed 1.1.1)
  - weather_status: 1.14.0 (installed 1.14.0)
  - workflow_ocr: 1.32.1 (installed 1.32.1)
  - yumisign_nextcloud: 1.31.0 (installed 1.30.3)

Nextcloud Signing status

Note: A temporary local patch was applied to MetadataRequestService.php to validate the workaround. The file was modified locally and therefore integrity check reports an expected INVALID_HASH for this file.

Nextcloud Logs

{"level":3,"app":"core","method":"PROPFIND","url":"/remote.php/dav/files/AC3C8359-809E-418F-9AD5-4DE6B4CC5531/HIDRO/03_SALAS/","message":"More than 1000 expressions in a list are not allowed on Oracle.","exception":{"Exception":"Doctrine\\DBAL\\Query\\QueryException","Message":"More than 1000 expressions in a list are not allowed on Oracle.","Trace":[{"file":"/var/www/nextcloud/lib/private/FilesMetadata/Service/MetadataRequestService.php","line":160,"function":"executeStatement","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/FilesMetadata/FilesMetadataManager.php","line":229,"function":"dropMetadataForFiles","class":"OC\\FilesMetadata\\Service\\MetadataRequestService","type":"->"}]}}


Additional information:

A local workaround was tested successfully by modifying lib/private/FilesMetadata/Service/MetadataRequestService.php.

The fix consists of:
- Processing file IDs in chunks of 1000 in getMetadataFromFileIds().
- Using the chunk variable instead of the full array in dropMetadataForFiles().

After applying the workaround and reloading PHP-FPM, the issue stopped reproducing.

Additional info

Tested workaround / patch

We applied the following local fix in our environment.

--- MetadataRequestService.php.orig
+++ MetadataRequestService.php
@@
 public function getMetadataFromFileIds(array $fileIds): array {
-    $qb = $this->dbConnection->getQueryBuilder();
-    $qb->select('file_id', 'json', 'sync_token')
-        ->from(self::TABLE_METADATA)
-        ->where($qb->expr()->in('file_id', $qb->createNamedParameter($fileIds, IQueryBuilder::PARAM_INT_ARRAY)))
-        ->runAcrossAllShards();
-
     $list = [];
-    $result = $qb->executeQuery();
-    while ($data = $result->fetch()) {
-        ...
-    }
+    $chunks = array_chunk($fileIds, 1000);
+
+    foreach ($chunks as $chunk) {
+        // Execute query using $chunk instead of all IDs
+    }

     return $list;
 }

@@
 $qb->delete(self::TABLE_METADATA)
-    ->where($qb->expr()->in('file_id', $qb->createNamedParameter($fileIds, IQueryBuilder::PARAM_INT_ARRAY)))
+    ->where($qb->expr()->in('file_id', $qb->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY)))

After applying this change and reloading PHP-FPM, the issue stopped reproducing:

Large SMB directories load normally.
No more More than 1000 expressions... errors.
No more parameter limit errors.

Start in lib/private/FilesMetadata/Service/MetadataRequestService.php, focusing on dropMetadataForFiles() and getMetadataFromFileIds(). Reproduce the issue through a large SMB directory accessed via WebDAV PROPFIND, then verify that file-ID queries are chunked and large metadata operations complete without oversized IN() clauses or QueryExceptions.