How do I disable ONLY_FULL_GROUP_BY in MySQL?

If you’re encountering the “ONLY_FULL_GROUP_BY” error in MySQL and want to disable it, you can follow these easy steps to make the necessary changes. Please note that modifying the MySQL configuration file requires root access or access to WHM >> Terminal via SSH.

Here’s a step-by-step guide to disabling ONLY_FULL_GROUP_BY:

1. Log in to your server using SSH as the root user or navigate to WHM >> Terminal.
2. Execute the following command to determine the enabled sql_mode options and make a note of the output:

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

Take note of the output but do not copy the example output provided below, as it may contain options incompatible with your MySQL version. For instance, MySQL 8 does not support NO_AUTO_CREATE_USER.

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 MySQL configuration file, “/etc/my.cnf”, using your preferred command-line text editor (e.g., vi, vim, nano).

   vi /etc/my.cnf

4. Add the following line at the bottom of the [mysqld] section, incorporating the options you noted in step 2 and excluding ONLY_FULL_GROUP_BY:

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

Avoid copying the “sql-mode” line from the example above as it may contain incompatible options for your MySQL version.

5. Save and close the file.
6. Restart MySQL to apply the changes:

   /scripts/restartsrv_mysql

7. To confirm that ONLY_FULL_GROUP_BY has been successfully removed from the enabled sql_mode options, run the following command:

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

By following these steps, you should be able to disable ONLY_FULL_GROUP_BY in MySQL. For more detailed information, you can refer to the article on How to Change the SQL Mode in MySQL or MariaDB.