Issue Bug Closed

#82929sys_file_references filled with duplicate/multiple entries

Sigfried Arnold 11/06/17, 12:22 updated 10/12/26, 10:11

Description

On multiple TYPO3 instances running on TYPO3 8.7.8 (some of them were clean installed with different Versions of the TYPO3 8 branche since the release and now updated to the latest Version) we get multiple entries for the same file.

They are not shown in the backend but are visible in the database and are taken into account while pages are being generated -

SELECT pid, fieldname, COUNT(*) as count FROM sys_file_reference GROUP BY pid,fieldname order by count DESC;

The worst case i found so far looks like this:

50    image    214572
232    assets    352
9    assets    98
9    image    96
103    image    75
138    image    72
138    assets    70
94    assets    42
109    assets    41
235    assets    40

The result is, that the page generation for those pages takes ages (or till max_execution_time is reached in this example, where 200k images are present).

This issue also might be connected to #80804 since the blocked apache processes sometimes do not unlock the lockfiles properly and then apache gets jammed.

Currently i have not figured out yet, what causes the duplication.

Also i found an older TYPO3 7.6 Instance, which does the same.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

History 23

  1. Frans Saris commented <span data-processor="relativetime" data-value="2017-11-06T13:04:28+00:00" > 11/06/17, 13:04 </span>

    Are you using multiple languages and or workspaces?

  2. Sigfried Arnold commented <span data-processor="relativetime" data-value="2017-11-06T13:16:12+00:00" > 11/06/17, 13:16 </span>

    On the 7.6 instance we used workspaces and multiple languages, but now workspaces are disabled and only languages are in use

    On the 8.7 instances none uses workspacese but all of them use at least one additional language, one uses 9 languages – but there is no corellation between language count and duplicates – the worst case only got 2 languages

  3. Frans Saris commented <span data-processor="relativetime" data-value="2017-11-06T20:20:27+00:00" > 11/06/17, 20:20 </span>

    Can you show some more column data. What fields are/are not identical

  4. Sigfried Arnold commented <span data-processor="relativetime" data-value="2017-11-07T15:35:34+00:00" > 11/07/17, 15:35 </span>

    identical fields are the following:

    sys_language_uid, l10n_parent, l10n_diffsource, uid_local, uid_foreign, tablenames, fieldname (also title, description, alternative and so on are identical)

    here is an example from a web without workspaces/versioning – so i removed all t3ver_* fields (they just contain 0)

    This is an example of “bad” case – this is taken from a bigger website with currently 9 languages in total – as you can see those records are inserted the same time – sometimes only a duplicate, somtimes a huge amount of multiple entries – this page only has one image in the media field – i’ve also provided a grouping by language – as you can see, it inserted 7 copies on a few languages, only L=16 worked properly and L=1 & 2 really went nuts.

    COUNT(*) sys_language_uid
    1        0
    18038    1
    18046    2
    7        3
    7        4
    7        5
    7        11
    1        15
    1        16
    uid     pid  FROM_UNIXTIME(tstamp)   FROM_UNIXTIME(crdate)  sorting   deleted        l10n_parent         uid_foreign   fieldname
                                                                               hidden           l10n_diffsource  tablenames
                                                                                    sys_language_uid   uid_local
    212      7    2017-05-11 16:43:40    2016-03-15 09:05:27    1031168   0    0    0    0      ...    18    7    pages    media
    39902    7    2017-06-07 17:31:16    2017-06-07 17:31:16    285952    0    0    1    212    ...    18    7    pages    media
    39903    7    2017-06-07 17:31:16    2017-06-07 17:31:16    512       0    0    1    212    ...    18    7    pages    media
    39936    7    2017-06-20 18:40:26    2017-06-20 18:40:26    472576    0    0    1    212    ...    18    7    pages    media
    39937    7    2017-06-20 18:40:26    2017-06-20 18:40:26    905728    0    0    1    212    ...    18    7    pages    media
    39938    7    2017-06-20 18:40:26    2017-06-20 18:40:26    188160    0    0    1    212    ...    18    7    pages    media
    39934    7    2017-06-20 18:40:26    2017-06-20 18:40:26    718080    0    0    1    212    ...    18    7    pages    media
    39935    7    2017-06-20 18:40:26    2017-06-20 18:40:26    596992    0    0    1    212    ...    18    7    pages    media
    39964    7    2017-07-07 08:34:50    2017-07-07 08:34:50    83456     0    0    1    212    ...    18    7    pages    media
    39965    7    2017-07-07 08:34:50    2017-07-07 08:34:50    801024    0    0    1    212    ...    18    7    pages    media
    39966    7    2017-07-07 08:34:50    2017-07-07 08:34:50    676608    0    0    1    212    ...    18    7    pages    media
    39967    7    2017-07-07 08:34:50    2017-07-07 08:34:50    555520    0    0    1    212    ...    18    7    pages    media
    39968    7    2017-07-07 08:34:50    2017-07-07 08:34:50    988672    0    0    1    212    ...    18    7    pages    media
    39969    7    2017-07-07 08:34:50    2017-07-07 08:34:50    926464    0    0    1    212    ...    18    7    pages    media
    39970    7    2017-07-07 08:34:50    2017-07-07 08:34:50    493312    0    0    1    212    ...    18    7    pages    media
    39971    7    2017-07-07 08:34:50    2017-07-07 08:34:50    617728    0    0    1    212    ...    18    7    pages    media
    39972    7    2017-07-07 08:34:50    2017-07-07 08:34:50    738816    0    0    1    212    ...    18    7    pages    media
    39973    7    2017-07-07 08:34:50    2017-07-07 08:34:50    146688    0    0    1    212    ...    18    7    pages    media
    39974    7    2017-07-07 08:34:50    2017-07-07 08:34:50    864256    0    0    1    212    ...    18    7    pages    media
    39975    7    2017-07-07 08:34:50    2017-07-07 08:34:50    21248     0    0    1    212    ...    18    7    pages    media
    39976    7    2017-07-07 08:34:50    2017-07-07 08:34:50    431104    0    0    1    212    ...    18    7    pages    media
  5. Frans Saris commented <span data-processor="relativetime" data-value="2017-11-14T21:44:27+00:00" > 11/14/17, 21:44 </span>

    Interesting thing is that a lot of timestamps are the same and looking at the sorting field it looks like they are somehow all linked to the same field.

    Is there some custom tca added there? Can you see what action triggers the creation of all the extra rows in the DB.

  6. Sigfried Arnold commented <span data-processor="relativetime" data-value="2017-11-15T09:35:27+00:00" > 11/15/17, 09:35 </span>

    In most cases there was Grid Elements and DCE present, that was our fist guess – but we have a few occurrences without any additional fields.

    So far we have not isolated the issue yet.

  7. Markus Opahle commented <span data-processor="relativetime" data-value="2017-12-06T11:14:37+00:00" > 12/06/17, 11:14 </span>

    I don’t know if this is related to this issue but I found a similar issue on several 8.7 instances. The only difference here is that the duplicates marked as deleted right after creation. This happens when you add l10n_mode=exclude to an inline relation to sys_file_references. The duplicates appear when you translate a record and they depend on the number of translations exist before (so you actually have to translate the record twice to reproduce the issue).
    It seems that an inline relationship to a translatable table and with l10n_mode=exclude is not handled well by \TYPO3\CMS\Core\DataHandling\Localization\DataMapProcessor which creates the duplicates in my case.
    Hope this helps

  8. Sigfried Arnold commented <span data-processor="relativetime" data-value="2018-07-02T18:14:44+00:00" > 07/02/18, 18:14 </span>

    Just got noticed by a customer with a TYPO3 7.6 instance with multiple languages with this issue – this time a few hundred copies. The instance runs on Azure with Ubuntu and MariaDB – and has a different architecure from our other instances i’ve seen so far. Others usually used CentOS with MySQL or RHEL with MySQL.

    Sadly this enviroment has no transaction log in MariaDB activated, so i cannot narrow down, what created those duplicates.

    Is there any new information available on this issue?

  9. Oliver Hader commented <span data-processor="relativetime" data-value="2019-02-06T16:15:43+00:00" > 02/06/19, 16:15 </span>

    Having a sequence of recreated records (e.g. 20 deleted, one visible record, all having same information) might the result of a flow in l10n_mode=exclude in combination with inline records that has been addressed in issue #87640 – maybe give it a try in order to avoid these re-synchronization problems.

  10. Sybille Peters commented <span data-processor="relativetime" data-value="2019-02-27T14:47:28+00:00" > 02/27/19, 14:47 </span>

    Oliver, the patch introduced for #87640 was only merged into TYPO3 9, but this bug here was reported for 8 and 7.

  11. Sybille Peters commented <span data-processor="relativetime" data-value="2019-02-27T15:06:55+00:00" > 02/27/19, 15:06 </span>

    Sigfried, I don’t quite see how the same fieldname and pid occuring multiple times would automatically point to a problem in the query you suggested:

    SELECT pid, fieldname, COUNT(*) as count FROM sys_file_reference GROUP BY pid,fieldname order by count DESC;

    But this query would with a higher probability, I think: It checks if there are duplicates with the same relation:

    SELECT pid, tablenames,uid_foreign,table_local,uid_local,COUNT(*) as count FROM sys_file_reference GROUP BY pid,tablenames,uid_foreign,table_local,uid_local order by count DESC;

    I do get hits for that (but not such huge amounts, all less than 10).

  12. Jonas Eberle commented <span data-processor="relativetime" data-value="2020-03-12T07:05:57+00:00" > 03/12/20, 07:05 </span>

    I also get hits for that in the range 2-4 in several instances.

    # I added a constraint count > 1
    SELECT 
      pid, tablenames, uid_foreign, table_local, uid_local, COUNT(*) as count 
    FROM 
      sys_file_reference 
    GROUP BY 
      pid, tablenames, uid_foreign, table_local, uid_local 
    HAVING 
      count > 1 
    ORDER BY 
      count DESC;

    The instances are: 8.7, 9.5 (upgraded), 9.5 (fresh)

  13. Sigfried Arnold commented <span data-processor="relativetime" data-value="2020-03-12T11:18:22+00:00" > 03/12/20, 11:18 </span>

    I was not “haunted” by this issue since (at least not in TYPO3 9.x) – but be aware, that those queries do not account for deleted records, so sometimes multiple references are fine – if there are only a few, you should check em if they are really duplicates or if they are just deleted

  14. Jonas Eberle commented <span data-processor="relativetime" data-value="2020-03-12T12:42:17+00:00" > 03/12/20, 12:42 </span>

    Taking into account deleted and hidden it would be

    SELECT
        pid, tablenames, uid_foreign, table_local, uid_local, COUNT(*) as count
    FROM
        sys_file_reference
    WHERE
        !deleted AND !hidden
    GROUP BY
        pid, tablenames, uid_foreign, table_local, uid_local
    HAVING
        count > 1
    ORDER BY
        count DESC;

    I still have duplicate records in all those instances.

  15. Benni Mack commented <span data-processor="relativetime" data-value="2020-03-17T16:58:18+00:00" > 03/17/20, 16:58 </span>

    Jonas Eberle wrote:
    > Taking into account deleted and hidden it would be
    >
    > […]
    >
    > I still have duplicate records in all those instances.

    Can you find out how this happens?

  16. Christian Kuhn commented and changed status Needs Feedback Closed <span data-processor="relativetime" data-value="2023-01-06T14:25:42+00:00" > 01/06/23, 14:25 </span>

    Hey. I hope it’s ok to close here: Keeping db integrity over multiple core version when upgrading core isn’t simple and a broader task. Extension “dbdoctor” from https://github.com/lolli42/dbdoctor tries to find such inconsistencies and to fix them, but it is not in a state that could be merged into core. We’ll see on how it evolves, though.

  17. Michael Nußbaumer commented <span data-processor="relativetime" data-value="2024-02-20T04:05:07+00:00" > 02/20/24, 04:05 </span>

    Jonas Eberle wrote in #note-18:
    > Taking into account deleted and hidden it would be
    >
    > […]
    >
    > I still have duplicate records in all those instances.

    This query is not quite right, it misses fieldname for grouping:

    SELECT
        pid, tablenames, fieldname, uid_foreign, table_local, uid_local, COUNT(*) as count
    FROM
        sys_file_reference
    WHERE
        !deleted AND !hidden
    GROUP BY
        pid, tablenames, fieldname, uid_foreign, table_local, uid_local
    HAVING
        count > 1
    ORDER BY
        count DESC;
  18. Rémy DANIEL commented <span data-processor="relativetime" data-value="2026-10-12T10:09:23+00:00" > 10/12/26, 10:09 </span>

    Up-to-date query:

    SELECT pid, tablenames, fieldname, uid_foreign, uid_local, sys_language_uid, COUNT(*) AS count
    FROM sys_file_reference
    WHERE !deleted
    GROUP BY pid, tablenames, fieldname, uid_foreign, uid_local, sys_language_uid
    HAVING count > 1 
    ORDER BY count DESC;
  19. Rémy DANIEL commented <span data-processor="relativetime" data-value="2026-10-12T10:11:11+00:00" > 10/12/26, 10:11 </span>

    Query to remove duplicates (works on mysql 8.4).

    DISCLAIMER: MAKE A BACKUP OF YOUR TABLE BEFORE

    DELETE sfr
    FROM sys_file_reference sfr
    JOIN (
        SELECT uid,
               ROW_NUMBER() OVER (
                   PARTITION BY pid, tablenames, fieldname, uid_foreign, uid_local, sys_language_uid
                   ORDER BY uid ASC
               ) AS rn
        FROM sys_file_reference
        WHERE !deleted
    ) duplicates ON sfr.uid = duplicates.uid
    WHERE duplicates.rn > 1;