Java ConnectionPool connection not closing, stuck in 'sleep'

Question

I have a webapp that uses JNDI lookups to get a connection to the database.

The connection works fine and returns the query no problems. The issue us that the connection does not close properly and is stuck in the 'sleep' mode (according to mysql administrator). This means that they become unusable nad then I run out of connections.

Can someone give me a few pointers as to what I can do to make the connection return to the pool successfully.

public class DatabaseBean {

private static final Logger logger = Logger.getLogger(DatabaseBean.class);

private Connection conn;
private PreparedStatement prepStmt;

/**
 * Zero argument constructor
 * Setup generic databse connection in here to avoid redundancy
 * The connection details are in /META-INF/context.xml
 */
public DatabaseBean() {
    try {
        InitialContext initContext = new InitialContext();
        DataSource ds = (DataSource) initContext.lookup("java:/comp/env/jdbc/mysite");
        conn = ds.getConnection();
    }
    catch (SQLException SQLEx) {
        logger.fatal("There was a problem with the database connection.");
        logger.fatal(SQLEx);
        logger.fatal(SQLEx.getCause());
    }
    catch (NamingException nameEx) {
        logger.fatal("There was a naming exception");
        logger.fatal(nameEx);
        logger.fatal(nameEx.getCause());
    }
}

/**
 * Execute a query. Do not use for statements (update delete insert etc).
 *
 * @return A ResultSet of the execute query. A set of size zero if no results were returned. It is never null.
 * @see #executeUpdate() for running update, insert delete etc.
 */

public ResultSet executeQuery() {
    ResultSet result = null;
    try {
        result = prepStmt.executeQuery();
        logger.debug(prepStmt.toString());
    }
    catch (SQLException SQLEx) {
        logger.fatal("There was an error running a query");
        logger.fatal(SQLEx);
    }
    return result;
}

SNIP

public void close() {
    try {
        prepStmt.close();
        prepStmt = null;

        conn.close();
        conn = null;
    } catch (SQLException SQLEx) {
        logger.warn("There was an error closing the database connection.");
    }
}
}

This is inside a javabean that uses the database connection.

public LinkedList<ImportantNoticeBean> getImportantNotices() {

    DatabaseBean noticesDBBean = new DatabaseBean();
    LinkedList<ImportantNoticeBean> listOfNotices = new LinkedList<ImportantNoticeBean>();

    try {
        PreparedStatement preStmt = noticesDBBean.getConn().prepareStatement("SELECT pseudonym, message, date_to, date_from " +
                "FROM importantnotices, users " +
                "WHERE importantnotices.username = users.username " +
                "AND NOW() >= date_from AND NOW() <= date_to;");

        noticesDBBean.setPrepStmt(preStmt);
        ResultSet result = noticesDBBean.executeQuery();

        while (result.next()) {
            ImportantNoticeBean noticeBean = new ImportantNoticeBean();

            noticeBean.setAuthor(result.getString("pseudonym"));
            noticeBean.setMessage(result.getString("message"));
            noticeBean.setDateTo(result.getDate("date_to"));
            noticeBean.setDateFrom(result.getDate("date_from"));

            listOfNotices.add(noticeBean);
        }

        result.close();

    } catch (SQLException SQLEx) {
        logger.error("There was an error in ImportantNoticesBean.getImportantNotices()");
        logger.error(SQLEx);
    } finally {
        noticesDBBean.close();
    }
    return listOfNotices;
}

<Context reloadable="true">

    <Resource name="jdbc/mysite"
              auth="Container"
              type="javax.sql.DataSource"
              username="user"
              password="password"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mysite"
              maxActive="10"
              maxIdle="5"
              maxWait="6000"
              removeAbandoned="true"
              logAbandoned="false"
              removeAbandonedTimeout="20"
            />
</Context>

Answer 1

You seem to be closing the connection properly - except for the case where prepStmt.close() throws a SQLException, I can't find a connection leak.

What pool implementation are you using? When you close a connection, the pool need not close the underlying MySQL connection immediately - after all that is the point of a connection pool! So from MySQL side, the connections would look alive, although your app is not using any; they might simply be held by the TC connection pool.

You might want to experiment with the settings of the connection pool.Ask it to shrink the pool when the system is idle. Or, ask it to refresh all connections periodically. Or, have a strict upper bound on the number of concurrent connections it ever gets from MySQL etc.

One way to check if your code has a connection leak is to force the ds.getConnection() to always open a new physical connection and conn.close() to release the connection (if your connection pool has settings for those). Then if you watch the connections on MySQL side, you might be able to figure out if the code really has a connection leak or not.

Answer 2

One thing that @binil missed, you are not closing the result set in the case of an exception. Depending on the driver implementation this may cause the connection to stay open. Move the result.close() call to the finally block.

Answer 3

The only mention of connection pools in the startup logs is:

AbandonedObjectPool is used (org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool@cd70f7)

This tells me that I don't actually have a connection pool at all. Do I have to explicitly declare one or do I get one implicitly with tomcat (and I am configured wrong)?

Answer 4

This is a similar question - Connection Pool Settings for Tomcat

This is my response to that question and it fixed the problem for the other guy. It may help you out too.

Tomcat Documentation

DBCP uses the Jakarta-Commons Database Connection Pool. It relies on number of Jakarta-Commons components:

* Jakarta-Commons DBCP
* Jakarta-Commons Collections
* Jakarta-Commons Pool

I'm using the same connection pooling stuff and I'm setting these properties to prevent the same thing it's just not configured through tomcat. But if the first thing doesn't work try these.

testWhileIdle=true
timeBetweenEvictionRunsMillis=300000

Answer 5

Ok I might have this sorted. I have changed the database config resource to the following:

*SNIP*
maxActive="10"
maxIdle="5"
maxWait="7000"
removeAbandoned="true"
logAbandoned="false"
removeAbandonedTimeout="3"
*SNIP*

This works well enough for now. What is happening, afaik, is that once I reach the ten connections then Tomcat is checking for abandoned connections (idle time > 3). It does this in a batch job each time that max connections is reached. The potential issue with this is if i need more than 10 queries run at the same time (not unique to me). The important thing is that removeAbandonedTimeout is less than maxWait.

Is this what should be happening? ie Is this the way that the pool should operate? If it is is seems, at least to me, that you would wait until something (the connection) is broken before fixing rather than not letting it 'break' in the first place. Maybe I am still not getting it.

Answer 6

I am using the same configuration as you are. If the connection in mysql administrator(windows) shows that it is in sleep mode it only means that is pooled but not in use. I checked this running a test program program with multiple threads making random queries to Mysql. if it helps here is my configuration:

  defaultAutoCommit="false"
  defaultTransactionIsolation="REPEATABLE_READ"
  auth="Container"
  type="javax.sql.DataSource"
  logAbandoned="true" 
       removeAbandoned="true"
  removeAbandonedTimeout="300" 
  maxActive="-1"
  initialSize="15"
  maxIdle="10"
  maxWait="10000" 
  username="youruser"
  password="youruserpassword"
  driverClassName="com.mysql.jdbc.Driver"
  url="jdbc:mysql://yourhost/yourdatabase"/>