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).
- 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!
,fv.fileid AS version_fileid
,fv.fspath AS version_fspath
FROM (SELECT STORAGE,fileid
, TRIM(LEADING 'files/' FROM path) AS fspath
, FROM_UNIXTIME(mtime) AS filetime
WHERE path LIKE 'files/%' AND mtime=0
JOIN (SELECT fileid
, SUBSTRING_INDEX(TRIM(LEADING 'files_versions/' FROM path),'.v',1) AS fspath
,path as versionpath
,size AS versionsize
,FROM_UNIXTIME(mtime) AS change_time
,SUBSTRING_INDEX(name,'.v',-1) AS original_mtime
,FROM_UNIXTIME(SUBSTRING_INDEX(name,'.v',-1)) AS original_time
WHERE path LIKE 'files_versions/%.v%') fv
uncommend the lines
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.