File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes BasicDataSource Connection Pool  Exception Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "BasicDataSource Connection Pool  Exception" Watch "BasicDataSource Connection Pool  Exception" New topic

BasicDataSource Connection Pool Exception

K.B. Stone

Joined: Sep 11, 2008
Posts: 8
Hi Experts,

I got exception "com.mysql.jdbc.CommunicationsException" when executing query to MySQL Server using one of the connection from connection pools.

From my observation, the connection works properly (I open a jsp for making an query to a table. The SQL statement executes successfully ), However, after 9 hours, when I trigger the same jsp again, It failed with long waiting, in fact, INFORMATION 2 appeared in catalina.out

I suspect that the connection in the connection pool may be stale, as I got some news about connection's 8hours expiry. How can I have the setting of data source, so that
- the connection in db pool will not expired, and
- once connection is stale in pool, re-creation of connection can be done automatically.

I am also seeking for any information/guideline in Connection Pool, Welcome to send me a reference. Thank you very much!

INFORMATION 1: Environment
TOMCAT : apache-tomcat-5.5.20
JDBC library
MySQL : 5.0.51a-community
MySQL Connector : mysql-connector-java-5.1.6-bin.jar
BasicDataSource.class : commons-dbcp-1.2.2.jar

INFORMATION 2 : Exception captured.

com.mysql.jdbc.CommunicationsException: The last packet successfully received from the server was50336 seconds ago.The last packet sent successfully to the server was 50336 seconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at com.mysql.jdbc.SQLError.createCommunicationsException(
at com.mysql.jdbc.MysqlIO.send(
at com.mysql.jdbc.MysqlIO.sendCommand(
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(
at com.mysql.jdbc.ConnectionImpl.execSQL(
at com.mysql.jdbc.PreparedStatement.executeInternal(
at com.mysql.jdbc.PreparedStatement.executeQuery(
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(
at com.nmss.ivrAdmin.LoginBean.login(
at org.apache.jsp.jsp.login_jsp._jspService(
at org.apache.jasper.runtime.HttpJspBase.service(
at javax.servlet.http.HttpServlet.service(
at org.apache.jasper.servlet.JspServletWrapper.service(
at org.apache.jasper.servlet.JspServlet.serviceJspFile(
at org.apache.jasper.servlet.JspServlet.service(
at javax.servlet.http.HttpServlet.service(
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(
at org.apache.catalina.core.ApplicationFilterChain.doFilter(
at org.apache.catalina.core.StandardWrapperValve.invoke(
at org.apache.catalina.core.StandardContextValve.invoke(
at org.apache.catalina.core.StandardHostValve.invoke(
at org.apache.catalina.valves.ErrorReportValve.invoke(
at org.apache.catalina.valves.AccessLogValve.invoke(
at org.apache.catalina.core.StandardEngineValve.invoke(
at org.apache.catalina.connector.CoyoteAdapter.service(
at org.apache.coyote.http11.Http11Processor.process(
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(
at org.apache.tomcat.util.threads.ThreadPool$
Caused by: Broken pipe
at Method)
at com.mysql.jdbc.MysqlIO.send(
... 29 more

INFORMATION 3 : DataSource Setting

dataSource = new BasicDataSource();

Thanks again!

[ September 11, 2008: Message edited by: Mr Monkey ]
[ September 11, 2008: Message edited by: Mr Monkey ]
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Mr Monkey, please check your private messages.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

Ii see you already have the "?autoReconnect=true" on your JDBC URL, you may also want to add configuration to the pool to check the connection when it gets taken from the pool. This will prevent the pool handing out stale connections.
K.B. Stone

Joined: Sep 11, 2008
Posts: 8
Initially, I expect that "?autoReconnect=true" could be workable for reconnection of the stale connection. But, in fact, that property seems no effect on the reconnection of stale connection, still have exception. Hence, I am seeking extra reference for properties setting and usage.
Jeff Thomson

Joined: Nov 08, 2008
Posts: 1
I had same issue with tomcat and mysql on my site My sql kills inactive threads after some time. The default for 5.0 and up being 28800 sec. If a connection is not used with in this time it's killed by mysql. You can have two fixes for it.
1> Either put a validation query in your Datasource definition of tomcat
for e.g. query will be "Select 1"
2> or you can put test connection attribute. So when ever you ask for a connection from pool its teted if it fails its droped from pool and pool provides a differnt connection.
Hope it helps
web page
Campbell Ritchie

Joined: Oct 13, 2005
Posts: 46412
"Simple Vik" please read the important administrative private message I have just sent you.

And please edit your post to show the correct web page; the link you quoted is to the vagree home page.
I agree. Here's the link:
subject: BasicDataSource Connection Pool Exception
It's not a secret anymore!