3 errors on 9.5.9 after upgrade from 8.7.27

[rowild]

(1/3) #1472074485 TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\SqlErrorException
Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'oesb.tx_a21glossary_main.short' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

(2/3) Doctrine\DBAL\Exception\DriverException
An exception occurred while executing 'SELECT substr(short, 1, 1) AS char FROM tx_a21glossary_main tx_a21glossary_main WHERE (tx_a21glossary_main.sys_language_uid IN (0, -1)) AND (tx_a21glossary_main.pid = 1) AND ((tx_a21glossary_main.deleted = 0) AND (tx_a21glossary_main.hidden = 0) AND (tx_a21glossary_main.starttime <= 1571000700) AND ((tx_a21glossary_main.endtime = 0) OR (tx_a21glossary_main.endtime > 1571000700)) AND (((tx_a21glossary_main.fe_group = '') OR (tx_a21glossary_main.fe_group IS NULL) OR (tx_a21glossary_main.fe_group = '0') OR (FIND_IN_SET('0', tx_a21glossary_main.fe_group)) OR (FIND_IN_SET('-1', tx_a21glossary_main.fe_group))))) GROUP BY char ORDER BY tx_a21glossary_main.short ASC': Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'oesb.tx_a21glossary_main.short' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

(3/3) Doctrine\DBAL\Driver\Mysqli\MysqliException
Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'oesb.tx_a21glossary_main.short' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

No idea, how to tackle this...

(Everything else works fine after the upgrade.)

[sypets]

As a workaround, you can change your MySQL setting on your server:

• https://stackoverflow.com/questions/23921117/disable-only-full-group-by

This depends a little on your MySQL version.

However, the new setting is actually a good thing and the code should be adapted IMHO.

The only_full_group_by is a mode that was added in 5.7.5. See http://rpbouman.blogspot.com/2014/09/mysql-575-group-by-respects-functional.html

For changing the code:

For example, the following SQL query is created in GlossaryRepository::findAllForIndex():

SELECT substr(short, 1, 1) AS charFROMtx_a21glossary_main tx_a21glossary_main WHERE (tx_a21glossary_main.sys_language_uid IN (0, -1)) AND (tx_a21glossary_main.pid = 17) AND ((tx_a21glossary_main.deleted = 0) AND (tx_a21glossary_main.hidden = 0) AND (tx_a21glossary_main.starttime <= 1578995220) AND ((tx_a21glossary_main.endtime = 0) OR (tx_a21glossary_main.endtime > 1578995220)) AND (((tx_a21glossary_main.fe_group = '') OR (tx_a21glossary_main.fe_group IS NULL) OR (tx_a21glossary_main.fe_group= '0') OR (FIND_IN_SET('0',tx_a21glossary_main.fe_group)) OR (FIND_IN_SET('-1', tx_a21glossary_main.fe_group))))) GROUP BY charORDER BYtx_a21glossary_main.short ASC

The problem is the "ORDER BY short".

I created a PR #11 and #12 as an alternative.