Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

3 errors on 9.5.9 after upgrade from 8.7.27 #10

Open
rowild opened this issue Oct 13, 2019 · 1 comment · May be fixed by #11 or #12
Open

3 errors on 9.5.9 after upgrade from 8.7.27 #10

rowild opened this issue Oct 13, 2019 · 1 comment · May be fixed by #11 or #12

Comments

@rowild
Copy link

rowild commented Oct 13, 2019

(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 added a commit to sypets/a21glossary that referenced this issue Jan 14, 2020
@sypets
[BUGFIX] Fix for sql_mode=only_full_group_by
51bc260 
In newer MySQL versions the query in GlossaryRepository::findAllForIndex()
will fail if sql_mode contains only_full_group_by.

The problem is the ORDER BY clause which does not match the GROUP BY.

Resolves: WapplerSystems#10
@sypets sypets linked a pull request Jan 14, 2020 that will close this issue
Open
@sypets
Copy link

sypets commented Jan 14, 2020 •
edited
Loading

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

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.

sypets added a commit to sypets/a21glossary that referenced this issue Jan 14, 2020
@sypets
!!![BUGFIX] Fix for sql_mode=only_full_group_by
63bd3df 
In newer MySQL versions the query in GlossaryRepository::findAllForIndex()
will fail if sql_mode contains only_full_group_by.

The problem is the ORDER BY clause which does not match the GROUP BY.

This patch replaces the Extbase query entirely with Doctrine-dbal
functionality.

The use of language fetching via getAspect() will not work for TYPO3
previous to 9.5

Resolves: WapplerSystems#10
@sypets sypets linked a pull request Jan 14, 2020 that will close this issue
Open
sypets added a commit to sypets/a21glossary that referenced this issue Jan 14, 2020
@sypets
!!![BUGFIX] Fix for sql_mode=only_full_group_by
5172ccd 
In newer MySQL versions the query in GlossaryRepository::findAllForIndex()
will fail if sql_mode contains only_full_group_by.

The problem is the ORDER BY clause which does not match the GROUP BY.

This patch replaces the Extbase query entirely with Doctrine-dbal
functionality.

The use of language fetching via getAspect() will not work for TYPO3
previous to 9.5

Resolves: WapplerSystems#10
sypets added a commit to sypets/a21glossary that referenced this issue Jan 14, 2020
@sypets
[BUGFIX] Fix for sql_mode=only_full_group_by
5feeea6 
In newer MySQL versions the query in GlossaryRepository::findAllForIndex()
will fail if sql_mode contains only_full_group_by.

The problem is the ORDER BY clause which does not match the GROUP BY.

Resolves: WapplerSystems#10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
2 participants
@rowild @sypets