Bug #92493

linkvalidator: scheduler task + list of broken links dies if too many pages ("Prepared statement contains too many placeholders")

Added by Sybille Peters over 1 year ago. Updated about 2 months ago.

Status:
Resolved
Priority:
Should have
Assignee:
-
Category:
Linkvalidator
Target version:
-
Start date:
2020-10-06
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
9
PHP Version:
Tags:
large-site, placeholderlimit, db, prepared statement
Complexity:
Is Regression:
Sprint Focus:

Description

Exception: Prepared statement contains too many placeholders - {"exception":"Doctrine\\DBAL\\Driver\\Mysqli\\MysqliException: Prepared statement contains too many placeholders

Note for reproducing : You can also reproduce this in the backend in an installation with many pages. You don't have to check broken links at all. Just load the list on a page with many subpages (and "Show this level" "infinite") (to get the "Prepared statement contains too many placeholders" exception).

You should create at least one broken link first and run the check for the page, to make sure displaying the list works correctly.

Scheduler task

Reproduce (scheduler task)

  1. site with many pages (I have 40000)
  2. create scheduler task with start page and depth "infinite"

Error in scheduler task

In the scheduler task it happens when the (old) number of broken links is calculated (before link checking is performed). Linkvalidator recursively fetches a list of all pages (depending on depth) and then performs a DB query on that

Problem is the way linkvalidator queries. It recursively collects a list of all pages from current page (depending on depth). And then performs a query on it with "WHERE record_pid in (223, 34, etc.).

While this may be efficient (in comparison to several queries) for small sites, it creates a problem for large sites.

Exception (in scheduler task)

Prepared statement contains too many placeholders - {"exception":"Doctrine\\DBAL\\Driver\\Mysqli\\MysqliException: Prepared statement contains too many placeholders in /var/www/mysite/htdocs/typo3_src-9.5.22/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:86
Stack trace:
#0 /var/www/mysite/htdocs/typo3_src-9.5.22/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliConnection.php(131): Doctrine\\DBAL\\Driver\\Mysqli\\MysqliStatement->__construct(Object(mysqli), 'SELECT `link_ty...')
#1 /var/www/mysite/htdocs/typo3_src-9.5.22/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(901): Doctrine\\DBAL\\Driver\\Mysqli\\MysqliConnection->prepare('SELECT `link_ty...')
#2 /var/www/mysite/htdocs/typo3_src-9.5.22/vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): Doctrine\\DBAL\\Connection->executeQuery('SELECT `link_ty...', Array, Array)
#3 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/core/Classes/Database/Query/QueryBuilder.php(191): Doctrine\\DBAL\\Query\\QueryBuilder->execute()
#4 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/linkvalidator/Classes/LinkAnalyzer.php(466): TYPO3\\CMS\\Core\\Database\\Query\\QueryBuilder->execute()
#5 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/linkvalidator/Classes/Task/ValidatorTask.php(351): TYPO3\\CMS\\Linkvalidator\\LinkAnalyzer->getLinkCounts(3)
#6 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/linkvalidator/Classes/Task/ValidatorTask.php(295): TYPO3\\CMS\\Linkvalidator\\Task\\ValidatorTask->checkPageLinks(3)
#7 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/scheduler/Classes/Scheduler.php(179): TYPO3\\CMS\\Linkvalidator\\Task\\ValidatorTask->execute()
#8 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/scheduler/Classes/Command/SchedulerCommand.php(145): TYPO3\\CMS\\Scheduler\\Scheduler->executeTask(Object(TYPO3\\CMS\\Linkvalidator\\Task\\ValidatorTask))
#9 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/scheduler/Classes/Command/SchedulerCommand.php(99): TYPO3\\CMS\\Scheduler\\Command\\SchedulerCommand->loopTasks()
#10 /var/www/mysite/htdocs/typo3_src-9.5.22/vendor/symfony/console/Command/Command.php(255): TYPO3\\CMS\\Scheduler\\Command\\SchedulerCommand->execute(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#11 /var/www/mysite/htdocs/typo3_src-9.5.22/vendor/symfony/console/Application.php(1001): Symfony\\Component\\Console\\Command\\Command->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#12 /var/www/mysite/htdocs/typo3_src-9.5.22/vendor/symfony/console/Application.php(271): Symfony\\Component\\Console\\Application->doRunCommand(Object(TYPO3\\CMS\\Scheduler\\Command\\SchedulerCommand), Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#13 /var/www/mysite/htdocs/typo3_src-9.5.22/vendor/symfony/console/Application.php(147): Symfony\\Component\\Console\\Application->doRun(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#14 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/core/Classes/Console/CommandRequestHandler.php(63): Symfony\\Component\\Console\\Application->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#15 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/core/Classes/Console/CommandApplication.php(48): TYPO3\\CMS\\Core\\Console\\CommandRequestHandler->handleRequest(Object(Symfony\\Component\\Console\\Input\\ArgvInput))
#16 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/core/bin/typo3(23): TYPO3\\CMS\\Core\\Console\\CommandApplication->run()
#17 /var/www/mysite/htdocs/typo3_src-9.5.22/typo3/sysext/core/bin/typo3(24): {closure}()
#18 {main}

Next Doctrine\\DBAL\\Exception\\DriverException: An exception occurred while executing 'SELECT `link_type`, COUNT(`uid`) AS `nbBrokenLinks` FROM `tx_linkvalidator_link` WHERE ((`record_uid` IN (?,

Reproduce (linkvalidator report)

  1. Site with many pages
  2. In report choose all link types and depth "infinite"

System

  • TYPO3 Version: 9.5.22 - I added some log messages, so the line numbers may not be correct
  • 40000 pages (not hidden and not deleted)

Files

exception.txt (744 KB) exception.txt Sybille Peters, 2020-10-06 16:54

Related issues

Related to TYPO3 Core - Bug #86859: Search with indexed_search plugin throws exception: Prepared statement contains too many placeholders New2018-11-05

Actions
Related to TYPO3 Core - Feature #89762: Add pagination to forms listResolved

Actions
Related to TYPO3 Core - Task #93305: Long lists of parameters in QueryBuilder handled differently ("too many placeholders")New2021-01-18

Actions
Related to TYPO3 Core - Epic #93547: Collection of problems with large sitesAccepted2021-02-19

Actions
Related to TYPO3 Core - Bug #95812: Record Statistics runs into an error when reaching it's limitsNew2021-10-28

Actions
Related to TYPO3 Core - Bug #96434: Provide quoted array to string-list implode supportResolvedStefan Bürk2021-12-27

Actions
#1

Updated by Sybille Peters over 1 year ago

  • Related to Bug #86859: Search with indexed_search plugin throws exception: Prepared statement contains too many placeholders added
#2

Updated by Sybille Peters about 1 year ago

#3

Updated by Gerrit Code Review about 1 year ago

  • Status changed from New 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/c/Packages/TYPO3.CMS/+/66896

#4

Updated by Sybille Peters about 1 year ago

  • Description updated (diff)
  • Status changed from Under Review to New
#5

Updated by Sybille Peters about 1 year ago

  • Description updated (diff)
#6

Updated by Sybille Peters about 1 year ago

  • Description updated (diff)
#7

Updated by Gerrit Code Review about 1 year ago

  • Status changed from New to Under Review

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/c/Packages/TYPO3.CMS/+/66896

#8

Updated by Sybille Peters about 1 year ago

  • Tags set to large-site
#9

Updated by Gerrit Code Review about 1 year 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/c/Packages/TYPO3.CMS/+/66896

#10

Updated by Gerrit Code Review about 1 year 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/c/Packages/TYPO3.CMS/+/66896

#11

Updated by Gerrit Code Review about 1 year 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/c/Packages/TYPO3.CMS/+/66896

#12

Updated by Sybille Peters about 1 year ago

  • Related to Task #93305: Long lists of parameters in QueryBuilder handled differently ("too many placeholders") added
#13

Updated by Sybille Peters 11 months ago

  • Related to Epic #93547: Collection of problems with large sites added
#14

Updated by Sybille Peters 11 months ago

  • Subject changed from linkvalidator: scheduler task + list of broken links dies if too many pages to linkvalidator: scheduler task + list of broken links dies if too many pages ("Prepared statement contains too many placeholders")
#15

Updated by Gerrit Code Review 3 months 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/c/Packages/TYPO3.CMS/+/66896

#16

Updated by Gerrit Code Review 3 months 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/c/Packages/TYPO3.CMS/+/66896

#17

Updated by Gerrit Code Review 3 months 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/c/Packages/TYPO3.CMS/+/66896

#18

Updated by Gerrit Code Review 3 months 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/c/Packages/TYPO3.CMS/+/66896

#19

Updated by Gerrit Code Review 3 months 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/c/Packages/TYPO3.CMS/+/66896

#20

Updated by Gerrit Code Review 3 months 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/c/Packages/TYPO3.CMS/+/66896

#21

Updated by Gerrit Code Review 3 months ago

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

#22

Updated by Sybille Peters 3 months ago

  • Description updated (diff)
#23

Updated by Sybille Peters 3 months ago

  • Description updated (diff)
#24

Updated by Sybille Peters 3 months ago

  • Description updated (diff)
#25

Updated by Gerrit Code Review 2 months ago

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

#26

Updated by Sybille Peters 2 months ago

  • Tags changed from large-site to large-site, too many placeholders, db, prepared statement
#27

Updated by Sybille Peters 2 months ago

  • Tags changed from large-site, too many placeholders, db, prepared statement to large-site, placeholderlimit, db, prepared statement
#28

Updated by Sybille Peters 2 months ago

  • Related to Bug #95812: Record Statistics runs into an error when reaching it's limits added
#29

Updated by Gerrit Code Review 2 months ago

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

#30

Updated by Gerrit Code Review 2 months ago

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

#31

Updated by Gerrit Code Review 2 months ago

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

#32

Updated by Gerrit Code Review about 2 months ago

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

#33

Updated by Gerrit Code Review about 2 months ago

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

#34

Updated by Gerrit Code Review about 2 months ago

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

#35

Updated by Gerrit Code Review about 2 months ago

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

#36

Updated by Sybille Peters about 2 months ago

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

Updated by Stefan Bürk 24 days ago

  • Related to Bug #96434: Provide quoted array to string-list implode support added

Also available in: Atom PDF