• Post Reply Bookmark Topic Watch Topic
  • New Topic

BasicDataSource Connection Pool Exception  RSS feed

Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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(SQLError.java:1070)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3246)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1917)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at com.nmss.ivrAdmin.LoginBean.login(LoginBean.java:73)
at org.apache.jsp.jsp.login_jsp._jspService(login_jsp.java:81)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:541)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3227)
... 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 ]
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mr Monkey, please check your private messages.
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I had same issue with tomcat and mysql on my site www.vagree.com. 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
Posts: 56831
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!