Tomcat JDBC Conencton Pool + MySQL给出了“Broken pipe”和“Broken pipe”。问题，即使是连接验证 [英] Tomcat JDBC Conencton Pool + MySQL gives "Broken pipe" problems, even with connection validation

问题描述

我正在配合Tomcat JDBC连接池以实现可靠性。目前的问题是，在测试环境中我在webapp中有这样的scanerio：

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:

• 第1天：一切正常


• 第2天：webapp几个小时都无法与MySQL通信，日志中的断管很多


• 第3天：令人惊讶的是，一切正常（没有注册或重启）

我已配置 validationInterval ， validationQuery ， validationTimeout 。这是我的数据源配置：

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>

我没有 autoReconnect = true 连接URL中的参数，只有UTF8编码。

I don't have autoReconnect=true parameter in connection URL, only UTF8 encoding.

确切的错误是：

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

这篇关于Tomcat JDBC Conencton Pool + MySQL给出了“Broken pipe”和“Broken pipe”。问题，即使是连接验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！