BasicDataSource Connection Pool Exception

[K.B. Stone]

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
===================================

Class.forName(driver).newInstance();
dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUsername("test");
dataSource.setPassword("abc123");
dataSource.setUrl("jdbc:mysql://localhost/testdb?autoReconnect=true");
dataSource.setMaxActive(1);
dataSource.setMaxIdle(1);



Thanks again!

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

[Paul Sturrock]

Mr Monkey, please check your private messages.

[David O'Meara]

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]

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]

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"
or
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
Thanks
web page

[Campbell Ritchie]

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