【工作】解决程序远程连接mysql 5.7.17 执行select查询报错”Got an error writing communication packets”

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

# cat mysql-error.log

发现了程序连接时候，数据库会报如下错误：

[Note] Aborted connection 16 to db: 'pigeon_commerce' user: 'pigeon' host: '******' (Got an error writing communication packets)

通过网上查找信息，引发此警告日志的原因大概有以下几个方面：

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服务器，查看用户的相关信息如下：

通过上图可以发现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;

3、接下来在A服务器上面远程登录B服务器，发现顺利登录，如下：

然后让研发重跑程序，发现连接不再报错，但是程序还是无法正常运行，程序内执行select语句还是会报错。

其实细看最上面的研发发来的报错信息中，有这样一行“_mysql_exceptions.OperationalError: (2027, ‘Malformed packet’)”

解决办法：

其实就是关闭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）