Duplicate files results in different Metadata
we have duplicate entries in sys_file. I know, it is hard to remove them, but as long as there are duplicate entries in sys_file we should try to return always the same file.
If you have duplicate entries in sys_file goto filelist into a folder with images. In my example I see 10 images, but there are 20 records in database. As you can see, the duplicate entries were merged. This is because you're using exec_SELECTgetRows with $uidIndexField="identifier". So only the LAST found record was returned!
Please enable "extended view" and click on yellow pen to edit metadata. Add some subheader or what else and save.
Now click on the little icon in front of the image, open clickmenu, and choose edit. Now you have opened another metadata record! But why? This is because you call findOneByStorageUidAndIdentifierHash() instead and this method returns the FIRST found record.
In my opinion BOTH methods should ALWAYS return the SAME record.
Updated by Stefan Froemken about 7 years ago
you can close this ticket now.
For all others who has similar problems I have created a SQL-Query which may help you. Before executing please backup you Database!
Following Query removes all duplicated entries in sys_file. Sure, it is fast (3 seconds for 60.000 entries), but it does NOT check, if one of the duplicate entries has already a relation to sys_file_reference:
DELETE FROM sys_file
USING sys_file, sys_file as tmp_table
WHERE (sys_file.uid > tmp_table.uid)
AND (sys_file.storage = tmp_table.storage AND sys_file.identifier_hash = tmp_table.identifier_hash);
The other Query contains the missing feature from Query above. It removes all duplicated entries from sys_file, as long as the entries does not have a relation to sys_file_reference. Before executing the Query it would be good to create an index on col: sys_file_reference.uid_local so that your Query was executed within 50 minutes (60.000 entries) Else it costs you 6 hours ans more:
LEFT JOIN sys_file_reference
ON sys_file.uid = sys_file_reference.uid_local, sys_file as tmp_table
WHERE sys_file.uid > tmp_table.uid
AND sys_file.storage = tmp_table.storage
AND sys_file.identifier_hash = tmp_table.identifier_hash
AND sys_file_reference.uid IS NULL