I examined related questions here on this topic, and also googled for some time. Still seems I don't understand something crytical in Tomcat's DBCP configuration or mechanics.
I got Tomcat 6, DBCP resource configured in server.xml
:
<Resource name="jdbc/myDB" auth="Container" description="Database connection"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://myhost:3306/mydb?autoReconnectForPools=true&useUnicode=true"
username="user"
password="password"
validationQuery="SELECT 1"
testOnBorrow="true"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="60000"
maxActive="20" maxWait="20000" maxIdle="10"
removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" />
Also I looked for mysql's wait_timeout
and it is default 28800
.
In summary I tried several options, but seems the problem is that mysql connections hit 28800 seconds of being idle and server closes them. I thought that pool should somehow handle this situation with validationQuery
and testOnBorrow
, but seems I'm wrong.
If I use autoReconnect
in url
after idle period My first query attempt results in "...CommunicationsException: The last packet successfully received from the server was 157,493,261 milliseconds ago.
" but after this it works normal.
If I use autoReconnectForPools
in url
or just don't use it - after idle period (8 hours) I got everytime ".MySQLNonTransientConnectionException: No operations allowed after connection closed
".
In both cases things go smoothly while app has load. So, I concluded that connections was closed from side of mysql.
Help, what have I missed? I'd like to solve this problem without changing wait_timeout in Mysql. Goal - stable app which may survive idle times if it occur :)