Bug #86859

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

Added by Sybille Peters about 3 years ago. Updated 2 months ago.

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

0%

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

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")Resolved2020-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
#1

Updated by Alexander Schnitzler over 1 year ago

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

Updated by Sybille Peters over 1 year ago

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

Updated by Sybille Peters 11 months ago

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

Updated by Sybille Peters 3 months ago

  • Description updated (diff)
#5

Updated by Sybille Peters 2 months ago

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

Updated by Sybille Peters 2 months ago

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

Also available in: Atom PDF