MySQL Error in 5.7+: only_full_group_by

Generally found in: v5.7+
Version on my machine: 5.7.14
Key words: only_full_group_by, 
Documentation: 6.1.7 Server SQL Modes


The issue is generally observed in 5.7+. If your team/ you previously worked with an older version, then it’s advisable to stay consistent with the stack. However, in specific cases where you can’t downgrade the version, this solution may work.

Disable FULL_GROUP_BY Mode temporarily:

You may need to execute this every time you do a fresh war deployment.

# execute the following commands

Change MySQL Conf to permanently change SQL mode in MySQL:

First we need to identify the location of the configuration file.

Note: If you’re on Mac, the binary mysql installer (not homebrew) doesn’t create a my.cnf file. You can find sample conf files in the support-files directory located under /usr/local/mysql/support-files.

You can find the cnf file by:

$ which mysqld
# let's say it gives you /usr/sbin/mysqld
$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
# It'll give the following output
# Default options are read from the following files in the given order: 
# /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

# The cnf file doesn't appear in the default non-home-brew installation
# The sample file can be found under the following directory

$ which mysqld
# let's say it gives you /usr/sbin/mysqld
# cd .. to 2 levels back to the mysql directory
# Try to locate the 'support-files' directory

$ cat /usr/sbin/support-files/my-default.cnf
# This contains a sample cnf file
# copy the contents and create one file under the user directory

$ touch ~/my.cnf

Make the following changes to the my.cnf file:



Although not required, restart your MySQL server as a precautionary measure.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s