MySQL error: User has already more than 'max_user_connections' active connec

PHP
1

I am getting this error when trying to connect to my database:
User has already more than ‘max_user_connections’ active connections. as a result, my website has been falling over sporadically on a daily basis, and it is starting to look really bad.

My site is hosted on a shared host. I am not sure exactly how many ‘max_user_connections’ is, but before i approach them, i just want to make sure the problem is not on my side.

This is how i manage connections to the database. I define a connection in config.php. This is stored in a variable. this variable is used by all my scripts.
In the footer of all scripts i close the connection. I am using PEAR::DB (i have not found out for sure yet, but does PEAR::DB maybe use pconnect as a default? …apparently that can cause problems).

As i understand it. every page require_once’s config.php. This would make a connection…only once per page/person…and then closes it ($db->disconnect()). every page calls another page which does tracking (using pseudo-cron). this would open and close another connection. There are also 2 adverts on a page which use phpadsnew. So they would open another new connection each, so essentially there are 4 threads running per request.

So at the very worst there could be 4 connections running / page / person. My site is not huge. It usually had about 10 concurrent users at a time - 30 at a max.

So i guess there could be as many as 120 concurrent connections to the database…does that sound bad?

I thought it could be possible that i might have some-how managed to put a connection in a loop or something silly like that, but according to the phpmanual this wouldnt really cause problems:

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters.

(i am taking this to mean that each thread still opens a new connection - hence 4 connections / page)

So from that it sounds quite unlikely that the problem would be from a faulty script. Is there a better way that i should be connecting. is it possible that phpadsnew is causing problems? or is the problem with an ill configured mysql, or just too many people on a shared host?

What can i do? cause this is starting to get really bad!

PS: all these problems started when my hosts swapped a hard-drive on the server.

The connectivity has also gone down giving the following errors:
Lost connection to MySQL server during query
Can’t connect to local MySQL server through socket
Too many connections

please help!

2

when your creating connection with mysql you are not closing the connection after your work done. i.e. dont forgot 2 write mysql_close();

3

I am closing the connection: $db->disconnect(); (PEAR)

4

Check your servere setting , if your are using windows 2000 pro then it supports some 2000 connections(not sure abt count). so check the settings of server

5

im on a linux server. shared host, so i dont have access to too many of the server settings.

i am unlikely to be the only one connecting to the mysql server on this machine, so i would assume that the number of connections which would be allotted to my account would be considerably less than 2000.

6

why dont you try the same query in “Server Management” forum. m not sure it is helpful or not.

7

I have got two error messages.
one reads: Too many connections
the other:
User has already more than ‘max_user_connections’ active connections

Does the first one mean that there are just simply too many connections to mysql on the server, and the second that my user account has opened too many connections?

8

Too many connections -------- simply too many connections to mysql on the server
and m not sure about second sounds related to the first query

9

Everytime u fires the query it connects to the servere so in this case after every connection we need to close the connection with the servere , else it leads to the similar problem that u are facing.

10

are you sure about that? So if i have a script like this:


connect
..
query1
query2
query3

disconnect

it should really be:


connect
query1
disconnect

connect
query2
disconnect

connect
query3
disconnect

???

I am pretty sure the code in the first would be right? because all the queries use the same $db resource…

11

Too bad that this doesn’t matter, since mysql_close() is automatically called at the end of each script.

You don’t need that, he doesn’t know what he’s talking about.

Do you happen to use persistent connections? This is often the primary reason why people get the kind of error you get.

12

You are talking absolute rubbish. If mysql_connect() were to be called 500 times by one script only one connection would ever exist unless mysql_connect() were to specify different hosts… and since this type of connection is not persistent no mysql_close() is necessary.

Use phpinfo() to check for any active persistent connections.

13

Too bad that this doesn’t matter, since mysql_close() is automatically called at the end of each script.

…i thought that was the case.

are you using persistent connections

I dont think so. I am using PEAR::DB.

I havent specified either way. from what i can tell PEAR uses normal connections. but i might be wrong.

my connection looks like this:


$dsn = "mysql://$username:$password@$host/$db_name";
$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die(header("location:".HTTP_ROOT."/errors/db.php"));
}

ironically /errors/db.php is becoming one of the most popular pages on my website :frowning:

14

[quote]
mysql.allow_persistent = off
[qoute]
…so i doubt i’m using persistent connections!

15

You need to either upgrade your hosting package or change hosts altogether then. Your site is just too big for your current hosting package, and judging by what you said at the beginning this is more due to the fact that your host is very restrictive.

16

is this correct?

if a connection is automatically closed at the end of a script, and no matter how many connections you open from a script as long as they have the same arguments no new connection is opened. scripts which are called using <img> tags (pseudo cron) count as new scripts, and therefore will open new connections. THEN: provided all scripts connect to the database: the number of db connections cannot be greater than: the number of scripts running.

If that is the case, then what i stated in my first post should be right, and i should get no more than a max of 90 concurrent connections, but mostly around 30. (also, i have now closed the main connection before calling the tracker file, so there will be no more than 3 concurrent connections / request, and prob less because the phpads scripts should run quckly and close their connections quickly)

Am i correct in thinking that that is a reasonable number of connections, even for an account on a shared host?

Just found out that my host allows a max of 200 concurrent connections, although phpmyadmin says the following:
max. concurrent connections 40
max connections 500
max user connections 15

…i’m not really sure what to make of all of that though…

17

yes!

If you have to access the database when someone requests the src of an <img> element there is something wrong with your logic.

18

is there anything which might cause mysql to act funny. so that it perhaps is not closing connections properly or something?

19

If you have to access the database when someone requests the src of an <img> element there is something wrong with your logic.

sorry, maybe i wasnt too clear on this. talking about when one does this:


<img src = "path/to/php/script/to/run/in/the/background.php" width=0 height=0 />
20

Ugh!