Sporadic MySQL connection problems.

can't connect to server/database during the night

[polishben]

Hi Everyone

I have been having problems on and off since January 07 with my website. I have got custom error handling in place to email me when serious errors occur. Every few days I get emails arrive to say the site wasn't able to connect to the DB during the night.

Examples of errors are:

Could not connect to server: Host 'dns0.example.net' is not allowed to connect to this MySQL server

Could not connect to server: #HY000Host 'dns0.example.net' is not allowed to connect to this MySQL server

Could not connect to database: Lost connection to MySQL server during query

Could not connect to server: Can't connect to MySQL server on '192.168.2.101' (4)

This is normally then followed by errors such as:

Could not execute query: Unknown system variable 'names'

Last night I got over 300 emails with errors, including a new one:

Could not connect to server: Too many connections

The site is on a host who are running:
Linux, PHP 4.4.3, MySQL 4.0.25, Apache 1.3.37

My DB connection code is:

$link = mysql_connect($hostName, $user, $pwd);
if (!$link) trigger_error('Could not connect to server: ' . mysql_error() , E_USER_ERROR );
if (!mysql_select_db($dbName, $link)) trigger_error('Could not connect to database: ' . mysql_error() , E_USER_ERROR );
//set utf8
$sql = 'set names "utf8";'; mysql_query($sql) or trigger_error('Could not execute query: ' . mysql_error() , E_USER_ERROR );

I have taken this up with my host who have basically said "well no-one else has reported a problem" and have since then ignored it. Does anyone have any suggestions as to what the problem could be?

My only idea at the moment is to simply change to a more helpful host.

Many thanks in advance.

Ben

[edited by: eelixduppy at 10:37 am (utc) on April 16, 2007]
[edit reason] exemplified errors [/edit]

[eelixduppy]

Seems to be an issue with the mysql server, but I'm not positive. Your code seems correct, though. Is the mysql server on the local server? Also, can you identify any patterns to when you are getting these errors. You could check your error logs to see when they are occurring, and possibly come up with a reason as to why it may not be working at that time. You may also want to continually speak with your host about the issue. Maybe it is something that they overlooked. If this has just started happening maybe a system change has caused it to come about. Seems kind of odd but it looks like you are going to have to take your forensic tools out for this one ;)

Good luck!

[polishben]

I don't know if MySQL runs locally or not - I presume so as the URL in my errors (dns.example.net) and my domain name both resolve to the same IP address.

The timestamps of the errors vary - generally between 0200 and 0400 though, but once I had one at 16:43. If it was always a specific time I would have guessed that some batch process is interfering with the database.
The errors aren't every day either, sometimes everyday, somtimes every few days - I am guessing it is subject to the volume of traffic if any at the time.

Again the big problem is that my host has become very unhelpful about it and is now ignoring the issue.
I am tempted to move host but am concerned that it will be a lot of hassle, and that the new host might not be any better anyway (why is it so hard to find decent hosts?!), plus I am concerned that changing IP address might affect my SEO efforts..

Thanks.

Ben

[jatar_k]

>> I am guessing it is subject to the volume of traffic

have you taken a look through your logs? That might tell you if there is some major traffic spike, if your host is playing games or you have some bad code.

Some of those errors could definitely come from traffic. Others could come from anywhere.

Maybe you have some over aggressive bots, who knows, but start with your logs. If you don't see any grand difference in traffic then on to step 2.

[polishben]

Thanks for the tip.

I spent a while going though the logs - doesn't seem to be anything obviously wrong, no error codes etc..

Whenever these errors are occuring it seems to be a bot that had made the request rather than a genuine visitor. The only difference I can see though is that the requests are sometimes HEAD or HTTP 1.0 - but surely that couldn't affect DB connectivity could it?

More ideas are still welcome!

Thanks.

Ben