MySQL: ERROR 2027 (HY000): Malformed packet

I did face this issue for normal select query. It was weird that when I was using small-case 's' in the query statement, it was giving me the same error. The I figured out that this happens as internally it is trying to retrieve the data from mysql cache. It was not because of the case of 's' in the select query.

//Returned Error
select * from foo;

//This worked fine
Select * from foo;

//This also worked fine
select SQL_NO_CACHE * from foo;

From this I was able to conclude that it was the issue as it was using Cached data.

I've faced the same issue with latest MySQL Client (>5.7) while trying to connect lower versions of MySQL like 5.1.xx.

To avoid this issue (ERROR 2027 (HY000): Malformed packet), create a user with latest password authentication.

ex: Login to MySQL 5.1.xx server and execute..

mysql> create user 'testuser'@'xx.xx.xxx.%' identified by 'testuser_Secret1';

Check if you have old_passwords enabled, then disable it for that session.

mysql> show session variables like 'old_passwords';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| old_passwords   | ON    |
+-----------------+-------+
mysql> set session old_passwords = 0;
mysql> GRANT select on test.* TO 'testuser'@'xx.xx.xxx.%' identified by 'testuser_Secret1';

Verify password that should begin with "*SOMETHING1123SHOWNBELOW3034".

mysql> select user,host,password from mysql.user where user = 'testuser';

+-----------+---------------+-------------------------------------------+
| user      | host          | password                                  |
+-----------+---------------+-------------------------------------------+
| testuser  | xx.xx.xxx.%   | *053CB27FDD2AE63F03D4A0B919E471E0E88DA262 |
+-----------+---------------+-------------------------------------------+

Now try logging from MySQL 5.7.xx Client and try to establish a connection to MySQL 5.1.xx server.

[testuser@localhost]# mysql -V 
mysql  Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using  EditLine wrapper

[testuser@localhost]# mysql -hxx.xx.xxx.xxx -u testuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1528853
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2020, 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>

For the people that has this error when the execute the query (not when connecting to DB), the problem is the cache configuration in database.

You can find the bug description here:

https://bugs.mysql.com/bug.php?id=86318

The solution:

disable the cache configuration:

query_cache_limit = 0
query_cache_size = 0
query_cache_type = 0

In the long term there are no negative repercussions, since the latest versions of MySQL no longer support this feature. With little data the cache works correctly, but in large quantities it generates a bottleneck.

More info about the cache removed from mysql 8.0:

https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/

i solved this issue. i was facing this issue in my PHP 7.2. First i created a new user and upgrade it in my script. Then i upgrade PHP 7.2 to 7.3. And it worked. :)