mySQL Too Many Connections Open error

getmea · #1 (**permalink**) 12-11-2007, 10:55 PM

Hi All...my website is 95% PHP driven with a mySQL connection at the top of every page. The site has been operational since earlier this year with no issues. Lately, though, my site has been erroring with a "Too many coonections open" error. This is not only happening when launching my site, but also attempting to log into my VDECK/mySQL account with iPower.com.

The first time I saw this error was last Wednesday at 2:PM Central time when I tried to test some new code. FYI: I currently do not average a lot of hits, less so at 2:PM in the afternoon on a Wednesday! I refreshed around 5-7 times and my site finally launched. I then logged into vdeck to update a table and the error occurred again.

I then tried to talk to iPower and hung up after a 45 minute wait time.

I periodically checked the site since and experienced the error again last night. This time, I got on the horn with iPower with a desire to hold indefinitely. NOT to my surprise, tbe level-1 could not duplicate it. He later instructed me (with the "help" of a level-2 or level-3) to "clear my browser cache" and re-try. Hmm...

This morning, I received an email from a visitor showing me the same sql error she received over night. She indicated she was finally able to enter my site but only after refreshing a few times. I then contacted iPower and managed to speak to a somewhat intelligent level-1. After some convincing, he took my vdeck id and pw, signed in, then immediately received the mySQL error while attempting to log in.

After further hold time, he reminded me his company was upgrading all acounts to the new VDECK system and servers. He prioritized my site's upgrade (to new VDECK version) to immediate status. But, he told me this is a common issue as a result of not properly releasing the DB.

I need some help here...I don't believe it's due to my site because:

1) Every page of mine has a release statement prior to the </html> statement
2) It's been operational for almost a year with no issues
3) My highest visit rate, early November, produced my highest sales
4) Error occured trying to login to VDECK/mySQL (not just visiting my site).

Please comfort me and assure me that "I see stupid people" out there (borrowed from "I see dead people")...

Thanks everyone!

kgun · #2 (**permalink**) 12-12-2007, 06:32 AM

Why not use the new PHP PDO object ?

getmea · #3 (**permalink**) 12-12-2007, 09:28 AM

I'm not sure if my webhost is PHP 5.0 able yet...

Jermooski · #4 (**permalink**) 12-12-2007, 10:17 AM

Hey Getmea,

I had the same error message before. The site I built, (PHP) had connection statements on various pages, with close connection statements. But, for some reason the "Too many connctions" error would pop up. I fixed the problem by making the Open and Close connection statements a function. Then, just included the function call on the top of the index page and at the bottom of the index page. The site was built with all includes. So, by putting Open at the top of the index page, the function was available to all the included pages. Does that make sense? Its just a house cleaning tactic. Organization thing a ma jig.

Example:
<?php include ("constants.php");?>
<?php include ("functions.php");?>
<?php fnOpenDBConnection(); ?>
<html>
<head>
</head>
<table width="100%" cellpadding="0" cellspacing="0" height="100%" class="">
<tr valign="top">
<td align="center" valign="top" height="181">
<?php include ("includes/incHeader.php"); ?></td>
</tr>
<tr>
<td>
<table cellpadding="0" cellspacing="0" width="100%" height="100%" class="">
<tr>
<td align="center" valign="top">
<?php include ("includes/incMainbody.php"); ?></td>
</tr>
<tr>
<td align="center" height="40">
<?php include ("includes/incFooter.php"); ?></td>
</tr>
</table>
</td>
</tr>
</table>
</body>
<?php fnCloseDBConnection(); ?>
</html>

Peace out
Giziis

**wige** · #5 (**permalink**) 12-12-2007, 10:33 AM

It sounds to me like you are on a shared host. Is it possible that someone else on the same server is using up the available connections to the mySQL server?

Generally, even if you do not close all of your connections properly the PHP processor will close them as soon as the page has been processed and sent to the server for delivery. Pages typically take less than half a second to be processed and even moderately busy servers are typically handling only 10 pages at any given moment. 10 connections should not exceed your SQL limit unless the hosting company has set some ridiculously low limits or someone else on your server is doing something they shouldn't and connections are not being throttled on a per-account basis.

getmea · #6 (**permalink**) 12-12-2007, 02:34 PM

jermooski:

I have something similar on EVERY page. I have a mysql_conenct string at the top and a mysql_close at the bottom.

wige:

Yes, most likely it is a shared server (I am not paying any extra for a single-domain server). I, too, suspect that maybe some other domain on my server is hogging the sql resources, but I don't have access to the logs (or even know how to get to them). My fist suspicion was when I attempted to manually login to mySQL to do some housekeeping and the error appeared. No reply yet from my web host...

hostBrain · #7 (**permalink**) 12-12-2007, 03:55 PM

Ask your host(if you can get in touch with them)
To see if they will increase the connections limit in the mysql config file.
Most won't, but can't hurt to ask.

Ipower likes to over-sell their servers, so consequently, the more sites using mysql on a server, the more likely this is to happen

getmea · #8 (**permalink**) 12-12-2007, 04:09 PM

thanks...will try contacting the host tonite...

kgun · #9 (**permalink**) 12-13-2007, 12:41 PM

Quote:

Originally Posted by getmea

I'm not sure if my webhost is PHP 5.0 able yet...

Than you should aske your hoster to upgrade the sooner than later. Using the Database object has the following advenatges:

Encapsulates database connection that makes it more secure. Character escaping is taken care of in the API, so SQL injection attacks is not that threat any longer.
You can

try { the connection }

and

catch (the error) { }

and continue.
There are drivers for the most important commercial and free database platforms.
Seamless integrate database connection(s) and SQL statements into your code.

getmea · #10 (**permalink**) 12-13-2007, 08:34 PM

I've read up on PHP 5; it has many advantages including being OOP. Sort of reminds me of Java, though

I'll recommend the upgrade to my web host.

thanks

kgun · #11 (**permalink**) 12-14-2007, 07:31 AM

mySQL Too Many Connections Open error

That is a typical feature with the old model. If you use the PDO object, you can use the prepare and execute methods that may be all you need to solve the problem. If not, you combine it with exception handling as indicated above.

Good OO code generally and PHP code especially always use exception handling to test for timeout, server down, broken links etc etc.

getmea · #12 (**permalink**) 12-14-2007, 12:03 PM

I can put in exception handling until the cows come home...but it won't fix the "too many connections open" issues...it will only trap it.

If I can't establish a connection and run queries, I don't have any products to show on my site. Mind as well shut down.

I tried to talk to the web host again yesterday (twice) but hung up after 55 minutes of holding.

DaveSawers · #13 (**permalink**) 12-14-2007, 06:46 PM

Time to switch hosting perhaps...

Shouldn't take more than an hour to switch to another hosting service (and 24 hours for the DNS change to propagate).

I'm sure you can find hosting locally, but if you want me to set you up on mine, drop me an email - david@activeminds.ca

getmea · #14 (**permalink**) 12-14-2007, 10:50 PM

Finally got an answer from the web host. It appears they took my advice and looked at logs (imagine that!). They found a blog web site on my same server which had been hogging a lot of mySQL connections. They're opinion is this sort of web site should be on a dedicated server. So they suspended the site.

According to their estimates, 200 connections and 50,000 queries are the max per hour. I'm not sure if these are at a "per server" or "per web site" threshold.

I'm up and running now...

thanks all for your suggestions!

Getmea

kgun · #15 (**permalink**) 12-15-2007, 08:27 AM

Quote:

Originally Posted by DaveSawers

Time to switch hosting perhaps...

Shouldn't take more than an hour to switch to another hosting service (and 24 hours for the DNS change to propagate).

I'm sure you can find hosting locally, but if you want me to set you up on mine, drop me an email - david@activeminds.ca

Dave interesting.

How fast are you to upgrade to newer versions of PHP?
How easy is it for you to install PHP processers that need recompilation of PHP?
What about XML technology related libraries and processors? Scroll down on RedCarpetRank - Webs outstanding sites, resources and articles
Do you supply or have an intention of supplying dedicated servers.
How stable are your servers? Do you use UPC batteries or diesel aggregats in case of electricty break?

kgun · #16 (**permalink**) 12-15-2007, 08:32 AM

Quote:

Originally Posted by getmea

According to their estimates, 200 connections and 50,000 queries are the max per hour. I'm not sure if these are at a "per server" or "per web site" threshold.

I'm up and running now...

thanks all for your suggestions!

Getmea

That is exactly why you need the functionality in PHP 5.0 and preferably higher. Not to say if you need PHP XML parsing / functionality on your site. Then you need the last version of PHP.

I have three different hosters.

Cheap in the USA. Very slow to upgrade, but good enough now for some sites.
More expensive in Denmark. The say I have only to ask what I need, and they will install it.
Expensive in Norway. Reliable, stable and are normally up to date on PHP.

DaveSawers · #17 (**permalink**) 12-15-2007, 10:39 AM

Quote:

Originally Posted by kgun

Dave interesting.

How fast are you to upgrade to newer versions of PHP?
How easy is it for you to install PHP processers that need recompilation of PHP?
What about XML technology related libraries and processors? Scroll down on RedCarpetRank - Webs outstanding sites, resources and articles
Do you supply or have an intention of supplying dedicated servers.
How stable are your servers? Do you use UPC batteries or diesel aggregats in case of electricty break?

They're not my servers, I don't manage them directly and I can only get new stuff installed on them by asking the managers directly. This is not a quick process.

I do however have just enough control to setup hosting without having to contact anyone else. The servers are in a collocation centre on the internet backbone for performance reasons.

parolespedia · #18 (**permalink**) 12-19-2007, 07:56 AM

I would say more than 95% PHP driven with a mySQL connection.