Mysql – Connection error to thesql database

authorizationconnectionMySQL

My application needs to do quite frequent connects to a mysql database on another machine. However I get frequent errors on connection; I can usually eventually connect but after many retries. The error message I get is:

Mysql.mysql(): Couldn't connect to SQL-server: Lost connection to MySQL server at 'reading authorization packet', system error: 2

I have read the information here: http://dev.mysql.com/doc/refman/5.0/en/error-lost-connection.html and also the comments to the bug http://bugs.mysql.com/bug.php?id=28359.

There are quite a number of aborted connects as you can see:

mysql> SHOW GLOBAL STATUS LIKE 'Aborted_connects';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_connects | 2540  | 
+------------------+-------+

I increased the timeout from 5 to 15 sec:

mysql> show variables like 'connect_timeout';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 15    | 
+-----------------+-------+

but this has not helped. Any suggestions how to debug this ? It makes the database interaction horribly slow when it on average has to try to connect 5 times before it works.

I have not yet tried to keep the connection open for the lifetime of the application, would that be better ? How do I prevent the connection from closing ?

If it helps the computer with the problems is Windows 7 32-bit, while the mysql server is on Debian Linux.

Best Answer

Looking over all the bug details an reports I suspect there is a network or client side issue here. Something is either corrupting or adding too much latency to the mysql data.

On a clear network, do a wireshark trace and check what the conversation is like. Ensure no traffic shaping is going on anywhere. Seems like a 64bit issue only so check a 32bit build just to ensure it's the same issue. Check on a Windows XP box - M$ did some funky stuff to the network stack on Vista which made it suck - bit time! We have no end of networking issues with Vista and Win7 machines!

If you do manage a wireshark trace please pastebin and put up the link as we'd all like a look I think.

Related Topic