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

Question

I use below code to connect to database

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, data fetch is working fine. Now I get an error message with an error

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

How to fix this error?

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

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

and every model I make, I am expanding from the model class.

Answer 1

Since your Model class creates a new Database object in its constructor, every time you create an instance of Model (or any class that extends it), you actually open a new database connection. If you create several Model objects, each of them has its own independent connection to the database, which is unusual, usually unnecessary, and not a good use of resources, but also actively harmful, since it has exhausted all the connections available to the server.

For example, a loop 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 to Model::__construct() , rather than letting it create 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, the control code (the code that your models will create) must itself call new Database() only once. Then this object created by the control code should be passed to the designers of all models.

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

For the use case, when you really need another independent database connection for the model, you can transfer it to another. In particular, it is useful for testing. You can replace the test database object or the object layout.

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

Permanent Connections:

As mentioned in the comments, using a persistent connection is probably a solution, but not a solution that I would recommend. PDO will try to reuse an existing connection with the same credentials (like all yours), but it is not necessary that the connection be cached through script execution. If you 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 can at least reduce this to a search / replace in the model code. The Database class requires a static property in order to maintain a connection for itself. Then the models call Database::getInstance() instead of new Database() to get the connection. You will need to search and replace in the model code to replace Database::getInstance() .

Despite the fact that it works well and is not difficult to implement, in your case it will make testing a little more difficult, since you will have to replace the entire Database class with a testing class with the same name. You cannot easily replace a test class with an instance based instance.

Apply a 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 only need to change the Model code to use Database::getInstance() :

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