Connection pooling with Java and MySQL in Tomcat web application

Question

I recently wrote and deployed a Java web application to a server and I'm finding an unusual problem which didn't appear during development or testing.

When a user logs in after so long and goes to display data from the database, the page indicates that there are no records to see. But upon page refresh, the first x records are shown according to the pagination rules.

Checking the logs, I find:

ERROR|19 09 2009|09 28 54|http-8080-4|myDataSharer.database_access.Database_Metadata_DBA| - Error getting types of columns of tabular Dataset 12

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.io.EOFException

STACKTRACE:

java.io.EOFException
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1956)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2368)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2867)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)

And so on for several hundred lines.

The application is currently set for about 100 users but is not yet in full use. It uses connection pooling between the Apache Tomcat servlets / jsps and a MySQL database with the following code example forming the general arrangement of a database operation, of which there are typically several per page:

// Gets a Dataset.
public static Dataset getDataset(int DatasetNo) {
    ConnectionPool_DBA pool = ConnectionPool_DBA.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;

    String query = ("SELECT * " +
                    "FROM Dataset " +
                    "WHERE DatasetNo = ?;");

    try {
        ps = connection.prepareStatement(query);
        ps.setInt(1, DatasetNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            Dataset d = new Dataset();
            d.setDatasetNo(rs.getInt("DatasetNo"));
            d.setDatasetName(rs.getString("DatasetName"));
            ...

            }

            return d;
        }
        else {
            return null;
        }
    }
    catch(Exception ex) {
        logger.error("Error getting Dataset " + DatasetNo + "\n", ex);            
        return null;
    }
    finally {
        DatabaseUtils.closeResultSet(rs);
        DatabaseUtils.closePreparedStatement(ps);
        pool.freeConnection(connection);
    }
}

Is anyone able to advise a way of correcting this problem?

I believe it is due to MySQL leaving connection poll connections open for up to eight hours but am not certain.

Thanks

Martin O'Shea.

---

Just to clarify one point made about my method of connection pooling, it isn't Oracle that I'm using in my application but a class of my own as follows:

package myDataSharer.database_access;

import java.sql.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import org.apache.log4j.Logger;

public class ConnectionPool_DBA {

    static Logger logger = Logger.getLogger(ConnectionPool_DBA.class.getName());

    private static ConnectionPool_DBA pool = null;
    private static DataSource dataSource = null;


    public synchronized static ConnectionPool_DBA getInstance() {
        if (pool == null) {
            pool = new ConnectionPool_DBA();
        }
        return pool;
    }

    private ConnectionPool_DBA() {
        try {
            InitialContext ic = new InitialContext();
            dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/myDataSharer");
        }
        catch(Exception ex) {
            logger.error("Error getting a connection pool's datasource\n", ex);
        }
    }

    public void freeConnection(Connection c) {
        try {
            c.close();
        }
        catch (Exception ex) {
            logger.error("Error terminating a connection pool connection\n", ex);           
        }
    }

    public Connection getConnection() {
        try {
            return dataSource.getConnection();
        }
        catch (Exception ex) {
            logger.error("Error getting a connection pool connection\n", ex);            
            return null;
        }
    }    
}

I think the mention of Oracle is due to me using a similar name.

Answer 1

There are a few pointers on avoiding this situation, obtained from other sources, especially from the connection pool implementations of other drivers and from other application servers. Some of the information is already available in the Tomcat documentation on JNDI Data Sources.

1. Establish a cleanup/reaper schedule that will close connections in the pool, if they are inactive beyond a certain period. It is not good practice to leave a connection to the database open for 8 hours (the MySQL default). On most application servers, the inactive connection timeout value is configurable and is usually less than 15 minutes (i.e. connections cannot be left in the pool for more than 15 minutes unless they are being reused time and again). In Tomcat, when using a JNDI DataSource, use the removeAbandoned and removeAbandonedTimeout settings to do the same.
2. When a new connection is return from the pool to the application, ensure that it is tested first. For instance, most application servers that I know, can be configured so that connection to an Oracle database are tested with an execute of "SELECT 1 FROM dual". In Tomcat, use the validationQuery property to set the appropriate query for MySQL - I believe this is "SELECT 1" (without quotes). The reason why setting the value of the validationQuery property helps, is because if the query fails to execute, the connection is dropped from the pool, and new one is created in its place.

As far are the behavior of your application is concerned, the user is probably seeing the result of the pool returning a stale connection to the application for the first time. The second time around, the pool probably returns a different connection that can service the application's queries.

Tomcat JNDI Data Sources are based on Commons DBCP, so the configuration properties applicable to DBCP will apply to Tomcat as well.