I am not able to connect a MySQL server remotely. The connection seems to be ok because with telnet [ip] [port] I get response:
4.1.3b-beta-nt-max▒ <0v '[uZ,? B {efSLa $, Q4N
When executed by command line or by MySQL Workbench 6.3
mysql -u [user] -p -h [host]
I get the same error:
ERROR 2027 (HY000): Malformed packet
It is a mysql client bug, I've searched about it and it is a old auth switch request. Your client it is out of date, using a old protocol communication, now, if it is a Workbench problem too your just the Client, you need to update or downgrade the MySQL Client first and try to run it alone.
Here, it is the same question with a more complete answer: https://dba.stackexchange.com/questions/135343/server-responds-with-empty-packet-during-session-negotiation-resulting-in-client
And, for the new Auth protocol, on connection phase: https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html
You must upgrade the "old_password" hashed password:
SET PASSWORD FOR 'existinguser'@'localhost' = PASSWORD('existingpass');
So you can login in an "old" MySQL server, using a recent Workbench version
If you need to connect to pre-4.1 MySQL servers from later MySQL versions (5.7+), you will need to use "--skip-secure-auth" option from the client. And the client version cannot be newer than v5.7.4 because this option had been removed in 5.7.5. You can download version 5.7.4_m14 from mysql's archive website. For example,
$ mysql -uuser -p -hserver --skip-secure-auth
I had the same error trying to connect to a MariaDB server with the MySQL client mysql-client. I solved it by installing mariadb-client (that overwrites the mysql binary, so use the same command to connect).
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.
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'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>
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. :)
