Recently, I am experiencing a weird error when connecting to a MySQL server remotely:
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0
Basically, this error is similar to the busy tone when you are making calls. The key thing is, it happens randomly. Sometimes the connection is okay, sometimes it takes any where from 0.01 to 30 seconds to establish a connection. Sometimes it gets time-out.
Long story short. Continue to read this article if you have met the following conditions:
- You try to connect to a MySQL server remotely, i.e., not localhost(127.0.0.1)
- It happens randomly. It can take anywhere from 0.01 seconds to 30 seconds to establish a connection. Sometimes it fails.
- You connect the server using IP address, i.e., it has nothing to do with the domain name, or skip-name-resolve in my.cnf
- You have included the client IP address in /etc/hosts.allow.
The key thing is: Random.
You probably have scratched your head for few hours (or days), gone through tons of useless suggestions on Google/Stackoverflow/Serverfault etc, and the problem still exists. Oh well, at least this has been happened on me in the past 24 hours.
Before we discussed the problem, let’s try to reproduce the problem:
#In the client computer, we try to connect to #the MySQL database remotely and run a simply command: time mysql -u root -pPASSWORD -h IP_ADDRESS -e "show databases;" #Case 1: Everything is okay real 0m0.001s user 0m0.001s sys 0m0.001s #Case 2: it takes 20 seconds to establish a connection. #That's not right. real 0m20.001s user 0m0.003s sys 0m0.003s #Case 3: Cannot even make the connection. ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0 real 0m49.617s user 0m0.003s sys 0m0.003s
If you also observe a similar symptoms, I can tell you that the problem may not be related to MySQL server or MySQL settings. I recommend you to check your network traffic. Here are my suggestions:
#Check which process is running. top #Or you can check which process is running by the web server user #In my case, apache is the web server user ps -u apache #Or you can check the current traffic using nload nload -u M
If you are lucky, you may notice that there is a huge network traffic going on. The traffic is the main problem that cause the problem. Try to kill that process or perform a reboot.
Let’s take my case an example. I noticed a weird process running by the apache user:
ps -u apache PID TTY TIME CMD 8112 ? 00:00:09 httpd 8113 ? 00:00:08 httpd 8334 ? 00:00:08 httpd 8796 ? 00:00:06 httpd 8802 ? 00:00:07 httpd 8891 ? 00:00:07 something (This is a malware)
After I kill that process, everything is back to normal again.
It’s been 5 years, but this post was very applicable to my research. We’re seeing the exact RANDOM issue you describe. I think your suggestion about network congestion is finally putting me on the right path to resolve this. Thank you for putting this out there on the Interwebz!