Disabling ONLY_FULL_GROUP_BY In Ubuntu

I’m run into some error message like:  “Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column”.  It’s turns out that this is caused by MySQL updating the way it behave in relation to aggregate functions since MySQL 5.7.  Here is how I solved it on Ubuntu .

You can find out what mode your MySQL server is running:

SELECT @@GLOBAL.sql_mode;

My Ubuntu 19.10 got this:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Then take this and delete “ONLY_FULL_GROUP_BY”.  You can then add this to MySQL configuration file.    On my Ubuntu there is a file located at /etc/mysql/my.cnf and inside here is specifies two folders where you could put the configuration:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

I have edited /etc/mysql/conf.d/mysql.cnf and put in

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