Error - 'max_user_connections' active connections in lock_may_be_available()

An active drupal 7 website, one fine day was throwing the following error PDOException: SQLSTATE[42000] [1203] User ******** already has more than 'max_user_connections' active connections in lock_may_be_available() (line 167 of ******************/includes/lock.inc). There were no changes done on the website nor to my webhosting so it was quite surprizing to suddenly see this error. However my website was not down. It was still working, Every time I get the error I would use refresh button in my browser and the page would appear correctly.  However I did the following

EDIT - 24th March 2015 -  Using the new module Asynchronous prefetch database query cache seems to solve this issue of locking. 

You will have to make some changes in your settings.php, by simple copy paste of code.  There are 5 settings where you will have to take help from your webhosting provider if you are using shared hosting. In my case I had to take help for following 5 settings.
  1.  mysqli_reap_async_query() is disabled on this server. Ask hosting provider to re-enable this function for you.
  2.  MYSQLI_ASYNC is not available on this server.  Ask hosting provider to make it available
  3.  IN My query_cache_type query_cache_type should be OFF. Current value: ON
  4. realpath_cache_size should be 128K at a minimum. Current value: 16K Increase the realpath_cache_size value inside /opt/alt/php53/etc/php.ini. Will require a restart of PHP. 
  5.  realpath_cache_ttl should be 3600 at a minimum. Current value: 120 Increase the realpath_cache_ttl value inside /opt/alt/php53/etc/php.ini. Will require a restart of PHP.
Even without above settings. My website locking issue was resolved.
 
Other older solutions to try. 
 
 
Solution 1 - What worked for most people and maybe for me also, was to flush all cache in Drupal. you can do it via Admin toolbar or from phpmyadmin 
 
Install the devel module https://drupal.org/project/devel to understand what queries are running
 
Solution 2 - The long term solution is to change 'max_user_connections' to a higher value, traditionally this is done via /etc/my.cnf on the *nix serverr where you add this in:
 
[code]
# Set max user connections to unlimited (0)
max_user_connections = 0
[/code]
 
The my.cnf file overrides the default setting, whatever that is, set up by the installer or host.
 
Being you're on a shared host, you might not have this option in your GUI hosting manager or access to my.cnf and might need to contact support and ask them to either increase the value or set to 0 if they are willing. They would check your usage to validate the request or might simply say no. If the later all the other solutions posed in this discussion are temporary, eventually as load increases, your connections will max out again. Sometimes this turns out to be a simple case of a host not being able to provide the resources you need.
 

Drupal uses pconnect() when connecting to the database for a slight performance improvement if PHP is configured to allow pconnect.

But there are times when PHP should be configured to disallow persistent connections to the database.

Each apache process can potentially obtain a persistent connection to each database the server is hosting. If there are lots of apache processes and lots of databases it is common to see the database throwing "too many connections" errors maybe like this:

PDOException: SQLSTATE[42000] [1203] User foo already has more than 'max_user_connections' active connections in lock_may_be_available()

If this occurs there are a couple of options:

  • Increase the number of available connections the database will allow. If most connections are idle most of the time, this is a viable solution.
  • Disable pconnect via the XXsql.allow_persistent = Off attribute in your php.ini. The performance hit from this setting is very slight.
Solution 3 
https://drupal.org/comment/8165481#comment-8165481. Implement a try catch block in your lock.php file in includes folder.
This worked for me. The error goes away though the website will become slow as its waiting for the connection
 
References:
https://drupal.org/node/930876 -- Main thread on this issue.

 

 
There are many other threads on drupal.org related to this issue, People say its related to Media files or the way your downloading is enabled. Also try the following