max_user_connections errors :: Prevention Tips

[GatorShashank]

Hello,

Many of you might have got or noticed the following error on your sites recently.

Warning: mysql_connect(): User cpaneluser_xxx has already more than 'max_user_connections' active connections in /home/username/public_html/script/path/file.php on line xx
User cpaneluser_xxx has already more than 'max_user_connections' active connections

Hostgator implies a 30 concurrent connections per mysql user limit on script to prevent heavy mysql load on the server and to hunt down scripts that use unnecessary mysql connections due to bad coding practices.

These errors can be prevented by making some coding tweaks into your code.

1. Use of mysql_connect() for connections to mysql dbs in your code. The advantage is :

"The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close()."

A detailed explanation can be found at :

http://us3.php.net/manual/en/function.mysql-connect.php

2. Use of mysql_close() explicitly. Although mysql_connect() closes the connections, use of mysql_close() in conjunction with it would make sure that the connection is totally closed.

3. Prevent use of mysql_pconnect(). The disadvantage is :

"The connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect)"

More details at :
http://us3.php.net/manual/en/functio...l-pconnect.php

I am not a php expert or mysql expert in any case. So if any programmer would like to add some more tips & tricks to this, they are always welcome.

[EDIT] The limit is 30 and no longer 25 on all the servers [/EDIT]

[Tomer]

Originally Posted by GatorJoe

Hello,

Many of you might have got or noticed the following error on your sites recently.

Warning: mysql_connect(): User cpaneluser_xxx has already more than 'max_user_connections' active connections in /home/username/public_html/script/path/file.php on line xx
User cpaneluser_xxx has already more than 'max_user_connections' active connections

Hostgator implies a 25 concurrent connections per mysql user limit on script to prevent heavy mysql load on the server and to hunt down scripts that use unnecessary mysql connections due to bad coding practices.

These errors can be prevented by making some coding tweaks into your code.

1. Use of mysql_connect() for connections to mysql dbs in your code. The advantage is :

"The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close()."

A detailed explanation can be found at :

http://us3.php.net/manual/en/function.mysql-connect.php

2. Use of mysql_close() explicitly. Although mysql_connect() closes the connections, use of mysql_close() in conjunction with it would make sure that the connection is totally closed.

3. Prevent use of mysql_pconnect(). The disadvantage is :

"The connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect)"

More details at :
http://us3.php.net/manual/en/functio...l-pconnect.php

I am not a php expert or mysql expert in any case. So if any programmer would like to add some more tips & tricks to this, they are always welcome.

Best Regards,
Joe,
Hostgator.com Technical Support Department.

We all know that the ugly MySQL warning/errors are very ugly and nothing you want your users to see. To prevent the error itself, you can use this:

mysql_connect()
or die ("Woah! It seems the database is being overloaded, please try to access this page later.");

That will help.

- Tomer

[sabre]

I've been noticing this!!
Has this been a recent change, because I haven't noticed it before??
Does this mean 25 connections for each of my reseller accounts??
Or 25 connections total for all my accounts?

What does this mean for us OsCommerce users??
25 connections seems way too limiting for an ecommerce situation!

-Jamie

[Tomer]

Originally Posted by sabre

I've been noticing this!!
Has this been a recent change, because I haven't noticed it before??
Does this mean 25 connections for each of my reseller accounts??
Or 25 connections total for all my accounts?

What does this mean for us OsCommerce users??
25 connections seems way too limiting for an ecommerce situation!

-Jamie

You're correct, that is very low, I think the default was 125?

- Tomer

[Fire]

does this impact the number of users that can be active on a forum???

[GatorShashank]

To answer a few questions asked above :

Has this been a recent change, because I haven't noticed it before??
---> Yes. it has been implemented just some days back.
Does this mean 25 connections for each of my reseller accounts??
---> This is for each mysql user. (Users you add to the databases)
Or 25 connections total for all my accounts?
---> I think the above question answers it all.

I am not sure about oscommerce's mysql connection logic. So, I cannot comment on that.

[Tomer]

Originally Posted by Fire

does this impact the number of users that can be active on a forum???

Yes, you can only have 25 users online with 1 user. You can create other users and every time you connect, check if it's full, and if so, try another user.

- Tomer

Originally Posted by Tomer

Yes, you can only have 25 users online with 1 user. You can create other users and every time you connect, check if it's full, and if so, try another user.

- Tomer

This is only active connections, once a connection is used it should close right away. Which means unless all 25 users have a open connection, you can have 100's of people on the forum. As long as 30 or so people do not click the same exact link, at the same exact time, there would not be a problem.

You get the max_connections error when 25+ connections are sitting idle and not used.

[Fire]

Originally Posted by GatorJay

This is only active connections, once a connection is used it should close right away. Which means unless all 25 users have a open connection, you can have 100's of people on the forum. As long as 30 or so people do not click the same exact link, at the same exact time, there would not be a problem.

You get the max_connections error when 25+ connections are sitting idle and not used.

so does that mean unless on 1 user 25 ppl or more click on the link at once i should be ok?

Originally Posted by Fire

so does that mean unless on 1 user 25 ppl or more click on the link at once i should be ok?

Exactly. Each connection should close after its used. There should never be a connection open long enough to have more than 25 open for a sustained period of time. (If that happens, then its either bad scripting or very high traffic in which you would need a semi or dedicated anyway.)

[Andeavour]

Beware developers using Dreamweaver...

By default Dreamweaver MX will use mysql_pconnect() in it's connection string, as indeed does the default installation of osCommerce. In the last few days I've had 4 of my clients fall foul of this...
In fact I have a support call out at this very moment to get the connections cleared down on Navigator as I've just had another client fall over for this very reason.

[sabre]

I have have about 5 OsCommerce sites on my re-seller account. If anyone comes accross any tips on improving the way OsCommerce handles this problem, I'd love to hear about it!

Thank you,
Jamie

[bodypainter]

This is a major problem.

This limit has made my Gallery 2 installs useless. My sites are pretty much 100% centered around Gallery 2. Therefore, my sites are now pretty much useless.

I am not a PHP coder. I am not going to be digging through my Gallery 2 scripts to change stuff.

What are my options? Unless you're willing to put things back like they were, the only option I see is to find a new host. I am open to suggestions.

Anyone?

If Gallery 2 is using mysql_pconnect() then you will have to go through and change them to mysql_connect() Unless there is a setting somewhere to do that automatically. The only other option with HostGator is to go with a semi-dedicated, or dedicated solution to remove the limit.

[bodypainter]

I just did a string search on my Gallery 2 folder and found 61 files with the string "mysql_pconnect".

Okay, I'm screwed. Time to make a backup and start the friggin' web host search all over again.

Originally Posted by bodypainter

I just did a string search on my Gallery 2 folder and found 61 files with the string "mysql_pconnect".

Okay, I'm screwed. Time to make a backup and start the friggin' web host search all over again.

Did a bit of searching for you on the Gallery 2 and I think you may be able to change this without all that hassle. Take a look at:
http://gallery.menalto.com/index.php...af51505ab4f4dc

Mezon , can you offer up some references to support that claim? It's not that I don't believe you, it's just that I haven't had any problems with pconnect in the past few years, and haven't heard of too many issues from the various commercial sites that I know use them. 
 
Either way, I added a new feature to G2 to allow you to disable them in your config.php. Add this line: 
 
Code:
$storeConfig['usePersistentConnections'] = false; 
 
 
right before: 
Code:
$gallery->setConfig('storage.config', $storeConfig); 
 
 
and we won't use pconnect.

Hopefully this will help you to get it working, we would hate to lose you as a customer!

[hotdog]

Just say you had 26 users click a link at the same time (could be any link as long as it called info from the database), the max connection error would display.

My question is who would get the error message, all 26 users or just the 26th person?

People should also consider banning bots if their scripts are close to the limit now, several bigger bots (google, intokini, msn ...) use several bots at the same time (bot1, bot2, ...) which could use up half of the 25 very easily. If you have access to a forums 'who's online' have a peek at it every now and then and count the search engine bots (spiders) on at one time. Sure they only crawl every 10+ seconds (or should) but get 3 bots from each main search engine and theres an 1 in a thousand chance they all will hit at the same time, and given most forums and things have on average 3 database calls per page, thats 27 hits - you're screwed
If you're running an ecommerce site the chances are you have more database connections per page (depending on the type).
Also if you offer RSS feeds you could use up your limit before anyone acually going to your site.

Think I might join the list of finding a new host too, after all these restrictions, mail, connections, ftp, outdated versions (Imagemavick)

[bodypainter]

Originally Posted by GatorJay

Hopefully this will help you to get it working, we would hate to lose you as a customer!

I had found that but that advice is 8 months old, a lifetime in the development of a beta product and as far as I can determine, it no longer applies.

At the very least, I don't have that line in my config.php file.

But I will ask some questions from the Gallery people. I don't want to move hosts. You guys have been pretty damn good overall until now.

Originally Posted by bodypainter

I had found that but that advice is 8 months old, a lifetime in the development of a beta product and as far as I can determine, it no longer applies.

At the very least, I don't have that line in my config.php file.

But I will ask some questions from the Gallery people. I don't want to move hosts. You guys have been pretty damn good overall until now.

Yes, it is 8 months old. But I also follow/use Gallery, (Ive tried the Gallery 2, but not using it for anything public yet) Im just thinking that if they added it then, its going to be in now. Its a pretty big thing as a majority of hosts do not even let you use pconnect at all. I would try adding the line of code to your config file(as it said) and seeing if that does the trick. Or of course as you said you can go to the forums and find something recent.

Either way, we would also rather you stay with us.

[bodypainter]

Okay, I dropped that line into my config files for my Gallery installs. We'll see what happens. Having read some more on the subject, I am hopeful that this will help.

Thanks for the responses.

[sabre]

I found it in OsCommerce in the config file:
define('USE_PCONNECT', 'false'); // use persistent connections?

This line is in Catalog/Includes/configure.php
and Admin/Includes/configure.php

Mine were all set to "false" which is the default.

-Jamie

[jlgreer1]

I had a "connection refused" error on a Simple Machines Forum earlier today. I wonder if it was this max_user_connections error? The site hasn't even been published yet. If it can't handle admin maintenance, it will be useless.

I need to investigate.

Jeff

[Ste]

I am getting this error on an Invision Forum. There were only 4 users online at the time.

I checked the code and found this:
"persistent" => "0",
Which means persistent connections is already turned off.

What else can we do?

[dbates]

One little "trick" you might want to try out. If you have a script that opens a database connection and then calls another script that opens a database connection, pass the existing connection along instead of opeing a new one. You can eat up connections really quick if you have nested calls to other scripts.

You can also work some connection magic by taking a little more time to prepare your SQL statements. For instance a crafty join not only saves time, it will only use one connection.

Those of you who don't know php from asp....The best bet is to shop around for software that is coded correctly. There's honestly not much need to have more than one connection open per user at a time. If I was actually hitting the 25 connection limit I'd make sure I had lots of flashy ads to pay for all the bandwith I would be using. (Plus I would wonder what the &*$% everyone was doing on my site)

[Hutch]

Here we go again...

Just had two commercial sites go down because of the mysql_pconnect() issue. Have never had this problem before on other hosts or to date with HostGator. Seems like 25 connections is way too restrictive!

But OK, I have replaced all the mysql_pconnect() commands with mysql_connect() yet I'm still getting errors. Will this resolve itself in a few hours after some set timeout???

I HAVE to get these sites working tonight!

Thanks.