Page 1 of 1

Could not connect to mySql : Too many connections

Posted: Sat Sep 13, 2008 3:19 pm
by pagegen
hey guys, i keep getting this error,
Could not connect to mySql : Too many connections
whats the best way to fix it?

i only have connection to database once

Code: Select all

// Connect to database
        $dbc = mysql_connect ('Localhost','username','password') OR die('Could not connect to mySql : ' . mysql_error());
        mysql_select_db (databseName) OR die('Could not connect to mySql : ' . mysql_error());  
 
 
and i am doing queries like this

Code: Select all

//----------------------------------------------------------------------//
                // SQL query, to show all items
                if ($action == '1') {
                $query = "SELECT * FROM TblItems WHERE item = '$item'";
                $result = mysql_query ($query);
                
                Echo '<table style="width: 100%">';             
                $i=2;
                echo '<tr>';
 
                while($row = mysql_fetch_array($result,MYSQL_ASSOC))
                {
                if ($row[netOption] == 1 && $row[qun] >= 1){
                $imageToShow = '<a href="addItem.php?item=' . $row[id] . '" ><img alt="image of this product" src="images/buy.gif" BORDER=0 width="70" /></a>';
                }
                elseif ($row[netOption] == 0 && $row[qun] >= 1){
                $imageToShow = '<img alt="only in store" src="images/buy2.gif" BORDER=0 width="70" />';
                }
                else {
                $imageToShow = '<img alt="only in store" src="images/buy3.gif" BORDER=0 width="70" />';
                }
                
                if($i%2==0){
                echo '</tr><tr>';
                }
                echo '<td>';
                
                Echo '<table style="width: 100%">';
                Echo '<tr><td><a href="images/' . $row[image] . '" target="-Blank"><img alt="image of this product" src="images/' . $row[image] .'" BORDER=0 width="70" /></a></td><td style="font-size: 0.9em"><a href="products.php?action=2&item=' . $row[item] . '&product=' . $row[id] . '">' . $row[name] . '</a><br>'. substr($row[dec], 0, 73) .'... <a href="products.php?item=' . $row[item] . '&product=' . $row[id] . '">more</a></td></tr>';
                Echo '<tr><td></td><td align=Right><b>£' . $row[price] . '</b><br>' . $imageToShow . '</td></tr>';
                Echo '</table><hr>'; 
                
                echo '</td>';
                $i++;
                }
                echo '</tr>';   
                Echo '</table>'; 
        
                }
        
                //----------------------------------------------------------------------//
                // SQL query, to show a product
                if ($action == '2') {
                $query = "SELECT * FROM TblItems WHERE item = '$item' && id = '$product'";
                $result = mysql_query ($query);
 
                while($row = mysql_fetch_array($result,MYSQL_ASSOC))
                {
                Echo '<font size="5" face="Algerian"><a href="products.php?item=' . $row[item] . '&product=' . $row[id] . '">' . $row[name] . '</a></font><br><br>';
                Echo '<a href="images/' . $row[image] . '" target="-Blank"><img alt="image of this product" src="images/' . $row[image] .'" BORDER=0 width="300" /></a><br>';
                
                
                
                Echo '<br>';
                }       
                }       
 
what am i doing wrong guyes?
isit too many queries

Re: Could not connect to mySql : Too many connections

Posted: Sat Sep 13, 2008 8:20 pm
by Stryks
I believe that this error is triggered by too many simultaneous connections to the server.

What I mean is, your page loads and the script connects. That gives you 1 server connection. Now, if 1000 people view the same page at the same time ... you now have 1000 connections. From memory (which is admittedly pretty ordinary at times) I think the default is 100 simultaneous connections.

Anyhow, what complicates this on shared hosting is that under some configurations (pretty much all shared hosting I have been on) many other sites are also contributing connections towards the maximum.

There are things that you can do, in terms of configuration ... but it depends on your development environment. Shared hosting will generally not budge, but if you own your server and it has the grunt, you could up the connection limit or reduce the wait_timeout setting.

But if you're on shared hosting, your best bet will be to let them know, and if the problem persists, ask to be moved to a different server or you'll be moving hosts.

But yeah, I'm not the most knowledgeable on this topic. Hopefully someone else can add some more to this.

Cheers.

Re: Could not connect to mySql : Too many connections

Posted: Sun Sep 14, 2008 6:50 am
by pagegen
thanks for your help, i will send a email to my host 2day and see what they say... :banghead:

Re: Could not connect to mySql : Too many connections

Posted: Sun Sep 14, 2008 9:01 am
by Bill H
You might try using persistent connections:

Code: Select all

$Link = mysql_pconnect($Host, $User, $Password);
This looks for an existing conncetion using the same parameters and, if one is found, uses that connection rather than opening a new one. If you do this, dont use mysql_close() at the end of the script. The server has its own parameter for closing these out after a period of non-use.

If you are on a shared host, they differ in their preference. Some actually prefer that you use persistent connections. It dependc on how their Apache is configured.

Re: Could not connect to mySql : Too many connections

Posted: Sun Sep 14, 2008 11:17 pm
by Stryks
Actually, I started witing into my post that persistant connections could be a solution. I seem to remember reading somewhere that it was a possible solution.

But as it's something that I wasn't to clear on, I looked in the manual and saw this under pconnect();
Using persistent connections can require a bit of tuning of your Apache and MySQL configurations to ensure that you do not exceed the number of connections allowed by MySQL.
So it looks like you're right on the money there. Best to find out what your host is geared for.

Just keep in mind though that if you use persistent connections, I think it is recommended that you also use mysql_free_result() when you are done with the data returned by your queries, especially for big sets of data.

Either way, your host should be able to let you know what the issue is and the best workaround scenario.

Cheers

Re: Could not connect to mySql : Too many connections

Posted: Sun Sep 14, 2008 11:48 pm
by Bill H
Just keep in mind though that if you use persistent connections, I think it is recommended that you also use mysql_free_result() when you are done with the data returned by your queries, especially for big sets of data.
Well, that would not have anything to do with persistent connections. It's just a function of the memory used by the result set, which has nothing to do with the type of connection. In any case, according to the manual:
All associated result memory is automatically freed at the end of the script's execution.

Re: Could not connect to mySql : Too many connections

Posted: Mon Sep 15, 2008 2:29 am
by Stryks
I believe the two *are* linked. From what I can gather, it would appear that memory used for a large result set can remain between connections, leading to a snowball effect on the servers memory usage.

But, as I have said, this isn't an area I have huge amounts of experience with, so instead of pointlessly trying to argue the ramifications of something that the OP may not use, I'll just drop a few links for the OP to read, should persistent connections be a path they choose to take.

Persistant Connections

mysql_pconnect

mysql_free_result

Don't forget to have a scan through the user contributed notes while you're there. Of course, the advice there is only as good as it's author, but its always good to get an idea of problems and triumphs people have had.

Cheers

Re: Could not connect to mySql : Too many connections

Posted: Mon Sep 15, 2008 9:13 am
by Bill H
Please show me in any of that where the use of mysql_free_result() is recommended when using persistent connections. The result set is data in memory that is not related to the connection other than by the fact that the connection is needed to allow the database to be accessed. The memory used by the result set is released when the script creating the result set is finished executing, and that is true regardless if the type of connection. I see nothing that says that is conditional upon it being a non-persistant connection. The doc on mysql_free_result() says to use it when you are worried about the large use of memory. That would apply while the script is still running, for instance if you are going to create another large data set. I read your three references (again, as I had read them all before) and I saw nothing that said the use of persistent connections meant the mysql_free_result() was more desireable.

As to the one commenter, the result set that was "held open" is one user out of how many(?) and the problem was more likely just a failure of the script to release the memory for reasons that had nothing to do with the persistant connection. Comments by users always need to be taken with a grain of salt. Note that several of the commenters says that persistant connections are "always bad and should never be used," or words to that effect, but you don't seem to take them too seriously.

Re: Could not connect to mySql : Too many connections

Posted: Mon Sep 15, 2008 10:49 pm
by Stryks
The references were not actually posted for you, they were posted for the OP so that they may read further about persistent database functions, as well as the other database function I felt was relevant.

I've stated my sketchy understanding of the potential problem. You have done the same (minus the sketchy :P ), and we clearly don't agree 100% (though if you say it's unrelated, I'll take your word for it. As I've said, I'm certainly no expert on this issue.)

At the end of the day, I'm fine with being wrong. Whatever knowledge I have came from being wrong over and over till I was right. A certain kind of logic suggests that being wrong is a good thing, because it gets me closer to eventually being right. :)

But yeah ... I think it would be more productive to stay with the OP, instead of trying to prove yourself right. I'm sure that if the OP comes back and decides to go with the persistent connections, your help will be very valuable to everyone ... myself included.

Until then, I don't know that all this means that much, that's all.

Cheers.