I spend more time on this and created a SQL query collecting all files with invalid (1970-01-01) file mtime from the DB and checking for files_versions of this files in the DB with their change time and original mtime.
one can use the output to correct the mtime easily (if your DB still has file versions entries).
Warning:
- At the moment I don’t know if it work when multiple versions of the damaged file exist (don’t have in my playground), simple sort/limit should choose good version but need verification!
SELECT f.fileid
,fv.fileid AS version_fileid
,f.fspath
,fv.fspath AS version_fspath
,versionpath
,f.size
,versionsize
,f.mtime
,f.filetime
,fv.change_time
,fv.original_mtime
FROM (SELECT STORAGE,fileid
,path
, TRIM(LEADING 'files/' FROM path) AS fspath
,size
,mtime
, FROM_UNIXTIME(mtime) AS filetime
FROM oc_filecache
WHERE path LIKE 'files/%' AND mtime=0
#AND fileid=308330
#LIMIT 10
) f
JOIN (SELECT fileid
, SUBSTRING_INDEX(TRIM(LEADING 'files_versions/' FROM path),'.v',1) AS fspath
,path as versionpath
,size AS versionsize
,mtime
,FROM_UNIXTIME(mtime) AS change_time
,SUBSTRING_INDEX(name,'.v',-1) AS original_mtime
,FROM_UNIXTIME(SUBSTRING_INDEX(name,'.v',-1)) AS original_time
FROM oc_filecache
WHERE path LIKE 'files_versions/%.v%') fv
ON f.fspath=fv.fspath
uncommend the lines
#AND fileid=308330
#LIMIT 10
to test the query on your known broken files and limit to few results only as long you are searching for solution.
P.S. don’t forget to perform occ files scan:all if you only touch the file on the disk.