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