'max_user_connections' set to 200 - still getting error

Question

Here is the mysql error: Connect failed: User 'db2498' has exceeded the 'max_user_connections' resource (current value: 200).

I set the my.cnf:

[mysqld]
max_connections = 500
max_user_connections = 200

I set the max_user_connections in mysql for the user to 200 also. I've had 1400 people hit the site in about 10-20 minutes. Each stay on for an average of 14 seconds, and I got about 1400 of these messages.

I'm using PHP/Mysql. This is the database class:

class DB{

public function __construct(){
    $this->conn = new mysqli($this->host,$this->user,$this->pass,$this->db);
    /* check connection */      
}
public function selectSomething(){
    /* select data & return */
}
public function __destruct()
{
$this->conn->close();
} 
}

This is how I call it:

$conn = new DB();
$result = $conn->selectSomething();
/* do something */

$result = $conn->selectSomething();
/* do something */

$result = $conn->selectSomething();
/* do something */

The users are on the site for an average of 14 seconds. Why am I getting this error? Is it the destruct? Do I have the database wrapper set up wrong? I'm lost, and tech support isn't much help.

Solution

As for your "max connections" problem, it could be one of three things:

1) The server has too many open connections already. A MySQL server can only handle a specific number of open connections before refusing to allow any more, and this limit is shared amongst all users of the server. It's usually set quite high, although it's easily possible for someone to effectively DoS a MySQL server by making lots of connections (but see below)

2) Your user account has a limited number of connections allowed per hour - any further connections within that hour would be rejected. This is set on a per-user basis.

3) Your user accounts has a limited number of allowable open connections - any further connections would be rejected. This is set on a per-user basis.

It's always important to read the error message you get returned on the connection attempt, as in most cases this will pinpoint the exact reason for failure.

If your account has a maximum number of connections limit (scenario #3), the error would be: Code:

ERROR 1226 (42000): User 'mysqldba' has exceeded the 'max_user_connections' resource (current value: 1) 

Where 'mysqldba' would be your username, and the 'current value' is the maximum number of open connections allowed from this user.

If you account has a maximum number of connections per hour limit (scenario #2), the error would be: Code:

ERROR 1226 (42000): User 'mysqldba' has exceeded the 'max_connections_per_hour' resource (current value: 1) 

Where, once again, 'mysqldba' would be your username, and the 'current value' is the maximum number of connections per hour allowed for this user.

If you got the error message (code 1040) indicates that the entire MySQL server has run out of connection slots - this is the DoS scenario I mention above.

What can you do about it? From what you've said, you don't have superuser privileges on this server, so nothing, apart from complain to the SysAdmin responsible for that server. They might increase the maximum number of connections allowed, which could solve the problem short-term, but if someone else using the server is creating a stupid number of database connections the slots would just fill up again. What they probably should do is to also enforce a per-user maximum open connection limit as well - this would stop the heavy users clogging up the server. In a shared-server situation like yours, this would make the most sense - 'power users' would/should have their own server, or could/should pay to increase their maximum open connections.

Comments

[Yipyo Ai]

I can SU. It's my box. here is the error: Warning: mysqli::mysqli() [mysqli.mysqli]: (42000/1226): User 'user3903' has exceeded the 'max_user_connections' resource (current value: 200). I have access to change the values. I've changed my.cnf. Is it my database wrapper? Is the connection not closing?

[Bud Damyanov]

I suspect that the connection was not closed correctly. Can you try to use persistent connection instead, i.e. mysql_pconnect(...), sometimes the mysqli wrapper is not working normally...

[zmonteca]

@bodi0 is dead on. Make sure you're using mysqli::close after your done with your queries.