MySQL Bugs: #86318: mysql 5.7.18 ERROR 2027 (HY000): Malformed packet

Bug #86318	mysql 5.7.18 ERROR 2027 (HY000): Malformed packet
Submitted:	15 May 2017 6:33	Modified:	10 Sep 2018 10:51
Reporter:	Jason Han (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	malformed packet, mysql command client, query cache

Description:
I use mysql5.7.18 and find this error, but mysql5.6.36 works well.
local login:
[root@ip mysql]# mysql -u root -S /usr/local/mysql5711/mysql.sock -p
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.18-log |
+------------+
root@192.168.1.21 : sbtest 09:48:56> desc sanguoyanyi;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| people | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from sanguoyanyi;
+------+--------+
| id   | people |
+------+--------+
|    1 | guojia |
+------+--------+
1 row in set (0.00 sec)
And this query shows ok.

Remote Login:
mysql --uroot -p -hip(the ip same as above )
root@ip : sbtest 09:37:38> select * from sanguoyanyi;
ERROR 2027 (HY000): Malformed packet
But if I specify all of the fields, the result shows ok:
root@ip : sbtest 09:48:46> select id,people from sanguoyanyi;
+------+--------+
| id   | people |
+------+--------+
|    1 | guojia |
+------+--------+
1 row in set (0.00 sec)
root@ip : sbtest 09:48:56> desc sanguoyanyi;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| people | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Show the value of query_cache:
root@ip : sbtest 09:38:11> 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@ip : sbtest 09:49:05> set global query_cache_size=0;
Query OK, 0 rows affected (0.00 sec)
root@ip : sbtest 09:58:37> set global query_cache_type=0;
Query OK, 0 rows affected (0.00 sec)
root@ip : sbtest 09:58:53> 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             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.00 sec)
The same query again:
root@ip : sbtest 09:58:58> select * from sanguoyanyi;
+------+--------+
| id   | people |
+------+--------+
|    1 | guojia |
+------+--------+
1 row in set (0.00 sec)
When set the query_cache off, the same query(select * from sanguoyanyi;) works ok.

Stop the mysql server:
service mysql stop

vim my.cnf:
query_cache_size = 0
query_cache_type = 0

Then start the mysql angin:
service mysql start

Remote Login:
mysql --uroot -p -hip(the ip same as above )
root@ip : sbtest 10:05:50> select * from sanguoyanyi;
+------+--------+
| id   | people |
+------+--------+
|    1 | guojia |
+------+--------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE `sanguoyanyi` (
  `id` int(11) DEFAULT NULL,
  `people` varchar(13) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

insert into sanguoyanyi values (1,'guojia');

Hi,

apart from not recommending the usage of the query cache, I cannot reproduce this.

[root@localhost mysql]# bin/mysqld_safe &
[1] 10373
[root@localhost mysql]# 2017-05-18T03:56:10.430249Z mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
2017-05-18T03:56:10.444351Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@localhost mysql]# bin/mysql -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> \q
Bye
[root@localhost mysql]# bin/mysql -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> \q
Bye
[root@localhost mysql]# bin/mysql -p123 --host=127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> \q
Bye
[root@localhost mysql]# bin/mysql -p123 --host=127.0.0.1 --protocol=tcp
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> \q
Bye

[root@localhost mysql]# bin/mysql -p123 --host=192.168.89.247
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> \q
Bye
[root@localhost mysql]# bin/mysql -p123 --host=192.168.89.247
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 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)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

mysql>

I can confirm that there is a problem with query cache enabled and the mysql client libraries with the following combinations:

C Client - mysqlcluster 7.4.12
Server - mysqlcluster 7.5.7 (mysql 5.7.19)
Symptom: Error message "Malformed packet"
Frequency: All the time
Disabling query cache seems to fix the problem.

C Client - mysqlcluster 7.5.7 (mysql 5.7.19)
Server - mysqlcluster 7.5.7 (mysql 5.7.19)
Symptom: Error message "2013 Lost connection to MySQL server during query"
Frequency: Infrequent
Disabling query cache seem to fix the problem

captured malfold packet

Attachment: mysql-issue86318.pcap.gz (application/x-gzip, text), 789 bytes.

Same issue is reported at https://github.com/go-sql-driver/mysql/issues/757

I captured pcap file.

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.15-log |
+------------+
1 row in set (0.07 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     |         |                |
| age   | int(11)     | YES  |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.08 sec)

mysql> show variables like 'query_cache%%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 12582912 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.08 sec)

Posted by developer:
 
Query cache removed by WL#10824 in 8.0 and up.