Project

General

Profile

Actions
Copy link

Bug #87855

closed

Indexed_search problem if sql_mode have option ONLY_FULL_GROUP_BY enabled

Added by Rene Tobias about 5 years ago. Updated almost 5 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Indexed Search
Target version:
Candidate for patchlevel
Start date:
2019-03-07
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
9
PHP Version:
7.2
Tags:
Complexity:
Is Regression:
Yes
Sprint Focus:

Description

Hey,

if we have our mysql configured that "sql_mode" has option ONLY_FULL_GROUP_BY enabled we get this error:

Core: Exception handler (WEB): Uncaught TYPO3 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
AND (`IP`.`sys_language_uid` = 0) AND (`ISEC`.`phash` = `IP`.`phash`)
AND (`ISEC`.`page_id` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
AND (`IW`.`wid` = `IR`.`wid`)
AND (`ISEC`.`phash` = `IR`.`phash`)
AND (`IW`.`metaphone` = '236360654')
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 [9015782, 43437476, 64875351, 73643714, 81925491, 88605078, 95998721, 97287112, 118353222, 124077205, 126891362, 158985898, 170926166, 174990755, 180309938, 181055417, 225146476, 231739081, 246129675, 254429896, 260439973, 263170134, 131, 110, 20, 21, 22, 126, 23, 112, 115, 117, 113, 118, 119, 114, 133, 134, 111, 19, 24, 13, 120, 121, 132, 2, 3, 140, 4, 5, 6, 122, 11, 12, 1]:
'dbname.IP.static_page_arguments' isn't in GROUP BY | Doctrine\DBAL\Exception\DriverException thrown in file /var/www/typo3/typo3_source/typo3_src-9.5.5/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php in line 126. Requested URL: http://mydomain/iskanje?tx_indexedsearch_pi2%%5Baction%%5D=search&tx_indexedsearch_pi2%%5Bcontroller%%5D=Search&cHash=723567e661557f17c933efa0ccd4e9cc

Is this something that needs to be fixed?

Related issues 1 (0 open — 1 closed)

Related to TYPO3 Core - Bug #86994: indexed_search doesn't index pages using route enhancers Closed2018-11-25

Actions
Actions
Copy link
 #1

Updated by Georg Ringer about 5 years ago

  • Status changed from New to Accepted
  • Is Regression set to Yes
Actions
Copy link
 #2

Updated by Georg Ringer about 5 years ago

  • Related to Bug #86994: indexed_search doesn't index pages using route enhancers added
Actions
Copy link
 #3

Updated by Gerrit Code Review about 5 years ago

  • Status changed from Accepted 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/+/59972

Actions
Copy link
 #4

Updated by Alexander Grein about 5 years ago

How this problem can be reproduced?

I can not detect any probleum during indexing, normal searching and advanced searching with default settings.

Actions
Copy link
 #5

Updated by Georg Ringer about 5 years ago

the query looks like this

SELECT `index_fulltext`.*, `ISEC`.*, `IP`.* FROM `index_fulltext` INNER JOIN `index_phash` `IP` ON `index_fulltext`.`phash` = `IP`.`phash` INNER JOIN `index_section` `ISEC` ON `IP`.`phash` = `ISEC`.`phash` WHERE (`ISEC`.`page_id` IN (123)) AND (MATCH (`index_fulltext`.`fulltextdata`) AGAINST ("test" IN BOOLEAN MODE)) AND (`ISEC`.`rl0` IN (1)) 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_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`;

if you set in your sql tool SET sql_mode = 'ONLY_FULL_GROUP_BY';

then the query will fail. however I need to add more fields to the group by 

index_fulltext.phash,index_fulltext.fulltextdata, index_fulltext.metaphonedata,IP.data_page_reg1,IP.static_page_arguments

to make it work

Actions
Copy link
 #6

Updated by Oliver Hader about 5 years ago

  • Description updated (diff)
Actions
Copy link
 #7

Updated by Oliver Hader about 5 years ago

however I need to add more fields to the group by

Does that mean, that IP.static_page_arguments is not the only field that you had to add?
Besides that, the current state would also fail on Microsoft SqlServer...

Actions
Copy link
 #8

Updated by Gerrit Code Review about 5 years ago

Patch set 1 for branch 9.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/+/60240

Actions
Copy link
 #9

Updated by Georg Ringer about 5 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
Actions
Copy link
 #10

Updated by Rene Tobias about 5 years ago

Georg Ringer wrote:

Applied in changeset e112790094513af4f9c1f81a4f1b67ce49156e55.

After patch i still get the same error:

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 (?, ?, ?, ?, ?, ?)) AND (`ISEC`.`phash` = `IP`.`phash`) AND (`ISEC`.`page_id` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AND (`IW`.`wid` = `IR`.`wid`) AND (`ISEC`.`phash` = `IR`.`phash`) AND (`IW`.`baseword` LIKE '%zakonodaja%') 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_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`, `IP`.`static_page_arguments` ORDER BY `order_val1` DESC, `order_val2` DESC' with params [37097260, 44124580, 47108738, 65616783, 168771488, 263105595, 90, 166, 87, 85, 104, 106, 80, 79, 78, 64, 63, 62, 21, 20, 19, 118, 10, 23, 22, 12, 11, 158, 61, 59, 58, 57, 56, 55, 54, 53, 52, 75, 73, 46, 44, 43, 72, 41, 40, 39, 38, 114, 37, 36, 35, 34, 33, 32, 102, 101, 100, 99, 98, 97, 96, 24, 16, 15, 14, 13, 157, 18, 17, 109, 110, 111, 66, 76, 84, 89, 83, 151, 153, 81, 107, 112, 152, 2]: 'mytypo3site.IP.data_page_reg1' isn't in GROUP BY
Actions
Copy link
 #11

Updated by Benni Mack almost 5 years ago

  • Status changed from Resolved to Closed
Actions
Copy link

Also available in: Atom PDF