[Bug]: occ files:cleanup is not working -- doesn't remove any of the million+ stale entries

⚠️ 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

I have over 1.4M stale entries in my oc_filecache table under just a single path files/DesktopHome:

# echo 'select count(*) from oc_filecache where storage = 1 and path like "files/DesktopHome/%";' | mysql -t nextclouddb
+----------+
| count(*) |
+----------+
|  1450799 |
+----------+     

Here is the oc_storages table for reference:

+---------------------------------+------------+-----------+--------------+
| id                              | numeric_id | available | last_checked |
+---------------------------------+------------+-----------+--------------+
| home::brian                     |          1 |         1 |         NULL |
| local::/usr/src/owncloud/data/  |          2 |         1 |         NULL |
| home::jennifer                  |          3 |         1 |         NULL |
| home::brianm                    |          4 |         1 |         NULL |
| local::/var/lib/owncloud/data/  |          5 |         1 |         NULL |
| local::/var/lib/nextcloud/data/ |          6 |         1 |         NULL |
+---------------------------------+------------+-----------+--------------+

But under that path on the actual filesystem there is nothing:

[root@server ~]# ls -l /var/lib/nextcloud/data/brian/files/DesktopHome/
total 0

And yet occ files:cleanup does nothing to clean that mess up:

# sudo -u apache php /usr/share/nextcloud/occ files:cleanup
0 orphaned file cache entries deleted
0 orphaned mount entries deleted           

Additionally such file appear in the NC files app but when you try to download or get details on them you get errors about them being non-available. The server also logs:

Mar  3 11:14:41 server Nextcloud[318606]: {"reqId":"ZeSh8KYbHCAExqsm5DVOlQAAABg","level":3,"time":"2024-03-03T16:14:41+00:00","remoteAddr":"10.75.22.192","user":"brian","app":"webdav","method":"GET","url":"/remote.php/dav/files/brian/DesktopHome/3","message":"{\"Exception\":\"Sabre\\\\DAV\\\\Exception\\\\ServiceUnavailable\",\"Message\":\"Could not open file\",\"Code\":0,\"Trace\":[{\"file\":\"/usr/share/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php\",\"line\":85,\"function\":\"get\",\"class\":\"OCA\\\\DAV\\\\Connector\\\\Sabre\\\\File\",\"type\":\"->\",\"args\":[]},{\"file\":\"/usr/share/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"httpGet\",\"class\":\"Sabre\\\\DAV\\\\CorePlugin\",\"type\":\"->\",\"args\":[[\"Sabre\\\\HTTP\\\\Request\"],[\"Sabre\\\\HTTP\\\\Response\"]]},{\"file\":\"/usr/share/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":472,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[\"method:GET\",[[\"Sabre\\\\HTTP\\\\Request\"],[\"Sabre\\\\HTTP\\\\Response\"]]]},{\"file\":\"/usr/share/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":253,\"function\":\"invokeMethod\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[[\"Sabre\\\\HTTP\\\\Request\"],[\"Sabre\\\\HTTP\\\\Response\"]]},{\"file\":\"/usr/share/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":321,\"function\":\"start\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[]},{\"file\":\"/usr/share/nextcloud/apps/dav/lib/Server.php\",\"line\":370,\"function\":\"exec\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[]},{\"file\":\"/usr/share/nextcloud/apps/dav/appinfo/v2/remote.php\",\"line\":35,\"function\":\"exec\",\"class\":\"OCA\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[]},{\"file\":\"/usr/share/nextcloud/remote.php\",\"line\":172,\"args\":[\"/usr/share/nextcloud/apps/dav/appinfo/v2/remote.php\"],\"function\":\"require_once\"}],\"File\":\"/usr/share/nextcloud/apps/dav/lib/Connector/Sabre/File.php\",\"Line\":505,\"message\":\"Could not open file\",\"exception\":{},\"CustomMessage\":\"Could not open file\"}","userAgent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36","version":"28.0.2.5"}

Steps to reproduce

Probably pretty obvious give the description above.

Expected behavior

occ files:cleanup should remove those 1.4M or so stale oc_filecache entries.

Installation method

Other Community project

Nextcloud Server version

29

Operating system

RHEL/CentOS

PHP engine version

PHP 8.0

Web server

Apache (supported)

Database engine version

MariaDB

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

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

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

Configuration report

{
    "system": {
        "log_type": "syslog",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "updatechecker": false,
        "check_for_working_htaccess": false,
        "asset-pipeline.enabled": false,
        "assetdirectory": "\/var\/lib\/nextcloud",
        "preview_libreoffice_path": "\/usr\/bin\/libreoffice",
        "apps_paths": [
            {
                "path": "\/usr\/share\/nextcloud\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/lib\/nextcloud\/apps",
                "url": "\/apps-appstore",
                "writable": true
            }
        ],
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "owncloud.example.com",
            "www.example.com"
        ],
        "overwrite.cli.url": "http:\/\/owncloud.example.com\/",
        "dbtype": "mysql",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "version": "29.0.0.19",
        "installed": true,
        "theme": "",
        "maintenance": false,
        "forcessl": false,
        "loglevel": 1,
        "log_authfailip": true,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "trashbin_retention_obligation": "auto",
        "htaccess.RewriteBase": "\/",
        "appstore.experimental.enabled": true,
        "mysql.utf8mb4": true
    }
}

List of activated Apps

Enabled:
  - activity: 2.21.1
  - calendar: 4.7.3
  - circles: 29.0.0-dev
  - cloud_federation_api: 1.12.0
  - comments: 1.19.0
  - contacts: 6.0.0
  - contactsinteraction: 1.10.0
  - dashboard: 7.9.0
  - dav: 1.30.1
  - federatedfilesharing: 1.19.0
  - federation: 1.19.0
  - files: 2.1.0
  - files_downloadlimit: 2.0.0
  - files_pdfviewer: 2.10.0
  - files_reminders: 1.2.0
  - files_sharing: 1.21.0
  - files_trashbin: 1.19.0
  - files_versions: 1.22.0
  - firstrunwizard: 2.18.0
  - logreader: 2.14.0
  - lookup_server_connector: 1.17.0
  - nextcloud_announcements: 1.18.0
  - notes: 4.10.0
  - notifications: 2.17.0
  - oauth2: 1.17.0
  - password_policy: 1.19.0
  - photos: 2.5.0
  - privacy: 1.13.0
  - provisioning_api: 1.19.0
  - recommendations: 2.1.0
  - related_resources: 1.4.0
  - serverinfo: 1.19.0
  - settings: 1.12.0
  - sharebymail: 1.19.0
  - support: 1.12.0
  - survey_client: 1.17.0
  - systemtags: 1.19.0
  - text: 3.10.0
  - theming: 2.4.0
  - twofactor_backupcodes: 1.18.0
  - updatenotification: 1.19.1
  - user_status: 1.9.0
  - viewer: 2.3.0
  - weather_status: 1.9.0
  - workflowengine: 2.11.0
Disabled:
  - admin_audit: 1.19.0
  - bruteforcesettings: 2.9.0
  - encryption: 2.17.0
  - files_external: 1.21.0
  - files_rightclick: 0.15.1 (installed 1.6.0)
  - files_texteditor: 2.15.1 (installed 2.15.1)
  - suspicious_login: 7.0.0
  - twofactor_totp: 11.0.0-dev
  - user_ldap: 1.20.0 (installed 1.14.1)

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

Start by tracing the occ files:cleanup command and how it evaluates entries in the oc_filecache table, using the reported files/DesktopHome case as the reproduction. Verify why entries for missing filesystem files are not classified as orphaned, then add coverage for the stale-entry scenario and confirm the command removes those entries without affecting valid cache records.