Bug #80800

Too many records on sys_file_reference table breaks TYPO3 when saving IRRE records

Added by José Ricardo over 2 years ago. Updated about 1 year ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
File Abstraction Layer (FAL)
Target version:
-
Start date:
2017-04-10
Due date:
% Done:

100%

TYPO3 Version:
9
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:
On Location Sprint

Description

When saving a record (eg: tx_news_domain_model_news) and uploading images on field with IRRE of FAL records (eg: fal_media) the database will generate lots of UPDATES on the sys_file_reference table, breaking it when this table is too big (250k+ records).

The reason for that is that the resorting method of TYPO3's Data Handler (sysext/core/Classes/DataHandling/DataHandler.php) verifies if there is a "sortby" configuration on the table's TCA, which, in the case of sys_file_reference, is the field "sorting". The resorting method is useful to recalculate the order of the record on a PID using the "sorting" field.

However, unless i'm wrong, there are no places on TYPO3 CMS where sys_file_reference records are listed, so this configuration is useless for this table. Setting it to empty string solves the problem and apparently has no side effects.

Version: TYPO3 7.6.16


Related issues

Related to TYPO3 Core - Bug #80875: Multiple problems with relation handler if too many records exist on a page Accepted 2017-06-12
Related to TYPO3 Core - Task #48098: Reversed order in sorting field in sys_file_reference compared to e.g. tt_content Rejected 2013-05-10
Related to TYPO3 Core - Bug #83160: Remove unused column sys_file_reference.sorting Closed 2017-11-29

Associated revisions

Revision 8da63a45 (diff)
Added by Stephan Großberndt about 2 years ago

[BUGFIX] Disable column sys_file_reference.sorting

Disable the column sys_file_reference.sorting by its according TCA
definition as it is not useful and leads to severe performance issues
adding new references when there are already many references.

Releases: master, 8.7
Resolves: #80800
Change-Id: I7b34a1be7efe3cf4818b43919389d9c815e74ec6
Reviewed-on: https://review.typo3.org/54342
Tested-by: TYPO3com <>
Reviewed-by: Benni Mack <>
Reviewed-by: Mathias Schreiber <>
Tested-by: Mathias Schreiber <>
Reviewed-by: Christian Kuhn <>
Tested-by: Christian Kuhn <>

Revision fbd1d72a (diff)
Added by Stephan Großberndt about 2 years ago

[BUGFIX] Disable column sys_file_reference.sorting

Disable the column sys_file_reference.sorting by its according TCA
definition as it is not useful and leads to severe performance issues
adding new references when there are already many references.

Releases: master, 8.7
Resolves: #80800
Change-Id: I7b34a1be7efe3cf4818b43919389d9c815e74ec6
Reviewed-on: https://review.typo3.org/54840
Tested-by: TYPO3com <>
Reviewed-by: Christian Kuhn <>
Tested-by: Christian Kuhn <>

Revision 728bfc19 (diff)
Added by Stephan Großberndt about 2 years ago

[!!!][TASK] Remove column sys_file_reference.sorting

The column `sorting` has been removed from table `sys_file_reference` as
it was not used in TYPO3 core and lead to severe performance issues on
instances with many records in the table.

Releases: master
Resolves: #83160
Related: #80800
Change-Id: Ibff4e11f2ab3954a71cf0ef86e4a75ec4f39eb8c
Reviewed-on: https://review.typo3.org/54858
Tested-by: TYPO3com <>
Reviewed-by: Mathias Schreiber <>
Tested-by: Mathias Schreiber <>
Reviewed-by: Stefan Neufeind <>
Tested-by: Stefan Neufeind <>

History

#1 Updated by José Ricardo over 2 years ago

  • Description updated (diff)

#2 Updated by José Ricardo over 2 years ago

  • Description updated (diff)

#3 Updated by Thomas Hohn over 2 years ago

  • Status changed from New to Needs Feedback

Please provide information about TYPO3 version - if it 8.7 - did you follow all the migrations steps in the install tool?

#4 Updated by José Ricardo over 2 years ago

  • Description updated (diff)

#5 Updated by José Ricardo over 2 years ago

Sorry, forgot it. It's 7.6.16 (i've updated the description as well).

#6 Updated by Thomas Hohn over 2 years ago

Hi

Had similar issues on a installation. Our fix was rebuild the ref_index since it contained a lot of crashed records. And
then we started to use https://github.com/NamelessCoder/asynchronous_reference_indexing since we had
similar issues.

#7 Updated by José Ricardo over 2 years ago

Gonna look the plugin, but the question is why sortby is necessary for sys_file_reference?

#8 Updated by Sascha Egerer over 2 years ago

  • Related to Bug #80875: Multiple problems with relation handler if too many records exist on a page added

#9 Updated by Alexander Opitz over 2 years ago

  • Status changed from Needs Feedback to New

#10 Updated by Stephan Großberndt about 2 years ago

  • Related to Task #48098: Reversed order in sorting field in sys_file_reference compared to e.g. tt_content added

#12 Updated by Stephan Großberndt about 2 years ago

  • Status changed from New to In Progress
  • Priority changed from Should have to Must have
  • TYPO3 Version changed from 7 to 9

#13 Updated by Gerrit Code Review about 2 years ago

  • Status changed from In Progress to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#14 Updated by Gerrit Code Review about 2 years ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#15 Updated by Gerrit Code Review about 2 years ago

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#16 Updated by Gerrit Code Review about 2 years ago

Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#17 Updated by Gerrit Code Review about 2 years ago

Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#18 Updated by Gerrit Code Review about 2 years ago

Patch set 6 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#19 Updated by Gerrit Code Review about 2 years ago

Patch set 7 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#20 Updated by Gerrit Code Review about 2 years ago

Patch set 8 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#21 Updated by Gerrit Code Review about 2 years ago

Patch set 9 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#22 Updated by Gerrit Code Review about 2 years ago

Patch set 10 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#23 Updated by Gerrit Code Review about 2 years ago

Patch set 11 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54342

#24 Updated by Susanne Moog about 2 years ago

  • Sprint Focus set to On Location Sprint

#25 Updated by Gerrit Code Review about 2 years ago

Patch set 1 for branch TYPO3_8-7 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54840

#26 Updated by Stephan Großberndt about 2 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#27 Updated by Stephan Großberndt about 2 years ago

  • Category set to File Abstraction Layer (FAL)

#28 Updated by Stephan Großberndt about 2 years ago

  • Related to Bug #83160: Remove unused column sys_file_reference.sorting added

#29 Updated by Benni Mack about 1 year ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF