possible solution to recover mtime

[isdnfan]

starting from here: https://help.nextcloud.com/t/desktop-client-3-4-0-destroys-local-time-stamp-and-keeps-uploading-data-to-server/128512/54?u=wwe

I didn’t find time to build reliable recovery procedure from this information as I’m not an expert in mysql but it should work as following:

• find files with invalid mtime
• find the most recent version (if many) of the file which has valid mtime
• perform one of the possible recovery options:
  1. safe: touch the file with valid mtime (and remove version in 2nd step)
  2. storage efficient: move version file to the original location and overwrite bad file
• perform occ files:scan all

additional sugar:

• compare the version file you use for recovery with the file in the system (diff/checksum) - AFAIK the contents are unchanged, so file contents and versions should be same
• remove newer versions having invalid mtime (there are github reports when upload with invalid mtime happened multiple times)

I didn’t investigate further - NC devs might know from the top of the head - i think oc_filecache table is not separated by user so if collect the information from DB you don’t need to iterate over each user to identify and recover the files, it should work in one go…

[isdnfan]

from https://help.nextcloud.com/t/desktop-client-3-4-0-destroys-local-time-stamp-and-keeps-uploading-data-to-server/128512/93?u=wwe

this query collects damaged files from DB and JOIN them with their file_versions

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

un/comment the lines

#AND fileid=308330
#LIMIT 10

for less results