As a brief note to self, I ran into this MySQL error when running a SQL
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:
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:
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.