mysql connection

Question

Im using this code for mysql connection

$con = mysql_connect("localhost:/var/lib/mysql/mysql.sock", "abc" , "xyz");
if (!$con)
{
    die('Could not connect: ');
}
mysql_select_db("database_name", $con);

Im including this file on the top of every php file that im using for parsing and then storing the data in database.Now when the original site gets down then my parsing files starts giving error "Warning: mysql_connect(): Too many connections in /home/clickindia/public_html/appuonline/db.php on line 2 Could not connect:"

What should i do to save my site from these errors.Do you think that i should use mysql_close($con); But how and where??? Im using die in my code is that correct or should i use close etc. plz help!!

Answer 1

You say you put this connection code "on the top of every php file".

My question is: do you use several of these files for a single page?

If this is the case, then you should open the connection only once for a page. This could be the explanation of the error.

Artem's advises are pertinent too.

Answer 2

This is really a sysadmin problem - you should contact your systems manager. If you have dedicated web hosts and dedicated database host that you manage the configuration of, there are two obvious solutions to this:

1. Increase max_connections
2. Reconfigure your application to consume fewer

Web servers which connect on every page can use a lot.

Answer 3

You might want to consider wrapping a singleton around database access. Benefits:

• You can only use one connection.
• You only open a connection if you need it (rather than arbitrarily connect on every page).

You can roll your own singleton or get one from a framework, thought that might be overkill depending on what you're working on. The basics would be something like this:

class DatabaseAccess {
     private static $instance;
     private $connection;

     //declare the constructor private so we can only use it in this class
     private function __construct( $host, $user, $pass, $db ) {
         $this->connection = mysql_connect( $host, $user, $pass );
         mysql_select_db( $db, $this->connection );
     }

     //provide access to a single instance
     public static function instance() {
         if ( ! isset( self::$instance ) ) {
             self::$instance = new DatabaseAccess('host', 'user', 'pass', 'db');
         }
         return self::$instance;
     }

     //functions to execute/query the db
     public function query( $query ) {
         return mysql_query( $query, $this->connection );
     }

}

If I were to go down this route, I'd probably do this by controlling access to an instance of mysqli and use it directly rather than wrap the mysql_* functions. You can find out more about the singleton pattern on wikipedia.

Your application would then work with the class like this:

$dba = DatabaseAccess::instance();
$result = $dba->query('select * from pages');
mysql_fetch_assoc($result);
//blah blah blah

Hope that helps!

Answer 4

1. Don't display errors to the public, especially on production.

2. Use exceptions instead of die.

3. Handle your failures gracefully - this kind of goes hand in hand with #1. Don't just die - redirect to an error page or show a temporary "couldn't save data" message.

4. Set up MySQL slaves and query those if you can. Replication will always help you with stability and single points of failure (to a certain degree).

Answer 5

mysql is having a limitation on the number of connections it can give to client ( php client library in this case ).

mysql_connect() is opening new connection(s) and not closing the older one. You can use mysql_pconnect to solve this problem. Take a look @ this function documentation of mysql pconnect

Answer 6

You could also use mysql_pconnect:

Establishes a persistent connection to a MySQL server.

mysql_pconnect() acts very much like mysql_connect() with two major differences.

• It will try to reuse an open connection that is the same.

• It will not close the connection at the end of the script.