CODEWITHSUNDEEP
phpmysqlapachexampp
Shane
Shane

Reputation: 379

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

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.

Upvotes: 11

Views: 43100

Answers (7)

Charles
Charles

Reputation: 51411

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.

Upvotes: 12

James Sullivan
James Sullivan

Reputation: 13

Quite late to the party, but I recently had this issue but wasn't able to fix it via the the regedit. Turns out I was calling PDO twice (I'm using PDO instead but connecting to MYSQL). My solution was to only use the initial $connection that I made

$connection = new PDO(connectionString);

and passing it from the top of the script to any helper functions or classes I used.

Upvotes: 0

Ares9323
Ares9323

Reputation: 1044

This is the Doldurma answer but for lazy people, you just have to place this inside a .reg file and than click it (reboot was not required in my case)

File content:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]

"TcpTimedWaitDelay"=dword:0000001e
"MaxUserPort"=dword:0000fffe
"TcpNumConnections"=dword:00fffffe
"TcpMaxDataRetransmissions"=dword:00000005

Upvotes: 8

thomas jackson
thomas jackson

Reputation: 29

Happens to me when developing my Laravel projects. For developmental purposes, it's no biggie especially when you are using windows system. But for production, I simply run on Linux and problem solved. Also, change the MySQL connection from "localhost" to "127.0.0.1" if possible.

Upvotes: 2

DolDurma
DolDurma

Reputation: 17331

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:

enter image description here

reference

Upvotes: 24

imsinu9
imsinu9

Reputation: 769

There is a simple Work around on this.

Download CurrPorts http://www.nirsoft.net/utils/cports.html - a standalone freeware tool that lists was programs and services are using which ports. Sure, you can use netstat, the Windows Administrative Tools, and Task Manager to figure this out yourself, but CurrPorts does that all for you.

Run Currports as administrator >> Select the service/application that creating problem by running to same port >> Stop the service. To stop service -

  1. Direct from Currports application.
  2. Start>>Run>>service.msc>> (select from the list).
  3. TaskManager.

Hope you boost me up by polling.

Upvotes: 0

paperclip
paperclip

Reputation: 2330

FWIW, I had the same issue on a similar setup (Windows XP). The same code worked without this issue on Mac (OS X).

I resolved the issue by using a persistent connection within the MySQLi class.

See here for more information: http://php.net/mysqli.persistconns

Make sure you are aware of all the gotchas / caveats associated to using a persistent connection.

Upvotes: -1

Related Questions