Database Systems Help

medlington · January 20 '10, 12:01 PM (#1)

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.

smoseley · January 20 '10, 12:46 PM (#2)

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

?>

medlington · January 21 '10, 11:37 AM (#3)

Thanks for the advice but I get an error when I use that code:

Quote:

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:

Quote:

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

smoseley · January 21 '10, 11:54 AM (#4)

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.

medlington · January 21 '10, 01:19 PM (#5)

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

smoseley · January 21 '10, 05:05 PM (#6)

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)) {  
    ....
}

medlington · January 22 '10, 09:42 AM (#7)

Thanks, I've done that but I still get these errors:

Quote:

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?

smoseley · January 22 '10, 10:29 AM (#8)

Try ($host, $user, $pass) (no quotes).

medlington · January 22 '10, 10:40 AM (#9)

Thanks but I still get the same errors,

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

Quote:

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?

smoseley · January 22 '10, 11:21 AM (#10)

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

medlington · February 1 '10, 10:01 AM (#11)

Thanks for the help.

The DB is on my web hosting companies server but after reading that thread it occured to me that there were several DB's with the same name on their server / IP address and that they were then seperated somehow. I thought that this could be the problem as in that page it mentions having multiple MYSQL installs so I started again with a new DB and made sure it had a unique name.

I've been away so got very confused with where I was at with this so had another look for how to do it and am now doing it with this method.

I have one file which has all the DB details in which is called 'config.php'

then I have a file that opens the DB like this:

PHP Code:


			
<?php

// This is an example opendb.php

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

mysql_select_db($dbname);

?>

Then comes some MYSQL processes and then finally I close it with another file which just contains this:

PHP Code:


			
mysql_close($conn);

I think this is doing the same as you have suggested but a different way and I dont get any errors.

Can you see any issues I may have doing it this way instead of the way you have recomended?

Thanks again.

Database Systems Help

Too many connections - MYSQL