mysql ERROR 2027 (HY000): Malformed packet

本地登录mysql：
[root@host-192-168-1-21 mysql]# mysql -u root -S /usr/local/mysql5711/mysql.sock -p
mysql> select * from hongloumeng;
+------+------------+
| id   | role       |
+------+------------+
|    1 | jiabaoyu   |
|    2 | lindaiyu   |
|    3 | xuebaochai |
|    4 | wangxifeng |
+------+------------+
4 rows in set (0.00 sec)


远程登录mysql：
mysql -u root -h 192.168.1.21 -p
root@192.168.1.21 : sbtest 02:24:55> select * from hongloumeng;
ERROR 2027 (HY000): Malformed packet
mysql> show variables like 'max_allowed%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)
root@192.168.1.21 : sbtest 02:38:18> select * from test;
ERROR 2027 (HY000): Malformed packet
root@192.168.1.21 : sbtest 02:38:28> select id from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
网上说造成2027的原因是read_rnd_buffer_size设置的太小，导致在初始化cache的时候，发现“只能存放小于等于2个记录”，但实际上此值为16M，足够大。
mysql> show variables like 'read_rnd_buffer_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| read_rnd_buffer_size | 16777216 |
+----------------------+----------+
1 row in set (0.00 sec)
查看query_cache：
root@192.168.1.21 : sbtest 02:40:30> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 2097152  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 67108864 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)
root@192.168.1.21 : sbtest 02:41:33> set global query_cache_type=0;
Query OK, 0 rows affected (0.00 sec)
root@192.168.1.21 : sbtest 02:44:26> set global query_cache_size=0;
Query OK, 0 rows affected (0.00 sec)
root@192.168.1.21 : sbtest 02:44:34> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 2097152 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.01 sec)
root@192.168.1.21 : sbtest 02:44:38> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | tim  |
|    2 | jack |
|    3 | lisa |
+------+------+
3 rows in set (0.00 sec)
发现关闭qc之后，查询成功。
后续操作，再开启qc：
root@192.168.1.21 : sbtest 02:45:11> set global query_cache_size=67108864;
Query OK, 0 rows affected (0.01 sec)
root@192.168.1.21 : sbtest 03:18:41> set global query_cache_type=1;
Query OK, 0 rows affected (0.00 sec)
root@192.168.1.21 : sbtest 03:18:50> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 2097152  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 67108864 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.01 sec)
root@192.168.1.21 : sbtest 03:24:31> select * from hongloumeng;
+------+------------+
| id   | role       |
+------+------------+
|    1 | jiabaoyu   |
|    2 | lindaiyu   |
|    3 | xuebaochai |
|    4 | wangxifeng |
+------+------------+
4 rows in set (0.00 sec)

重新开启qc之后，还是能查询成功。

参考资料：

http://weibo.com/p/1001603841620565999455?from=page_100505_profile&wvr=6&mod=wenzhangmod

https://dba.stackexchange.com/questions/102567/sql-error-2027-malformed-packet