1. 在用cmd命令行启动或停止mysql数据库时,一定是要在 以管理员身份运行 的情况下才能正常启动或停止,否则会报 错误5 的错
2. [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
情况描述:在使用Navicat时,执行查询语句 select * from table时 报此错误
步骤:
1.先查看sql_mode
select @@global.sql_mode;
查询结果
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
解决办法:
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
上面这句语句 重新设置了全局sql_mode,只对新建数据库有效。对已经存在的数据库,需要执行下面语句:
set sql_mode
='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
最有效的解决办法:
修改my.cnf(windows下是my.ini)配置文件,删掉only_full_group_by这一项
Linux ubuntu中并没有sql_mode这一配置项,所以需要添加该项
sql_mode
=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;
注意:[mysqld] 没有被注释,重启mysql服务
如果只想修改一个会话中的配置项而不影响其它会话,使用命令
set sql_mode
=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;