Mysql – How to get rid of “maximum user connections” error

max-connectionsMySQLPHP

I am using MySQLi for my webapp but whenever I want to visit some particular page, I get mysqli_connect() [function.mysqli-connect]: (42000/1203): User ***_user already has more than 'max_user_connections' active connections.

I tried already to close all connections but this does not improve the situation.

Is there a way to know exactly what connections are open at any particular moment or any other useful data that can help me resolve this issue ?

BTW, I'm using PHP 5.2.17 and MySQL 5.1.

Best Answer

The option max_user_connections is a limit imposed, not on the total number of simultaneous connections in the server instance, but on the individual user account.

Let's say the user is called db_user@localhost. You can find out what this user's connection limit is by running this query:

SELECT max_user_connections FROM mysql.user
WHERE user='db_user' AND host='localhost';

If this is a nonzero value, change it back with:

GRANT USAGE ON *.* TO db_user@localhost WITH MAX_USER_CONNECTIONS 0;

or

UPDATE mysql.user SET max_user_connections = 0
WHERE user='db_user' AND host='localhost';
FLUSH PRIVILEGES;

This will cause mysqld to allow the user db_user@localhost to use the global setting max_user_connections as its limit.

Once you get to this point, now check the global setting using

SHOW VARIABLES LIKE 'max_user_connections';

If this is a nonzero value, you need to do two things

THING #1 : Look for the setting in /etc/my.cnf

[mysqld]
max_user_connections = <some number>

comment that line out

THING #2 : Set the value dynamically

SET GLOBAL max_user_connections = 0;

MySQL restart is not required.

CAVEAT

I have discussed this setting in the past

Give it a Try !!!

Related Question