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;
    

    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 [email protected] 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 !!!

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM