Published on Sep 15 2014 in Databases Java

Possible causes for javax.servlet.ServletException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: User dbuser already has more than 'max_user_connections' active connections

Mismatch between MySQL max_user_connections and JDBC pool limits

The most simple and obvious reason is that your connection pool limits are set above max_user_connections. This can be easily verified and limits can be adjusted either in MySQL or in JDBC pool configuration.

Creating and abandoning (not closing properly) JDBC pools on each HTTP request

If you are using connection pooling you need to ensure that you create connection pool only once in your code and then use it multiple times whenever needed. Common error is to create a new pool for each request and not close it. In such case MySQL connection limit gets quickly exhausted. Even if you close it properly it is ineffective from resource usage point of view.

Create pool ONCE and use it until you stop the application - this is the proper way.

For example:

public class HibernateTest extends HttpServlet
 private static SessionFactory sf = HibernateCounter.createSessionFactory();
 private static SessionFactory createSessionFactory() {
 Configuration configuration = new Configuration();
 configuration.configure();
 sr = new ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry();
 return configuration.buildSessionFactory(sr);
}

public static SessionFactory
 getSessionFactory(){ return sf; }
   ........
}

doGet {
 Session s=getSessionFactory().openSession();
 s.beginTransaction();
 YOUR_SQL_QUERIES HERE
 s.getTransaction().commit();
 s.close();
}

The SessionFactory sf will be so called singleton - a class that will have only 1 instance through the applications' lifetime.

A simple test if you create multiple pools that get abandoned and use up your MySQL connection limit is to:

  1. start your webapplication
  2. access a page within it that is using MySQL data
  3. check number of open connections by your database user (mysqladmin processlist)
  4. Refresh your page (Ctrl+R) a few times and check number of open connections after each refresh
    If the number grows near linearly then you are affected by the programming flaw.

If your code is correct the number of connections will only vary between initialSize and maxActive (Apache DBCP Connection Pool) or min_size and max_size (C3P0 Connection Pool). Also remember to always set maxActive (max_size) lower then MySQL max_user_connections. You can check MySQL user connections limit with:

echo "show variables like 'max_user_connections%'" | mysql