Why do I see a PHP Fatal Error saying that the GROUP BY clause is incompatible with sql_mode=only_full_group_by? :

Why do I see a PHP Fatal Error saying that the GROUP BY clause is incompatible with sql_mode=only_full_group_by? Print

Modified on: Fri, Feb 10, 2017 at 4:14 PM

A SimpleRisk user reported seeing the following error message when going to the Reporting tab on a new SimpleRisk instance:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'simplerisk.a.calculated_risk' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by'

This is documented in the following SimpleRisk issue:

https://github.com/simplerisk/bundles/issues/16

This happens because the SimpleRisk application has not been configured to work properly in "only_full_group_by" sql mode. As it sounds like this may be made the default mode in MySQL 5.7, we have an item on the roadmap to look into updating and testing this for a future release, but for now, the steps to fix this are:

To unset that value without clearing any other sql_mode values, use the command:

SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

To permanently disable the only_full_group_by SQL mode, edit the /etc/my.cnf file and look for a line that reads "sql_mode=". Make sure that "only_full_group_by" is not set. If it is, remove it, and restart your mysql server.

More details on fixing this issue can be found here.

Did you find it helpful? Yes No

How can we help you today?

Why do I see a PHP Fatal Error saying that the GROUP BY clause is incompatible with sql_mode=only_full_group_by? Print

Related Articles