How to Disable ONLY_FULL_GROUP_BY in MySQL Using SSH

MySQL’s ONLY_FULL_GROUP_BY mode is a SQL mode that, when enabled, requires all columns in the SELECT clause to be either aggregated or included in the GROUP BY clause. While this mode helps enforce strict SQL standards and prevents potentially ambiguous queries, it can sometimes interfere with certain queries, especially those written for older versions of MySQL or for applications that rely on specific behaviors. Disabling ONLY_FULL_GROUP_BY might be necessary in such cases.

Disabling ONLY_FULL_GROUP_BY in MySQL involves modifying the server’s SQL mode configuration. This can be done using various methods, including through the MySQL command-line client or by directly editing MySQL’s configuration files. However, for users accessing MySQL databases on remote servers, one common method is through SSH (Secure Shell) access.

Connecting to MySQL via SSH

Before proceeding with disabling ONLY_FULL_GROUP_BY, ensure that you have SSH access to the server hosting your MySQL database. SSH allows secure access to the server’s command line, where you can execute commands and make configuration changes.

To connect to your server via SSH, use a terminal application such as Terminal on macOS/Linux or PuTTY on Windows. The SSH command typically follows this format:

ssh username@server_address

Replace username with your server username and server_address with the IP address or domain name of your server. You will be prompted to enter your password to authenticate.

Once connected, you can proceed with modifying MySQL’s configuration to disable ONLY_FULL_GROUP_BY.

Modifying MySQL Configuration

To disable ONLY_FULL_GROUP_BY, you need to modify MySQL’s configuration file. The location of this file may vary depending on your server setup, but it’s commonly found at /etc/mysql/my.cnf or /etc/my.cnf.

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 >> Terminal. ONLY_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

   sql_mode=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, 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

   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;"

Restarting MySQL Service

After modifying the MySQL configuration, you need to restart the MySQL service for the changes to take effect. Execute the following command in your SSH session:

/scripts/restartsrv_mysql

This command restarts the MySQL service, applying the updated configuration.

Verifying Changes

To verify that ONLY_FULL_GROUP_BY has been successfully disabled, you can log in to the MySQL command-line client from your SSH session:

mysql -u username -p

Replace username with your MySQL username, and you’ll be prompted to enter your password.

Once logged in, execute the following SQL command to check the current SQL mode:

SELECT @@sql_mode;

The output should display the SQL mode without ONLY_FULL_GROUP_BY included.

Conclusion

Disabling ONLY_FULL_GROUP_BY in MySQL using SSH involves modifying the server’s configuration file and restarting the MySQL service to apply the changes. By following the steps outlined above, you can effectively manage SQL modes and tailor MySQL’s behavior to suit your application’s requirements. However, exercise caution when modifying MySQL configurations, and always ensure that changes are thoroughly tested in a non-production environment before applying them to production systems.

Check out our cheap and reliable web hosting