By Shhh on

Hi everybody

I hosted my site a month ago I am totally happy with drupal. But recently (from two days) I am getting some problems while accessing my site (some times) ...... when I open my site I am getting following error

Warning: mysql_connect(): User habits6_habits has already more than 'max_user_connections' active connections in /home/habits6/public_html/includes/database.mysql.inc on line 31
User habits6_habits has already more than 'max_user_connections' active connections

Please help me regarding this issuse and let me know what could be the problem.

Thanks for drupal help

Comments

Shhh’s picture

Shhh commented

when i mailed to my hosting service guys ...the following are the reply.....please let me know what to do next:

The mysql service was loaded with a ton of requests on your account in the LOCKED status. I have restarted the sql service so it should work again but there is some sort of error in your code you need to correct.

Thanks in advance

sami_k’s picture

sami_k commented

Try turning on the cache so that anonymous users get a cached version of the page. Furthermore edit your robot.txt to indicate fewer bots at a time may make site requests.

--
Please read the handbook, search the forums, then ask...
http://drupal.etopian.net (Drupal Support)

Shhh’s picture

Shhh commented

Thanks for the fast reply, I am sorry I did not search the forums before posting this topic.

romca’s picture

romca commented

I have experienced the same problem yesterday - deadlock - an the site was completely dead
Reading/surfing I found that InnoDB tables can have this problem. One of the causes is the very high traffic and loss of connections (but deadlock is even something more different). Combined you will easily reach the max_users_connection limit and this will render your site useless.

some hints
- use cache, if you can
- use throttle
- use bad behaviour module (as this should shield you against spammers)
- set interactive_timeout so that your connections expire quickly

I have made these changes to the database.mysql.inc - to the function mysql_connect

the old version:
$connection = mysql_connect($url['host'], $url['user'], $url['pass'], TRUE) or die(mysql_error());

the new version:
$connection = mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, MYSQL_CLIENT_INTERACTIVE) or die(mysql_error());
mysql_query("SET SESSION interactive_timeout=120", $connection);

to get the timeout run phpinfo() and watch for the connection timeout inside your mysql, and also timeout of your apache process (or IIS if you use IIS)

basically, after the timeout, you script will be cut off, BUT the opened connection will be there eating out of your max_users_connections. If you set the interactive_timeout limit, it will be closed automatically (note: mysql has a global wait_timeout limit but as a default there is oftten 28800 = 8 hours, the connection will expire in 8 hours!)

hth

reggie75’s picture

reggie75 commented

Thanks, ive been having this problem with our host for a long time too. And we are going thru the infamous tussle of "not my problem, yours". Im excited about trying out the the session interactive_timeout fix mentioned above.

My server gives this problem every 2 days (surprisingly!) . If it doesnt give this prob in a week's time, I think ill know my problem is fixed.

Thanks,
Reggie

reggie75’s picture

reggie75 commented

So far I have NOT had the max_users error yet- which makes me want to believe this fix works.
However, im seeing some warnings on some cache-related SQL queries (eg: duplicate entry exists on INSERT INTO CACHE ..) .

maybe i just need to clear my cache table after this fix (though i dont know why)

but all in all, the site is working well and i dont see any breakages PLUS no error as well.

great so far!
thanks,
reggie

reggie75’s picture

reggie75 commented

i began having the "max users exceeded" problems again a week after i added this fix.
seems this fix delays the error, but i dont think it eliminated it.

i finally switched providers. im at liquidweb now.
its been about 10 days and i havent had this problem ever since. (almost similar shared hosting package as my previous isp)

reggie75’s picture

reggie75 commented

i got this same problem a month after i moved to the new provider.
cant figure out whats causing it yet, but the fix above does delay the problem from coming by about 10 times.

im keeping the fix, and investigating further in other modules to find the cause of the mysql overload.