How do I disable ONLY_FULL_GROUP_BY in MySQL?

To disable ONLY_FULL_GROUP_BY in MySQL, remove ONLY_FULL_GROUP_BY from the sql-mode setting in the MySQL configuration file, /etc/my.cnf. This file can only be modified via SSH as the root user or WHM >> TerminalONLY_FULL_GROUP_BY cannot be disabled for a single account or database.

Follow these steps to disable ONLY_FULL_GROUP_BY:

  1. Log in to 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 (Do not copy the output shown below as it may contain options that are incompatible with your version of MySQL--for example, NO_AUTO_CREATE_USER is not supported by MySQL 8):

    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: vivimnano, etc).
    vi /etc/my.cnf
    vim /etc/my.cnf
    nano /etc/my.cnf
  4. Add sql-mode= to the bottom of the [mysqld] section, followed by the output you copied in step 2, and remove ONLY_FULL_GROUP_BY.

    Example (Do not copy the sql-mode line shown below as it may contain options that are incompatible with your version of MySQL--for example, NO_AUTO_CREATE_USER is not supported by MySQL 8):

    sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  5. Save and close the file.
  6. Restart MySQL to load the change by running the following command:
    /scripts/restartsrv_mysql
  7. Run the following command to confirm ONLY_FULL_GROUP_BY was removed from the enabled sql_mode options:
    mysql -sse "SELECT @@GLOBAL.sql_mode;"
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

HowTo: Restore MySQL Database From the Command Line

HowTo: Restore MySQL Database From the Command Line   Do not use phpMyAdmin to...

How to configure MySQL / MariaDB to listen publicly so that remote connections will work

Introduction   In order to connect to MySQL or MariaDB from a computer outside of the server,...