I tryed hard to find a universal solution to link the file damaged by the sync with valid versions and allow subsequent recovery… at the moment my sql skills only allow this summary of intermediate steps which hopefully helps others. for some really strange reason there is no clear relation (in terms of unique ID) between the files and their versions… both are listed in the oc_filecache table… the only difference is
-
real files have prefix
files/ - while versions have
files_versions/prefix and.v<unixepoch>suffix in the path column
so in my eyes the only way to detect a file and it’s version belong together is to strip files/ prefix and search for records with prefix files_versions/ and same value at the end (and strip the suffix after .v).
here I explain the procedure with one file I found with the search term @mgallien provided:
# relevant data from oc_filecache for files with invalid mtime
select storage,fileid,trim(LEADING 'files/' FROM path) as path,size,mtime,from_unixtime(mtime) as mtime from oc_filecache WHERE path like 'files/%' and mtime=0 and fileid=308330 \G;
this query shows one file with the id 308330 - skip the and fileid.. to see all files with invalid mtime and skip \G to see the results as table… some hints:
- trim(LEADING ‘files/’ FROM path)
shows the path of the original file without files/ prefix - from_unixtime(mtime) as mtime
converts unix epoch to human readable time - path like ‘files/%’ and mtime=0
lists regular files with invalid change time - and fileid=308330
limits results to one specific file - \G
makes results appear as list rather than table
# file version for specific file path collectected from above query
select storage,fileid,SUBSTRING_INDEX(trim(LEADING 'files_versions/' FROM path),'.v',1) as original_path,path,size,mtime as change_mtime,from_unixtime(mtime) as change_time,from_unixtime(SUBSTRING_INDEX(name,'.v',-1)) as original_mtime FROM oc_filecache WHERE path like CONCAT('files_versions/','Documents/PowerShell/PowerShell_Advanced_Kurs_2019/...eRequired/--Switch.txt','.v%') \G;
- SUBSTRING_INDEX(trim(LEADING ‘files_versions/’ FROM path),’.v’,1) as original_path
shows the raw value of path (exactly the same as path in the above query - from_unixtime(SUBSTRING_INDEX(name,’.v’,-1)) as original_mtime
the suffix of thepathvalue shows when this file version was created (mtime of the original file) - WHERE path like CONCAT(‘files_versions/’,‘Documents/PowerShell/PowerShell_Advanced_Kurs_2019/…eRequired/–Switch.txt’,’.v%’)
filters the table for filename from the above query but prepernds thefiles_versions/prefix and.v...suffix - in my case there is only one version but depending on how long desktop client 3.4.0 was syncing you might have multiple. I used CONCAT so you can directly feed path from the above query as middle parameter
At the moment my SQL skills are not enough to construct recovery action from this findings which is valid for different architectures but with this starting point it’s not hard script something using your preferred scripting/programming tool something which
- builds the list of affected files using the first query
- repeats the second query using the file paths from the first result as input and
– collects a list of existing file versions - depending on your approach and skills
– either extract correct modification dates from the versions and change the creation/modification date on the files
– or move the version you like to the original location and recover by rinningocc files:scan