How to disable only_full_group_by in MySQL or Sequelize

Question

In Sequelize, how would I do the equivalent of SET sql_mode='' to avoid getting the following error?

SequelizeDatabaseError: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'assets.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Or, alternatively, how do I change the default configuration of MySQL such that the sql_mode is always ''?

Thanks!

Answer 1

Get the existing sql_mode and remove only the ONLY_FULL_GROUP_BY value rather than making it completely empty.

SELECT @@sql_mode; -- Get the current sql_mode

You might get a result as follows;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Now remove the ONLY_FULL_GROUP_BY from the result and update the sql_mode

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";

Now you are done.

Note that SET GLOBAL is to update the global setting which will not take effect until you restart the mysql server or service. SET SESSION will immediately take this effect on your current session even without restarting mysql server. Therefore, you can use either one or both depending on your requirement.

Alternatively, instead of disabling this setting, from MySQL 5.7 onwards, you can simply modify the query by using ANY_VALUE function on whichever the field is throwing error. For example ANY_VALUE(assets.group_id) as mentioned in the error message you have posted.

Answer 2

Try this:

SET GLOBAL sql_mode = '';