数据库错误：[mysqli.mysqli]：用户已经有超过'max_user_connections'的活动连接

I have a site that gets just about 100 people everyday but I got this error message when log in as a user:

我有一个每天只有大约100人的网站但是当我以用户身份登录时收到此错误消息:

Warning: mysqli::mysqli() [mysqli.mysqli]: (42000/1203): User mexautos_Juan already has more than 'max_user_connections' active connections in /home/mexautos/public_html/kiubbo/data/model.php on line 26

Warning: mysqli::query() [mysqli.query]: Couldn't fetch mysqli in /home/mexautos/public_html/kiubbo/data/model.php on line 87
Query failed

I refresh the page a couple of time and now its ok, but since I dont have that many users I suspect the error its in my code, where should I look for it?

我刷新页面几次,现在还可以,但是由于我没有那么多用户,我怀疑我的代码中出现了错误,我应该在哪里寻找它?

Thx

Edit: this is the model file:

编辑:这是模型文件:

<?php
/* 

    Model is the base class from which the other
    model classes will be derived. It offers basic
    functionality to access databases

*/ 
require_once($_SERVER['DOCUMENT_ROOT'].'/config.php'); 
require_once(SITE_ROOT.'includes/exceptions.php'); 

class Model {

    private $created;
    private $modified;

    static function getConnection()
    {
        /* 

            Connect to the database and return a
            connection or null on failure

        */

        $db = new mysqli (DB_HOST, DB_USER, DB_PASS, DB_NAME);
        if(!$db) {
            echo mysql_error();
            throw new Exception('Could not connect to database', EX_NO_DATABASE_CONNECTION);
        }

        return $db;

    }

    static function execSQL($query)
    {
    /*
            Execute a SQL query on the database
            passing the tablename and the sql query.
            Returns the resultset
    */


        $db = null;
        $results = null;
        //echo "query is $query";

        try
        {
            $db = Model::getConnection();
            $results = $db->query($query);
            if(!$results) {
                throw new Exception('Query failed', EX_QUERY_FAILED );
            }
        }
        catch(Exception $e)
        {
            /*  errors are handled higher in the
                    object hierarchy
            */

            throw $e;
        }

        Model::closeConnection($db);

        return $results;
    }

    static function execSQl2($query)
    {
    /*
            Execute a SQL query on the database
            passing the tablename and the sql query.
            Returns the LAST_INSERT_ID
    */


        $db = null;
        $lastid = null;
        //echo "query is $query";

        try
        {
            $db = Model::getConnection();
            $results = $db->query($query);
            if(!$results) {
                throw new Exception('Query failed', EX_QUERY_FAILED );
            }
            $lastid = $db->insert_id;
        }
        catch(Exception $e)
        {
            /*  errors are handled higher in the
                    object hierarchy
            */

            throw $e;
        }

        Model::closeConnection($db);

        return $lastid;
    }

    function delete($table, $id, $conditions = '')
    {
        $query = "delete from $table where id = $id";
        try
        {
            $db = Model::getConnection();
            $results = Model::execSQL($query);
            if(!$results){
                throw new Exception('Could not delete this object', EX_DELETE_ERROR);
            }
            return $results->num_rows;
        }

        catch(Exception $e)
        {
            throw $e;
        }
    }

    static function closeConnection($db)
    {
        $db->close();
    }

    function getCreated()
    {
        return $this->created;
    }

    function setCreated($value)
    {
        $this->created = $value;
    }

    function getModified()
    {
        return $this->modified;
    }

    function setModified($value)
    {
        $this->modified = $value;
    }



}

?>

4 个解决方案

#1

this should fix your problem, but i didn't test it. the difference: if getConnection() is called the first time, a connection is made and stored. the rest of the time, the already established connection is used.

这应该解决你的问题,但我没有测试它。差异:如果第一次调用getConnection(),则建立并存储连接。其余时间,使用已建立的连接。

i removed the action in closeConnection, because that would make the first change useless. altough it would be nicer to remove the closeConnection call from execSQL.

我删除了closeConnection中的操作,因为这会使第一个更改无用。尽管从execSQL中删除closeConnection调用会更好。

normally (as far as i know), the database connection automatically closes when the script terminates (as long as mysqli doesn't support persistance). but it would be better to call (a working) closeConnection manually after all the database-stuff is finished.

通常(据我所知),当脚本终止时,数据库连接会自动关闭(只要mysqli不支持持久性)。但是在完成所有数据库之后,最好手动调用(工作)closeConnection。

<?php
class Model {

    private $created;
    private $modified;

    private static $db = false;

    static function getConnection()
    {
        /* 

            Connect to the database and return a
            connection or null on failure

        */


        if (self::$db === false) {
            self::$db = new mysqli (DB_HOST, DB_USER, DB_PASS, DB_NAME);
        }

        if(!self::$db) {
            echo mysql_error();
            throw new Exception('Could not connect to database', EX_NO_DATABASE_CONNECTION);
        }

        return self::$db;
    }

    static function closeConnection()
    {
        // self::$db->close();
    }

    // moar functions (...)
}

?>

and ... i'd recommend using an existing database access abstraction layer to avoid such problems in the future.

并且...我建议使用现有的数据库访问抽象层来避免将来出现此类问题。

#2

Every hit on your website that opens a database connection is using the same database username and password. Your database settings have limited the number of connections on a per-user basis, and you are going over that maximum.

您网站上打开数据库连接的每次点击都使用相同的数据库用户名和密码。您的数据库设置限制了每个用户的连接数,并且您将超过该最大值。

Check out this MySQL manual page: http://dev.mysql.com/doc/refman/5.0/en/user-resources.html

查看此MySQL手册页:http://dev.mysql.com/doc/refman/5.0/en/user-resources.html

Your model class isn't that great, as it seems to be opening and closing a database connection on every individual query. This is a very bad use of resources, as opening and closing connections are expensive. I would write a destructor function on your model object that called $db->close(), and change getConnection() to open a connection once, and then return it every time after that. This means converting your model class to a non-static usage, but it would be much easier on the database to do so.

您的模型类不是很好,因为它似乎是在每个单独的查询上打开和关闭数据库连接。这是一种非常糟糕的资源使用,因为打开和关闭连接很昂贵。我会在模型对象上编写一个析构函数,它调用$ db-> close(),并更改getConnection()以打开一次连接,然后每次都返回它。这意味着将模型类转换为非静态用法,但在数据库上这样做会容易得多。

Anyhow, it's possible with all the connecting and deconnecting your class is doing that MySQL has connections backing up and they aren't clearing fast enough before you hit your max user limit.

无论如何,你可以通过所有连接和断开连接来确保MySQL有连接备份,并且在你达到最大用户限制之前它们没有足够快地清除。

#3

Look at your code that's handling your database connections. Are you using a pool? Are you using one of the PHP database abstraction frameworks?

查看处理数据库连接的代码。你在用游泳池吗?您使用的是PHP数据库抽象框架之一吗?

Are you handling connections in each database access? If so, you're looking for code that doesn't explicitly release/close the database connections. (if you're doing it this way, I'd suggest looking at an article like this: http://www.devshed.com/c/a/PHP/Database-Abstraction-With-PHP/ )

您是否在每个数据库访问中处理连接?如果是这样,您正在寻找未明确释放/关闭数据库连接的代码。 (如果你这样做的话,我建议看一下这样的文章:http://www.devshed.com/c/a/PHP/Database-Abstraction-With-PHP/)

#4

There are two problems here; one exacerbating the other.

这里有两个问题;一个加剧了另一个。

@zombat has identified the bigger problem: you don't need to connect and disconnect for each query. Although MySQL has a fast setup and teardown cycle, it wastes other resources. It makes more sense to open the connection once in the code's setup step, and then use the connection repeatedly for each query, until the page ends. I would suggest using an instance variable for the mysqli object.

@zombat发现了一个更大的问题:您不需要为每个查询连接和断开连接。虽然MySQL具有快速的设置和拆卸周期,但它浪费了其他资源。在代码的设置步骤中打开连接一次更有意义,然后对每个查询重复使用连接,直到页面结束。我建议为mysqli对象使用一个实例变量。

(If you have a structure where there are multiple databases and which one depends on the object, then you need to enhance your database handler to keep track of which database connections it has open so it only opens the ones it needs. But this is a much more advanced topic that most people won't need to do.)

(如果您的结构中有多个数据库,哪一个依赖于对象,那么您需要增强数据库处理程序以跟踪它已打开的数据库连接,以便只打开它所需的数据库。但这是一个大多数人不需要做的更高级的话题。)

The "other resources" playing up here are MySQL connections. If mysqli is creating persistent connections, it will not actually be closing the connection to MySQL (it should also, in fact, be re-using the connections so you wouldn't even have this problem, but I digress). MySQL's default value for timing out such connections is several hours, so you're probably running into that limit. If you see hundreds of "sleeping" threads in SHOW PROCESSLIST then this is what is happening. The parameter to change is wait_timeout. And max_connections could be too low, too.

这里播放的“其他资源”是MySQL连接。如果mysqli正在创建持久连接,它实际上不会关闭与MySQL的连接(事实上,它也应该重新使用连接,所以你甚至不会遇到这个问题,但我离题了)。 MySQL用于超时此类连接的默认值是几个小时,因此您可能遇到了这个限制。如果你在SHOW PROCESSLIST中看到数百个“休眠”线程,那么这就是正在发生的事情。要更改的参数是wait_timeout。并且max_connections也可能太低了。

But I recommend you fix your database handler first.

但我建议您先修复数据库处理程序。

#1