Linked Questions

Popular Questions

Too many connections with PDO persistent connection?

Asked by At

I've been struggling with this for quite a while, and it's to the point where I need to ask for help because even with all the research I've done I can't get a handle on why this is happening.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1040] Too many connections'

This happens upon loading a single page (index.php), and I am the only user (dev). As you can see here, the MySQL connection limit is set @ 50 but I am narrowly surpassing that. This is an improvement over the 100~ connections that were being created before I refactored the code.

MySQL connections before

Here are the stats after the page has loaded once.

MySQL connections after

I've narrowed the issue down to several causes:

  • I don't fully understand how PDO/MySQL connections work.
  • I am creating too many connections in my code even though I am trying to only create one that I can share.
  • I need to increase the connection limit (seems unlikely).

Most of the SO questions I've found, tell the OP to increase the connection limit without truly knowing if that's the best solution so I'm trying to avoid that here if it's not needed. 50 connections for one page load seems like way too many.

These are the classes I am instantiating on the page in question.

$DataAccess = new \App\Utility\DataAccess();
$DataCopyController = new App\Controllers\DataCopyController($DataAccess);
$DriveController = new App\Controllers\DriveController($DataAccess);
$Helper = new App\Utility\Helper();
$View = new App\Views\View();

I am creating the DAL object then injecting it into the classes that need it. By doing it this way I was hoping to only create one object and one connection, however this is not what's happening obviously. Inside the DAL class I've also added $this->DbConnect->close() to each and every query method.

Here is the constructor for the DataAccess() class.

public function __construct() {

    $this->DbConnect = new \App\Services\DbConnect();
    $this->db = $this->DbConnect->connect("read");
    $this->dbmod = $this->DbConnect->connect("write");

    $this->Helper = new Helper();
}

Here is the DbConnect() class.

class DbConnect {

  private $db;
  private $dbmod;

  private function isConnected($connection) {
    return ($connection) ? TRUE : FALSE;
  } 

  public function connect($access) {

    $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false
                ];

    if ($access == "read") {
        if ($this->isConnected($this->db)) {
            return $this->db;
        } else {
            if (strpos($_SERVER['SERVER_NAME'], DBNAME_DEV) === false) {
                $this->db = new PDO("mysql:host=127.0.0.1; dbname=".DBNAME, 
                                                                    DBUSER, 
                                                                    DBPASS, 
                                                                    $options
                                                                       );
            } else {
                $this->db = new PDO("mysql:host=" . DBHOST_DEV ."; dbname=".DBNAME_DEV, 
                                                                   DBUSER, 
                                                                   DBPASS, 
                                                                   $options
                                                                       );
            }
            return $this->db;
        }
    } elseif ($access == "write") {
        if ($this->isConnected($this->dbmod)) {
            return $this->dbmod;
        } else {
            if (strpos($_SERVER['SERVER_NAME'], DBNAME_DEV) === false) {
                $this->dbmod = new PDO("mysql:host=127.0.0.1; dbname=".DBNAME, 
                                                                       DBUSER_MOD, 
                                                                       DBPASS, 
                                                                       $options
                                                                       );
            } else {
                $this->dbmod = new PDO("mysql:host=" . DBHOST_DEV . "; dbname=".DBNAME_DEV, 
                                                                       DBUSER_MOD, 
                                                                       DBPASS, 
                                                                       $options
                                                                       );
            }
        }
        return $this->dbmod;
    }
  }

  public function close() {
    $this->db = null;
    $this->dbmod = null;
  }
}

I've also tried instantiating the DbConnect() class on index.php and injecting that rather than DataAccess() but the result was the same.

EDIT: I also want to add that this MySQL server has two databases, prod and dev. I suppose the connection limit is shared between both. However, the prod database gets very little traffic and I am not seeing this error there. When I refreshed the stats, there were no connections to the prod database.

Related Questions