How to solve MySQL Syntax error or access violation: 1055 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

A couple of weeks ago working in a legacy project that needed to be upgraded and moved to a new server got me in the next exception in a couple of modules of the application:

According to the MySQL documentation, having the only full group by mode in the sql mode will reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

In some cases, as the standard query won't work with a default configuration in the mysql client, you may like as a good practice to rewrite it in a way that the exception doesn't appear anymore, however due to time and costs of development, you will need a faster and not so expensive solution. The point of this solution is that the query worked in an older version of MySQL with different settings, so you may run the same queries by simply changing the sql mode of your MySQL client and we'll show you how to do it in this short article.

1. Locate MySQL my.cnf file

The first thing that you need to do is to find the configuration file of MySQL. For example with many distribution as Plesk, you can find the file at /etc/mysql/my.cnf or at /etc/my.cfn, however this may vary, so you will be able to find it using a linux command like:

find / -name my.cnf

This will output the path to the files with such name. Once you find the file, you will be able to change the setting that will remove the exception from appearing.

2. Modify sql mode

Inside the [mysqld] setting block you need to update the value of the sql_mode property to an empty string which will remove the 'only_full_group_by' mode:

# Inside the mysqld block
[mysqld]
# add new option
sql_mode = ""

Edit the my.cnf file using either nano, vi, emacs or via SFTP and save the changes. For example, the my.cnf file would end up like this with our new setting:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = ::ffff:127.0.0.1
local-infile=0

# Important: remove limitation of group by with the following line
sql_mode = ""

Save changes in the file and restart mysql with the cli depending of your os and installation process e.g:

# Ubuntu
sudo service mysql restart

# CentOS
/etc/init.d/mysqld start

Now the queries with the issue should be able to run now and the exception won't appear anymore.

Happy coding !