MySqlException: Unable to connect to any of the specified MySQL hosts

Usually in batch type of MySql client code you might have seen this exception before: MySqlException: Unable to connect to any of the specified MySQL hosts (or similar, this one is specific to MySql .Net client)

Of course it might be what it says, but actually message can be a little misleading if it happens in some batch jobs (with huge number of connects and disconnects) but nowhere else. After a little digging and debugging in the MySql .Net client library code it became obvious that it was a low level TCP socket issue. Because of the high number of connects and disconnects, OS (WinXP/2003 in this case) network layer was not finding enough user ports to assign to the server (by design TCP keeps the connection in a TIME_WAIT state before disposing the connection).

Little googling quickly relieved a few solutions: First one is simple. If you can alter the code, the obvious solution is to use pooling or keep the connection around while querying the database. This would be much more efficient as well. Other suggestion is to mingle with the OS variables. On Microsoft Windows XP / Server 2003 these are registry variables MaxUserPort and TcpTimedWaitDelay under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters. On Vista and server 2008 MaxUserPort have different meaning and can be set using netsh utility.

Here are my registry settings on XP/2003:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\MaxUserPort 0xFFFF (DWORD)
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\MaxUserPort\TcpTimedWaitDelay 60 (DWORD)

You need to create them. By default they don’t exists.

On Vista/2008 you can use netsh to change it to something like:

netsh int ipv4 set dynamicport tcp start=10000 num=50000

Here is example code to reproduce the issue:(In Ruby, for fun)

require "mysql"
c = 1;
20000.times do
  c += 1
  dbh = Mysql.real_connect("localhost", "root", "", "test")
  dbh.query("select 1")

You should see exceptions in about 3900 iteration in XP/2003 and 16000 in Vista/2008, if they have the default values for their TCP parameters.

I have not tested this with any other TCP servers or OSes, but I would expect the issue and the solutions to be similar.

Happy hacking..


Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled

Commerce Server 2002 Best Practices

Microsoft Exchange Server Analyzer: MaxUserPort value is too low

The default dynamic port range for TCP/IP has changed in Windows Vista and in Windows Server 2008

MS08-037 : More entropy for the DNS resolver

January 02, 2009 | 399 Words

Ziya Suzen (Maxwell Troy)'s personal notes on software development and related technical subjects.