Turn Off SQL_MODE - ONLY_FULL_GROUP_BY

Turn Off SQL_MODE - ONLY_FULL_GROUP_BY

Turn Off SQL_MODE - ONLY_FULL_GROUP_BY

SQL standards says that GROUP BY clause should have table columns, not expression. But MYSQL always allows expression in GROUP BY clause. “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘db.table.col’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by” is an error which indicates that selected expressions always differs from grouping.

Disabling ONLY_FULL_GROUP_BY Function With Command

MYSQL need to examine the parse of the given expression and find the grouping expression in the same tree. This error may occur when only_full_group_by function is enabled. To solve this error you have to disable the function. Following command should be executed to disable the function.

Code:
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
In the MYSQL query, select part columns should be in the GROUP_BY portion. You can also disable all modes of sql by setting the value of sql mode to empty string.

Turn Off only_full_group_by using Config File

You can also turn off only_full_group_by function by editing config file my.conf. Usually you can see the my.cnf file in /etc/my.cnf or /etc/mysql/my.conf. Inside the file the heading will be [mysql] and value will be sql_mode. You can execute this command if you are not sure about the file.

Code:
$ mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
First of all you have to check the sql_mode by executing the below query,

Code:
SELECT @@sql_mode;
SQL Mode are the global configuration for SQL commands. Strict mode is used to control the missing and invalid values in the data-change MYSQL statements. The result will be like the below statement

Code:
mysql> show variables like 'sql_mode'\G
OUTPUT:

Code:
Variable_name: sql_mode
Value:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
MySQL server is operated in various modes, depending on the sql_mode variable these modes can be used for various clients. ONLY_FULL_GROUP_BY is the default mode in the MySQL server. These sql_modes are the global configuration of each sql command.

You can copy the values of sql_mode, delete ONLY_FULL_GROUPBY, update my.cnf and combine in a statement under [mysqld] section as given below

[mysqld]

Code:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
The above statement is used to disable the file permanently.

Restarting mysql

After doing this you can restart mysql server with this command.

Code:
$ mysql service restart
If you want to disable the file at runtime use the below expression.

Code:
SET @@GLOBAL.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES are the sql_mode settings that are enabled recently. After giving the expression reconnect the file.
Author
Bhawani Singh
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from Bhawani Singh

Top