www.webdeveloper.com
Results 1 to 11 of 11

Thread: Too many connections error

  1. #1
    Join Date
    Apr 2014
    Posts
    8

    Too many connections error

    Hello,

    The following script is the only code I have on my small site.
    I am getting too many connections error and it's definitely not down to too much traffic nor is any known error occuring with the script.
    The error seems to occur at random times after flushing.

    I don't think this script is causing the error, but if it's not then what could be?

    Please double check below:

    PHP Code:
    <?php
    $mysqli 
    mysqli_connect("localhost""root""""test");

    if(
    mysqli_connect_errno())
    {
        
    printf("Connect failed: %s\n"mysqli_connect_error());
        exit();
    }
    else
    {
        
    $sqldate "SELECT title,datetime,sport,venue FROM events ORDER BY datetime ASC";
        
    $resdate mysqli_query($mysqli$sqldate);
        
    $sqlsport "SELECT title,datetime,sport,venue FROM events ORDER BY sport ASC";
        
    $ressport mysqli_query($mysqli$sqlsport);
        
    $sqlvenue "SELECT title,datetime,sport,venue FROM events ORDER BY venue ASC";
        
    $resvenue mysqli_query($mysqli$sqlvenue);
        
    $sqllatlng "SELECT name,lat,lng FROM venues";
        
    $reslatlng mysqli_query($mysqli$sqllatlng);
        
    mysqli_close($mysqli);
        
        
    $outputdate = array();
        
    $outputweek = array();
        
    $todaysDate = new DateTime(date('Y-m-d H:i:s'));
        
    $weekFromNow = new DateTime(date('Y-m-d H:i:s'));
        
    $numberOfWeeks 1;
        
    $weekFromNow->modify('+'.$numberOfWeeks.' weeks');
        
        if(
    $resdate)
        {
            while(
    $checkEventsDate mysqli_fetch_array($resdateMYSQLI_ASSOC))
            {
                
    array_push($outputdate$checkEventsDate);
                
    $tempDate = new DateTime($checkEventsDate['datetime']);
                if((
    $tempDate >= $todaysDate) && ($tempDate <= $weekFromNow)) {
                    
    array_push($outputweek$checkEventsDate);
                }
            }
        }
        
        
    $outputsport = array();
        
        if(
    $ressport)
        {
            while(
    $checkEventsSport mysqli_fetch_array($ressportMYSQLI_ASSOC))
            {
                
    array_push($outputsport$checkEventsSport);    
            }
        }
        
        
    $outputvenue = array();
        
        if(
    $resvenue)
        {
            while(
    $checkEventsVenue mysqli_fetch_array($resvenueMYSQLI_ASSOC))
            {
                
    array_push($outputvenue$checkEventsVenue);    
            }
        }
        
        
    $outputlatlng = array();
        
        if(
    $reslatlng)
        {
            while(
    $checkEventsLatLng mysqli_fetch_array($reslatlngMYSQLI_ASSOC))
            {
                
    array_push($outputlatlng$checkEventsLatLng);    
            }
        }
    }

    $output = array($outputdate,$outputsport,$outputvenue,$outputweek);

    for(
    $i 0$i count($output); $i++)
    {
        for(
    $j 0$j count($output[$i]); $j++)
        {
            
    $date date_format(new DateTime($output[$i][$j]["datetime"]), 'g:ia \o\n l jS F Y');
            
    $output[$i][$j]["datetime"] = $date;
        }
    }

    array_push($output$outputlatlng); 

    print(
    json_encode($output));

    ?>

    Please note this code is on a live server, I just replaced the connection text with local.

    Thanks for reading!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,633
    Does the live code by any chance use a "p:" in front of the host name parameter to mysqli_connect, specifying a persistent connection? In theory it shouldn't really be an issue, but you could try removing it if it is there and see if that makes a difference. For that matter, I suppose you could add it if it's not there, and see if it helps.

    I'd also look through the PHP error log and see if anything "interesting" shows up there that might point toward abnormal script terminations.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Apr 2014
    Posts
    8
    Quote Originally Posted by NogDog View Post
    Does the live code by any chance use a "p:" in front of the host name parameter to mysqli_connect, specifying a persistent connection? In theory it shouldn't really be an issue, but you could try removing it if it is there and see if that makes a difference. For that matter, I suppose you could add it if it's not there, and see if it helps.

    I'd also look through the PHP error log and see if anything "interesting" shows up there that might point toward abnormal script terminations.
    No PHP errors in the log and it is not a persistent connection. It is just mysqli_connect.

    Thanks for the reply though.

  4. #4
    Join Date
    Feb 2014
    Location
    Canada
    Posts
    155
    The only thing I can think of would be putting in a mysqli_close(), even though it's only 1 connection (unless there's more outside of the snippet). PHP should free up the resources even without telling it to close the connection but it's worth a shot.

  5. #5
    Join Date
    Apr 2014
    Posts
    8
    Quote Originally Posted by Error404 View Post
    The only thing I can think of would be putting in a mysqli_close(), even though it's only 1 connection (unless there's more outside of the snippet). PHP should free up the resources even without telling it to close the connection but it's worth a shot.
    Already have. Tbh I was just looking confirmation that there is indeed nothing wrong with the code.
    Sadly there seems to be very little on what causes too many connections error besides code error and too much traffic, neither of which are my problem.

    I wonder if that means someone was trying to hack my database or that there was a server glitch.

  6. #6
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    you have 4 queries active at the same time. this may be what is impacting your script. in php i try to do one set of instructions at a time, example:
    1. run query
    2. fetch data
    3. process data
    4. flush query (mysqli_free_result())

  7. #7
    Join Date
    Apr 2014
    Posts
    8
    Quote Originally Posted by ShrineDesigns View Post
    you have 4 queries active at the same time. this may be what is impacting your script. in php i try to do one set of instructions at a time, example:
    1. run query
    2. fetch data
    3. process data
    4. flush query (mysqli_free_result())
    Using Microtime() the time for all queries to complete was 5.6ms. How much time are you allowed per script?

    Also I actually did what you have there before, but I thought that would just keep the connection open for longer.

  8. #8
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    ^ old school techniques for keeping memory usage low (back when php was allotted a gigantic 8mb of memory by default).
    script execution time is not much of a factor with today's technology.
    i suspect that your mysql has the connection limit and/or max connections set too low.

  9. #9
    Join Date
    Apr 2014
    Posts
    8
    Quote Originally Posted by ShrineDesigns View Post
    ^ old school techniques for keeping memory usage low (back when php was allotted a gigantic 8mb of memory by default).
    script execution time is not much of a factor with today's technology.
    i suspect that your mysql has the connection limit and/or max connections set too low.
    Max connections is at 15 which is loads for a small site like mine.
    As for connection limit, I don't know what that is.

  10. #10
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    my testing server (on my pc) is set to 100 (the default config for "dev mode" i.e. minimalist mode), which is overkill for me. 15 in a production env is way to low imo. if you can increase your max connections to 50, that would remove connection bottle-necking. if you can't, then you need to add in a caching workaround (save sql results in a file).

  11. #11
    Join Date
    Apr 2014
    Posts
    8
    Quote Originally Posted by ShrineDesigns View Post
    my testing server (on my pc) is set to 100 (the default config for "dev mode" i.e. minimalist mode), which is overkill for me. 15 in a production env is way to low imo. if you can increase your max connections to 50, that would remove connection bottle-necking. if you can't, then you need to add in a caching workaround (save sql results in a file).
    I'm not really in a 'production environment' though. I don't think I get 15 hits a day, never mind simultaneously.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles