Bug #103707
openDuplicate entries in sys_file table
0%
Description
We have encountered an issue where there are duplicate entries in the sys_file
table pointing to the same physical file. This results in the Filelist module not displaying references to those files correctly. Consequently, these files can be deleted even though there are references pointing to another sys_file
entry that shares the same file in the filesystem.
To check for duplicates, you can run:
SELECT COUNT(*), `identifier` FROM `sys_file` GROUP BY `identifier` HAVING COUNT(*) > 1;
Here is an example of a duplicate entry:
SELECT * FROM `sys_file` where identifier like "/user_upload/Bilder/Wirtschaftspruefer/Veranstaltung_2024/mbs_04.jpg"; +------+-----+------------+--------------+---------+---------+------+----------+----------------------------------------------------------------------+------------------------------------------+------------------------------------------+-----------+------------+------------+------------------------------------------+--------+---------------+-------------------+ | uid | pid | tstamp | last_indexed | missing | storage | type | metadata | identifier | identifier_hash | folder_hash | extension | mime_type | name | sha1 | size | creation_date | modification_date | +------+-----+------------+--------------+---------+---------+------+----------+----------------------------------------------------------------------+------------------------------------------+------------------------------------------+-----------+------------+------------+------------------------------------------+--------+---------------+-------------------+ | 2792 | 0 | 1711288107 | 1711288107 | 0 | 1 | 2 | 0 | /user_upload/Bilder/Wirtschaftspruefer/Veranstaltung_2024/mbs_04.jpg | ed3516d8ff67f42ae9a0b4ca9443989421b21324 | 928bfe2c54bf0575ae5ad65b55cb3deee6aaa658 | jpg | image/jpeg | mbs_04.jpg | 61782595dddae96224e259cf7170dd646fb4e3d1 | 562574 | 1711288107 | 1711288107 | | 2794 | 0 | 1712041049 | 1712041049 | 0 | 1 | 2 | 0 | /user_upload/Bilder/Wirtschaftspruefer/Veranstaltung_2024/mbs_04.jpg | ed3516d8ff67f42ae9a0b4ca9443989421b21324 | 928bfe2c54bf0575ae5ad65b55cb3deee6aaa658 | jpg | image/jpeg | mbs_04.jpg | 61782595dddae96224e259cf7170dd646fb4e3d1 | 562574 | 1712041049 | 1711288107 |
In another instance, there are multiple sys_file
records pointing to files outside the fileadmin
directory. This might indicate that the problem is not related to the Filelist module and user uploads, but could also be rooted in page rendering.
| uid | pid | tstamp | last_indexed | missing | storage | type | metadata | identifier | identifier_hash | folder_hash | extension | mime_type | name | sha1 | size | creation_date | modification_date | +------+-----+------------+--------------+---------+---------+------+----------+---------------------------------------------------------------------+------------------------------------------+------------------------------------------+-----------+---------------+-------------+------------------------------------------+------+---------------+-------------------+ | 2754 | 0 | 1680786128 | 0 | 0 | 0 | 2 | 0 | /typo3conf/ext/sitepackage/Resources/Public/Icons/Flags/flag_at.svg | aa8d82349adeaa66fa834c0bab77a507d74bbd3a | 15b08915cf0ec6b4ffef65022ba6219d4111ee18 | svg | image/svg+xml | flag_at.svg | 87fcd7ee2c2fe0e53935bd952efdb97dde1b3c66 | 226 | 1680786117 | 1680786007 | | 2755 | 0 | 1680786128 | 0 | 0 | 0 | 2 | 0 | /typo3conf/ext/sitepackage/Resources/Public/Icons/Flags/flag_at.svg | aa8d82349adeaa66fa834c0bab77a507d74bbd3a | 15b08915cf0ec6b4ffef65022ba6219d4111ee18 | svg | image/svg+xml | flag_at.svg | 87fcd7ee2c2fe0e53935bd952efdb97dde1b3c66 | 226 | 1680786117 | 1680786007 |
This issue appears to be at least four years old, as i found the extension https://extensions.typo3.org/package/elementareteilchen/unduplicator, which was created to find and fix such duplicates. The extension dates back to 2020.
We found these problems in many other projects. However, we do not have a TYPO3 v12 or v13 instance that is not an upgrade from TYPO3 v11, so we are currently unsure if this issue affects TYPO3 v12 or v13 as well.