3

From what I can tell this is recent error that has started to occur on my localserver:

 Can't connect to MySQL server on 'localhost' (10048).

My site will work for a while then it will randomly throw this error then start working again. Any advice on how to fix this? I have searched online and most people claim that it is a windows based error which needs a registry fix but I wanted to see if anyone else has encountered this issue and has any better advice? Thanks!

1
  • Which Windows edition are you using?
    – gekkz
    Dec 31, 2009 at 15:13

7 Answers 7

2

I'd suggest looking at the output of "netstat -ano" when this error occurs. If this is Windows Server 2003, the default ephemeral port range is only 1024-5000. If this port range is full, you won't be able to make new outbound connections, including "outbound" to localhost. You can get around this by changing the MaxUserPort setting in the registry, possibly along with the TCPTimedWaitDelay setting if netstat shows you have lots of connections in a TIME_WAIT state. 'netstat -ano |find /C ":"' will give you a (somewhat misleading) count of how many sockets are active.

However, if you find this port range is all in use, as others mentioned, you may have an underlying issue with connections not being closed. This could be a code problem.

Also, if you have your web server and your database server running on the same machine, you may want to consider a connection protocol other than TCP/IP, such as memory or named pipes.

1

Thanks to jlupolt's post as it precisely pointed out the key to solve the problem I was facing.

Some high exposure banner ads drive a lot of traffic to our site during a short period of time (hours) and "Can't connect to MySQL server..." error randomly (not always) occurs.

The memory usage and CPU loading of db server was still very low during this period and the open connection of mysql server is far below the limit of max_connections of it. So it should not be mysql's problem.

After following jlupolt's suggestion we found that default ephemeral port range (1024~5000) of the web server (not the db server, it's another machine in our case) is all full with TIME_WAIT state. After further serching we found this page:

B.5.2.2.1. Connection to MySQL Server Failing on Windows http://dev.mysql.com/doc/refman/5.1/en/can-not-connect-to-server.html

After raising the MaxUserPort to 20000 and lowering TcpTimedWaitDelay to 20 (needed a reboot) the connection error never occured. The value of MaxUserPort and TcpTimedWaitDelay we used may not be optimum for your case. The values depend on how fast the number of tcp/ip connections is growing and the combination should allow enough amount of new connections and time for unused connection to be released.

1

Solution:

Open the Registry Editor regedit

The TcpTimedWaitDelay set:

Find HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\TCPIP\Parameters registry subkey and create a new REG_DWORD value named TcpTimedWaitDelay.
Set this value to decimal 30, hexadecimal 0×0000001e. This value will wait time is 30 seconds.
The default value: 0xF0 (16229), the waiting time is set to 240 seconds.

The MaxUserPort set (to increase the maximum port connection):

Find HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\TCPIP\Parameters registry subkey, and create a new REG_DWORD value named MaxUserPort.
Set to the lowest value of decimal 32768.
The default value: 5000 (decimal).

Close the Registry Editor, and restart the windows system.

0

You might want to make sure that you're closing all of your connections to the database. You might accidently be leaving them open when you're calling your queries and as time goes on, Windows will reach it's maximum number of connections and throw the can't connect error. When you try again a little while later, windows may have released a few of the connections and you are once again able to hit the database.

Good luck and hope this helps some.

2
  • Well, I have created a DB class and it has a set connection method. Whenever a call to the databse is needed, I need to call this set connection method which passes the query and executes, closes the connection then returns the query result. So I am sure that every time a connection is opened it is closed because it all passes through this one method... Thanks though for your help
    – tempyAcc
    Dec 31, 2009 at 15:19
  • Are your sure the connection is being explicitly and correctly closed? Too often people assume the class will do it for them. This isn't like a file handle. Dec 31, 2009 at 22:18
0

There have been a few 'localhost' issues recently.

Have a look in /etc/hosts and make sure you have a none commended out entry for the localhost (eg: 127.0.0.1 localhost) -some of the newer linux flavors (and windows 7) have stopped this recently.

Also, worth checking if you have sql bound to both the loopback and the actual IP address and try accessing it there.

0

Some debugging tips:

  • can you connect to the TCP port? Try telnet localhost 3306 If it says "Connection Refused" you have a network problem

  • Are your connections exceeding MySQL's max connections? Try increasing the variable "max_connections" in my.cnf then restarting MySQL

  • Are you holding connections open for more than 8 hours? MySQL will automatically close a connection after 8 hours. There's various things you can do about this-- try googling if that is the issue.

0

Try connecting directly to 127.0.0.1

Under some circumstances, attempting to connect to "Localhost" will attempt to connect over an internal "socket" connection instead of via mysql's network interface, using the IP fixes this.

You must log in to answer this question.