Disable ONLY_FULL_GROUP_BY

sachin

Getting error group by clause and contains nonaggregated column. How can I overcome it permanently?

sachin

Method 1: Temporary and will be reset back after restart of mysql service.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Method 2: Permament
Get Items from query

SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','') copy_me;
+----------------------------------------------------------------------------------------------------+
| copy_me                                                                                            |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Add this to the end of the file vi /etc/my.cnf

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Restart mysql service by sudo service mysqld restart.
This will disable ONLY_FULL_GROUP_BY for ALL users