Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 440
Piotr Müller Avatar asked Apr 16 '14 07:04

Piotr Müller


2 Answers

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
like image 105
geoand Avatar answered Sep 27 '22 20:09

geoand


You need to set 'testOnBorrow' to 'true', and probably 'maxAge' to less than the server's configured 'wait_timeout', as hinted in the message.

like image 22
user207421 Avatar answered Sep 27 '22 20:09

user207421