如何修复消息:SQLSTATE [08004] [1040]连接过多 [英] How to fix Message: SQLSTATE[08004] [1040] Too many connections

问题描述

我正在使用以下代码进行数据库连接

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

如何解决此错误?

我有一个模型类，正在创建新的数据库.

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.

推荐答案

由于您的Model类会在其构造函数中实例化一个新的Database对象，因此每次您实例化Model(或扩展它的任何类)时，您实际上是在打开 new 数据库连接.如果创建多个Model对象，则每个对象都有其自己的独立数据库连接，这种连接不常见，通常是不必要的，不会很好地利用资源，但由于它已经耗尽了服务器的所有可用连接，因此也非常有害.

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.

例如，循环创建Model对象的数组:

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)

使用依赖项注入:

解决方案是使用依赖项注入并将Database对象 pass 传递到Model::__construct()中，而不是允许它实例化自己的对象.

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;
  }
}

然后要使用它，控制代码(将实例化模型的代码)本身应仅调用一次new Database().然后，必须将由控制代码创建的对象传递给所有模型的构造函数.

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);

持久连接:

如评论中所述，使用持久连接可能是一种解决方案，但不是我建议的解决方案. PDO会尝试重用具有相同凭据的现有连接(就像您所有凭据一样)，但是您不必一定要在脚本执行期间缓存该连接.如果您确实决定这样做，则需要将属性传递给Database构造函数.

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'");
}

相关文档在这里: http://php.net /manual/en/pdo.connections.php#example-950

使用单例模式(也不推荐)，您至少可以将其减少为模型代码中的搜索/替换. Database类需要一个静态属性来为其自身保持连接.然后，模型调用Database::getInstance()而不是new Database()来检索连接.您需要搜索并替换模型代码以替换Database::getInstance().

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().

尽管它运行良好且并不难实现，但在您的情况下，这会使测试变得更加困难，因为您必须用相同名称的测试类替换整个Database类.您不能轻易地按实例替换测试类.

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.

将单例模式应用于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;
   }
}

现在您只需要更改Model代码即可使用Database::getInstance():

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();
   }
}

这篇关于如何修复消息:SQLSTATE [08004] [1040]连接过多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！