I'm fighting with configuring Tomcat JDBC Connection Pool to achieve reliability. Current issue is that in test environment I have such scanerio in webapp:
I have configured validationInterval
, validationQuery
, validationTimeout
. This is my data source config:
<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource"
destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="username" value="${dbUser}" />
<property name="password" value="${dbPass}" />
<property name="url" value="${dbUrl}" />
<property name="defaultAutoCommit" value="false" />
<property name="defaultTransactionIsolation">
<util:constant static-field="java.sql.Connection.TRANSACTION_SERIALIZABLE" />
</property>
<property name="maxActive" value="300" />
<property name="maxIdle" value="25" />
<property name="initialSize" value="5" />
<property name="validationInterval" value="5000" />
<property name="validationQuery" value="SELECT 1"/>
<property name="validationQueryTimeout" value="3" />
<property name="minIdle" value="5" />
<property name="initSQL" value="SET time_zone = '+00:00';" />
</bean>
I don't have autoReconnect=true
parameter in connection URL, only UTF8 encoding.
The exact error is:
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
The last packet successfully received from the server was 38,700,615
milliseconds ago. The last packet sent successfully to the server was
38,700,615 milliseconds ago. 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.
Caused by: java.net.SocketException: Broken pipe
We had some similar problems with one of our applications and after a lot of digging we added the following properties that solved all our connection problems:
maxAge="180000"
testOnBorrow="true"
testWhileIdle="true"
validationInterval="0" //forces the connection pool to validate each time a connection is given to the application
You need to set 'testOnBorrow' to 'true', and probably 'maxAge' to less than the server's configured 'wait_timeout', as hinted in the message.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With