mysql5.7.18 ERROR 2027 (HY000): Malformed packet问题总括

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/wukong_666/article/details/72156143

ERROR 2027 (HY000): Malformed packet问题 

概要

  • 数据库版本:5.7.18
  • 远程登录mysql,select * from table报ERROR 2027。
  • 数据库IP地址:192.168.1.21。

操作过程

1、本地登录

  • 本地登录mysql:
  1. mysql -uroot -p -S /usr/local/mysql5711/mysql.sock
  • 查看mysql版本:
  1. mysql> select version();
  2. +------------+
  3. | version() |
  4. +------------+
  5. | 5.7.18-log |
  6. +------------+
  7. 1 row in set (0.00 sec)
  • 本地查询:
  1. mysql> select * from sanguoyanyi;
  2. +------+--------+
  3. | id | people |
  4. +------+--------+
  5. | 1 | guojia |
  6. +------+--------+
  7. 1 row in set (0.00 sec)

2、远程登录

  • 远程登录并查询:
  1. mysql -uroot -p -h192.168.1.21
  2. root@192.168.1.21 : sbtest 09:37:38> select * from sanguoyanyi;
  3. ERROR 2027 (HY000): Malformed packet
  • select * 会报错,但是指定所有字段正常查询:
  1. root@192.168.1.21 : sbtest 09:48:46> select id,people from sanguoyanyi;
  2. +------+--------+
  3. | id | people |
  4. +------+--------+
  5. | 1 | guojia |
  6. +------+--------+
  7. 1 row in set (0.00 sec)
  8. root@192.168.1.21 : sbtest 09:48:56> desc sanguoyanyi;
  9. +--------+-------------+------+-----+---------+-------+
  10. | Field | Type | Null | Key | Default | Extra |
  11. +--------+-------------+------+-----+---------+-------+
  12. | id | int(11) | YES | | NULL | |
  13. | people | varchar(13) | YES | | NULL | |
  14. +--------+-------------+------+-----+---------+-------+
  15. 2 rows in set (0.00 sec)
  • 查看query_cache设置情况:
  1. root@192.168.1.21 : sbtest 09:38:11> show variables like 'query_cache%';
  2. +------------------------------+----------+
  3. | Variable_name | Value |
  4. +------------------------------+----------+
  5. | query_cache_limit | 2097152 |
  6. | query_cache_min_res_unit | 4096 |
  7. | query_cache_size | 67108864 |
  8. | query_cache_type | ON |
  9. | query_cache_wlock_invalidate | OFF |
  10. +------------------------------+----------+
  11. 5 rows in set (0.01 sec)
  • 关闭query_cache:
  1. root@192.168.1.21 : sbtest 09:49:05> set global query_cache_size=0;
  2. Query OK, 0 rows affected (0.00 sec)
  3. root@192.168.1.21 : sbtest 09:58:37> set global query_cache_type=0;
  4. Query OK, 0 rows affected (0.00 sec)
  5. root@192.168.1.21 : sbtest 09:58:53> show variables like 'query_cache%%';
  6. +------------------------------+---------+
  7. | Variable_name | Value |
  8. +------------------------------+---------+
  9. | query_cache_limit | 2097152 |
  10. | query_cache_min_res_unit | 4096 |
  11. | query_cache_size | 0 |
  12. | query_cache_type | ON |
  13. | query_cache_wlock_invalidate | OFF |
  14. +------------------------------+---------+
  15. 5 rows in set (0.00 sec)
  • 关闭qc之后再次查询,发现此时查询结果正常
  1. root@192.168.1.21 : sbtest 09:58:58> select * from sanguoyanyi;
  2. +------+--------+
  3. | id | people |
  4. +------+--------+
  5. | 1 | guojia |
  6. +------+--------+
  7. 1 row in set (0.00 sec)

3、在mysql配置文件my.cnf将query_cache关闭

  • 在配置文件里配置:
  1. query_cache_size = 0
  2. query_cache_type = 0
  • 然后重新启动mysql,再进行远程登录mysql,然后使用select * 查询,发现没有报错。

4、关于read_ rnd_ buffer_ size参数

  • 网上说造成2027的原因是read_ rnd_ buffer_ size设置的太小,导致在初始化cache的时候,发现“只能存放小于等于2个记录”,但实际上此值为16M,足够大。
  1. mysql> show variables like 'read_rnd_buffer_size';
  2. +----------------------+----------+
  3. | Variable_name | Value |
  4. +----------------------+----------+
  5. | read_rnd_buffer_size | 16777216 |
  6. +----------------------+----------+
  7. 1 row in set (0.00 sec)
  • 为了排除这个参数的影响,尝试将此值改小
  1. root@192.168.1.21 : sbtest 10:03:25> set global read_rnd_buffer_size=64000;
  2. Query OK, 0 rows affected (0.00 sec)
  3. root@192.168.1.21 : sbtest 10:03:55> show variables like 'read_%';
  4. +----------------------+----------+
  5. | Variable_name | Value |
  6. +----------------------+----------+
  7. | read_buffer_size | 16777216 |
  8. | read_only | OFF |
  9. | read_rnd_buffer_size | 64000 |
  10. +----------------------+----------+
  11. 3 rows in set (0.01 sec)
  • 改小read_ rnd_ buffer_ size 值为64k左右之后再做查询,还是查询成功:
  1. root@192.168.1.21 : sbtest 10:03:59> select * from hongloumeng;
  2. +------+------------+
  3. | id | role |
  4. +------+------------+
  5. | 1 | jiabaoyu |
  6. | 2 | lindaiyu |
  7. | 3 | xuebaochai |
  8. | 4 | wangxifeng |
  9. +------+------------+
  10. 4 rows in set (0.00 sec)
  • 查看关于read_ rnd_ buffer_ size参数定义之后,它针对的存在sort的语句,所以在这个case里,应该和这个参数无关。
    • This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization.When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. See Section 8.2.1.15, “ORDER BY Optimization”. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer Server System Variables allocated for each client, so you should not set the global variable to a large value. Instead, change the 
      session variable only from within those clients that need to run large queries. 
      The maximum permissible setting for read_rnd_buffer_size is 2GB.

5、参考资料

http://weibo.com/p/1001603841620565999455?from=page_100505_profile&wvr=6&mod=wenzhangmod 
https://dba.stackexchange.com/questions/102567/sql-error-2027-malformed-packet 
https://www.percona.com/blog/2007/07/24/what-exactly-is-read_rnd_buffer_size/ 
https://www.percona.com/blog/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/

mysql 5.6.36 同样操作

  • mysql 5.6.56版本进行相同操作,并没有出现上述问题。

后续

  • 认为可能这会是一个bug,所以做了一下提交: 
    http://bugs.mysql.com/86318
  • 经过2017.05.23试验,发现应该是和客户端本身的版本有关。上述试验server端是5.7.18,但client端是5.6版本,猜测应该是版本不同,通信协议可能存在差异。今天client改用5.7.18版本的进行试验,发现并没有出现ERROR 2027 Malformed packet的报错;同时用5.6的client去连接server,发现有ERROR 2027的报错。
展开阅读全文

没有更多推荐了,返回首页