Topics

Anahita Project

Anahita Project's Topics

Nick Swinford

Nick Swinford

September 24 2017

ONLY_FULL_GROUP_BY sql mode causes error

As of MySQL 5.7.5, the ONLY_FULL_GROUP_BY sql mode is default and that causes issues with some errors in Anahita. #bug

Exception in ComStoriesControllerStory : Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'demo.story.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by of the following query : SELECT DISTINCT IF (story.body '' AND story.body IS NOT NULL,story.id,IF(story.story_comment_id IS NOT NULL AND story.story_object_id IS NOT NULL , CONCAT_WS(',',story.name,story.component,DATE(story.created_on), story.story_object_id), CONCAT_WS(',',story.name,story.component,DATE(story.created_on),CASE TRUE WHEN CONCAT(story.name,story.component) LIKE 'cover_editcom_people' THEN CONCAT(story.story_target_id) WHEN CONCAT(story.name,story.component) LIKE 'avatar_editcom_people' THEN CONCAT(story.story_target_id) WHEN story.name LIKE "avatar_edit" THEN IF(story.story_subject_id = story.story_target_id, "", story.id) WHEN story.name LIKE "actor_follow" THEN story.story_subject_id ELSE CONCAT(story.story_target_id,story in /var/www/demo/vendor/anahita/anahita/src/libraries/default/base/controller/resource.php on line 210 The new server comes with an upgraded version of MySQL, 5.7.19, which has the ONLY_FULL_GROUP_BY sql_mode on by default (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by) and seems to be causing some issues.

Rastin Mehr
Rastin Mehr
September 24 2017 Permalink
Yes that's the one. We need to update the syntax for 5.7.5 I'll take a look this week. In the meantime, if you found a fix please do a PR.
Nick Swinford
Nick Swinford
September 29 2017 Permalink
Do you know of anyway to exclude a column from a query?
Rastin Mehr
Rastin Mehr
September 29 2017 Permalink
What are you trying to do?
Nick Swinford
Nick Swinford
September 29 2017 Permalink
One of the problems here is that it's selecting columns that aren't necessary and aren't being properly grouped. For instance, the body column is being selected because it's part of the story entity, but it's always null.

I'd like to try excluding some of them and grouping the rest.
Rastin Mehr liked this
Rastin Mehr
Rastin Mehr
September 29 2017 Permalink
I believe the query is being constructed here: https://github.com/anahitasocial/anahita/blob/master/src/components/com_stories/domains/behaviors/aggregatable.php if any bug fixes are happening it would be here. Are you sure the the body is never used in stories? I need to check and confirm that today in our data.
Nick Swinford
Nick Swinford
September 30 2017 Permalink
Okay, so here's an example of the sql generated by that with a few extra cases include because I have some apps installed.

Loading ...


The approach I'm attempting to solve this issue is to use aggregate functions (https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html) on all the columns that are a problem. This is easy for columns that are just static values and I've fixed them so far like this:

Loading ...


Doing it like this replaces the previous select for that column. For instance, "verified" becomes "max(verified) as 'verified'", which solves the issue with that column.

I can't seem to find a workable solution for relation fields like story_subject_id. No matter what I try, it just adds the column to the select list again. If you look at the sql, you'll see that story_subject_id is actually already in the query twice. I need away to remove it the second time.
Rastin Mehr liked this
Rastin Mehr
Rastin Mehr
September 30 2017 Permalink
Thank you, I need take some time and look into this. It's hard to debug a query by skimming the post.
Rastin Mehr
Rastin Mehr
November 01 2017 Permalink
@NicholasJohn16 I dabbled with this, but eventually decided that disabling ONLY_FULL_GROUP_BY is the best option:

Loading ...
Rastin Mehr
Rastin Mehr
November 01 2017 Permalink
I'm still reading on ONLY_FULL_GROUP_BY setting and looking for a clean way to update the queries. It basically wants all the non aggregated columns to be included in the group by part of the query. Until then, disabling it is the quickest fix.
Rastin Mehr
Rastin Mehr
November 02 2017 Permalink
I did another attempt and I ended up with custom group code everywhere and still ended up with query bugs. I hope people who decided to enable MySql ONLY_FULL_GROUP_BY as default get a bad skin rash.
Rastin Mehr
Rastin Mehr
November 02 2017 Permalink
Another approach is that we wrap all the other selected values that aren't aggregated or mentioned in the group with ANY_VALUE() function. Theoretically we should be able to put this in the query builder.
Nick Swinford
Nick Swinford
November 08 2017 Permalink
Yeah, that's the work around I came up with. I set the sql_mode in my my.ini file instead though.

Did you end up with query bugs outside of the stories query? I'm worried this might be an issue in other places.
Rastin Mehr
Rastin Mehr
November 08 2017 Permalink
Yes hastags, locations, and mentions are affected too. Basically anywhere there is a group in the query.
Nick Swinford
Nick Swinford
November 08 2017 Permalink
Ugh.
Rastin Mehr
Rastin Mehr
November 05 2018 Permalink
@NicholasJohn16 I treated the ONLY_FULL_GROUP_BY issue the same way Wordpress is doing. Removing it per database session.

https://github.com/anahitasocial/anahita/blob/889581806fac594ccadd34f82cb449add1ac04f4/src/libraries/anahita/database/adapter/mysqli.php#L193

Out of all the strict MySql 5.7 requirements, this is the only one we are leaving out which I think is a reasonable compromise. The performance overhead seems minimal. In fact there is a bit of performance boost in the Anahita 4.3.13 release.
Nick Swinford liked this

Powered by Anahita