mysql connect failures - intermittent, infrequent

Mysterious unreproduceable connect failures

[surfin2u]

My site's php code experiences the inability to connect to mysql on an intermittent and generally infrequent basis. Sometimes there are as many as 4 failures in 1 day, and other times weeks go by without a single failure. The page that failed will connect without problem a minute later, making this problem especially tough to track down. The site makes thousands of successful database connections every day.

I log the error to a file and email myself a message that includes this code whenever there's a connect error:

mysql_errno()." ".mysql_error()

Here's what that code outputs when there's an error:

2003 Can't connect to MySQL server on 'dbserver_here' (49)

I wonder if anyone else has run into this. I have written plenty of code to make my site less reliant on the database, since hosting outfits seem unable to provide reliable mysql database access. Am I the only one experiencing this?

[vinyljunkie]

I've never run into a problem like this myself, but I'm curious if you've asked your host if they may have been doing any sort of server maintenance when this is going on.

Also, have you considered switching web hosts? You might get better uptime for your databases with someone else.

Just a thought.

[surfin2u]

I have been in contact with the tech support people at the hosting outfit that I'm using. There is no maintenance going on when these errors arise. The problem only seems to last a few seconds before clearing up, but that's long enough to be an issue for my busy site.

I have been through a number of hosts and switching is always an option. My present host has been very responsive, and I'm not sure that they're at fault, so I'm reluctant to leave and risk ending up at a worse outfit.

[jatar_k]

Is it possibly a max connections problem?

[surfin2u]

Wish it was as easy as a max connections issue. It usually happens on very simple pages that only do a single connection. It also happens at seemingly random times, that don't correlate to the server being busy or idle.

My next stab at a solution will be to modify the code to make the connection first thing in the code for the page, rather than in code that's executed near the end of the page. I don't see any reason why that would help, but what the heck, I'm short on ideas to try.

[coopster]

Max connections [dev.mysql.com] on a shared server means much more than just your account, surfin2u. Max connections means your site, plus all the others running on that server.

You can see what your hosting provider has it set at by running this command ...

SHOW VARIABLES;

That said, it's rare that you should find yourself maxing that out. There are so many other uncertain factors on a shared host it is tough to track. How often are they rebooting the server? How often are they restarting apache? How often do they restart MySQL ... the list goes on.

Usually, if you are certain your code is written tight and operating as designed, problems come down to configuration/server changes.

[vinyljunkie]

Here's a truly wild idea. How about modifying your script so that it will attempt a database connection a second time before issuing an error message? I don't know what interval of time you'd want to wait before trying that in the script, but if all else has failed maybe this is an option.

[surfin2u]

Thanks for both of those suggestions. I will look into them and report back on how things turn out.