Connection pooling problems
Since moving to using Tomcat from Resin for our production server and using spring templates for database connections the database connection pooling started to suffer from stale connections resulting in errors like this:
java.sql.SQLException: Communication link failure: java.io.EOFException, underlying cause: null
** BEGIN NESTED EXCEPTION **
After hunting around for a solution I found that adding the following parameters to the database configuration in JNDI in context.xml solved the issue which would eventually cause a permgen out of memory error completely freezing tomcat and the VM it was running on:
validationQuery=”select 1″ testOnBorrow=”true”
copied below is the post I found that was most useful in solving this from http://stackoverflow.com/questions/1448974/problem-with-connection-pooling-with-java-and-mysql-in-tomcat-web-application:
There are a few pointers on avoiding this situation, obtained from other sources, especially from the connection pool implementations of other drivers and from other application servers. Some of the information is already available in the Tomcat documentation on JNDI Data Sources.
As far are the behavior of your application is concerned, the user is probably seeing the result of the pool returning a stale connection to the application for the first time. The second time around, the pool probably returns a different connection that can service the application’s queries.
Tomcat JNDI Data Sources are based on Commons DBCP, so the configuration properties applicable to DBCP will apply to Tomcat as well.