TYPO3 Core

Hello Frans,

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:

DELETE sys_file
FROM sys_file
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

Stefan

For anyone coming across this years later like I did: There's a handy script on https://github.com/ElementareTeilchen/unduplicator that fixes this issue.