0

I have several servers with a lot of outgoing traffic using nginx. When the bandwidth is exhausted (1Gbit), I have problems connecting to my mysql server from those servers.

The connect always fails with the following message:

mysql conneciton failed with SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading authorization packet',

The bandwidth is exhausted, but I would expect the connection and data transfer just to be slower. But connects fail completely. After several attempts it sometimes works. once connected, it works just fine.

All other services (Ssh, http) are working just fine as well.

What can I do to solve this problem and why does it occur?

Improve this question
3
  • What versions of MySQL and PHP (assuming) are you using? There used to be a bug in PHP that caused this. Also what is your connect_timeout? Lastly, if your network is exhausted -- you will simply need more network.
    – jeffatrackaid
    Jan 2, 2014 at 19:26
  • @jeffatrackaid I am using PHP (PHP 5.4.6-1ubuntu1.5), however the error can be reproduced with the terminal mysql client version (mysql Ver 14.14 Distrib 5.5.34). My network is really exhausted (I server downloads from that server that saturate the bandwidth completely). I know that this will cause packets to be dropped, however I was wondering why ssh, http etc work just fine and only mysql does not. Should not packets be re-sent by tcp?
    – The Shurrican
    Jan 3, 2014 at 20:01
  • tcp should handle the bandwidth congestion just fine... however mysql has problems. nothing else has. since i do not use only atomic statements (no transactions or stuff like lastinsertid) i ended up writing a database connection object that sends all the sql statmenets to a remote rest http api that serializes the response - that works just fine.
    – The Shurrican
    Jan 6, 2014 at 18:13

2 Answers 2

Reset to default
0

If the connection truly is saturated to the point where it can not stuff any more data down the pipe you're going to lose packets. If enough packets are lost for the same connection in a short period of time then that connection will likely be dropped due to the known mechanics of TCP.

How to solve it...

First, the obvious: Get a connection that can handle the traffic. If it's overloaded, it's overloaded.

Second, if possible you can/should set up a dedicated connection for the database connectivity. Segregate the traffic so that filling up one gigabit link does not interfere with the other.

Third, find out what is actually using up all the bandwidth. If it is unwanted traffic, block it. If it's coming from the Internet at large, have your upstream/bandwidth provider block it.

Improve this answer
2
  • The connection is truly saturated, and the traffic is also not unwanted (it is a download server). I cannot set up a separate since the server is only connected with 1 gbit port. I know that the saturation leads to dropping / queueing of packets (depending of the handling on the network layer). My question is why only mysql is having problems and all other tcp connections (ssh, http) work just fine, only a little slower?
    – The Shurrican
    Jan 3, 2014 at 20:20
  • You'll have to grab a packet capture (preferably from both sides of the connection) to be certain. One possibility for example: The TCP 3-way handshake: Client sends SYN, server replies SYN-ACK, Client sends ACK, Server sees ACK <-- all have to happen for a TCP connection to 'establish'. ssh/http from the outside would only require 1 packet to go out the congested outbound link (SYN-ACK). A mysql connection from the inside would require two to go out that same link. (SYN, ACK).
    – user143703
    Jan 3, 2014 at 21:27
0

Because of your buggy connection to mysql server connection gets dropped by the server probably with "bad handshake". you will see this error because server will decide not to write anything more to the socket, as he did not see anything from the client side and will enforce the flag "net->error= 2"(this can be seen in function 'my_real_read' in net_serv.cc).

This flag has the effect that even if the server calls a function that writes to the net back to the client with actual documented error it will be thrown away. Usually documented "Bad Handshake" error for saturated net timeouts.

Mysql connection timeout is quite small. I believe 5 seconds by default you can increase it in my.cnf with "connect_timeout" system variable from default 5 to 20. That should help, once the connection is established you will get retires while timing out in saturated net. Setup second net path to the DB server.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .