ONLY_FULL_GROUP_BY SQL mode is configured error

Get ready to learn how to deal with ONLY_FULL_GROUP_BY SQL mode is configured error. Our Plesk Support team is here to lend a hand with your queries and issues.

ONLY_FULL_GROUP_BY SQL mode is configured error

Is your MySQL query failing when the ONLY_FULL_GROUP_BY SQL mode is configured? Worry no more! Our experts are here with a solution.

In this specific situation, the MySQL query in phpMyAdmin often terminates with a similar message:

#1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘exampl_db.column’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Furthermore, a database SELECT query with umlaut symbols stops working within a PHP script with mysqli function.

Some of my customers also noticed that the mass password reset script tends to fail with this message:

[FATAL_ERROR] [MYSQL ERROR] Unable to execute query. Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘psa.domains.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

According to our experts, the MySQL query fails due to the ONLY_FULL_GROUP_BY SQL mode and the executed query being incompatible.

Solution

The only way to resolve this issue is to disable the  ONLY_FULL_GROUP_BY SQL mode. Let’s take a look at how we can do this:

1. First, we have to connect to the Plesk server through SSH.
2. Next, we have to open my.cnf configuration file and remove the ONLY_FULL_GROUP_BY from the sql-mode variable.
3. Then, save and exit the file.
4. Finally, restart MySQL for the changes to take effect.

This quick fix will resolve the error caused when ONLY_FULL_GROUP_BY SQL mode is enabled.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

To sum up, our Support Techs demonstrated how to resolve the issues caused by the ONLY_FULL_GROUP_BY SQL mode is configured error.