Mysqldump Error: ONLY_FULL_GROUP_BY

Question

I use mysqldump and get error. In my config files writed property for sql_mode and in mysql console global and session property similary.

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

Error:

mysqldump -uroot -p  --compatible=ansi --skip-extended-insert --compact Terminal > dump.sql

mysqldump: Error: 'Expression #6 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 'information_schema.FILES.EXTRA' 
which is not functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by' when trying to dump tablespaces

if i use this property in my.cnf for [mysqldump] i get error...

mysqldump: [ERROR] unknown variable 'sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

How do i fix that?

Answer 1

I have this error in mysqldump with the parameter mysqldump --compatible=ansi ...

It works without this parameter.