stale connection pooling checks with mysql do not work

[mpiccione99]

I'm actually not sure where which forum to post this question on. At any rate, we have a ridiculous problem on our production server where the connections in the connection pool time out and we have to autorestart because the application can't reconnect. We have every possible check for stale connections in the configuration and have autoReconnect enabled. What could the problem possibly be?

Here is the connection pool configuration:

<!-- JDBC -->
<bean id="myDataSource" singleton="true" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.user}"/>
<property name="password" value="${db.pw}"/>
<property name="validationQuery" value="select 1"/>
<property name="testOnBorrow" value="true"/>
<property name="testOnReturn" value="false"/>
<property name="testWhileIdle" value="true"/>
<property name="timeBetweenEvictionRunsMillis" value="36000" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="300" />
<property name="logAbandoned" value="true" />
</bean>

Here is the connect string:

db.url=jdbc:mysql://localhost:3306/zas?zeroDateTimeBehavior=convertToNull&autoReconne ct=true


Here is the error message:

org.springframework.dao.RecoverableDataAccessExcep tion: PreparedStatementCallback; SQL [select * from zasTransaction where transactionId = ? order by received asc]; The last packet successfully received from the server was185036 seconds ago.The last packet sent successfully to the server was 185036 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.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsExce ption: The last packet successfully received from the server was185036 seconds ago.The last packet sent successfully to the server was 185036 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 org.springframework.jdbc.support.SQLExceptionSubcl assTranslator.doTranslate(SQLExceptionSubclassTran slator.java:97)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:607)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:641)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:670)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:678)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:710)
at com.zoombak.zas.dao.Dao.getTransaction(Dao.java:38 1)
at com.zoombak.zas.dao.DaoProxy.getTransaction(DaoPro xy.java:616)
at com.zoombak.zas.activation.ActivationManager.check ForTransaction(ActivationManager.java:121)
at com.zoombak.zas.activation.ActivationManager.proce ssSetServiceProfileRequest(ActivationManager.java: 67)
at com.zoombak.zas.servlet.SetServiceProfileServlet.p rocessRequest(SetServiceProfileServlet.java:305)
at com.zoombak.zas.servlet.SetServiceProfileServlet.d oPost(SetServiceProfileServlet.java:445)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run( JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsExce ption: The last packet successfully received from the server was185036 seconds ago.The last packet sent successfully to the server was 185036 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 sun.reflect.GeneratedConstructorAccessor75.newInst ance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Construc tor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:40 6)
at com.mysql.jdbc.SQLError.createCommunicationsExcept ion(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3246)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:19 17)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(P reparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeQuery(Prep aredStatement.java:1885)
at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeQuery(DelegatingPreparedStatement.java:93 )
at org.springframework.jdbc.core.JdbcTemplate$1.doInP reparedStatement(JdbcTemplate.java:648)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:591)
... 24 more
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutp utStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStre am.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedO utputStream.java:65)
at java.io.BufferedOutputStream.write(BufferedOutputS tream.java:109)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3225)
... 32 more

[Enrico Pizzi]

Don't use BasicDataSource in a production environment...this is wrong!

Always use container-managed data sources accessed in Spring via jndi using the jee: namespace (or defining JndiObjectFactoryBean). BasicDataSource, SimpleDataSource, DriverManagerDataSource etc. where all designed for testing purposes and for non-web applications, they do not have the monitoring-managing-handling facilities that an application server defined data source can offer and that are almost mandatory in the production environment of a widely used web application. Container-driven data sources also offer you a lot of nice bonuses like powerful transaction management(JTA), multi-datasources, datasources factories etc.

[Marten Deinum]

There is nothing wrong with using BasicDataSource in production ( i have used it in many applications without any problem) it is a fully capably connectionpool. In fact Tomcat (earlier versions) even used if for JNDI based connectionpools.

We also needed to set the validationQuery and connectionQuery properties to execute a query to determine validity.

I haven't used commons-dbcp recently because it isn't really maintained anymore, I either use [url=http://www.tomcatexpert.com/blog/2010/03/12/explaining-jdbc-pool-high-concurrency-alternative-connection-pooling-module]jdbc-pool[/url[ (which is now also used by tomcat) or BoneCP. Both of them are faster and are maintained.