MySQL报错

Scroll Down

【1】1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated..
在centos7中刚装的mysql,在执行下面的ql的时候,即统计出表中的所有日期的语句的时候出现了这个错误。

select DATE_FORMAT(logintime,"%Y-%m-%d") from exam_login_log group by 
DATE_FORMAT(logintime,"%Y-%m-%d")  order by logintime desc

解决:
my.cnf配置文件的[mysqld]下添加如下内容,之后重启mysql即可。

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

show variables like "sql_mode";可以查看当前的sql_model.

【2】You can change this value on the server by setting the max_allowed_packet' variable...
错误

You can change this value on the server by setting the max_allowed_packet' variable. 

解决:
my.cnf配置文件的[mysqld]下添加如下内容,之后重启mysql即可。

max_allowed_packet = 20M

【3】MySQL异常:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决::在my.ini中加入如下代码:

[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