sql_mode "only_full_group_by" in MySql

If you are getting one of the following errors, you have this problem;

1. Error Code: 1055. 'test.somefield' isn't in GROUP BY
2. SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.somefield' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Check the current sql_mode in your mysql server with the following command to see if the only_full_group_by is enabled;

SELECT @@sql_mode;

This will give a list of sql_modes enabled in the server. Example as follows,

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

As you can see from the above result, you have a value called "only_full_group_by". That means if you have a group by clause in your sql statement, only the fields which are listed in a group by clause can be used in the select clause or order by clause, unless you aggregate the fields. i.e, using aggregate functions such as min(), max() etc

NOTE: As of MySQL Server version 5.7.5, the only_full_group_by is enabled in the sql_mode by default.

Temporary Fix:
Temporarily you can fix this issue by disabling this check in the server by globally and/or only in the active session by issuing the following command respectively.

SET GLOBAL sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

SET SESSION sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Permanent Fix:
- Make sure nonagregated field list in select or order by clause are in group by
- Restructure the sql statement