Lost connection to Mysql server during query问题解决

一. 复现出现错误的操作：

业务要求创建视图并提供拥有只能查看视图权限的用户。经过操作创建用户和赋权的操作，本以为完事，用新用户连接Navicat发现报出如下问题:

其中创建用户和赋权的SQL语句如下：

create USER 'XXXX'@'%' IDENTIFIED  BY 'XXXX';

grant select on xx.xxxx to 'XXXX'@'%';

show grants for 'XXXX'@'%';查看新用户的权限信息：

 

跑到数据库服务器上通过命令行登录新用户，发现select语句一切正常。测试环境数据版本为8.0.19。

 二.正常的操作：

在开发环境进行上述操作，发现一切正常。开发环境数据库版本为8.0.23。

三.解决问题：

经过尝试，问题出在创建用户时是否指定加密插件。其中开发环境8.0.23版本数据库经尝试，无论是否指定加密插件都视图均可正常访问。

测试环境8.0.19版本数据库，创建用户时需要指定加密插件为MYSQL_NATIVE_PASSWORD。操作SQL如下：

CREATE USER 'xxxx'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'xxxx';

grant select on xxxx.xxxxto 'xxxx'@'%';

在不指定加密插件的情况下，权限表user中对应用户的加密插件字段plugin为caching_sha2_password。这个不同导致本次错误产生。

四. 总结：

mysql8.0新特性之一，默认使用caching_sha2_password作为身份验证插件。具体为什么会引发看似并不相关的问题，待有时间查阅资料后会记录出来。