Documentation Home
MySQL and Windows
Related Documentation Download this Excerpt
PDF (US Ltr) - 2.7Mb
PDF (A4) - 2.7Mb
EPUB - 2.3Mb
HTML Download (TGZ) - 2.2Mb
HTML Download (Zip) - 2.2Mb

MySQL and Windows  /  Connection to MySQL Server Failing on Windows

Chapter 2 Connection to MySQL Server Failing on Windows

When you're running a MySQL server on Windows with many TCP/IP connections to it, and you're experiencing that quite often your clients get a Can't connect to MySQL server error, the reason might be that Windows does not allow for enough ephemeral (short-lived) ports to serve those connections.

The purpose of TIME_WAIT is to keep a connection accepting packets even after the connection has been closed. This is because Internet routing can cause a packet to take a slow route to its destination and it may arrive after both sides have agreed to close. If the port is in use for a new connection, that packet from the old connection could break the protocol or compromise personal information from the original connection. The TIME_WAIT delay prevents this by ensuring that the port cannot be reused until after some time has been permitted for those delayed packets to arrive.

It is safe to reduce TIME_WAIT greatly on LAN connections because there is little chance of packets arriving at very long delays, as they could through the Internet with its comparatively large distances and latencies.

Windows permits ephemeral (short-lived) TCP ports to the user. After any port is closed it will remain in a TIME_WAIT status for 120 seconds. The port will not be available again until this time expires. The default range of port numbers depends on the version of Windows, with a more limited number of ports in older versions:

  • Windows through Server 2003: Ports in range 1025–5000

  • Windows Vista, Server 2008, and newer: Ports in range 49152–65535

With a small stack of available TCP ports (5000) and a high number of TCP ports being open and closed over a short period of time along with the TIME_WAIT status you have a good chance for running out of ports. There are two ways to address this problem:

  • Reduce the number of TCP ports consumed quickly by investigating connection pooling or persistent connections where possible

  • Tune some settings in the Windows registry (see below)


The following procedure involves modifying the Windows registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore it if a problem occurs. For information about how to back up, restore, and edit the registry, view the following article in the Microsoft Knowledge Base:

  1. Start Registry Editor (Regedt32.exe).

  2. Locate the following key in the registry:

  3. On the Edit menu, click Add Value, and then add the following registry value:

    Value Name: MaxUserPort
    Data Type: REG_DWORD
    Value: 65534

    This sets the number of ephemeral ports available to any user. The valid range is between 5000 and 65534 (decimal). The default value is 0x1388 (5000 decimal).

  4. On the Edit menu, click Add Value, and then add the following registry value:

    Value Name: TcpTimedWaitDelay
    Data Type: REG_DWORD
    Value: 30

    This sets the number of seconds to hold a TCP port connection in TIME_WAIT state before closing. The valid range is between 30 and 300 decimal, although you may wish to check with Microsoft for the latest permitted values. The default value is 0x78 (120 decimal).

  5. Quit Registry Editor.

  6. Reboot the machine.

Note: Undoing the above should be as simple as deleting the registry entries you've created.

Download this Excerpt
PDF (US Ltr) - 2.7Mb
PDF (A4) - 2.7Mb
EPUB - 2.3Mb
HTML Download (TGZ) - 2.2Mb
HTML Download (Zip) - 2.2Mb
User Comments
  Posted by Matthew Kerr on November 16, 2005
I discovered that this happened when I specified a linked table connection string in Access that doesn't include the database name.

"ODBC;DSN=foo" eventually breaks;
"ODBC;DSN=foo;DATABASE=bar" works fine.


Set up DSN "foo" to connect to database "bar".

Set the connection string on some tables to "ODBC;DSN=foo" and open them a few times. Run a 'netstat -a' and you will see lots of ports allocated.

Set the connection string to "ODBC;DSN=foo;DATABASE=bar" and try again. This time, it only allocates one or two ports to do everything.

Hope this helps!
  Posted by Nicholas Goodwin on April 25, 2006
I had the same problem with MySQL 4.0.20 on Windows XP after a server crash. I could access the server locally, netstat showed it was listening on port 3306 but I could not even telnet to it from any other machine.

The only solution was to switch off the mysql service, delete everything from the data folder, copy back the contents of the mysql directory and the database I wanted to use, then restart everything. This rebuilt the database and evrything was back to normal.

  Posted by Shane Bester on March 19, 2014
Also take a look here:
(Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled)
Sign Up Login You must be logged in to post a comment.