mysql8.0.22 设置sql_mode后不能启动解决

直接上答案  /etc/my.cnf 的配置全文

[mysqld]
sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

原来的错误配置

sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'

 

MYSQL8以上已经取消了NO_AUTO_CREATE_USER

ONLY_FULL_GROUP_BY 要求sql中select后面的字段必须出现在group by后面，或者被聚合函数包裹，不然会抛出下面的错误

this is incompatible with sql_mode=only_full_group_by