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.

up vote 21 down vote accepted

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 !!!

protected by Community Sep 20 at 22:35

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.