Duplicate entries in oc_filecache

ℹ️ Support
1

Nextcloud version (eg, 12.0.5): 12.0.5
Operating system and version (eg, Ubuntu 17.04):Debian 9
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4.25
PHP version (eg, 7.1): PHP 7.0.19-

I moved some directories between shared folders but rows for moved files still exist in oc_filecache.

select fileid, storage , path , path_hash parent, name size, mtime from oc_filecache where name like ‘%FritzBox7590%’ order by 1;

gives

fileid storage path parent size mtime
86267 17 __groupfolders/11/Hardware/FritzBox7590 869e8d4b31df69dfa23876c5c5ccd9c9 FritzBox7590 1515242880
84612 17 __groupfolders/7/600_Betrieb/Hardware/FritzBox7590 46690b5c5c768bc43e743e60df2079ab FritzBox7590 1515242880

The entry with fileid 84612 does not exist in the filesystem, it was moved to the path of fileid 86267 .

Both are shown on the screen in the files application

Synchronized data on a local maschine are fine.

How do I get rid of these files, I did a major reshuffle so there are a lot of them?

occ files:cleanup found nothing

occ files:scan --all did not correct the error

I am tempted to delete the rows from oc_filercache, bit I read somewhere that this is not advisable .

I read the postings here which seem to deal with similar difficulties but to no avail.

Any Ideas and help welcome ! thanks a lot

Norbert

2

I just tried occ maintenance:repair --include-expensive , but it changed nothing

3

I just deleted the wrong lines from from oc_filecache. No problems so far

4

Similar issue here, too:
Environment: Nextcloud 13.0.0, nginx, php7.0, Ubuntu 16.04

In my case, it’s happening in the group folders app. A user moves a folder from one “group folders” to another, and the resulting folder shows duplicate entries for each file.

I used the follow SQL to narrow down the problem:

SELECT DISTINCT
  parent,
  mid(path,1,length(path) - length(name)) dir
 FROM oc_filecache 
 WHERE parent IN
   (SELECT parent
    FROM oc_filecache
    GROUP BY parent
    HAVING COUNT(DISTINCT MID(path,1,LENGTH(path) - LENGTH(name))) > 1)
 ORDER BY parent;

This results in:

+--------+-----------------------------------------+
| parent | dir                                     |
+--------+-----------------------------------------+
|  25896 | __groupfolders/6/Path/to/Old/Folder/    |
|  25896 | __groupfolders/7/New/Folder/            |
|  26083 | __groupfolders/17/Path/to/Some/Folder2  |
|  26083 | __groupfolders/6/Change/to/Folder2      |
...
+--------+-----------------------------------------+

I was able to correct by removing the offending entries and rebuilding, e.g.:

CREATE TEMPORARY TABLE for_delete
SELECT DISTINCT
  parent
 FROM oc_filecache 
 WHERE parent IN
   (SELECT parent
    FROM oc_filecache
    GROUP BY parent
    HAVING COUNT(DISTINCT MID(path,1,LENGTH(path) - LENGTH(name))) > 1);

DELETE FROM oc_filecache WHERE parent IN (SELECT parent FROM for_delete);

DROP TEMPORARY TABLE for_delete;

The affected directories then appear empty. I fixed this by rebuilding:

sudo -u www-data php occ files:scan --all