[Bug]: files:scan creates oversized files_metadata DELETE query and causes severe resource usage

[hunterrand]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

Running occ files:scan --all after moving a large number of folders within an admin-configured local external storage causes repeated QueryException errors while Nextcloud attempts to delete stale entries from files_metadata.

The scan remains running, consumes substantial CPU, makes the Nextcloud web interface inaccessible, and does not terminate cleanly. The Docker container continues to report as running.

The instance uses MariaDB, but Nextcloud reports both of the following database-independent query-limit errors:

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

The failing query is:

DELETE FROM `*PREFIX*files_metadata` WHERE `file_id` IN (:dcValue1)"

The exception originates from:

OC\FilesMetadata\Service\MetadataRequestService->dropMetadataForFiles()
OC\FilesMetadata\FilesMetadataManager->deleteMetadataForFiles()
OC\Files\Cache\Cache->removeChildren()
OC\Files\Cache\Scanner->removeFromCache()

This appears to occur because a large number of file IDs are passed to one metadata-deletion query instead of being processed in smaller batches.

Steps to reproduce

1. Configure a local external storage mount assigned to a user.
2. Populate the mount with a large directory tree containing many files and folders.
3. Move a large number of top-level directories outside Nextcloud, changing their paths within the mounted storage.
4. Run: occ files:scan --all
5. Observe repeated QueryException entries in nextcloud.log.
6. Observe high CPU usage and loss of access to the Nextcloud web interface. (100% all cores)
7. Attempt to terminate the scan process.
8. Observe that the PHP/occ process does not terminate cleanly and that stopping the entire Nextcloud container is required. With no intervention entire server locks and crashes while CPU continues to heat up with CPU fan at 100%.

Expected behavior

I expected it to scan the directories an update them as it has done in the past. Previously it took ~10 hours to scan all of the directories on an older system (FX-8320, 12GB RAM).

files:scan should reconcile moved or missing files without constructing a query that exceeds supported parameter or expression limits.

Large groups of file IDs should be divided into bounded batches before deleting records from files_metadata.

The web interface should remain responsive, and the scan process should terminate normally when interrupted.

Nextcloud Server version

34

Operating system

Other

PHP engine version

PHP 8.4

Web server

Nginx

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": {
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "192.168.1.114:444",
            "cloud.yorkhq.com"
        ],
        "dbtype": "mysql",
        "version": "34.0.1.2",
        "overwrite.cli.url": "https:\/\/192.168.1.114:444",
        "overwriteprotocol": "https",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "memcache.local": "\\OC\\Memcache\\APCu",
        "filelocking.enabled": true,
        "memcache.locking": "\\OC\\Memcache\\APCu",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": "6379"
        },
        "upgrade.disable-web": true,
        "maintenance_window_start": 10,
        "maintenance": false,
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "ssl",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauth": true,
        "mail_smtpport": "465",
        "mail_sendmailmode": "smtp",
        "mail_smtpstreamoptions": {
            "ssl": {
                "allow_self_signed": false,
                "verify_peer": true,
                "verify_peer_name": true
            }
        },
        "app_install_overwrite": [],
        "defaultapp": "dashboard,files,office",
        "loglevel": 2
    }
}

List of activated Apps

Enabled:
  - activity: 7.0.0
  - app_api: 34.0.0
  - appstore: 1.0.0
  - bruteforcesettings: 7.0.0
  - circles: 34.0.0
  - cloud_federation_api: 1.18.0
  - comments: 1.24.0
  - contactsinteraction: 1.15.0
  - dav: 1.39.0
  - federatedfilesharing: 1.24.0
  - federation: 1.24.0
  - files: 2.6.0
  - files_downloadlimit: 5.2.0-dev.0
  - files_external: 1.26.0
  - files_lock: 34.0.0
  - files_pdfviewer: 7.0.0-dev.0
  - files_reminders: 1.7.0
  - files_sharing: 1.26.0
  - files_trashbin: 1.24.0
  - files_versions: 1.27.0
  - firstrunwizard: 7.0.0-dev.0
  - logreader: 7.0.0
  - lookup_server_connector: 1.22.0
  - nextcloud_announcements: 6.0.0
  - notifications: 7.0.0-dev.1
  - oauth2: 1.22.0
  - office: 1.0.0
  - password_policy: 6.0.0-dev.0
  - photos: 7.0.0
  - privacy: 6.0.0-dev.1
  - profile: 1.3.0
  - provisioning_api: 1.24.0
  - recommendations: 7.0.0
  - related_resources: 5.0.0-dev.0
  - richdocuments: 11.0.1
  - serverinfo: 6.0.0
  - settings: 1.17.0
  - sharebymail: 1.24.0
  - support: 6.0.0
  - survey_client: 6.0.0-dev.0
  - systemtags: 1.24.0
  - text: 8.0.0
  - theming: 2.9.0
  - twofactor_backupcodes: 1.23.0
  - twofactor_totp: 16.0.0
  - updatenotification: 1.24.0
  - user_status: 1.14.0
  - viewer: 7.0.0-dev.0
  - weather_status: 1.14.0
  - webhook_listeners: 1.6.0
  - workflowengine: 2.16.0
Disabled:
  - admin_audit: 1.24.0
  - dashboard: 7.14.0 (installed 7.14.0)
  - encryption: 2.22.0
  - suspicious_login: 12.0.0-dev.0
  - twofactor_nextcloud_notification: 8.0.0
  - user_ldap: 1.25.0

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

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

Query:
DELETE FROM `*PREFIX*files_metadata`
WHERE `file_id` IN (:dcValue1)



Doctrine\DBAL\Query\QueryException:
The number of parameters must not exceed 65535.
Restriction by PostgreSQL.

Query:
DELETE FROM `*PREFIX*files_metadata`
WHERE `file_id` IN (:dcValue1)

Additional info

I use Unraid, with Nextcloud in a Docker container. I have two shares mounted as external storage for Nextcloud. I moved 9TB of data up a level, so out of a folder and up into the next higher level folder - this was done on July 16th on an older system - FX 8320 and 12GB of RAM.

July 18th I upgraded to a new system - Intel 285K with 128GB of RAM. Notably APPDATA was stored on a single cache drive, and on the new system I added a second cache drive to mirror - in the process I accidently wiped out the APPDATA share. I then recovered APPDATA from a backup I made on July 12th, so Nextcloud was restored except the scan I had done to reflect the changes - moving folder up a level.

I started the scan again with the same command as before and it worked for about 20 minutes. I then lost my display and the fans kicked up to 100% - and the whole system locked up. Even holding the power button didn't do anything - system was shutoff at the PSU.

I then ran a memory test and the system failed - I turned off XMP and the system passed - so I thought it was a memory issue. I went back in and started the scan again and the thermal runaway started, but I was able to stop the Docker container before the system locked up. Terminal commands would not stop or kills any processes - what did work was going to the Docker tab on unraid and stopping the container there.

I then scanned each folder separately and the system was able to handle that - so that's when I went to the logs and found the issue.