Question regarding solution 1, step 6 (I’m currently planning the data dir change): basically we update the storages table by modifying the id column value. So all files in the filecache table will just continue to work.
When I moved my data directory once in the past (~ 2018), I did not do that. This is why I - now and until today - still see this in my nc_storages table:
SELECT storage, COUNT(*)
FROM nc_filecache
GROUP BY storage
ORDER BY COUNT(*) DESC;
I’m getting quite huge numbers for the old storage with id 20 (not that much as for 39 and for other storage IDs starting with home:username, but anyway a few thousands).
Thinking of handling (storing and backing up and archiving …) those - likely completely outdated und unnecessary - entries in nc_filecache already for years, wasting backup storage etc., here`s the question:
Is there a safe way to remove those entries from the database?
Is that maybe what occ files:cleanup is intended for?
The local:: file storage is actually used only for the appdata_<instanceid>, which contains mostly cache stuff. The by far biggest part is the file previews from the preview directory.
I guess, if you let Nextcloud just add a new local:: storage by skipping step 6, it will attempt to re-create and/or find the same files and add them as new filecache table entires. It would hence create duplicates.
A quick check for the appdata dir would be this:
select * from nc_filecache where storage=20 limit 2;
select * from nc_filecache where storage=39 limit 2;
The first entry is the base dir, i.e. empty path, the 2nd should be the appdata_<instanceid> dir. If this is the same in both cases, then most likely, all cache entries from storage 20 are obsolete duplicates. You could then delete the storage entry, and run occ files:cleanup to cleanup the orphaned filecache entries. occ files:scan-app-data can be used to rescan the appdata_<instanceid> dir, just in case not all old entries were duplicates, and were magically still effectively used.
A good reason to update the local:: storage entry, since especially the previews can be a huge number of entries.
Indeed that’s what likely happened for me back in 2018 when I definitely did not change anything in the database, only moved the data and edited config.php.
Quick check: The results differ a bit:
select * from nc_filecache where storage=20 limit 2;
Definitely the .../appdata_xxx/preview folder is the largest, likely matching with many of the nc_filecache entries: 14.0 GiB and more than 338.000 files.
I will need to check the remaining posts in this thread to see how to handle the previews after the data directory migration. Likely a good chance to purge them and let them fully recreate again (did that 2 years ago for the last time after making changes to preview settings). That time I used
Since it was not the 2nd entry in your case, maybe check for where path='appdata_xxx' entries again, to see whether there is the expected duplicate. Or check for storage=20 entries where mtime or storage_mtime is above 1546322400 (UNIX time for ~2019), as another indicator that those entries seem to be orphaned.
There is nothing to do about it. Nextcloud continues to use them when you update the local:: entry.
IIRC, previews were moved from another directory to appdata_xxx/preview a while ago, but after 2019. So there are likely no filecache entries for those with storage=20. But probably there is another long untouched dir next to appdata_xxx (in the root of the Nextcloud data), with actual obsolete preview files, and respective filecache entries. Not sure whether those have been cleaned or migrated automatically at some point.
And occ files:cleanup should remove all storage=20 entries, after you removed that one from the storages table. AFAIK this is what it means with “orphaned”, not whether the file exist on disk, as the command runs pretty fast without triggering much disk activity in my case.
So for both (20 = old until 2018, 39 = “new”) there’s an entry with the appdata_ocXXX.
With select * from nc_filecache where storage=20 ORDER BY storage_mtime DESC LIMIT 10; I checked for the storage_mtime of the youngest entry, which is 1520177080 equaling the day of the data dir migration in 2018.
Doing some individual checks for files in storage 20 usually returns two entries: one for the same file in storage 20, one e. g. in storage 15 which equals home:username.
I tend to day those are enough indicators that the old storage is not needed anymore. Not sure how to ultimately sort this out. Maybe by checking with a rather complex JOIN party if every entry (file) with storage=20 also exists a 2nd time in any other storage.
Okay so I would completely delete the line with numeric_id = 20 in nc_storages before running that command which likely kicks all entrys with storage=20 from nc_filecache table afterwards? Not sure about the integrity of the primary key (numeric_id) which is increasing - a missing number (20) right inbetween is no issue?
Pardon me for getting a bit off-topic. If a moderator wants, feel happy to split the last few posts, always slightly related to the data dir migration but also fine as a separate topic (“database: filecache table cleanup after data migration”).
That is interesting, so it seems all (local) files were stored in the local:: storage earlier, while per-user files are stored in the respective home:: storage nowadays.
and verifying that left entries are related to files which do not exist anymore. For me, as I would need to look up the SQL queries to do that, it wouldn’t be worth it. occ files:scan and files:scan-app-data can be used to regenerate missing cache entries, in the unlikely case we draw false conclusions.
Right, no big risk implied. The primary key gap is no issue, I guess there are already gaps in your case. My oc_storages table starts at 3.
It seems like instantly after editing config.php and providing the new path, it was automatically added as new storage with the highest (latest) numeric_id (42) in nc_storages.
So now what:
Option A: Keep it this way?
Option B: Remove the storage 42 with the new data dir path (delete line) and edit the line 39 with the previous data dir path so it reflects the new path?
I tend to say option B is what we want, only this way the references in nc_filecache match the nc_storages table and everything should continue to work. Please confirm @MichaIng .
Also wondering why this happens. And yes, I triple-checked maintenance mode is enabled.
Update regarding option B: nc_filecache already contains few entries referencing the new storage id 42 ! What the…?!?
Hmm, maybe maintenance mode is not enough (anymore) to prevent Nextcloud from touching the database, or OPcache invalidation happened a request too late. Maybe better to stop the webserver and/or PHP-FPM in that case, to assure nothing triggers any Nextcloud script.
Anyway, I agree in that case option B deleting the new storage entry should be fine to keep things clean and avoid the invalidation of the whole old storage.
Indeed, stopping webserver and PHP-FPM should make the whole process much more bulletproof.
That’s what I did meanwhile (executing option B). Deleting new 42, modifying previous 39 to reflect the new path. Also running sudo -u www-data php /var/www/nextcloud/occ files:cleanup directly after disabling maintenance mode removed those 26 nc_filecache entries from storage 42: 26 orphaned file cache entries deleted.
→ “original storage” is unknown
→ “deleted by” is unknown
→ “deleted at” is unknown
Also the “changed” column is completely mixed or “strangely sorted”:
Not sure if all of this is due to the data directory migration (did not check the trashbin before the migration) or if it’s due to a current bug in NC (running v33.0.3 currently).
Indeed, there were gaps. Enabled maintenance mode, cleaned the old id=20 storage entry with id=local::/var/www/owncloud/data/ (deleted from nc_storages), disabled maintenance mode, ran sudo -u www-data php /var/www/nextcloud/occ files:scan --all (no changes) and sudo -u www-data php /var/www/nextcloud/occ files:cleanup which deleted several thousands orphaned file cache entries.
I see the same two symptoms in my trashbin: unknown last 3 columns, false modified sorting. with some entries modified X days/weeks ago further down than expected. I guess something messed up with the two ways of how the modification time is shown, as absolute date or as diff from today (for everything within 1 month, it seems). The way it is displayed of course must not have an effect on how it is sorted, but obviously it has.
However, unrelated to the data migration.
I am glad the cleanup worked as expected, and confirmed all assumptions. I’ll do some tests tomorrow, and update the guide to recommend stopping PHP-FPM or the webserver.
I hope cron does not cause a storage entry. In your case, with the E2EE keys, it looks like a Nextcloud client connection triggered it. And OPcache cannot be the issue: if an outdated config.php entry did not contain the enabled maintenance mode, then the data dir change would not be present either. The maintenance mode does prevent any actual (user) file changes and syncs, but obviously some meta data is still checked and in case created. Not ideal, I’d prefer a database lock, as on e.g. shared hostings, admins have no control on the webserver/PHP, and any unexpected database change during diagnosis/debug/cleanup/rescue steps can potentially cause harm or at least mess up things. Worth to have a look into where these theming and E2EE entries are created, and why maintenance mode is not preventing it.
