How to fix Message: SQLSTATE[08004] [1040] Too many connections

Question

I am using below code for database connection

class Database extends PDO{

    function __construct(){

        try {
            parent::__construct(DB_TYPE.':host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PASS);
            $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
        } catch(PDOException $e){
            Logger::newMessage($e);
            logger::customErrorMsg();
        }

    }
}

every thing like login , fetching data was working fine . Now suddenly I am having a exception error message

Message: SQLSTATE[08004] [1040] Too many connections

Code: 1040

How to fix this error ?

I have a model class there I am creating new database.

 class Model {

protected $_db;

public function __construct(){
    //connect to PDO here.
    $this->_db = new Database();
    }
  }

and every model I make , I am extending from model class.

Answer 1

Because your Model class instantiates a new Database object in its constructor, each time you instantiate a Model (or any class extending it), you are in effect opening a new database connection. If you create several Model objects, each then has its own independent database connection, which is uncommon, usually unnecessary, not a good use of resources, but also actively harmful as it has used up all the server's available connections.

For example, looping to create an array of Model objects:

// If a loop creates an array of Model objects
while ($row = $something->fetch()) {
  $models[] = new Model();
}
// each object in $models has an independent database connection
// the number of connections now in use by MySQL is now == count($models)

Use dependency injection:

The solution is to use dependency injection and pass the Database object into the Model::__construct() rather than allow it to instantiate its own.

class Model {

  protected $_db;

  // Accept Database as a parameter
  public function __construct(Database $db) {
    // Assign the property, do not instantiate a new Database object
    $this->_db = $db;
  }
}

To use it then, the controlling code (the code which will instantiate your models) should itself call new Database() only once. That object created by the controlling code must then be passed to the constructors of all models.

// Instantiate one Database
$db = new Database();

// Pass it to models
$model = new Model($db);

For the use case where you actually need a different independent database connection for a model, you can hand it a different one. In particular, this is useful for testing. You can substitute a test database object, or a mock object.

// Instantiate one Database
$db = new Database();
$another_db = new Database();

// Pass it to models
$model = new Model($db);
$another_model = new Model($another_db);

Persistent connections:

As mentioned in the comments, using a persistent connection is possibly a solution, but not the solution I would recommend. PDO will attempt to reuse an existing connection with the same credentials (as all yours will have), but you don't necessarily want the connection to be cached across script execution. If you did decide to do it this way, you need to pass the attribute to the Database constructor.

try {
  // Set ATTR_PERSISTENT in the constructor:
  parent::__construct(DB_TYPE.':host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PASS, array(PDO::ATTR_PERSISTENT => true));
  $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $this->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
}

The relevant documentation is here: http://php.net/manual/en/pdo.connections.php#example-950

Singleton solution:

Using a singleton pattern (also not recommended), you could at least reduce this to a search/replace in the model code. The Database class needs a static property to keep a connection for itself. Models then call Database::getInstance() instead of new Database() to retrieve the connection. You would need to do a search and replace in the Model code to substitute Database::getInstance().

Although it works well and isn't difficult to implement, in your case it would make testing a little more difficult since you would have to replace the entire Database class with a testing class of the same name. You can't easily substitute a test class on an instance by instance basis.

Apply singleton pattern to Database:

class Database extends PDO{
   // Private $connection property, static
   private static $connection;

   // Normally a singleton would necessitate a private constructor
   // but you can't make this private while the PDO 
   // base class exposes it as public
   public function __construct(){
        try {
            parent::__construct(DB_TYPE.':host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PASS);
            $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
        } catch(PDOException $e){
            Logger::newMessage($e);
            logger::customErrorMsg();
        }

    }

   // public getInstance() returns existing or creates new connection
   public static function getInstance() {
     // Create the connection if not already created
     if (self::$connection == null) {
        self::$connection = new self();
     } 
     // And return a reference to that connection
     return self::$connection;
   }
}

Now you would need to change only the Model code to use Database::getInstance():

class Model {
    
  protected $_db;
    
   public function __construct(){
     // Retrieve the database singleton
     $this->_db = Database::getInstance();
   }
}