Bug #87855

Indexed_search problem if sql_mode have option ONLY_FULL_GROUP_BY enabled

Added by Rene Tobias Tobias about 1 year ago. Updated 11 months ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Indexed Search
Start date:
2019-03-07
Due date:
% Done:

100%

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

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

Associated revisions

Revision e1127900 (diff)
Added by Georg Ringer about 1 year ago

[BUGFIX] Add field static_page_arguments to group by of query

With #86994 a new field has been added to the database table
`index_phash`. This fields needs to be added to the GROUP BY
statement as well.

Resolves: #87855
Releases: master, 9.5
Change-Id: I7b5270434b152e463ccf36886d963017fa6ad548
Reviewed-on: https://review.typo3.org/c/59972
Tested-by: TYPO3com <>
Tested-by: Susanne Moog <>
Tested-by: Benni Mack <>
Reviewed-by: Josef Glatz <>
Reviewed-by: Susanne Moog <>
Reviewed-by: Benni Mack <>

Revision cd16fcc6 (diff)
Added by Georg Ringer about 1 year ago

[BUGFIX] Add field static_page_arguments to group by of query

With #86994 a new field has been added to the database table
`index_phash`. This fields needs to be added to the GROUP BY
statement as well.

Resolves: #87855
Releases: master, 9.5
Change-Id: I7b5270434b152e463ccf36886d963017fa6ad548
Reviewed-on: https://review.typo3.org/c/60240
Tested-by: TYPO3com <>
Tested-by: Benni Mack <>
Reviewed-by: Benni Mack <>

History

#1 Updated by Georg Ringer about 1 year ago

  • Status changed from New to Accepted
  • Is Regression set to Yes

#2 Updated by Georg Ringer about 1 year ago

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

#3 Updated by Gerrit Code Review about 1 year 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

#4 Updated by Alexander Grein about 1 year ago

How this problem can be reproduced?

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

#5 Updated by Georg Ringer about 1 year 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

#6 Updated by Oliver Hader about 1 year ago

  • Description updated (diff)

#7 Updated by Oliver Hader about 1 year 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...

#8 Updated by Gerrit Code Review about 1 year 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

#9 Updated by Georg Ringer about 1 year ago

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

#10 Updated by Rene Tobias Tobias about 1 year 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

#11 Updated by Benni Mack 11 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF