mysql: remove only_full_group_by and make it persistent

update it from the console (will revert upon reboot)

from the command line go to the mysql console

mysql

then edit the sql_mode entry

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

SELECT @@sql_mode;

SELECT @@GLOBAL.sql_mode;

 

 

for a persistent change, in mysql 8 (ubuntu 20.04)

add

sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

to

/etc/mysql/mysql.conf.d/mysqld.cnf

 

the default code is

sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

we'll just remove the only_full_group_by and restart mysql (service mysql restart)

sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

 

Leave a Reply

Your email address will not be published. Required fields are marked *