SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 49
  1. #1
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:B (i have not found out for sure yet, but does PEAR:B 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. #2
    Non-Member bitbytes's Avatar
    Join Date
    May 2006
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am closing the connection: $db->disconnect(); (PEAR)

  4. #4
    Non-Member bitbytes's Avatar
    Join Date
    May 2006
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    Non-Member bitbytes's Avatar
    Join Date
    May 2006
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why dont you try the same query in "Server Management" forum. m not sure it is helpful or not.

  7. #7
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    Non-Member bitbytes's Avatar
    Join Date
    May 2006
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    Non-Member bitbytes's Avatar
    Join Date
    May 2006
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #10
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bitbytes
    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.
    are you sure about that? So if i have a script like this:

    Code:
    connect
    ..
    query1
    query2
    query3
    
    disconnect
    it should really be:

    Code:
    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. #11
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bitbytes
    when your creating connection with mysql you are not closing the connection after your work done. i.e. dont forgot 2 write mysql_close();
    Too bad that this doesn't matter, since mysql_close() is automatically called at the end of each script.

    Quote Originally Posted by toasti
    it should really be:

    Code:
     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...
    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. #12
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bitbytes
    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.
    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. #13
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:B.

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

    my connection looks like this:
    Code:
    $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

  14. #14
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    [quote]
    mysql.allow_persistent = off
    [qoute]
    ..so i doubt i'm using persistent connections!

  15. #15
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #16
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #17
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by toasti
    is this correct?
    yes!
    Quote Originally Posted by toasti
    scripts which are called using <img> tags (pseudo cron) count as new scripts
    If you have to access the database when someone requests the src of an <img> element there is something wrong with your logic.

  18. #18
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is there anything which might cause mysql to act funny. so that it perhaps is not closing connections properly or something?

  19. #19
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    <img src = "path/to/php/script/to/run/in/the/background.php" width=0 height=0 />

  20. #20
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by toasti
    sorry, maybe i wasnt too clear on this. talking about when one does this:
    Ugh!

  21. #21
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's actually quite normal to access the database with a script that gets referenced in an img tag. For example you'd use it if your host doesn't give you access to cron jobs or if you want access restrictions on photos.

  22. #22
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...thinking about that. if you store images in a database (which i dont), then you would actually need a new connection for each picture cause u usually show them with a php script like:
    Code:
    	
    	$sql = "select img from images where ID = ?";
    	$rs = $db->query($sql,array($img_id));
    	$row = $rs->fetchRow(DB_FETCHMODE_ASSOC);
    	header('Content-type: image/jpeg');
    	$im = imagecreatefromstring($row['notice_img']);
    	imagejpeg($im);
    and
    Code:
    <img src = "showdbimage.php?imgid=<?php echo $img_row['ID']; ?>"
    ..so if u had a gallery showing 20 pics / page u would then be looking at over 20 connections/request! ..worth considering!

  23. #23
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ugh!
    why's that so bad?

    ..thanks for backing me up Icheb

  24. #24
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by toasti
    if you store images in a database (which i dont),
    Only a moron would!

  25. #25
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by toasti
    ...thinking about that. if you store images in a database (which i dont)
    I was actually refering to access restriction, not storing images in the database. You store the path to the file in the database and then use readfile() to push the image to the user if he has access.
    And btw, vBulletin uses a technique similar to this (only not for access restriction) for the user avatars.

    bokehman, thanks for that insightful comment.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •