Project

General

Profile

Actions

Bug #86859

closed

Search with indexed_search plugin throws exception: Prepared statement contains too many placeholders

Added by Sybille Peters over 5 years ago. Updated 8 months ago.

Status:
Resolved
Priority:
Should have
Assignee:
-
Category:
Indexed Search
Target version:
-
Start date:
2018-11-05
Due date:
% Done:

100%

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

Description

Searching with indexed_search for a term (word) with a large number of results throws exception:

An exception occurred while executing 
'SELECT `ISEC`.*, `IP`.*, MAX(`IR`.`flags`) AS `order_val1`, SUM(`IR`.`freq`) AS `order_val2` FROM `index_phash` `IP`, `index_section` `ISEC`, `index_words` `IW`, `index_rel` `IR` 
WHERE (`IP`.`phash` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 

.... etc. 

)) AND (`IW`.`wid` = `IR`.`wid`) AND (`ISEC`.`phash` = `IR`.`phash`) AND (`IW`.`baseword` LIKE '%institut%') GROUP BY `IP`.`phash`, `ISEC`.`phash`, `ISEC`.`phash_t3`, `ISEC`.`rl0`, `ISEC`.`rl1`, `ISEC`.`rl2`, `ISEC`.`page_id`, `ISEC`.`uniqid`, `IP`.`phash_grouping`, `IP`.`data_filename`, `IP`.`data_page_id`, `IP`.`data_page_reg1`, `IP`.`data_page_type`, `IP`.`data_page_mp`, `IP`.`gr_list`, `IP`.`item_type`, `IP`.`item_title`, `IP`.`item_description`, `IP`.`item_mtime`, `IP`.`tstamp`, `IP`.`item_size`, `IP`.`contentHash`, `IP`.`crdate`, `IP`.`parsetime`, `IP`.`sys_language_uid`, `IP`.`item_crdate`, `IP`.`cHashParams`, `IP`.`externalUrl`, `IP`.`recordUid`, `IP`.`freeIndexUid`, `IP`.`freeIndexSetId` ORDER BY `order_val1` DESC, `order_val2` DESC' with params [754, 2499, 
6477, 11827, 12442, 1 

... etc. 

Prepared statement contains too many placeholders 

System environment

TYPO3 version 8.7.20

Steps to reproduce

  1. Get installation with a large number of pages / words.
  2. Search for a term that is common on those page.

(I could probably test to get the exact number, but I assume, someone knows this without tedious tests).

Exception

Exception is thrown in IndexSearchRepository::execFinalQuery when $queryBuilder->execute is called. There may be other occurances.

Best to generally search for expr()->in(

Comment

As indexed_search is not suited for large installations and this may not get fixed, I think at least the docs should name the restrictions specifically, e.g. what exactly are the limits, e.g. number of pages, number of words, if possible.


Files

stacktrace.txt (12.6 KB) stacktrace.txt Sybille Peters, 2018-11-05 21:09

Related issues 4 (3 open1 closed)

Related to TYPO3 Core - Bug #80875: Multiple problems with relation handler if too many records exist on a pageAcceptedSascha Egerer2017-06-12

Actions
Related to TYPO3 Core - Bug #92493: linkvalidator: scheduler task + list of broken links dies if too many pages ("Prepared statement contains too many placeholders")Closed2020-10-06

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
Actions #1

Updated by Alexander Schnitzler over 3 years ago

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

Updated by Sybille Peters over 3 years ago

  • Related to Bug #92493: linkvalidator: scheduler task + list of broken links dies if too many pages ("Prepared statement contains too many placeholders") added
Actions #3

Updated by Sybille Peters about 3 years ago

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

Updated by Sybille Peters over 2 years ago

  • Description updated (diff)
Actions #5

Updated by Sybille Peters over 2 years ago

  • Tags set to large-site, placeholderlimit, db, prepared statement
Actions #6

Updated by Sybille Peters over 2 years ago

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

Updated by Gerrit Code Review 9 months ago

  • Status changed from New to Under Review

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

Actions #8

Updated by Gerrit Code Review 9 months ago

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

Actions #9

Updated by Gerrit Code Review 9 months ago

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

Actions #10

Updated by Gerrit Code Review 9 months ago

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

Actions #11

Updated by Gerrit Code Review 9 months ago

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

Actions #12

Updated by Gerrit Code Review 9 months ago

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

Actions #13

Updated by Gerrit Code Review 9 months ago

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

Actions #14

Updated by Gerrit Code Review 8 months ago

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

Actions #15

Updated by Stefan Bürk 8 months ago

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

Updated by Gerrit Code Review 8 months ago

  • Status changed from Resolved to Under Review

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

Actions #17

Updated by Gerrit Code Review 8 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/+/80080

Actions #18

Updated by Gerrit Code Review 8 months ago

Patch set 2 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/+/80080

Actions #19

Updated by Stefan Bürk 8 months ago

  • Status changed from Under Review to Resolved
Actions

Also available in: Atom PDF