Php, MySQL - Too Many Connections to Database Error

MySQL Error Too many connections

Shared-hosting providers generally allow a pretty small amount of simultaneous connections for the same user.

What your code does is :

open a connection to the MySQL server
do it's stuff (generating the page)
close the connection at the end of the page.

The last step, when done at the end of the page is not mandatory : (quoting mysql_close's manual) :

Using mysql_close() isn't usually
necessary, as non-persistent open
links are automatically closed at the
end of the script's execution.

But note you probably shouldn't use persistent connections anyway...

Two tips :

use mysql_connect insead of mysql_pconnect (already OK for you)
Set the fourth parameter of mysql_connect to false (already OK for you, as it's the default value) : (quoting the manual) :

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.

What could cause the problem, then ?

Maybe you are trying to access several pages in parallel (using multiple tabs in your browser, for instance), which will simulate several users using the website at the same time ?

If you have many users using the site at the same time and the code between mysql_connect and the closing of the connection takes lots of time, it will mean many connections being opened at the same time... And you'll reach the limit :-(

Still, as you are the only user of the application, considering you have up to 200 simultaneous connections allowed, there is something odd going on...

Well, thinking about "too many connections" and "max_connections"...

If I remember correctly, max_connections does not limit the number of connections you can open to the MySQL Server, but the total number of connections that can bo opened to that server, by anyone connecting to it.

Quoting MySQL's documentation on Too many connections :

If you get a Too many connections
error when you try to connect to the
mysqld server, this means that all
available connections are in use by
other clients.

The number of connections allowed is
controlled by the max_connections
system variable. Its default value is
100. If you need to support more connections, you should set a larger
value for this variable.

So, actually, the problem might not come from you nor your code (which looks fine, actually) : it might "just" be that you are not the only one trying to connect to that MySQL server (remember, "shared hosting"), and that there are too many people using it at the same time...

... And if I'm right and it's that, there's nothing you can do to solve the problem : as long as there are too many databases / users on that server and that max_connection is set to 200, you will continue suffering...

As a sidenote : before going back to GoDaddy asking them about that, it would be nice if someone could validate what I just said ^^

php, mysql - Too many connections to database error

There are a bunch of different reasons for the "Too Many Connections" error.

Check out this FAQ page on MySQL.com: http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

Check your my.cnf file for "max_connections". If none exist try:

[mysqld]
set-variable=max_connections=250

However the default is 151, so you should be okay.

If you are on a shared host, it might be that other users are taking up too many connections.

Other problems to look out for is the use of persistent connections and running out of diskspace.

Handling MySql 'Too many connections' error on shared hosting

I know this has been a while, but here is the solution that I came up with. Since there is no way for me to prevent the errors, i simply handle them with the following code in my common.php file.

$db = "";   // db object
$er = "";     // error object

/*setdb() is the function that actually gets and starts the db connection.
    It returns either the db object, or false. The loop will try up to 5 times 
    to connect with .1 second breaks in between. if that fails then it logs an
    error, and the page fails to load. This has not happened in over 5 months on
    a live site.*/ 

for ($i = 0; $i = 5; $i++) {     //  short loop
    if (setdb() !== false) {
        $db = setdb();          // if successful breaks
        break;
    } else {
        if ($i = 5) {         // after 5 trys, logs error.
            file_put_contents('sqlerror.er', $er . "\r\n", FILE_APPEND);
        }
    }
    usleep(100000);           // .1second sleep
}

function setdb(){
    $username = "my-username";
    $password = "***************"; 
    $host = "localhost";
    $dbname = "my_database";

    $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
    $miscErr = "[1040] Too many connections";

    try {         // try to make connection
        $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options);
    }
    catch(PDOException $ex) {
        $er = $ex;
        $pos = strpos($ex, $miscErr);
        if ($pos !== false) {
            return false;           //return false on error
        }
        file_put_contents('sqlerror.er', $ex . "\r\n", FILE_APPEND);
    }
    return $db;         // return true
}

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 

session_start();

PHP/MySQL - too many connections issue

It looks like you are using constants to connect to the DB - which means the class will only support connections with one set of parameters.

You could make the connection static and then you will only get one per script no matter how many times you instantiate the class. The PHP manual provides a good explanation of static usage.

This won't scale if you require a second connection to a different DB, but by then you'll be needing to re-factor the class a little anyway and shift the other constants.

if ( !class_exists( 'DB' ) ) {

class DB {

    // make $cxn a static property shared between instances of this class
    private static $cxn = null;

    public function __construct() {
        $this->connect();
    }

    private function connect() {
        if(!is_null(self::$cxn)) {
            // a connection has already been tried, and succeeded
            return;
        }

        self::$cxn = new \mysqli( DB_HOST, DB_USER, DB_PASS, DB_NAME );
        if ( self::$cxn->connect_error ) {
            die( "Connection failed: " . self::$cxn->connect_errno . ' ' . self::$cxn->connect_error );
        }
    }

Error too many connections on CodeIgniter website

In our case the solution was lowering the mysql variable "wait_timeout" from 8 hours (the default, wtf!?) to 180 seconds. And it can still be lowered more if needed. This had to be done by the hosting company, as we do not have root access to our server.

All the other solutions I mentioned in the question were not working, like "pconnect = false" and "db->close".

"Threads_connected" are now always under 100 or 200, instead of the almost 1000 from before this fix.

My team wrestled with this problem for two days, and there's lots of people on the Web asking for solutions but without any (working) answers.

Cheers :)

mysql too many connection error.

Don't open a new Connection for every word. You only need one connection open for the lifetime of your inserts. I'm not sure about the true lifetime of a PDO object, I know they're cleaned up when they're not used, but garbage collection might not do that for a couple of minutes, and for 60,000 words, you're going to hit your limit of connections to the database faster than it can clean them up.

$file = new SplFileObject('list.txt');
$p = new PDO('mysql:host=localhost; dbname=test_dictionary', 'root', 'test');

foreach ($file as $line => $word) {

    $p->query("INSERT INTO words (english) VALUES('$word') ");
}