Help with 'max_user_connections' in a php class

[rr1024]

Hello all, I think there maybe an easy solution to this problem. Basically on the weekends my site gets pretty busy and I'm on a shared server with max connections set to 500 and I get the following error

mysql_pconnect(): User XXXX has already more than 'max_user_connections' active connections in /home/public_html/users/db.inc.php on line 25

I marked line 25 below, I thought it would be a simple thing to just ourput a temporary server is busy message with the "if" condition I added but the if is totaly ignored, i.e. If I upload this file as is on my test server I would have though it would ALWAYS return the error message. but it doesn't it connects as normally??? I figured I'd do that as a test and if it returned the error message then all I would have to do is put a "!" in front of $this-> in the if condition.

If you can see what I'm doing wrong please feel free to point out my stubidity. 🙂

FYI, I've only been doing php for a year and I've just started to try to learn classes, so I'm a little puzzled.

class CMySQL
{
        var $host, $sock, $port, $user, $passwd, $db, $connected, $link;

function connect()
    {
        if ( $this->connected )
                return;
            if (strlen($this->port)) $this->port = ":".$this->port;
            if (strlen($this->sock)) $this->sock = ":".$this->sock;
line - 25           // $this->link = mysql_pconnect( $this->host . $this->port . $this->sock, $this->user, $this->passwd );
Replace - 25 with            if ( $this->link = mysql_pconnect( $this->host . $this->port . $this->sock, $this->user, $this->passwd ) )
            {
                 $ret  = "<table cellspasing=2 cellpadding=2 align=center style=\"border: 1px solid red\"><tr><td bgcolor=red>";
                 $ret .= "<font color=white><b>Error</b></font>";
                 $ret .= "</td></tr>";
                 $ret .= "<tr><td>";
                 $ret .= "Sorry, the Server is temporarily busy! :-( <b> USE your browers refresh or back button in just few seconds.</b><br>Please, try again later. We appreciate your patience and we very, very, very SORRY for the inconvenience.<br><br>We are experiencing some growing pains and we know this hurts you, so an Error report has been sent ";
                 $ret .= "</td></tr></table>";
                 return $ret;
                 die();
            }
            if ( $this->link )
                $this->connected = TRUE;
            else
                $this->connected = FALSE;
        }

    function select_db()
    {
            return mysql_select_db( $this->db );
}



}

$MySQL                      = new CMySQL;

$MySQL->host                = $db['host'];
$MySQL->sock                = $db['sock'];
$MySQL->port                = $db['port'];
$MySQL->user                = $db['user'];
$MySQL->passwd              = $db['passwd'];
$MySQL->db                  = $db['db'];
$MySQL->connected           = FALSE;


$MySQL->connect();
$MySQL->select_db();

Thanks for any help anyone can provide on this.

[mtmosier]

Are you certain the error message is not being returned? This code doesn't check the return value. The contents of $ret are never outputted. And die() isn't reached because the function returns before it.

[rr1024]

Well, now that you mention it the way you did, I am not sure now...lol

but you did give me an idea by bring that point up.

I wonder if there is a way to do this with a while or for loop or something in php that would do something like this...

for ( something up to a limit of 10 trys)

if ( !$this->link = mysql_pconnect( $this->host . $this->port . $this->sock, $this->user, $this->passwd ) )
{
wait 30 mil seconds
increase count for the "for" loop
}
else
{
     echo "<table cellspasing=2 cellpadding=2 align=center style=\"border: 1px solid red\"><tr><td bgcolor=red>";
     echo "<font color=white><b>Error</b></font>";
     echo "</td></tr>";
     echo "<tr><td>";
     echo "Sorry, the Server is temporarily busy! :-( <b> USE your browers refresh or back button in just few seconds.</b><br>Please, try again. We appreciate your patience and we very, very, very SORRY for the inconvenience.<br><br>We are experiencing some growing pains and we know this hurts you, so an Error report has been sent";
     echo "</td></tr></table>";

}

I think if I can figure out how to do something like this it would be even better because the error is only temporary until the server has an open connection to allow it to connect.

but this is a little beyond my level I think.

[mtmosier]

I think this is what you mean.

$attempts = 10;
$delay = 30000;  //  Delay in microseconds

for ($i = 0; $i < $attempts; $i++)
{
    if ( !$this->link = mysql_pconnect( $this->host . $this->port . $this->sock, $this->user, $this->passwd ) )
    {
        usleep($delay);  //  Not available on windows before PHP 5.0
    }
    else
    {
        break;
    }
}
if ( !$this->link )
{
     echo "<table cellspasing=2 cellpadding=2 align=center style=\"border: 1px solid red\"><tr><td bgcolor=red>";
     echo "<font color=white><b>Error</b></font>";
     echo "</td></tr>";
     echo "<tr><td>";
     echo "Sorry, the Server is temporarily busy! :-( <b> USE your browers refresh or back button in just few seconds.</b><br>Please, try again. We appreciate your patience and we very, very, very SORRY for the inconvenience.<br><br>We are experiencing some growing pains and we know this hurts you, so an Error report has been sent";
     echo "</td></tr></table>";

//  Return false, or exit or something
}

[Roger_Ramjet]

This code is using persistent connections, viz 'mysql_pconnect'

line - 25 // $this->link = mysql_pconnect( $this->host . $this->port . $this->sock, $this->user, $this->passwd );

The error is saying that the user has too many connections and is attempting to exceed the max number allowed.

mysql_pconnect(): User XXXX has already more than 'max_user_connections' active connections

I don't see wher they are being propogated in the code you posted, but somewhere some piece of script is continually opening new connections instead of using the existing persistent one.

Simple fix is to find where the connections are being established and change them to mysql_connect : ie non-persistent. The use of persistent connections is not recommended. They are there for the few cases where you need to process a series of sql queries, usually involving table locking or temporary tables. Read the manual carefully, and the user notes.

If you decide that pconnect is the only thing that wil work, rare, then you must amend your code to test for an existing one before opening a new one, and to release the connection when finished.

[mtmosier]

If you decide that pconnect is the only thing that wil work, rare, then you must amend your code to test for an existing one before opening a new one, and to release the connection when finished.

Sorry to hijack the thread, but I'm curious, having never used pconnect myself. The php manual says this about pconnect:

when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

That would seem to imply that php automatically does the testing for existing database connections for you. But you said you need to look for one yourself. Am I misinterpreting the manual, or you for that matter?

(Of course I avoid pconnect due to the many various other issues noted in the comments in the manual. But thats just me.)

[Roger_Ramjet]

For 'would' read 'should'. The authors expect that you will write a connection function that you call when required, rather than writing the connection code every time you need a connection. Look at the code posted here by experienced PHP proggies and you will see that they all use includes and requires for their db connection scripts and common functions scripts.

If PHP handled t for you then you would not be able to open multiple connections, once you had one it would keep on returning that link, not giving you a new one when you need a new one.

[rr1024]

I don't know why the original author of the code choose to use pconnect???. I would imagine there was a reason but who knows they could have been out to lunch that day.

I'll read up on pconnect and myconnect but I'm sure I will need some advice.

I'll post the whole file this evening when I get home from work.

I'm sure the function that is requesting connection is just below the connection class posted above. Then below that I know the rest is nothing but MySQL insert, delete and update functions, which are used thoughout the rest of the code

[Roger_Ramjet]

General Rule: use mysql_connect. Only think about pconnect if connect does not work in the specific case.

Now it may be that this one does need pconnect, but then it also needs to close the connection somewhere.

[rr1024]

I'm not sure what you mean by if php handled t, I assumed it did because I can't find any function in any of the files that defince t()

I'm a little confuse but I think I sort of understand? This script uses _t() though out the code so since that is the case that is why I'm getting this error? Because someone else comes along and starts using the system and needs a new connection but can't get one???

If it works like that then that would imply you can only have users = max_connections right?

I'll try to re-read your post so I understand when I get home this evening...Thanks

Originally posted by Roger Ramjet
For 'would' read 'should'. The authors expect that you will write a connection function that you call when required, rather than writing the connection code every time you need a connection. Look at the code posted here by experienced PHP proggies and you will see that they all use includes and requires for their db connection scripts and common functions scripts.

If PHP handled t for you then you would not be able to open multiple connections, once you had one it would keep on returning that link, not giving you a new one when you need a new one.

[rr1024]

As you can see I at least temporarily implemented mtmosier code and it does work although I extended the time to 250mil seconds. I tried it on the live server during very busy time but guess what, the message was displayed just like expected. However, above the message appeared the Error again...lol
i.e. mysql_pconnect(): User XXXX has already more than 'max_user_connections' active connections in /home/public_html/users/db.inc.php on line 30
Line 30 this time because of the extra code.

Now I feel a little better because at least they get some message telling them something and of course not to go away..lol

I will try to use myconnect instead of pconnect and will let you know my results.

About closing the connection, I don't see where it ever gets closed?

class CMySQL
{
        var $host, $sock, $port, $user, $passwd, $db, $connected, $link;

function connect()
{
        if ( $this->connected )
                return;
            if (strlen($this->port)) $this->port = ":".$this->port;
            if (strlen($this->sock)) $this->sock = ":".$this->sock;
// line 25 ---- $this->link = mysql_pconnect( $this->host . $this->port . $this->sock, $this->user, $this->passwd );
           $attempts = 10;
           $delay = 2500000;  //  Delay in microseconds
           for ( $i = 0; $i < $attempts; $i++)
           {
                 if ( !$this->link = mysql_pconnect( $this->host . $this->port . $this->sock, $this->user, $this->passwd ) )
                 {
                      usleep($delay);  //  Not available on windows before PHP 5.0
                 }
                 else
                 {
                      break;
                 }
           }
           if ( !$this->link )
           {
                 echo "<table align=center border=\"1\" cellpadding=\"2\" cellspacing=\"2\" style=\"border-color:#9C21A5\"><tr><td bgcolor=\"#9C21A5\">";
                 echo "<font color=white face=\"Arial, Helvetica, sans-serif\"><b>Community Server is Temporaraily Busy!</b></font>";
                 echo "</td></tr>";
                 echo "<tr><td>";
                 echo "Our Community is currently in HIGH DEMAND!<br>USE your browers refresh or back button in just few seconds.</b><br>Please, Please, Please try again. We appreciate your patience and we very, very, very SORRY for the inconvenience.<br><br>We are experiencing some growing pains and we know this hurts you, so an Error report has been sent.";
                 echo "</td></tr></table>";
                 exit;
           }
           if ( $this->link )
                $this->connected = TRUE;
           else
                $this->connected = FALSE;
    }

function select_db()
{
         return mysql_select_db( $this->db );
}



}

$MySQL                      = new CMySQL;

$MySQL->host                = $db['host'];
$MySQL->sock                = $db['sock'];
$MySQL->port                = $db['port'];
$MySQL->user                = $db['user'];
$MySQL->passwd              = $db['passwd'];
$MySQL->db                  = $db['db'];
$MySQL->connected           = FALSE;


$MySQL->connect();
$MySQL->select_db();

function db_connect()
{
        global $MySQL;
        global $BUG_REPORT_EMAIL;
        global $MySQL;

    if ( !$MySQL->select_db() )
    {
        echo PrintErr( _t("_DB_ACTIVATION_FAILED") );
        $msg = "Error in $_SERVER[PHP_SELF]: " . mysql_error();
        db_report_error( $msg);
    }
    return true;
}

function db_res( $query, $error_checking = 1 )
{
        global $BUG_REPORT_EMAIL;
        global $MySQL;

    $res = mysql_query( $query, $MySQL->link );
    if ( $error_checking && !$res )
    {
            echo PrintErr( _t("_DB_ACCESS_ERROR"));
        $msg = "Error in $_SERVER[PHP_SELF]: " . mysql_error() . "\nQuery: '$query'";
           db_report_error( $msg);
}
return $res;
}

function db_report_error($msg){
    global $DEBUG;
    global $BUG_REPORT_EMAIL;

    if ($DEBUG) {
        trigger_error($msg,E_USER_ERROR);
    } else {
    mail( $BUG_REPORT_EMAIL, "Error", $msg);
}
    exit;
}

function db_arr( $query )
{
        global $BUG_REPORT_EMAIL;
        global $MySQL,$DEBUG;

       $res = mysql_query( $query, $MySQL->link  );
       if ( !$res )
       {
           $msg="Error in $_SERVER[PHP_SELF]: " . mysql_error() . "\nQuery: '$query'";
           echo PrintErr(_t('_DB_ACCESS_ERROR'));
           db_report_error( $msg);
       }
       $arr = mysql_fetch_array( $res );
       return $arr;
}

function fill_array( $res )
{
        global $MySQL;

    if (!$res)
       return false;
    $i = 0;
    while( $r = mysql_fetch_array( $res ) )
        $arr[$i++] = $r;
    return $arr;
}

function getParam( $param_name )
{
        if ( !$line = db_arr( "SELECT VALUE FROM GlParams WHERE Name = '$param_name'" ) )
            return false;
        return $line[VALUE];
}

function getParamDesc( $param_name )
{
    if ( !$line = db_arr( "SELECT `desc` FROM GlParams WHERE Name = '$param_name'" ) )
        return false;
    return $line[desc];
}


function setParam( $param_name, $param_val )
{
        $param_val = str_replace_mysql ($param_val);
        if ( !$res = db_res( "UPDATE GlParams SET VALUE = '$param_val' WHERE Name = '$param_name'" ) )
                return false;
    return true;
}


function str_replace_mysql($str)
{
        $option = $str;
        $option_tmp = "";
        $len = strlen($option);
        for ($i = 0 ; $i<$len ; ++$i )
        {
            if ( $i == 0 )
            {
                if ( $option[$i] == "'" )  $option_tmp .= "\\";;
            }
            else
            {
                if ( $option[$i] == "'" && $option[$i-1] != "\\")
                {
                    $option_tmp .= "\\";
                }
            }
            $option_tmp .= $option[$i];
            //$option = str_replace("'","\\'",$option);
        }

    return $option_tmp;
}

function PrintErr($out)
{
        $ret  = "<table cellspasing=2 cellpadding=2 align=center style=\"border: 1px solid red\"><tr><td bgcolor=red>";
        $ret .= "<font color=white><b>Error</b></font>";
        $ret .= "</td></tr>";
        $ret .= "<tr><td>";
        $ret .= $out;
        $ret .= "</td></tr></table>";
        return $ret;
}


if ( !db_connect() )
        exit();

[mtmosier]

You can suppress error messages by putting a @ in front of the function call. So you could use this for line 30

if ( !$this->link = @mysql_pconnect( $this->host . $this->port . $this->sock, $this->user, $this->passwd ) )

I had assumed you were using a custom error handler for your site.

You would normally close your database connection when done with it by calling mysql_close. However, according to the php manual this function doesn't work on connections opened with pconnect. I believe you're simply not supposed to close these.

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()).

[Roger_Ramjet]

Yep. Scratch my earlier post, should have RTFM'ed properly before opening my mouth. What I wrote was what I remember form a book or article somewhere, and like so many of those it was wrong, or my memory of it is anyway.

From what I understand now, PHP does reuse the same connection once open. The spawning of connections is because of concurrent users. As the number of child processes grows to handle the concurrent client connections, so the number of persistent db connections grows. If your site is busy the max connections limit can soon be reached.

from the manual

If persistent connections don't have any added functionality, what are they good for?

The answer here is extremely simple -- efficiency. Persistent connections are good if the overhead to create a link to your SQL server is high. Whether or not this overhead is really high depends on many factors. Like, what kind of database it is, whether or not it sits on the same computer on which your web server sits, how loaded the machine the SQL server sits on is and so forth. The bottom line is that if that connection overhead is high, persistent connections help you considerably. They cause the child process to simply connect only once for its entire lifespan, instead of every time it processes a page that requires connecting to the SQL server. This means that for every child that opened a persistent connection will have its own open persistent connection to the server. For example, if you had 20 different child processes that ran a script that made a persistent connection to your SQL server, you'd have 20 different connections to the SQL server, one from each child.

Note, however, that this can have some drawbacks if you are using a database with connection limits that are exceeded by persistent child connections. If your database has a limit of 16 simultaneous connections, and in the course of a busy server session, 17 child threads attempt to connect, one will not be able to.

So my gut instinct is that I would only use a persistent connection if web server and db server where on seperate boxes with a slow link. Otherwise they have no advantages.

[rr1024]

Thanks that did raise my level of understanding of this, I did read though my_connect and my_pconnect last night but your explaination clarified it. Thanks very much.

My Test Results for using my_connect, I went ahead and replace the my_pconnect with my_connect on my test server and it seemed to work from the user side of the script.

However, when I tried to login to the admin side I got sql errors saying it could not find some table in the data base and I think it is the way they did the login portion of the script.
Login in this case is index.php but all the admin functions are in the index.php too.
I'm going to poke around with this later tonight.

Thanks for the @, you know I was reading the php manual on the pconnect and it stated somewhere in there about the @ and it didn't even dawn on me....

I like to call this "having a senior moment" 🙂

Thanks guys, you are a great help!!!!