【现象描述】

研发告知通过程序从一台 A 服务器连接 B 服务器的数据库报错,无法正常连接,程序的报错信息如下:
20170714185738

而B服务器上面的数据库是5.7版本,由其他同事搭建,之前没有参与管理。正好这个同事近期不在公司,所以跟研发要过连接的数据库名、账号和密码后,登录服务器查看现象。

【解决过程】

一、首先想到的是,既然无法连接无法,那么数据库的错误日志中应该会有报错

# cat mysql-error.log
发现了程序连接时候,数据库会报如下错误:
[Note] Aborted connection 16 to db: 'pigeon_commerce' user: 'pigeon' host: '******' (Got an error writing communication packets)
通过网上查找信息,引发此警告日志的原因大概有以下几个方面:
1、网络连接超过了connect_timeout(默认10s)的时间
2、客户端连接一个没有授权的数据库
3、数据库密码错误
4、连接包信息错误
5、客户端没有执行MySQL_close()关闭、由于连接一直没有关闭导致时间超过wait_timeout or interactive_timeout这两个变量的值(这两个变量的超时时间是8小时)
6、max_allowed_packet参数设的过小
1)、最先查看的是系统的max_allowed_packet参数:
MySQL [(none)]> show variables like 'max_allowed_packet%';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 524288000 |
+--------------------+-----------+
1 row in set (0.01 sec)
可以看到大小为500M,不存在设置过小的问题。
2)、查看connect_timeout参数
MySQL [(none)]> show variables like 'connect_timeout%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 60    |
+-----------------+-------+
1 row in set (0.01 sec)
大小为60秒,应该也不是这个问题,后来尝试调大此参数,程序还是报错。
3)、关于客户端没有执行MySQL_close()关闭,此问题应该不存在,因为研发后来反应程序刚跑就报错。
4)、根据上面研发的反馈现象,我怀疑是不是账号认证出了问题?可是研发说之前他直接使用账号密码远程登录数据库是没有问题的。
于是我在A服务器上使用研发提供的账号密码远程登录B的数据库,发现如下报错:
# mysql -upigeon -p -h B服务器ip
Enter password:
ERROR 1524 (HY000): Plugin '*7F7569E3C6956DCDA47E7F4E0AC4E2F269DBBCB8' is not loaded
很显然引起并非研发所说的没有问题,程序的报错应该是因为远程无法接报错导致的。

二、那么现在的方向就是解决上面的报错问题

1.登录B服务器,查看用户的相关信息如下:
20170714194358
通过上图可以发现pigeon这个用户使用"*7F7569E3C6956DCDA47E7F4E0AC4E2F269DBBCB8"认证插件,而系统并没有加载这个认证插件,导致用户远程连接报错。
在mysql 5.7版本中不再支持mysql_old_password认证插件,mysql数据库的user表中要求plugin字段非空,且默认值是mysql_native_password(使用MySQL本地密码)认证插件
一开始我是将plugin字段更改为mysql_native_password,但是远程登录却提示“ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded”,看来还是需要加载插件才行。
后来,看到上面的用户后面plugin为空且可以正常登录,我想的是删掉已有的pigeon用户,重新创建相同账号密码权限的用户,但是发现不行,新创建的用户还是和上面一样,plugin字段会有"*7F7569E3C6956DCDA47E7F4E0AC4E2F269DBBCB8"内容。
2、接下来的思路就是想,可不可以使用命令把plugin插件的内容设置为空,这样就不会使用认证功能了,命令如下:
MySQL [(none)]> use mysql;
MySQL [(none)]> update user set authentication_string=PASSWORD("密码") where User='pigeon';
MySQL [(none)]> update user set plugin="" where User='pigeon';
MySQL [(none)]> flush privileges;
经常上面操作后发现,确实清除了认证,如下所示:
20170714195823
3、接下来在A服务器上面远程登录B服务器,发现顺利登录,如下:
20170714203023
然后让研发重跑程序,发现连接不再报错,但是程序还是无法正常运行,程序内执行select语句还是会报错。

三、到此,按照网上的说法并没有解决此报错问题,之后的解决过程就不叙述,直接上解决办法

其实细看最上面的研发发来的报错信息中,有这样一行“_mysql_exceptions.OperationalError: (2027, 'Malformed packet')”
根据此线索,查找信息发现了解决办法,参考网址:
http://blog.csdn.net/wukong_666/article/details/72156143
解决办法:
其实就是关闭MySQL的query cache功能
#修改如下两个参数为0
# vi /etc/my.cnf
query_cache_size = 0
query_cache_type = 0
# /etc/init.d/mysqld restart
参数说明:
query_cache_size:
分配给QC的内存。如果设为0,则相当于禁用QC。要注意QC必须使用大约40KB来存储它的结构,如果设定小于40KB,则相当于禁用QC。QC存储的最小单位是1024 byte,所以如果你设定了一个不是1024的倍数的值,这个值会被四舍五入到最接近当前值的等于1024的倍数的值。
query_cache_type:
设置为0 : 完全禁止QC,不受SQL语句控制(另外可能要注意的是,即使这里禁用,上面一个参数所设定的内存大小还是会被分配);
设置为1 :  启用QC,可以在SQL语句使用SQL_NO_CACHE禁用;
设置为2:  可以在SQL语句使用SQL_CACHE启用。

【题外话】

关于开启Query  Cache引起的问题和为什么要关闭 MySQL Query Cache,可以看下下面两篇文章,会有启发。
http://blog.csdn.net/liqfyiyi/article/details/50178591( 为什么要关闭 MySQL Query Cache?)
http://blog.csdn.net/liqfyiyi/article/details/50178565(线上环境到底要不要开启query cache)
http://blog.csdn.net/dba_waterbin/article/details/9201645(线上大量 Waiting on query cache mutex)
Last modification:November 7th, 2019 at 03:28 am
如果觉得我的文章对你有用,请随意赞赏