Database Error

Question

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

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



}

?>

Answer 1

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?

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

Answer 2

You might want to take a look at this doc page--there are some useful tips in the comments.

Answer 3

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

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.

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.

Answer 4

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.

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.

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.

<?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.

Answer 5

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.

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

But I recommend you fix your database handler first.