ApsaraDB RDS:What do I do if the "1055 (42000): SELECT list is not in GROUP BY clause and contains nonaggregated column" error message is displayed when I use my ApsaraDB RDS for MySQL instance?

Cause 1: ONLY_FULL_GROUP_BY is set in the instance parameters

When ONLY_FULL_GROUP_BY is included in the sql_mode parameter, MySQL rejects any SELECT statement that references a non-aggregated column not listed in the GROUP BY clause. For example, the following query triggers the error:

SELECT name, address, MAX(age) FROM t GROUP BY name;
-- ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
-- and contains nonaggregated column 'mydb.t.address' which is not functionally
-- dependent on columns in GROUP BY clause; this is incompatible with
-- sql_mode=only_full_group_by

Solution: Remove ONLY_FULL_GROUP_BY from the instance parameters

This disables the strict check globally for the instance.

1. Log on to the ApsaraDB RDS console and click your instance ID.

2. In the left-side navigation pane, click Parameters.

3. Find sql_mode, remove ONLY_FULL_GROUP_BY from its value, and save the change.

For detailed steps, see Modify the parameters of an ApsaraDB RDS for MySQL instance.

Cause 2: A client driver modifies sql_mode for the session

Some client drivers automatically modify sql_mode for the current session before running certain operations. The ADO.NET driver, for example, changes the value of the sql_mode parameter before executing an INSERT statement that needs to return an auto-increment column value, and does not restore sql_mode after the INSERT completes.

If the same session then runs a SELECT ... GROUP BY query, it fails with error 1055.

Solution: Add the following statement in your application code after the INSERT and before the subsequent query to reset sql_mode to the default:

SET session sql_mode = default;