About

How to disable only_full_group_by from server sql-mode Permanently

Lets Try

Sep 28, 2023. 1 min read

Solution 1: Remove ONLY_FULL_GROUP_BY from phpmyadmin Temporary

Through phpmyadmin It's very easy to Disable ONLY_FULL_GROUP_BY but there is only 1 issue with this method that is if the server is restarted than you need to disable it again.

Open phpmyadmin & select localhost
Click on menu Variables & scroll down for sql mode
Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save.

Solution 2: Remove `ONLY_FULL_GROUP_BY` from `/etc/mysql/my.cnf` Permanently

To disable the ONLY_FULL_GROUP_BY mode from the SQL mode of your MySQL server, you will need to modify the server configuration file and then restart the MySQL server. Here are the steps to do so:

Edit the MySQL Configuration File:

Open the MySQL configuration file in a text editor. The location of this file can vary depending on your operating system and MySQL installation. Common locations include:

Linux (Ubuntu) command: nano /etc/mysql/my.cnf

Modify the SQL Mode:

Paste [mysqld] section in the configuration file, and within that section, place the sql_mode option. It may look something like this:

[mysqld]
# ... other stuff will probably be here
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
group_concat_max_len=4000000

Save the Configuration File:

Save your changes and close the configuration file.

Restart MySQL Server:

After making the changes, you need to restart the MySQL server to apply the new configuration. Use the following command to restart MySQL on Linux:

sudo systemctl restart mysql

Other Notes:

what is `group_concat_max_len` ?

group_concat_max_len is a MySQL system variable that defines the maximum length (in bytes) of the result produced by the GROUP_CONCAT function. The GROUP_CONCAT function is used to concatenate values from multiple rows into a single string, with the values separated by a specified delimiter.

the maximum value for group_concat_max_len in MySQL is 1,073,741,823 (2^30 - 1). This is the maximum value for a 32-bit signed integer, and MySQL uses a 32-bit signed integer for this variable.

However, please note that MySQL's behavior and maximum values may change in newer versions of the database system. I recommend checking the MySQL documentation or the documentation specific to your MySQL version to verify the maximum value for group_concat_max_len if you are using a more recent version of MySQL. You can find the official MySQL documentation here: MySQL Documentation.