Tag: MySQL

Categories
Java Tomcat

Solution to “Tomcat can’t stop [Abandoned connection cleanup thread]”

I am preparing a web service for one of my larger projects, using Tomcat, Hibernate and MySQL. While developing I stumbled across several Tomcat error messages that suggest some threads could not be stopped. Most of the threads could simply be vanished by calling Hibernate’s SessionFactory.close() method. However, a few threads seem to remain, e.g.

Jul 09, 2014 10:55:40 AM org.apache.catalina.loader.WebappClassLoader clearReferencesJdbc
SEVERE: The web application [/examples] registered the JDBC driver [com.mysql.jdbc.Driver] but failed to 
unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
Jul 09, 2014 10:55:40 AM org.apache.catalina.loader.WebappClassLoader clearReferencesThreads
SEVERE: The web application [/examples] appears to have started a thread named [Abandoned connection cleanup 
thread] but has failed to stop it. This is very likely to create a memory leak.

and in fact, Tomcat stopped working after a few redeployments due to memory problems. Two main reasons:

  1. The MySQL AbandonedConnectionCleanupThread does not shutdown correctly.
  2. The MySQL J/Connector driver doesn’t unregister anymore from the DriverManager.

At least with J/Connector V5.1.31 the problem disappears by using this shutdown procedure (hooked in by a ServletContextListener):

1
2
3
4
5
6
7
8
9
10
11
12
13

   try {
      com.mysql.jdbc.AbandonedConnectionCleanupThread.shutdown();
   } catch (Throwable t) {}
   // This manually deregisters JDBC driver, which prevents Tomcat 7 from complaining about memory leaks
   Enumeration<java .sql.Driver> drivers = java.sql.DriverManager.getDrivers();
   while (drivers.hasMoreElements()) {
      java.sql.Driver driver = drivers.nextElement();
      try {
         java.sql.DriverManager.deregisterDriver(driver);
      } catch (Throwable t) {}
   }
   try { Thread.sleep(2000L); } catch (Exception e) {}
</java>

I still have to observe long-term effects and will keep you informed.

Categories
Java RS Library

Using Pooled Data Connections with RS Libs Data Hibernate Library

The standard way of configuring a data source for RSLib’s Data Hibernate Library is to use the driver’s DataSource implementation as shown here:

1
2
3
4
5
6
7
8
9
10

<?xml version="1.0" encoding="UTF-8"?>
<dbconfig>
   <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
 
   <datasource class="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource">
      <property name="url">jdbc:mysql://db-host:3306/db-name</property>
      <property name="user">db-user</property>
      <property name="password">db-password</property>
   </datasource>
</dbconfig>

However, this setup will result in creating a new connection each time you start a new transaction. This is most likely not what you want in a productive application. Instead you shall use a pooled DataSource, as the c3p0 project provides:

1
2
3
4
5
6
7
8
9
10
11

<?xml version="1.0" encoding="UTF-8"?>
<dbconfig>
   <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
 
   <datasource class="com.mchange.v2.c3p0.ComboPooledDataSource">
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://db-host:3306/db-name?autoReconnect=true</property>
      <property name="user">db-user</property>
      <property name="password">db-password</property>
   </datasource>
</dbconfig>

I stumbled across this problem while implementing a project and receiving a

java.net.SocketException: No buffer space available (maximum connections reached?): connect

exception.

Update July 26nd: You will need the autoReconnect=true parameter with MySQL to avoid problems with connections in the pools not being used for quite a long time.

Update August 18th: This URL parameter doesn’t work correctly. The solution is to add a c3p0.properties file in your classpath:

1
2
3
4
5
6
7
8
9
10
11

c3p0.maxPoolSize=50
c3p0.minPoolSize=5
c3p0.timeout=0
c3p0.maxStatements=0
c3p0.maxStatementsPerConnection=0
c3p0.idleConnectionTestPeriod=300
c3p0.acquireIncrement=1
c3p0.validate=true
c3p0.preferredTestQuery=SELECT 1;
c3p0.testConnectionOnCheckin=false
c3p0.testConnectionOnCheckout=true

(Don’t try to set these properties via your hibernate.cfg file. Some of them cannot be set there!)