The only_full_group_by in MySQL is the new default mode, which changed in version 5.7.5.
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
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.