Cry MySQL How to...

Troubleshooting the error: ERROR [HY000] [MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'localhost' (10048)

When connecting to a MySQL database through ODBC the following error is generated:

ERROR [HY000] [MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'localhost' (10048)

The precise presentation of this error may vary according to the software used, this wording was generated from an ASP.NET application.

Other symptoms:

• The application has previously been able to successfully connect to the database.
• Waiting a minute or so, and the application can connect again without the error.
• The error seems to manifest itself under "stress" conditions, when lots of connections are being made opened and closed in a short space of time (minutes).

These notes only apply to the error above when the "other symptoms" are present.

Cause

The error is caused not by MySQL or the MySQL ODBC driver, but by the behaviour of Windows. For performance reasons Windows maintains a connection on a TCP port after it is closed for (by default) 4 minutes. The advantage of this is that if the connection needs to be reopened it can be done at a lower cost. The trouble is that under heavy load when lots of connections are opened and closed to the database, the system can hit a limit on the maximum number of connections allowed (even though as far as the application is concerned these connections have been closed).

Resolution

• To decrease the delay before Windows releases an open TCP connection, use regedit to edit the following registry value of:

  HKLM\
    SYSTEM\
      CurrentControLSet\
        Services\
         Tcpip\
          Parameters\
            TcpTimedWaitDelay

  This value does not exist by default and will need to be created. It should be a DWORD. The valid range is 0x1E - 0x12C (30seconds to 300 seconds), the default is 0xF0 (240 seconds).

  Decreasing the value of TcpTimedWaitDelay should help, but is unlikely to permanently solve the problem for a busy system (because if the system is busy the limits will still be hit).

• Consider whether it is possible to modify the application to re-use database connections instead of closing and reopening them.

  In theory enabling connection pooling would address this problem. For more information about connection pooling see http://support.microsoft.com/default.aspx?scid=kb;EN-US;q169470. To enable connection pooling for the MySQL driver:

  1. Start > Control Panel > Administrative Tools > Data Sources (ODBC).
     This will open up the "ODBC Data Source Administrator".
  2. On the "Connection Pooling" tab, double click the ODBC Driver you are using to connect to MySQL.
     This will open up the "Set Connection Pooling Attributes" dialog.
  3. To enable pooling select "Pool Connections to this driver"

These notes have been tested against MySQL version 4.1 and 5.0 with version 3.51 of the ODBC driver.