Bug #80875

Multiple problems with relation handler if too many records exist on a page

Added by Sascha Egerer about 2 years ago. Updated 2 months ago.

Status:
Accepted
Priority:
Should have
Assignee:
Category:
DataHandler aka TCEmain
Start date:
2017-06-12
Due date:
% Done:

100%

TYPO3 Version:
8
PHP Version:
Tags:
Complexity:
medium
Is Regression:
Sprint Focus:

Description

I've multiple problems with the relation handler if i modify (edit, delete) a record that is stored on a page where many records of that type exist.

Steps to reproduce:

  1. Install tt_address (should also work with any other record that uses categories)
  2. Create a sys_category record
  3. Create a Page of type folder
  4. Store 70.000 Records of type tt_address in that folder and put all records into the same sys_category

Steps that fail:

  1. Open one of the records and try to save the record
    PHP Fatal error:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 294912 bytes) in /xxx/vendor/typo3/cms/typo3/sysext/core/Classes/Database/ReferenceIndex.php on line 226
    
  1. Try to delet a record
    Core: Exception handler (WEB): Uncaught TYPO3 Exception: An exception occurred while executing 'SELECT `uid`, `t3ver_oid`, `t3ver_state` FROM `tt_address` WHERE (`pid` = ?) AND (`t3ver_oid` IN (?, ?, ?, ?, ### HERE ARE AROUND 70.000 PLACEHOLDERS!### , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AND (`t3ver_wsid` <> ?) ORDER BY `t3ver_state` DESC' with params [-1, 6877, ... ###HERE ARE AROUND 70.000 IDS!###, 0]:
    
    Prepared statement contains too many placeholders | Doctrine\DBAL\Exception\DriverException thrown in file /xxx/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php in line 115. Requested URL: https://xxx/typo3/index.php?route=%2Frecord%2Fcommit&token=b7ea833b34189d38b4fd1100f4a55766c16b0613&redirect=%2Ftypo3%2Findex.php%3FM%3Dweb_list%26moduleToken%3D114051d1fe77316e97e2afd42f8034d93a647a7d%26id%3D1316%26table%3D%26imagemode%3D1%26search_field%3Da%26table%3Dtt_address&cmd[tt_address][1300][delete]=1&prErr=1
    

There a multiple probelms but all of them are related to the realtion handling.
The relation handler does fetch all ids and puts them into a prepared statement. That fails as prepared statements to only allow 65.536 placeholders.
So this should be solved by a subquery and not by fetching all ids.
The other problem is, that all records are fetched and this ends up in a excessive memory usage.

It looks like all actions are executed as expected (records are deleted and saved) but the state of the databse is or can be inconsistent as the save process fails somewhere in the middle.


Subtasks

Bug #81555: Do not use prepared statements for reference index queriesClosedSascha Egerer

Bug #85257: OrphanRecordsCommand fails if list of ids is too longClosed

Bug #85795: Too many records on reference index updating break TYPO3Closed

Bug #85797: Too many record uids in plain data resolver break TYPO3Closed


Related issues

Related to TYPO3 Core - Bug #80800: Too many records on sys_file_reference table breaks TYPO3 when saving IRRE records Closed 2017-04-10
Related to TYPO3 Core - Bug #81993: 'Uncaught TYPO3 Exception' pops up when news records are localized Closed 2017-07-28
Related to TYPO3 Core - Bug #82233: File Abstraction Layer: Update storage index (scheduler) failure due to query constraints. Closed 2017-08-29

Associated revisions

Revision dfffc87d (diff)
Added by Sascha Egerer over 1 year ago

[BUGFIX] Process large uid lists in chunks in RelationHandler

Uid lists in the relation handler can be very big. To avoid exceeding
query limits like maximum number of placeholder per query or the max
allowed statement length these large lists are split into chunks of
safe length before processing.

Change-Id: I176acb85feb91c6162a77016c1918cf5a992625c
Resolves: #81555
References: #80875
Releases: master, 8.7
Reviewed-on: https://review.typo3.org/53193
Tested-by: TYPO3com <>
Reviewed-by: Manuel Selbach <>
Tested-by: Manuel Selbach <>
Reviewed-by: Christian Kuhn <>
Tested-by: Christian Kuhn <>

Revision e3a4bd77 (diff)
Added by Sascha Egerer over 1 year ago

[BUGFIX] Process large uid lists in chunks in RelationHandler

Uid lists in the relation handler can be very big. To avoid exceeding
query limits like maximum number of placeholder per query or the max
allowed statement length these large lists are split into chunks of
safe length before processing.

Change-Id: I176acb85feb91c6162a77016c1918cf5a992625c
Resolves: #81555
References: #80875
Releases: master, 8.7
Reviewed-on: https://review.typo3.org/55439
Tested-by: TYPO3com <>
Reviewed-by: Christian Kuhn <>
Tested-by: Christian Kuhn <>

Revision a3367ca8 (diff)
Added by Sascha Egerer about 1 year ago

[BUGFIX] Do not use named parameter for list of orphan records ids

The OrphanRecordsCommand fetches records by a list of ids.
This list can get very big so the database will fail if the
number of placesholders exceeds its limit.

Resolves: #85257
Related: #80875
Releases: master, 8.7
Change-Id: Ia6b9398f4e54157301abb57fac5adb7f51130907
Reviewed-on: https://review.typo3.org/57213
Tested-by: TYPO3com <>
Tested-by: Thomas Rawiel <>
Reviewed-by: Susanne Moog <>
Reviewed-by: Benni Mack <>
Tested-by: Benni Mack <>

Revision e2e07114 (diff)
Added by Sascha Egerer about 1 year ago

[BUGFIX] Do not use named parameter for list of orphan records ids

The OrphanRecordsCommand fetches records by a list of ids.
This list can get very big so the database will fail if the
number of placesholders exceeds its limit.

Resolves: #85257
Related: #80875
Releases: master, 8.7
Change-Id: Ia6b9398f4e54157301abb57fac5adb7f51130907
Reviewed-on: https://review.typo3.org/57230
Tested-by: TYPO3com <>
Reviewed-by: Benni Mack <>
Tested-by: Benni Mack <>

History

#1 Updated by Sascha Egerer about 2 years ago

  • Description updated (diff)

#2 Updated by Thomas Hohn about 2 years ago

We actually ended up using https://github.com/NamelessCoder/asynchronous_reference_indexing instead since we faced issues like this too.

#3 Updated by Benni Mack about 2 years ago

  • Target version changed from 8 LTS to next-patchlevel

#4 Updated by Sascha Egerer about 2 years ago

Thomas Hohn wrote:

We actually ended up using https://github.com/NamelessCoder/asynchronous_reference_indexing instead since we faced issues like this too.

This extension is not production ready. Looks like file references are not updated correctly.

The error does also occur if i try to update the reference index via the CLI tasks or in the backend db tools.

#5 Updated by Oliver Hader about 2 years ago

Thx for the report! Sascha, could you please give a stack-track for both steps mentioned above? Thx in advance!

#6 Updated by Sascha Egerer about 2 years ago

  • Status changed from New to Accepted
  • Assignee set to Sascha Egerer

I'll take care and post an update

#7 Updated by Sascha Egerer about 2 years ago

  • Related to Bug #80800: Too many records on sys_file_reference table breaks TYPO3 when saving IRRE records added

#8 Updated by Anu Bhuvanendran Nair almost 2 years ago

  • Related to Bug #81993: 'Uncaught TYPO3 Exception' pops up when news records are localized added

#9 Updated by Ricky Mathew almost 2 years ago

  • Related to Bug #82233: File Abstraction Layer: Update storage index (scheduler) failure due to query constraints. added

#10 Updated by Thomas Rawiel about 1 year ago

Sascha, here is the requested stack trace,
when running

./typo3cms cleanup:orphanrecords -vvv
via CLI
(TYPO3 8.7.16)

[ Doctrine\DBAL\Exception\DriverException ]      
 An exception occurred while executing 'SELECT `uid` FROM `tx_dmdeveloperlog_domain_model_logentry` WHERE `uid` NOT IN (?, ###ABOUT 140000 Placeholders###, ?) ORDER BY `uid` ASC' with params [###LIST-OF-IDs###]:  

 Prepared statement contains too many placeholders  

Exception trace:
#0 ()
  /var/www/src/typo3_src-8.7.16/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:115
#1 Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException()
  /var/www/src/typo3_src-8.7.16/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:128
#2 Doctrine\DBAL\DBALException::driverExceptionDuringQuery()
  /var/www/src/typo3_src-8.7.16/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:855
#3 Doctrine\DBAL\Connection->executeQuery()
  /var/www/src/typo3_src-8.7.16/vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:206
#4 Doctrine\DBAL\Query\QueryBuilder->execute()
  typo3/sysext/core/Classes/Database/Query/QueryBuilder.php:187
#5 TYPO3\CMS\Core\Database\Query\QueryBuilder->execute()
  typo3/sysext/lowlevel/Classes/Command/OrphanRecordsCommand.php:109
#6 TYPO3\CMS\Lowlevel\Command\OrphanRecordsCommand->execute()
  typo3conf/ext/typo3_console/Libraries/symfony-process.phar/vendor/symfony/console/Command/Command.php:252
#7 Symfony\Component\Console\Command\Command->run()
  typo3conf/ext/typo3_console/Libraries/symfony-process.phar/vendor/symfony/console/Application.php:946
#8 Symfony\Component\Console\Application->doRunCommand()
  typo3conf/ext/typo3_console/Libraries/symfony-process.phar/vendor/symfony/console/Application.php:248
#9 Symfony\Component\Console\Application->doRun()
  typo3conf/ext/typo3_console/Libraries/symfony-process.phar/vendor/symfony/console/Application.php:148
#10 Symfony\Component\Console\Application->run()
  typo3conf/ext/typo3_console/Classes/Console/Core/Kernel.php:162
#11 Helhum\Typo3Console\Core\Kernel->handle()
  typo3conf/ext/typo3_console/Scripts/typo3-console.php:19
#12 {closure}()
  typo3conf/ext/typo3_console/Scripts/typo3-console.php:21
#13 require()
  typo3cms:18

Caused by:

 [ Doctrine\DBAL\Driver\Mysqli\MysqliException ]
 Prepared statement contains too many placeholders

Exception trace:
#0 ()
  /var/www/src/typo3_src-8.7.16/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:94
#1 Doctrine\DBAL\Driver\Mysqli\MysqliStatement->__construct()
  /var/www/src/typo3_src-8.7.16/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliConnection.php:120
#2 Doctrine\DBAL\Driver\Mysqli\MysqliConnection->prepare()
  /var/www/src/typo3_src-8.7.16/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:844
#3 Doctrine\DBAL\Connection->executeQuery()
  /var/www/src/typo3_src-8.7.16/vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:206
#4 Doctrine\DBAL\Query\QueryBuilder->execute()
  typo3/sysext/core/Classes/Database/Query/QueryBuilder.php:187
#5 TYPO3\CMS\Core\Database\Query\QueryBuilder->execute()
  typo3/sysext/lowlevel/Classes/Command/OrphanRecordsCommand.php:109
#6 TYPO3\CMS\Lowlevel\Command\OrphanRecordsCommand->execute()
  typo3conf/ext/typo3_console/Libraries/symfony-process.phar/vendor/symfony/console/Command/Command.php:252
#7 Symfony\Component\Console\Command\Command->run()
  typo3conf/ext/typo3_console/Libraries/symfony-process.phar/vendor/symfony/console/Application.php:946
#8 Symfony\Component\Console\Application->doRunCommand()
  typo3conf/ext/typo3_console/Libraries/symfony-process.phar/vendor/symfony/console/Application.php:248
#9 Symfony\Component\Console\Application->doRun()
  typo3conf/ext/typo3_console/Libraries/symfony-process.phar/vendor/symfony/console/Application.php:148
#10 Symfony\Component\Console\Application->run()
  typo3conf/ext/typo3_console/Classes/Console/Core/Kernel.php:162
#11 Helhum\Typo3Console\Core\Kernel->handle()
  typo3conf/ext/typo3_console/Scripts/typo3-console.php:19
#12 {closure}()
  typo3conf/ext/typo3_console/Scripts/typo3-console.php:21
#13 require()
  typo3cms:18

cleanup:orphanrecords [--dry-run]

#11 Updated by Sascha Egerer about 1 year ago

Thomas Rawiel wrote:

Sascha, here is the requested stack trace,
when running
[...] via CLI
(TYPO3 8.7.16)

[...]

Will be handled in #85257

#12 Updated by Gleb Levitin 11 months ago

  • Related to Bug #85795: Too many records on reference index updating break TYPO3 added

#13 Updated by Gleb Levitin 11 months ago

  • Related to deleted (Bug #85795: Too many records on reference index updating break TYPO3)

#14 Updated by Gleb Levitin 11 months ago

  • Related to Bug #85795: Too many records on reference index updating break TYPO3 added

#15 Updated by Gleb Levitin 11 months ago

  • Related to deleted (Bug #85795: Too many records on reference index updating break TYPO3)

#16 Updated by DMK E-BUSINESS GmbH 7 months ago

It would be great to see some progress. It's not a good sign that an enterprise CMS is not able to edit a category with several thousand referenced elements in the backend.

#17 Updated by Benni Mack 2 months ago

  • Target version changed from next-patchlevel to Candidate for patchlevel

Also available in: Atom PDF