Detect duplicated references in the database (Related to the bug 66920)

We have found recently a small but very annoying bug (https://forge.typo3.org/issues/66920), which results some inconsistent records in the database. As in the bug report described, it can happen wit any instance without any extension, and also inside any extension like powermail.

The bug was fixed in a record time – thanks again for the core developers: Andreas Fernandez, Nicole Cordes and Mathias Schreiber – but the solution won’t fix any already inconsistent records in the database, it prevents only the error to occur in the future.

What is this bug? (It is still present in the version 6.2.12  and below)

„We have found a bug with one of our small product extension. The original problem was, that some user could not copy a product, because it showed an error, but after the error message and an unsuccessful copy, the IRRE records are doubled in the original record.

We made some experiment, and it seems to be a core bug.

How to reproduce it:
1) Create a simply Image CE with one image.
2) Go to the List Modul and copy this content element
3) Select the root page
4) Try to insert it

After clicking on the insert, TYPO3 should show an error:

„1: Attempt to insert record on page ‚[root-level]‘ (0) where this table, tt_content, is not allowed“.

So far so good

5) Open the original Image CE and you have 2 reference instead of 1. And the second has pid = 0 in DB.

It seems, if an error occurs, the child records are not removed from the db, but somehow referenced to the original record, which you copied. This probably happens also by different errors, which blocks the copy process of the original record.“

So if you faced with this problem, or if you would like to proof your system were not affected, then here is a small SQL Script, which helps you to identify and solve this kind of inconsistent relations.

Find the potential corrupt records in the sys_file_reference:

SELECT * FROM  sys_file_reference sr
INNER JOIN
(SELECT DISTINCT uid_foreign, uid_local, fieldname,  tablenames, sys_language_uid,
MAX(pid) as pid FROM sys_file_reference
WHERE deleted = 0
GROUP BY uid_foreign, uid_local, fieldname, tablenames, sys_language_uid
HAVING COUNT(uid_foreign) > 1) duplicates
ON duplicates.uid_foreign = sr.uid_foreign AND duplicates.uid_local = sr.uid_local
AND duplicates.fieldname = sr.fieldname AND duplicates.tablenames = sr.tablenames
AND duplicates.sys_language_uid = sr.sys_language_uid AND sr.deleted = 0
AND sr.pid = 0 AND duplicates.pid > 0;

In this example we are testing the sys_file_references to find any duplicated image or file content. If you have any extension with an M:1 or M:M relation and you used IRRE elements to present them in the parent, you can change the following script to satisfy your needs.

To get the duplicates deleted, you can use an update – generated from the select:

UPDATE sys_file_reference sr
INNER JOIN
(SELECT DISTINCT uid_foreign, uid_local, fieldname,
tablenames, sys_language_uid, MAX(pid) as pid FROM sys_file_reference
WHERE deleted = 0
GROUP BY uid_foreign, uid_local, fieldname, tablenames, sys_language_uid
HAVING COUNT(uid_foreign) > 1) duplicates
ON duplicates.uid_foreign = sr.uid_foreign
AND duplicates.uid_local = sr.uid_local
AND duplicates.fieldname = sr.fieldname AND duplicates.tablenames = sr.tablenames
AND duplicates.sys_language_uid = sr.sys_language_uid AND sr.deleted = 0
AND sr.pid = 0 AND duplicates.pid > 0
SET sr.deleted = 1;