Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 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 found out what this user's connection limit is. Start by running this query:

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

share|improve this answer
    
Thanks for the answer. In fact, it turns out that my free web host is allowing only 3 simultaneous connections per user (it's FREE for a reason, right?) and I can't change that, of course. The limited number of connections by user is not a big deal in this case since I'm just testing my app on the wild and the web host I will be using allows up to 30 connections. Last question please : if 31 visitors to my webapp triggers 31 queries (with 30 as the max) simultaneously, will this cause an error to all of them or just to the 31st user ? –  ahmed Jul 28 '13 at 13:52
    
From the 31st user and on, all new connect attempts will see that error. The first 30 connections will not be affected. –  RolandoMySQLDBA Jul 28 '13 at 20:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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