search

2pisoftware / cmfive

DEPRECATED! Please see https://github.com/2pisoftware/cmfive-core for new version
http://cmfive.com
4 stars 4 forks source link

Fresh install of MySQL doesn't play nice with Inbox #225

Open adam-buckley opened 8 years ago

adam-buckley commented 8 years ago

By default, the SQL_MODE flag is set to "only_full_group_by". We should adjust our queries to work with this flag screenshot_2016-11-07_11-28-43

careck commented 8 years ago

could you please explain how a query needs to be adjusted to work with this?

In principle I think we shouldn't let our codebase be beholden by default settings of ONE operating system or Linux distribution. Maybe MySQL has a different setting when installed on CentOS or CloudOS or Debian or Redhat or SuSE ....

Is this a database setting that can be checked from within the installer maybe and then print an alert?

adam-buckley commented 8 years ago

It's not the cause of any operating system...

From the MySQL 5.7 reference:

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION. The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5.

Version 5.7.5 was released in late 2014 so maybe we haven't noticed this issue because we seem to use older versions of MySQL, the default installed in Ubuntu 14.04 is 5.5 or 5.6.

What the only_full_group_by flag does:

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

It's hard to explain the fixes for our queries, but here is the MySQL article on using group by in 5.7