When you upgrade your MySQL server to version 5.7.5 or later, you may find that existing queries containing a GROUP BY clause suddenly fail. This is not a bug, but a significant change in how MySQL handles functional dependencies.

The Error

You will typically see an error message like this:

Error 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘hostname.columnname.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Why is this happening?

Starting with version 5.7.5, MySQL changed the way GROUP BY behaves to be SQL-99 compliant.

In previous versions, MySQL was “forgiving” and allowed you to select columns that weren’t part of the GROUP BY clause, even if they weren’t part of an aggregate function (like SUM or COUNT). This often led to unpredictable results, as MySQL would just pick a value from one of the rows in the group.

The new ONLY_FULL_GROUP_BY mode:

  • Rejects queries where the SELECT list, HAVING condition, or ORDER BY list refer to non-aggregated columns that are neither named in the GROUP BY clause nor functionally dependent on them.
  • Forces better query writing habits and ensures deterministic results.

The Workaround (Configuration Change)

If you need a quick fix to get legacy applications running, you can disable this check in your configuration.

1. Locate your configuration

If you used Homebrew on macOS, you can find the default template and copy it to the global configuration path:

sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

2. Modify sql_mode

Open /etc/my.cnf (or your relevant my.cnf path) and add or update the sql_mode line to exclude ONLY_FULL_GROUP_BY:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3. Restart MySQL

brew services restart mysql

The Professional Fix (Query Refactoring)

While the configuration change is a valid workaround, the recommended solution is to fix your SQL queries. You have two main options:

  1. Add the missing columns: Include all selected columns in your GROUP BY clause.
  2. Use Aggregate Functions: Wrap non-aggregated columns in a function like ANY_VALUE(column_name). This function is specifically designed to tell MySQL: “I know what I’m doing, just give me any value from this group.”

Example of using ANY_VALUE:

SELECT name, ANY_VALUE(address), COUNT(*) 
FROM users 
GROUP BY name;

By following the official SQL standards, you ensure your database logic remains robust and portable across different SQL engines!