Problem with too many connections to SQL database

New Member Joined: May 25, 2008 Posts: 13

As we from time to time got the error message:

Quote:

MySQL is Reporting
Too many connections

We sent a message to our host stating our problem and asking what could be done about this. They told us they set the amount of connections allowed to 300 if I'm not mistaking?.. And told us this is enough and that the problem is within the script..

Now I found that quite hard to believe as so many ppl use this script, I figure a bug like this would not be unnoticed.. Anyway here is the error log they sent us:

Quote:

[29-May-2008 23:16:29] PHP Warning: Unknown: 4 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:16:32] PHP Warning: Unknown: 3 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:16:32] PHP Warning: Unknown: 4 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:16:32] PHP Warning: Unknown: 3 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:16:35] PHP Warning: Unknown: 4 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:16:36] PHP Warning: Unknown: 6 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:16:39] PHP Warning: Unknown: 3 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:16:39] PHP Warning: Unknown: 3 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:16:47] PHP Warning: Unknown: 4 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:17:00] PHP Warning: Unknown: 10 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:17:02] PHP Warning: Unknown: 3 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0
[29-May-2008 23:17:06] PHP Warning: Unknown: 4 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0

Is this something that we could have done wrong during the setup of the website? We are just running the standard release (2.20.01) offcourse with slightly modified theme but nothing special, only modified tables/images. Sentinel is up to date aswell.

Does anybody have an idea on where to look to solve this problem?

Grtz

Bongo

Posted: Thu May 29, 2008 5:48 pm

We looked at the free_result thing a while back. If I recall correctly, when a script finishes all result sets are freed anyway. So, a decision was made to "deprecate" the use of free_result because it really just results in extra overhead.

What PHP version and what MYSQL version are you running? Who is the host? Are there other *Nuke sites running on that host? How many concurrent users are you running? I'm wondering if just perchance you could have a lot of bots or search engines hammering at your site. If you look at IP tracking do you see anything there about who is accessing your site and whether they are doing anything "strange"?

Regular Joined: Sep 26, 2007 Posts: 60

I believe you can just create an array of users, here is what I have in my config.php file:

Code:




$db_user_array[] = "user1"; //enter 1st user name


$db_user_array[] = "user2"; //enter 2nd user name


$db_user_array[] = "user3"; //enter 3rd user name


$db_user = $db_user_array[ rand( 0, ( sizeof($db_user_array) -1 ) ) ];

If you have the same problem as me, each user is allowed a certain amount of connections per hour. Your hosting provider may be throttling that amount. Just make sure they all have the same password.

Posted: Fri May 30, 2008 6:55 am

@ fkelly:
As far as I know we are the only ones running a ravennuke website on this host. Don't know about the concurrent users though but the 300 connections limit only applies to our database and not the other websites on the same server though...

PHP version: 5.2.6
SQL: 5.0.58 (if I'm not mistaking)

But when following your lead on checking the hits, well... I was always just checking regular statistics (the nuke stats module) and never saw crazy amounts of hits... But now checked the sentinel logs and gave me +/- 45.000 hits from IP's belonging to one of our users. While the average user had about 600-900 hits... What I noticed was that 99% of these hits were directed at the RSS feed that came with the latest release. So we now deactivated the RSS, and I sent a pm to the user (he's not trying to hack us or sth I know that for sure), my guess is though that he is using some bad kind of RSS reader... Correct me if I'm wrong..

@ adaykin:

That's indeed very handy and may be a solution for us, but our provider would probably notice we're just switching users I think :-s I'll keep the script though in case we don't get it sorted out... But I just hope it was this RSS stuff..

Tnx for your replies,

Bongo

Posted: Fri May 30, 2008 7:03 am

There is definitely a difference between "number of connections" vs. "number of queries exceeded for user". Definitely cannot mix the two. What adaykin is highlighting is the latter, where a single user has exceeded its number of allowed queries per some defined time period (usually 50K per hour). So, this is NOT the same as your "number of connections" issue.

The really odd thing is that these would have to be concurrent sessions (hits). As fkelly has said, the connections are cleared up upon completion of the PHP script UNLESS there is some kind of connection persistence going on. Unfortunately, although I have heard that persistence is possible, I don't know how to go about checking if its in use or whether it requires code changes to even "enable". We definitely have NOT placed any sort of persistence code into RN.

Are these 45K hits mostly occurring in large bursts? I'.e., simultaneous?

So, definitely some things to check on.

Posted: Fri May 30, 2008 7:12 am

Some hosts do use mysql user throttling. The official blurb is that "it prevents misuse" the *real* reason is usually because they have oversold accounts on the server and it is a way for them to squeeze a bit of extra income out of each server or, in one hosts case, the use a seperate mysql server of older specifications and need to use a throttle because the server simply isn't up to the job.

Bottom line is either switch hosts (one that does not need to use a throttle because their servers are of decent spec) or use the multiple mysql user approach that adaykin mentioned.

Accessing an RSS feed shouldn't be a problem (depending on which one it is) as it is basically static data, so a DB read is not required. Having said that, I would not expect one user to be hitting one url that hard so I'm wondering if he has added the feed to his browser bar so it is constantly refreshing wherever he goes on the 'net.

Your hosts control panel should have extensive logs, so it would be well worthwhile checking those too.

Posted: Fri May 30, 2008 7:21 am

I could be mistaken, but I think we're talking about two different problems here. See my previous post and explanation. Just my hunch...

Posted: Fri May 30, 2008 7:21 am

I didn't point you to the fix Adaykin listed because that didn't seem to be your problem. As M. points out there is a difference between concurrent connections and number of mysql queries. I'm just thinking, depending on your site and what you have activated, there can be 100 or more SQL calls for any page load. If the RSS feed is loading the home page or anything other page in your site it could be incurring this overhead each time and driving your host nuts. I'd tell that user to deactivate his feed and see if the problem goes away.

Posted: Fri May 30, 2008 7:36 am

Ok I just looked up already some of the stuff you told me, to begin with:

The accessing of the RSS feed from this one IP is happening every second for several minutes... Safe to say that is not quite normal..

And I just looked up the settings from our host (as we already sent him an email in the past) and he replied to us that the settings they are using are:

Code:




max_connections = 300


max_user_connections = 300


table_cache=1200

But even if those are too low, I'm wondering why it is giving this error..

Is there a way that I could turn on some error reporting so I could view in the browser rightaway on what page the error shows up? Also when I googled this error it never showed this error on the website when it came up with a hit, although when opening this page in cache it did show the error on the bottom. But then again, when checking the website log, I didn't see any searchbots that were overdoing it, just a few per hour or something, nothing special..

Also when I checked the acceslogs after the website gave this too many connections error, I didn't see anything special for the few previous connections. I'll look a bit further back next time it happens..

Tnx,

Bongo

Posted: Fri May 30, 2008 8:01 am

Additional information:

I just received this way too long list from my host (I'll try to summarize the most important/out of the ordinary stuff):

4,075,851 queries since the starting of the server (up and running 17hours)

Received: 485 MB
Sent: 661 MB

max. concurrent connections 101

82,77% were Select queries
10,97% were Update queries

Select_full_join 112 k The number of joins that do not use indexes. (If this value is not 0, you should carefully check the indexes of your tables.)

Hope this is of any use to you, if you need more information, plz let me know

Thanks

Bongo

Involved Joined: Jul 25, 2006 Posts: 354

I keep getting this error page

Where can I change the text it says :S

There seems to be a problem connecting to the MySQL server.

MySQL is Reporting
User blah has already more than 'max_user_connections' active connections

If you are the System Administrator and installing this for the first time, did you remember to create your database first?

There seems to be a problem with the System Configuration Table - it's missing.

If you are the System Administrator and installing this for the first time, did you remember to run the INSTALLATION/installSQL.php file?

If you are not the System Administrator, please report this to the Administrator and/or Web Master ASAP.

We will be back as soon as possible.

Posted: Thu Jul 23, 2009 9:34 pm

Change in php.ini.. or find a new host Smile

Posted: Thu Jul 23, 2009 11:31 pm

The host has been getting ddos'd so my site displays this page sometimes

just the text and bullshit on it i want to change it

where is php.ini

Posted: Fri Jul 24, 2009 3:04 am

The error message is generated from db/db.php
However, if your host is being ddos'd I would expect to see the error more consistently. Double check with them that they are not using a mysql connection limit as many cheaper hosts do.