aspose file tools*
The moose likes JDBC and the fly likes BasicDataSource Connection Pool  Exception Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "BasicDataSource Connection Pool  Exception" Watch "BasicDataSource Connection Pool  Exception" New topic
Author

BasicDataSource Connection Pool Exception

K.B. Stone
Greenhorn

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(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
Bartender

Joined: Apr 14, 2004
Posts: 10336

Mr Monkey, please check your private messages.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
David O'Meara
Rancher

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
Greenhorn

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
Greenhorn

Joined: Nov 08, 2008
Posts: 1
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
Sheriff

Joined: Oct 13, 2005
Posts: 39408
    
  28
"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: http://aspose.com/file-tools
 
subject: BasicDataSource Connection Pool Exception