User already has more than 'max_user_connections

Question

Yesterday I get issue :

[2018-09-09 13:04:02] main.CRITICAL: SQLSTATE[HY000] [1203] User user_database already has more than 'max_user_connections' active connections {"exception":"[object] (Zend_Db_Adapter_Exception(code: 1203): SQLSTATE[HY000] [1203] User user_database already has more than 'max_user_connections' active connections at /home/czasowe/domains/czasowewyprzedaze.pl/public_html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php:144, PDOException(code: 1203): SQLSTATE[HY000] [1203] User user_database already has more than 'max_user_connections' active connections at /home/czasowe/domains/czasowewyprzedaze.pl/public_html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php:128)"} []

Anyone know how to solve this issue ?

Answer 1

Increase your mysql limits using max_connection value.

How to fix that:

1. Open my.cnf
2. Find max_connections value
3. Set high value (100000 or more)
4. Save file & reboot mysql.

You can check opened connection in MySQL using "SHOW PROCESSLIST" query. Sometimes PHP may running in infinite loop if something goes wrong and didn't close connection when it needed.

Answer 2

Quoting from https://dev.mysql.com/doc/refman/5.7/en/user-resources.html (but do please re-check appliance for your own MySQL server version)

For MAX_USER_CONNECTIONS, the limit is an integer representing the maximum number of simultaneous connections by the account. If this limit is set to zero, the global max_user_connections system variable value determines the number of simultaneous connections. If max_user_connections is also zero, there is no limit for the account.

and also

For the MAX_USER_CONNECTIONS limit, an edge case can occur if the account currently has open the maximum number of connections permitted to it: A disconnect followed quickly by a connect can result in an error (ER_TOO_MANY_USER_CONNECTIONS or ER_USER_LIMIT_REACHED) if the server has not fully processed the disconnect by the time they connect occurs.

So, first, you need to figure out the MAX_USER_CONNECTIONS value and pick it up from there.

Maybe that value is set to really low number, or, your site is simply too overloaded with customers that MySQL eventually opens connections that cannot be closed due to lack of resources, or, perhaps there is a coding bug that does not close connections (doubtful, but possible). These are wild guesses. You simply need to narrow down the possibilities by gathering more data.