CommunicationsException and Connection Pooling

[Luke Shannon]

Hello;

I have a simple application that requires DB access. During some of it operations it was creating too many connections to the DB.

I created a simple connection pooling class.

This resolved the problem. The application obtains 10 connections to the DB and never needs any more than that.

The problem now is if the application has been used and than sits for a while (over night lets say). In the morning when I start it up I get the following exception:

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:1902)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2348)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2858)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)
at model.contact.ContactOptions.getNames(ContactOptions.java:66)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.commons.el.ArraySuffix.evaluate(ArraySuffix.java:314)
at org.apache.commons.el.ComplexValue.evaluate(ComplexValue.java:145)
at org.apache.commons.el.ExpressionEvaluatorImpl.evaluate(ExpressionEvaluatorImpl.java:263)
at org.apache.commons.el.ExpressionEvaluatorImpl.evaluate(ExpressionEvaluatorImpl.java:190)
at org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:899)
at org.apache.jsp.secure.auction.Auction_jsp._jspx_meth_c_forEach_0(Auction_jsp.java:153)
at org.apache.jsp.secure.auction.Auction_jsp._jspService(Auction_jsp.java:117)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:704)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:474)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:409)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:312)
at servlet.auction.AuctionInit.doGet(AuctionInit.java:55)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:300)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:374)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:743)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:675)
at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:866)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)


** END NESTED EXCEPTION **



Last packet sent to the server was 91 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2560)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2858)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)
at model.contact.ContactOptions.getNames(ContactOptions.java:66)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.commons.el.ArraySuffix.evaluate(ArraySuffix.java:314)
at org.apache.commons.el.ComplexValue.evaluate(ComplexValue.java:145)
at org.apache.commons.el.ExpressionEvaluatorImpl.evaluate(ExpressionEvaluatorImpl.java:263)
at org.apache.commons.el.ExpressionEvaluatorImpl.evaluate(ExpressionEvaluatorImpl.java:190)
at org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:899)
at org.apache.jsp.secure.auction.Auction_jsp._jspx_meth_c_forEach_0(Auction_jsp.java:153)
at org.apache.jsp.secure.auction.Auction_jsp._jspService(Auction_jsp.java:117)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:704)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:474)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:409)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:312)
at servlet.auction.AuctionInit.doGet(AuctionInit.java:55)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:300)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:374)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:743)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:675)
at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:866)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)

I don't know what to do with this. Here is my connection pool class. Any tips or suggestions would be very welcome.

public class DefaultJDBCConnectionFactory implements JDBCConnectionFactory {
 
private Hashtable connections;
 
private int increment;
 
private String strCon;
 
static Logger myLogger = Logger
.getLogger(DefaultJDBCConnectionFactory.class);
 
public DefaultJDBCConnectionFactory(String strCon, int intialConnections,
int increment) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
this.strCon = strCon;
this.increment = increment;
connections = new Hashtable();
//create all the intial connections required
for (int i = 0; i < intialConnections; i++) {
connections.put(DriverManager.getConnection(strCon), Boolean.FALSE);
}
myLogger.info(DefaultJDBCConnectionFactory.class.getName() + new Date()
+ " Connection pool created with " + connections.size()
+ " connections");
}
 
public synchronized Connection getConnection() throws SQLException {
Connection con = null;
Enumeration cons = connections.keys();
synchronized (connections) {
while (cons.hasMoreElements()) {
con = (Connection) cons.nextElement();
Boolean b = (Boolean) connections.get(con);
if (b == Boolean.FALSE) {
//this is an unused connection, lets test it
try {
con.getMetaData();
} catch (SQLException e) {
//bad connection, needs to be replaced
con = DriverManager.getConnection(strCon);
}
//update the hashtable to show this connection is taken
connections.put(con, Boolean.TRUE);
//return connection
return con;
}
}
}
//if we have got to here there were no free connections
//lets make more
for (int i = 0; i < increment; i++) {
connections.put(DriverManager.getConnection(strCon), Boolean.FALSE);
}
myLogger.info(DefaultJDBCConnectionFactory.class.getName() + new Date()
+ " Had to increase the number of connections. Now there are "
+ connections.size() + " in the pool.");
//recurse to get a free connection
return getConnection();
}
 
public void returnConnection(Connection returnMe) {
Connection con;
Enumeration cons = connections.keys();
while (cons.hasMoreElements()) {
con = (Connection) cons.nextElement();
if (con == returnMe) {
connections.put(con, Boolean.FALSE);
break;
}
}
myLogger.debug(DefaultJDBCConnectionFactory.class.getName()
+ new Date() + " Connection returned. " + connections.size()
+ " in the pool");
}
 
/**
 * Releases all the connections in the pool.
 * Used when there is no chance that the application will require a connection.
 * The best place is the contextDestroyed method of the FWListener Servlet.
 * @throws Exception
 */
public void releaseConnections() throws Exception {
Connection con = null;
Enumeration cons = connections.keys();
while (cons.hasMoreElements()) {
con = (Connection) cons.nextElement();
con.close();
con = null;
}
connections = null;
}
 

}

[Jeanne Boyarsky]

Luke,
As you've noticed, connections timeout after a while. The easiest solution is to catch the exception and try one more time. After the first one, the connection is dropped from the pool. The second request should get a new Connection.

[Luke Shannon]

Hi;

Thanks for your response.

I am a little confused. Do you mean something like this?

synchronized (connections) {
while (cons.hasMoreElements()) {
con = (Connection) cons.nextElement();
Boolean b = (Boolean) connections.get(con);
if (b == Boolean.FALSE) {
//this is an unused connection, lets test it
try {
con.getMetaData();
} catch (SQLException e) {
try {
//bad connection, needs to be replaced
con.close();
connections.remove(con);
con = DriverManager.getConnection(strCon);
} catch (SQLException e1) {
// TODO Auto-generated catch block
con = DriverManager.getConnection(strCon);
}
}
//update the hashtable to show this connection is taken
connections.put(con, Boolean.TRUE);
//return connection
return con;
}
}
}

[ August 15, 2005: Message edited by: Luke Shannon ]

[Jeanne Boyarsky]

Luke,
Almost. You really only need one statement to get the connection:
con = DriverManager.getConnection(strCon);

The first one is implicit from when you call:
Boolean b = (Boolean) connections.get(con);