How to disable ONLY_FULL_GROUP_BY in MySQL

Introduction

This article provides the procedure to disable ONLY_FULL_GROUP_BY in MySQL.

Please note that ONLY_FULL_GROUP_BY cannot be disabled for a single account or database.

Procedure

1. Access the server's command line as the 'root' user via SSH or "Terminal" in WHM.
2. Run the following command to determine which sql_mode options are enabled.

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

3. Copy the output from the above command.
4. Open the /etc/my.cnf file in your preferred text editor.
5. Add sql-mode= to the bottom of the [mysqld] section.
6. Paste the copied output from above as the sql-mode value.
7. Remove ONLY_FULL_GROUP_BY from the sql-mode value.
8. Save the changes and exit the text editor.
9. Restart MySQL.

   /scripts/restartsrv_mysql

10. Run the following command to confirm that ONLY_FULL_GROUP_BY was removed from the enabled sql_mode options.

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

Additional resources

How to edit a file on command-line

How to change the SQL mode in MySQL or MariaDB.