mysql8.0连接时出现Client does not support authtication protocol及Expression #1 of ORDER BY..in GROUP BY问题

一、mysql8.0连接时出现Client does not support authtication protocol..consider upgrading mysql client

    使用mysql8.0搭建起数据库之后，在本地连接mysql的时候遇到报错：Client does not support authtication protocol requested by server;consider upgrading mysql client ，我这里是用的navicat for mysql客户端。这是由于mysql8.0更新了加密规则，mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password。而navicat当前版本还不支持最新的加密规则。看到之前也有一篇文章涉及到这个问题：在docker环境下启动php的注意事项-docker完整挂载php目录、在Docker查看容器完整启动命令以及mysql ERROR 2059问题_docker php启动进程为什么是33 tap-CSDN博客 解决起来很简单的，通过以下3种方法处理就可以使用navicat连接MySQL8了。

1.如之前文章所说可以在mysql的启动文件中配置加密规则

[mysqld]
default_authentication_plugin=mysql_native_password

2.创建用户密码的时候指定加密规则，这个方便简单。

CREATE DATABASE `test`
create user '用户名'@'%' identified WITH mysql_native_password by '密码';
grant all privileges on 数据库名.* to '用户名'@'%' with grant option;
flush privileges;

3.对于已经创建的用户修改一下加密规则即可。

ALTER USER '用户名'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
FLUSH PRIVILEGES;

    另外记得之前在操作mysql8.0遇到不能直接使用grant privileges授权的同时创建用户，在8.0中这两步必须分开，即先创建用户然后再授权。常用的mysql相关配置如下(添加到mysql8中的/etc/my.cnf)：

[mysqld]
port=3309	#端口
max_connections=200  # 允许最大连接数
max_connect_errors=10 # 允许连接失败的次数
character-set-server=utf8  # 服务端默认字符集
default-storage-engine=INNODB  # 创建表时默认存储引擎
default_authentication_plugin=mysql_native_password # 默认使用加密规则
sql_mode = ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION‘

[mysql]
default-character-set=utf8	#mysql客户端默认字符集

[client]
port=3309	#设置mysql客户端默认端口
default-character-set=utf8

4. 重启mysql8即可

最后使用systemctl status 和systemctl restart查看和重启mysql8。

systemctl status mysqld.service
systemctl restart mysqld.service

二、Mysql8:Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

    在MYSQL8进行数据库操作的时候出现报错，具体错误：

[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。

        此错误和MYSQl的sql模式有关，一般的mysql中的@@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语句：select version(), @@sql_mode;查询看到。但在mysql8中其值已经默认添加ONLY_FULL_GROUP_BY项。得到的结果是：

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

        增加了sql_mode=only_full_group_by的数据库,在使用时就会报错,ONLY_FULL_GROUP_BY要求sql中select后面的字段必须出现在group by后面，或者被聚合函数包裹，不然会抛出上面的错误。

#修改sql_mode
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
#修改etc/my.cnf文件:
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

    理论上打开这个模式，也应该是在执行group by查询的时候如不合规格才会报错，但我在执行时却是在执行insert语句，却也在最后报这个错误，是不是有些诡异。