How do I disable ONLY_FULL_GROUP_BY in MySQL?

Question

How do I disable ONLY_FULL_GROUP_BY in MySQL?

Answer

To disable ONLY_FULL_GROUP_BY in MySQL, you will need to remove ONLY_FULL_GROUP_BY from the sql_mode setting by using the MySQL configuration file /etc/my.cnf. This file can only be modified via SSH as the root user or WHM >> Terminal. ONLY_FULL_GROUP_BY can not be disabled for a single account or database.

You may follow these steps to disable ONLY_FULL_GROUP_BY:

1. Log into your server via SSH as the root user or navigate to WHM >> Terminal. 

2. Run the following command to determine which sql_mode options are enabled and copy the output:

mysql -sse "SELECT @@GLOBAL.sql_mode;"

Example output:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3. Open the /etc/my.cnf file using any command-line text editor you prefer (ex: vi, vim, nano, or other).

6. Add sql-mode= to the bottom of the [mysqld] section, followed by the output you copied in step 2. You may also remove ONLY_FULL_GROUP_BY at this time.

Example:

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 7. Save the file and restart MySQL to load the change by running the following script:

/scripts/restartsrv_mysql

8. Run the following command to confirm ONLY_FULL_GROUP_BY was removed from the enabled sql_mode options:

mysql -sse "SELECT @@GLOBAL.sql_mode; 

You may also find our article on How to change the SQL mode in MySQL or MariaDB helpful for more information.