How to get rid of "maximum user connections" error ?
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.
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
[email protected]. 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 [email protected] WITH MAX_USER_CONNECTIONS 0;
UPDATE mysql.user SET max_user_connections = 0 WHERE user='db_user' AND host='localhost'; FLUSH PRIVILEGES;
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
[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.
I have discussed this setting in the past
May 24, 2011: What are user connections - when are the created and destroyed?
Jun 16, 2011: Would it be sensible to create a MySQL user for each user account of a web application?
Mar 22, 2012: How can I limit MySQL connections?
Feb 22, 2013: Mysql : Maximum number of connections on a per user basis - Possible?
Give it a Try !!!