How can I enable/disable only_full_group_by in MySQL?

The only_full_group_by in MySQL is the new default mode, which changed in version 5.7.5. 


 Problem Details 

If you have group by function in your sql request you may have encountered this painful error.


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


Disable ONLY_FULL_GROUP_BY in mysql will be the solution of this problem. 

  

 Before disable, you can check sql_mode by executing following command 

 SELECT @@GLOBAL.sql_mode;

 

 Output will like: 

 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

 Step – 1: Open my.cnf file which you will find /etc/mysql/my.cnf  this file can opened by nano or vim

vi /etc/mysql/my.cnf  

 

Step – 2: add sql_mode all parameter except "ONLY_FULL_GROUP_BY" under mysqld as following  

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

Step 3: save and close 

Step 5: restart mysql 

systemctl restart mysqld


Please check again sql_mode by executing following command 

 SELECT @@GLOBAL.sql_mode;

 

 you will see now ONLY_FULL_GROUP_BY will disable. 

  

 If want to enable again, you need add "ONLY_FULL_GROUP_BY" in /etc/mysql/my.cnf file and restart service.