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