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>


Answers

The issue us that the connection does not close properly and is stuck in the 'sleep' mode

This was actually only half right.

The problem I ran into was actually that each app was defining a new connection to the database sever. So each time I closed all the connections App A would make a bunch of new connections as per it's WEB.xml config file and run happily. App B would do the same. The problem is that they are independent pools which try to grab up to the server defined limit. It is a kind of race condition I guess. So when App A has finished with the connections it sits waiting to to use them again until the timeout has passed while App B who needs the connection now is denied the resources even though App A has finished with the and should be back in the pool. Once the timeout has passed, the connection is freed up and B (or C etc) can get at it again.

e.g. if the limit is 10 (mySQL profile limit) and each app has been configured to use a max of 10 the there will be 20 attempts at connections. Obviously this is a bad situation.

The solution is to RTFM and put the connection details in the right place. This does make shared posting a pain but there are ways around it (such as linking to other xml files from the context).

Just to be explicit: I put the connection details in the WEB.xml for each app and the had a fight about it.



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"/>


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.



This video can help you solving your question :)
By: admin