I have had a hard time finding DBCP settings that worked well and I have found few examples of full configuration here is what were using. We have a Struts based application with Generated JDBC Daos and we use Spring to manage transactions. We deploy to tomcat 5.5 and tomcat 6 and MySQL 5. 

We were getting the Broken Pipe Socket Exception errors like this:

Executing Update
ERROR! ERROR! ERROR! ERROR! ERROR! ERROR! ERROR! ERROR! ERROR! ERROR!com.cms.shared.gen.firestorm.cmssql.exceptions.InmasterDaoException: SQLException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

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:2744)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1612)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
    at com.cms.shared.gen.firestorm.cmssql.jdbc.InmasterDaoImpl.findByDynamicSelect(InmasterDaoImpl.java:4433)
    at com.cms.shared.gen.firestorm.cmssql.jdbc.InmasterDaoImpl.findAll(InmasterDaoImpl.java:3465)

The key here was to set the idle time to less than the that the mysql wait_timeout and we had to test all connections in the pool during each eviction run.

Here is our Database Connection Pool Settings Configured  via Spring but the same settings should apply if configuring with JNDI in context.xml.

<!-- note in xml &amp; must be used in the JDBC url to seperate JDBC URL Parameters since & is a reserved xml character -->

<bean id="mainDataSource" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource"
       destroy-method="close"
       p:driverClassName="com.mysql.jdbc.Driver"
       p:url="jdbc:mysql://yourserver:3306/yourdatabase?

zeroDateTimeBehavior=convertToNull

&amp;dumpMetaDataOnColumnNotFound=true&amp;dumpQueriesOnException=true"

       p:username="username"
       p:password="password"
       p:initialSize="0" 
       p:maxActive="100" 
       p:maxIdle="30" 
       p:maxWait="10000" 
       p:removeAbandoned="true" 
       p:removeAbandonedTimeout="200" 
       p:logAbandoned="true" 
       p:defaultAutoCommit="false" 
       p:defaultReadOnly="false" 
       p:validationQuery="select 1"
       p:timeBetweenEvictionRunsMillis="1800000" 
       p:minEvictableIdleTimeMillis="10800000" 
       p:numTestsPerEvictionRun="-1"
       p:testWhileIdle="true" 
       p:testOnReturn="true" 
       p:testOnBorrow="true" 
       p:defaultTransactionIsolation="2" 
      /> 

A  key thing here is we did NOT use autoReconnect=true as a JDBC url parameter.

       p:validationQuery="select 1" - used to validate that the connection is valid whenever it is returned/borrowed/retrieved  to/from the pool

       p:timeBetweenEvictionRunsMillis="1800000"  - every 30 minutes
       p:minEvictableIdleTimeMillis="10800000" - 3 hours

The last peice for us seemed to be
       p:numTestsPerEvictionRun="-1" -1 meaning test all idle connection in the eviction run

Add a comment

Posted by Robert Sinner on September 26, 2008 3:54:00 PM PDT #