Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11
  1. #1
    Senior Member medlington's Avatar
    Join Date
    Nov 2005
    Location
    Sheffield, UK
    Posts
    377
    Member #
    11968
    Liked
    4 times
    Hi,

    I've been working on a Mysql DB for a few weeks now and been getting along quite well. All of a sudden my scripts have stopped working and I get an error 'Too many connections open'. Im guessing that this is because I havent been closing the connection each time in my script.

    I've Googled this abit but the information Im finding is telling me that I dont need to close the connections unless they are persistant connections which Im pretty sure these aren't.

    Can anyone tell me what I should be doing? should I be closing the connections each time or could this be something else that is causing this error. Like I said these errors came about without me altering any of the scripts.

    Any help would be appreciated.

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    You're probably opening a new connection for each query you're executing on a page nad not closing it.

    You can do simple connection persistence by implementing an include for your connection that does something like this:

    connection.php
    PHP Code:
    <?php
    // Create a global for your DB connection
    $globalDatabaseConnection null;

    // Create a connection function
    function connect() {
        global 
    globalDatabaseConnection;
        
    // If global isn't set, set it!
        
    if (is_null($globalDatabaseConnection)) {
            
    $globalDatabaseConnection mysql_connect("localhost""username""pwd");
            
    mysql_select_db("database_name"$globalDatabaseConnection);
        }
        return 
    $globalDatabaseConnection;
    }

    // Create a "free memory" function
    function disconnect() {
        global 
    globalDatabaseConnection;
        
    mysql_close($globalDatabaseConnection);
        unset(
    $globalDatabaseConnection);
    }
    ?>
    So now on every page, you can do something like this:

    whatever.php
    PHP Code:
    <?php

    // Include your connection utility
    require_once("connection.php");

    // Connect with a reference (&) - this will ensure that you're not duplicating memory / connections
    $conn =& connect();

    // A bunch of SQL stuff


    // Close connectino before end of page (optional)
    disconnect();
    ?>

  4. #3
    Senior Member medlington's Avatar
    Join Date
    Nov 2005
    Location
    Sheffield, UK
    Posts
    377
    Member #
    11968
    Liked
    4 times
    Thanks for the advice but I get an error when I use that code:

    Parse error: syntax error, unexpected T_STRING, expecting T_VARIABLE or '$' in /home/fhlinux160/a/atumstudios.com/user/htdocs/sql/add_administrator/connect_DB_function.php on line 13
    I then tried adding the '$' before the globalDatabaseConnection; so that it was a variable as I thought this was what the error was saying so my code then loked like this:

    Code:
    // Create a global for your DB connection
    $globalDatabaseConnection = null;
    
    // Create a connection function
    function connect() {
        global $globalDatabaseConnection;
        // If global isn't set, set it!
        if (is_null($globalDatabaseConnection)) {
            $globalDatabaseConnection = mysql_connect("$localhost", "$username", "$pwd");
            mysql_select_db("$database_name", $globalDatabaseConnection);
        }
        return $globalDatabaseConnection;
    }
    
    // Create a "free memory" function
    function disconnect() {
        global $globalDatabaseConnection;
        mysql_close($globalDatabaseConnection);
        unset($globalDatabaseConnection);
    }
    but then I get these errors:

    Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/fhlinux160/a/atumstudios.com/user/htdocs/sql/add_administrator/connect_DB_function.php on line 16

    Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home/fhlinux160/a/atumstudios.com/user/htdocs/sql/add_administrator/connect_DB_function.php on line 17

    Warning: mysql_query() [function.mysql-query]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/fhlinux160/a/atumstudios.com/user/htdocs/sql/add_administrator/add_jobseeker_exists2.php on line 7..........
    and loads more but I think they are all basically saying that it cant connect to the DB.

    The connection script I was using before was this:

    Code:
    <?php
    $host ="##";
    $user = "##";
    $pass = "#######";
    $DBName = "##";
    mysql_connect($host, $user, $pass) or die(mysql_error());
    mysql_select_db($DBName) or die(mysql_error());
    ?>
    which I was calling at the beggining of my script with this line:

    Code:
    include 'connect_DB_function.php';
    Whats the difference between the:
    Code:
    require_once("connection.php");
    which you suggested and the include line which I was using?

    Also is there a way to close the DB connection when I have connected to it the way I have done? Im sure there is but I cant find a tutorial that I fully understand to show me how.

    Thanks again

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Ok, the script I gave you should replace the one you're currently using.

    The problem with the connection you're using is that you're not persisting the connection link in a variable.

    You should always do something like $link = mysql_connect(...) which will maintain that connection and allow you to reuse it throughout your script.

    You should also reference that link in all of yoru queries, etc. Otherwise, you can run into the issue of opening multiple connections

    To close a connection, you have to reference its link like so: mysql_close($link); - this will close one connection by its link identifier.

    I suggest that you read through the example I posted and try to figure out what I'm doing there. This is a very important part of coding.

  6. #5
    Senior Member medlington's Avatar
    Join Date
    Nov 2005
    Location
    Sheffield, UK
    Posts
    377
    Member #
    11968
    Liked
    4 times
    ok, I've tried to add my understanding of your script into the comments:
    PHP Code:
    <?php
    $host 
    ="#####";
    $user "#####";
    $pass "#####";
    $DBName "#####";

    // Create a global for your DB connection (this is a global variable to be called throughout my scripts?
    $globalDatabaseConnection null;

    // this is creating a function called connect
    function connect() {
    // this line is defining the variable globalDatabaseConnection as a global variable
        
    global $globalDatabaseConnection;
        
    // If global isn't set, set it! - this line is saying that if the $globalDatabaseConnection variable is Null then proceed with the function
        
    if (is_null($globalDatabaseConnection)) {
        
    // this line is assigning the DB connection script to the variable $globalDatabaseConnection
            
    $globalDatabaseConnection mysql_connect("$host""$user""$pass");
        
    // this line is then trying to open the databse name using the connection details in  $globalDatabaseConnection    
            
    mysql_select_db("$DBName"$globalDatabaseConnection);
        }
        return 
    $globalDatabaseConnection;
    }

    // Create a "free memory" function
    function disconnect() {
        
    // sets the variable $globalDatabaseConnection to a global scope
        
    global $globalDatabaseConnection;
        
    // closes connection described within the variable $globalDatabaseConnection
        
    mysql_close($globalDatabaseConnection);
        
    // destroys the variable $globalDatabaseConnection to save resources
        
    unset($globalDatabaseConnection);
    }
    ?>
    I think thats correct.

    Now my issue is that when I just run the connect script I dont get any errors but when I try to include it into a script I've already written using the other connection method I get a bunch of errors.

    You mention that I need to reference the $link to the DB whenever I query the DB. The script I am using at the moment is using a query like this:

    PHP Code:
    $query "SELECT * FROM job_seekers
            ORDER BY 
    $orderby";  
    $result mysql_query($query) or die(mysql_error()); 
    Then I loop through the results to print them out with this line

    PHP Code:
      while($row mysql_fetch_array($result)){ 
    How would I go about referencing the $link within this call?

    I've had a look through the php manual for this but I just get lost in it. Im not a programmer really but have a fairly good understanding of what its doing but no idea about the syntax.

    Thanks again

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Everywhere you're connecting to the database, you have to do this:

    PHP Code:
    require_once("connection.php");
    $link connect();
    $query "SELECT * FROM job_seekers ORDER BY $orderby";  
    $result mysql_query($query$link) or die(mysql_error()); 
    while(
    $row mysql_fetch_array($result)) {  
        ....


  8. #7
    Senior Member medlington's Avatar
    Join Date
    Nov 2005
    Location
    Sheffield, UK
    Posts
    377
    Member #
    11968
    Liked
    4 times
    Thanks, I've done that but I still get these errors:


    Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/fhlinux160/a/atumstudios.com/user/htdocs/sql/add_administrator/connect_to_DB2.php on line 17

    Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home/fhlinux160/a/atumstudios.com/user/htdocs/sql/add_administrator/connect_to_DB2.php on line 19

    Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/fhlinux160/a/atumstudios.com/user/htdocs/sql/add_administrator/testscript.php on line 5
    Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
    the lines of code it seems to be refering to are:

    line 17:
    PHP Code:
            $globalDatabaseConnection mysql_connect("$host""$user""$pass"); 
    line 19:
    PHP Code:
                 mysql_select_db("$DBName"$globalDatabaseConnection); 
    and line 5:
    PHP Code:
    $result mysql_query($query$link) or die(mysql_error()); 
    Do you know why I still get these errors?

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Try ($host, $user, $pass) (no quotes).

  10. #9
    Senior Member medlington's Avatar
    Join Date
    Nov 2005
    Location
    Sheffield, UK
    Posts
    377
    Member #
    11968
    Liked
    4 times
    Thanks but I still get the same errors,

    could it be something to do with this part of the error:

    Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
    I've not seen any errors mentioning 'socket' before or having the (2) in?

  11. #10
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Are you sure you're providing the correct connection information?

    Is this on your own box? Looks like mysql may have stopped running?

    Read this: http://coderemix.com/tutorials/cant-...l-MySQL-server


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:26 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com