Tomcat JDBC Conencton Pool + MySQL gives "Broken pipe" problems, even with connection validation

connection-pooling, java, jdbc, mysql, tomcat

Solution

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

Problem

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: - day 1: everything works fine - day 2: webapp cannot comunicate with MySQL for several hours, lot of "Broken pipe" in logs - day 3: suprisingly, everything works fine again (without ingerention or restart) 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 ```

Original source