Oc_filecache full of orphaned files -- how to remove fast and efficiently?

ℹ️ Support
1

Nextcloud version (eg, 20.0.5): 28.0.2
Operating system and version (eg, Ubuntu 20.04): AlmaLinux 8.9
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4.37
PHP version (eg, 7.4): 8.0.30

The issue you are facing:
I am seeing a constant spew of

{"reqId":"EKkY7U5bTUvDriPxJba7","level":3,"time":"2024-02-25T08:36:13+00:00","remoteAddr":"","user":"--","app":"PHP","method":"","url":"--","message":"file_get_contents(/var/lib/nextcloud/data/brian/files/DesktopHome/.wine.old/fake_windows/users/brian/My Documents/.wine.old/fake_windows/users/brian/My Documents/.wine.old/fake_windows/users/brian/My Documents/.wine.old/fake_windows/users/brian/My Documents/.wine.old/fake_windows/users/brian/My Documents/.wine.old/fake_windows/users/brian/My Documents/.wine.old/fake_windows/users/brian/My Documents/.wine.old/fake_windows/users/brian/My Documents/.wine.old/fake_windows/users/brian/My Documents/.wine.old/dosdevices/e:/.wine.old/dosdevices/z:/net/pc/home/brian/.shotwell/thumbs/thumbs128/thumb0000000000001878.jpg): Failed to open stream: No such file or directory at /usr/share/nextcloud/lib/private/Files/Storage/Local.php#327","userAgent":"--","version":"28.0.1.1","data":{"app":"PHP"}}

in my logs. When I query the oc_filecache table I can indeed see 1.4 million:

# echo 'select count(*) from oc_filecache where path like "%/DesktopHome/%";' | mysql -t nextclouddb
+----------+
| count(*) |
+----------+
|  1453080 |
+----------+

such entries where there is no actual file on the filesystem. These are orphaned entries in the database.

Is this the first time you’ve seen this error? (Y/N): Y

Steps to replicate it:
Unknown

The output of your Nextcloud log in Admin > Logging:
It’s empty. Or maybe just taking a really long time to render due to the load the constant spew of the above is having on the database.

I have 1.4M records in the oc_filecache table. I am sure the vast majority of them are indeed orphaned files all below the /var/lib/nextcloud/data/brian/files/DesktopHome/ path. I’d like to just remove anything from oc_filecache that is in that path but I am not sure that just issuing a MySQL command to do that won’t break the database consistency in some way. I.e. if those records are referenced somewhere else that will be orphaned in the DB.

I did try to run:

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

but as you can see it has had no effect.

How can I efficiently just remove these orphaned oc_filecache entries?

2

You can use a similar sql command to delete all entries from oc_filecache and then run a manual file scan on just that folder:
https://docs.nextcloud.com/server/stable/admin_manual/configuration_server/occ_command.html#scan

But this will resync all the files with the client!

If you run just the filescan command, normally it updates files that are on the filesystem but not in the filecache-table, perhaps it removes entries without files. You can also try the repair-tree command:
https://docs.nextcloud.com/server/stable/admin_manual/configuration_server/occ_command.html#repair-tree

Sounds like a different issue, but in case it helps it is perhaps faster.

If you do such major changes and especially for manual changes of the DB, do a full backup of your Nextcloud before, so you can revert to the current state.

3

Lots more activity in the ticket I opened for the broken occ files:cleanup.

TL;DR: There does not seem to be any functioning way to clean this mess up without being a NextCloud DB expert and understanding what relationships might exist to/from entries in the oc_filecache table.

4

In such cases, it is great to link bug reports, so we don’t start trying to help on things that don’t require help from our side (or we can take into account the stuff happening at github…)