Bug #92493

linkvalidator: scheduler task + list of broken links dies if too many pages

Added by Sybille Peters 21 days ago. Updated 21 days ago.

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

0%

TYPO3 Version:
9
PHP Version:
Tags:
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

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/htdocs4/typo3_src-9.5.22/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:86
Stack trace:
#0 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/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/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(901): Doctrine\\DBAL\\Driver\\Mysqli\\MysqliConnection->prepare('SELECT `link_ty...')
#2 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): Doctrine\\DBAL\\Connection->executeQuery('SELECT `link_ty...', Array, Array)
#3 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/typo3/sysext/core/Classes/Database/Query/QueryBuilder.php(191): Doctrine\\DBAL\\Query\\QueryBuilder->execute()
#4 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/typo3/sysext/linkvalidator/Classes/LinkAnalyzer.php(466): TYPO3\\CMS\\Core\\Database\\Query\\QueryBuilder->execute()
#5 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/typo3/sysext/linkvalidator/Classes/Task/ValidatorTask.php(351): TYPO3\\CMS\\Linkvalidator\\LinkAnalyzer->getLinkCounts(3)
#6 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/typo3/sysext/linkvalidator/Classes/Task/ValidatorTask.php(295): TYPO3\\CMS\\Linkvalidator\\Task\\ValidatorTask->checkPageLinks(3)
#7 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/typo3/sysext/scheduler/Classes/Scheduler.php(179): TYPO3\\CMS\\Linkvalidator\\Task\\ValidatorTask->execute()
#8 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/typo3/sysext/scheduler/Classes/Command/SchedulerCommand.php(145): TYPO3\\CMS\\Scheduler\\Scheduler->executeTask(Object(TYPO3\\CMS\\Linkvalidator\\Task\\ValidatorTask))
#9 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/typo3/sysext/scheduler/Classes/Command/SchedulerCommand.php(99): TYPO3\\CMS\\Scheduler\\Command\\SchedulerCommand->loopTasks()
#10 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/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/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/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/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/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/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/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/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/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/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/typo3/sysext/core/Classes/Console/CommandApplication.php(48): TYPO3\\CMS\\Core\\Console\\CommandRequestHandler->handleRequest(Object(Symfony\\Component\\Console\\Input\\ArgvInput))
#16 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/typo3/sysext/core/bin/typo3(23): TYPO3\\CMS\\Core\\Console\\CommandApplication->run()
#17 /var/www/t3dev.uni-oldenburg.de/htdocs4/typo3_src-9.5.22+patch+linkvalidator+logging/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)

exception.txt View (744 KB) 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 New 2018-11-05
Related to TYPO3 Core - Feature #89762: Add pagination to forms list New 2019-11-24

History

#1 Updated by Sybille Peters 21 days ago

The number of placeholders would be cut in half if we did not have this:

->where(
                $queryBuilder->expr()->orX(
                    $queryBuilder->expr()->andX(
                        $queryBuilder->expr()->in(
                            'record_uid',
                            $queryBuilder->createNamedParameter($this->pids, Connection::PARAM_INT_ARRAY)
                        ),
                        $queryBuilder->expr()->eq('table_name', $queryBuilder->createNamedParameter('pages'))
                    ),
                    $queryBuilder->expr()->andX(
                        $queryBuilder->expr()->in(
                            'record_pid',
                            $queryBuilder->createNamedParameter($this->pids, Connection::PARAM_INT_ARRAY)
                        ),
                        $queryBuilder->expr()->neq('table_name', $queryBuilder->createNamedParameter('pages'))
                    )
                )
            )

This is not really necesary. At one point, there was some confusion about record_pid:

  • for tt_content records, it is the page id
  • for pages, is it the page id or the parent page id?

There were bugs because what record_pid for pages was sometimes the uid of the current page or the pid (uid of parent page). This was now fixed, but I think we went the wrong path. It is now the uid of parent page (as in pid). But we never need that. We rather need to often fetch all records (tt_content, pages, etc) for a list of page uids.

Anyway, changing this again might cut the number of placeholders down by 50% but it won't solve a general problem on large sites.

#2 Updated by Sybille Peters 8 days ago

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

#3 Updated by Sybille Peters 1 day ago

Also available in: Atom PDF