MySQL error: Expression of SELECT list is not in GROUP BY clause, nonaggregated column

As a brief note to self, I ran into this MySQL error when running a SQL SELECT and GROUP BY query:

02:14:33.911 [main] ERROR scalikejdbc.StatementExecutor$$anon$1 - SQL execution failed
(Reason: Expression #3 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'aad8final.u.alias' which is not functionally dependent on columns
in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by):

select tn.nid, count(tn.nid) as countOfTnNid, u.alias, n.title 
from taxonomy_index tn, url_alias u, node_field_data n 
where tn.tid in (select tid from taxonomy_index where nid = 9423) 
and tn.nid = u.nid 
and tn.nid = n.nid 
and n.status = 1 
and n.nid != 9423 
group by tn.nid 
order by countOfTnNid 
desc limit 5

As that error message shows, the problem is that the SQL SELECT query is incompatible with this MySQL setting:

sql_mode=only_full_group_by

I read about a number of possible solutions on Stack Overflow and on the MySQL website. The MySQL site in particular is helpful with these comments:

“this query is illegal in standard SQL-92 because the nonaggregated name column in the select list does not appear in the GROUP BY ... For the query to be legal in SQL-92, the name column must be omitted from the select list or named in the GROUP BY clause.”

So the problem seems to be that MySQL is compatible with SQL-92 by default (and not SQL:1999, which they mention after this). (Or you could say that the problem is with my query.) As a result of reading different possible solutions, I added this line to the /etc/mysql/my.cnf file:

[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

After that I restarted MySQL:

sudo service mysql restart

One potential point of confusion is that there are a number of MySQL configuration files under the /etc/mysql directory, as shown by this directory listing:

drwxr-xr-x  4 root root 4096 May 18 03:39 .
drwxr-xr-x 99 root root 4096 May 16 06:50 ..
drwxr-xr-x  2 root root 4096 May  6 06:02 conf.d
-rw-------  1 root root  317 May  6 06:03 debian.cnf
-rwxr-xr-x  1 root root  120 Jan 22  2019 debian-start
lrwxrwxrwx  1 root root   24 Jun 12  2016 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r--  1 root root  839 Apr 20  2016 my.cnf.fallback
-rw-r--r--  1 root root 1105 May 18 03:39 mysql.cnf
drwxr-xr-x  2 root root 4096 May 18 03:38 mysql.conf.d

To get through today’s emergency I added that sql_mode line to the end of the mysql.cnf file, but I encourage you to look at all of the files under that directory. For instance, the mysql.conf.d/mysqld.cnf seems to have most of the actual configuration information in it.

In summary, if you run into the MySQL “SQL execution failed (Reason: Expression .. of SELECT list is not in GROUP BY clause and contains nonaggregated column .. which is not functionally dependent on columns in GROUP BY clause” error, I hope this solution is helpful.