MySQL/PHP Error:[2002] Only one usage of each socket address (protocol/network address/port) is normally permitted

Question

I couldn't find a solution to this already on stackoverflow, most other related topics had to do with Apache not starting first and getting this error. My issue is that after apache is running some of my users that are connected to our php/mysql website will receive this error:

PHP Warning:  mysql_connect() [function.mysql-connect]:
[2002] Only one usage of each socket address (protocol/network address/port)
is normally permitted.

This seems to be totally random and when I monitor my worker threads for Apache, there are typically lots of idle workers available to accept new connections/requests.

My Site is running on Windows XP SP3, Xampp 1.7.7, Quad Core, 4gigs of RAM, 1TB HD, Specs for php/mysql:

Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/1.0.0e PHP/5.3.8 mod_perl/2.0.4 Perl/v5.10.1

Any help on what I should change in any of my configurations to make this go away would be greatly appreciated. I've looked on google and even on Xampp forums but most people experience this issue prior to launching Apache, but Apache is running fine for me when users are experiencing this error.

Answer 1

you can modify windows REGISTRY to fix that,

first in regedit open this path:

HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

and create 4 new DWORD as this key and values:

TcpTimedWaitDelay
REG_DWORD: 0000001e (hex)

MaxUserPort
REG_DWORD: 0000fffe (hex)

TcpNumConnections
REG_DWORD: 00fffffe (hex)

TcpMaxDataRetransmissions
REG_DWORD: 00000005 (hex)

like with this screen shot:

reference

Answer 2

The error isn't actually coming from MySQL, but from Windows itself:

When a connection is closed, on the side that is closing the connection the 5 tuple { Protocol, Local IP, Local Port, Remote IP, Remote Port} goes into a TIME_WAIT state for 240 seconds by default.

In this case, the protocol is fixed - TCP

The local IP, remote IP and remote PORT are also typically fixed. So the variable is the local port.

What happens is that when you don't bind a port in the range 1024-5000 is used. So roughly you have 4000 ports. If you use all of them in 4 minutes - meaning roughly you make 16 web service calls per second for 4 minutes you will exhaust all the ports. That is the cause of this exception.

In other words, you've run out of ports in the dynamic range. That probably shouldn't be happening. How many concurrent users are you dealing with here?

The linked blog has workarounds:

1. Increase the dynamic port range through registry editing.
2. Reduce the time the system wants connections to spent in TIME_WAIT through registry editing.
3. Run a bit of code to do the above registry edit without regedit.

What a wide variety of workarounds!

See also this question on the Visual Studio forums, which explains:

The port will be locked for another minute or two to catch all packets which might have been sent before the application was terminated but haven't arrived yet. In Winsock API you can set socket option SO_REUSEADDR to resolve this (also this option can be set in .NET Socket class), but TcpListener is too high-level and doesn't let you set this option.

It's very likely that the underlying code to connect to MySQL or the code that handles connections in Apache isn't trying to use SO_REUSEADDR.

I'm going bet that your changing of the keepalive timeout had a direct impact here. Even though reducing it theoretically frees up the socket, Windows disagrees and keeps the socket reserved.