October CMS - SQL request incompatible with sql_mode=only_full_group_by

Question

i have a strange issue in my local environment : wamp64 mysql 5.7.14 OctoberCMS 382

this encountered in several plugins, For example, this one that attempts to display a list of slides in the backend

Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dbname.flosch_slideshow_slides.sort_order' which is not functionally dependent on columns in GROUP BY clause;

this is incompatible with sql_mode=only_full_group_by

(SQL:

select flosch_slideshow_slides.slideshow_id, count(*) as count

from flosch_slideshow_slides

where flosch_slideshow_slides.slideshow_id in (2, 1)

group by flosch_slideshow_slides.slideshow_id

order by sort_order asc

)

other example slightly different. this one that attempts to display a list of products on the frontend

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #51 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.jiri_jkshop_products_categories.category_id' which is not functionally dependent on columns in GROUP BY clause;

this is incompatible with sql_mode=only_full_group_by

(SQL:

select * from jiri_jkshop_products inner join jiri_jkshop_products_categories on id = jiri_jkshop_products_categories.product_id where active = 1 and category_id in (8) and visibility = 1 group by id order by title asc

)

Those requests are triggered here :

Backend\Behaviors\ListController->listRender()

so i don't have the hand on it

I specify that it works perfectly in the remote server and in the local environment of my mate

Another interesting point is that since I applied the latest updates for the system (from version 365 to 382) and all the plugins installed. Then, to test, I did the same on the remote server and it still works

As i work mainly locally, this is very very annoying

have you some ideas to fix it ? ideally without modify mysql/sql_mode

thanks & regards, Patrick

Answer 1

For a long time now, MySQL has contained a notorious nonstandard extension to GROUP BY, which allows oddball behavior in the name of efficiency. This extension has allowed countless developers around the world to use GROUP BY in production code without understanding what they were doing.

In particular, it's foolish to use SELECT * in a GROUP BY query, because a standard GROUP BY clause requires enumerating the columns. Many developers have been foolish this way.

Read this. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

The MySQL team has been trying to fix this misfeature without messing up production code. They added a sql_mode flag in 5.7.5 named ONLY_FULL_GROUP_BY to compel standard behavior. In a recent release, they turned on that flag by default. When you upgraded your local MySQL to 5.7.14, the flag got switched on and your production code, dependent on the old extension, stopped working.

Your choices?

1. fix the offending SQL queries, or get the plugin authors to do that.
2. roll back to a version of MySQL compatible out-of-the-box with the application software you use.
3. change your server's sql_mode to get rid of the newly set ONLY_FULL_GROUP_BY mode.

You can change the mode by doing a SET command.

SET  sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

should do the trick if you do it right after your application connects to MySQL.

Or, you can find the init file in your MySQL installation, locate the sql_mode= line, and change it to omit ONLY_FULL_GROUP_BY, and restart your server.

That first offending query? add sort_order to the GROUP BY clause, like this.

select flosch_slideshow_slides.slideshow_id, count(*) as count
  from flosch_slideshow_slides
 where flosch_slideshow_slides.slideshow_id in (2, 1)
 group by flosch_slideshow_slides.slideshow_id, sort_order
 order by sort_order asc

Will this break anything? Probably not.

Your second offending query? It contains SELECT *, so you will have to unravel the code surrounding the query to find out which columns are actually used before you can fix the GROUP BY clause. If this is a plugin, that job would best be done by its authors.