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

【现象描述】

研发告知通过程序从一台 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)

免责声明:务必仔细阅读

  • 本站为个人博客,博客所转载的一切破解、path、补丁、注册机和注册信息及软件等资源文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。

  • 本站为非盈利性站点,打赏作为用户喜欢本站捐赠打赏功能,本站不贩卖软件等资源,所有内容不作为商业行为。

  • 本博客的文章中涉及的任何解锁和解密分析脚本,仅用于测试和学习研究,禁止用于商业用途,不能保证其合法性,准确性,完整性和有效性,请根据情况自行判断.

  • 本博客的任何内容,未经许可禁止任何公众号、自媒体进行任何形式的转载、发布。

  • 博客对任何脚本资源教程问题概不负责,包括但不限于由任何脚本资源教程错误导致的任何损失或损害.

  • 间接使用相关资源或者参照文章的任何用户,包括但不限于建立VPS或在某些行为违反国家/地区法律或相关法规的情况下进行传播, 博客对于由此引起的任何隐私泄漏或其他后果概不负责.

  • 请勿将博客的任何内容用于商业或非法目的,否则后果自负.

  • 如果任何单位或个人认为该博客的任何内容可能涉嫌侵犯其权利,则应及时通知并提供身份证明,所有权证明至admin@proyy.com.我们将在收到认证文件后删除相关内容.

  • 任何以任何方式查看此博客的任何内容的人或直接或间接使用该博客的任何内容的使用者都应仔细阅读此声明。博客保留随时更改或补充此免责声明的权利。一旦使用并复制了博客的任何内容,则视为您已接受此免责声明.

您必须在下载后的24小时内从计算机或手机中完全删除以上内容.

您使用或者复制了本博客的任何内容,则视为已接受此声明,请仔细阅读


更多福利请关注一一网络微信公众号或者小程序

一一网络微信公众号
打个小广告,宝塔服务器面板,我用的也是,很方便,重点是免费的也能用,没钱太难了,穷鬼一个,一键全能部署及管理,送你3188元礼包,点我领取https://www.bt.cn/?invite_code=MV9kY3ZwbXo=


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

发表评论

发表评论

一一网络-提供最优质的文章集合

立即查看 了解详情