数据库连接每隔20天作用就会断掉，druid无法识别报错误

Question

 基础环境：mysql、spring-data-jpa、hibernate、tomcat、druid，datasource配置如下：

        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>

        <!-- 开启监控 -->
        <property name="filters" value="stat"/>

        <property name="maxActive" value="${jdbc.pool.maxActive}"/>
        <property name="initialSize" value="${jdbc.pool.initialSize}"/>
        <property name="maxWait" value="${jdbc.pool.maxWait}"/>
        <property name="minIdle" value="${jdbc.pool.minIdle}"/>

        <!-- 连接Idle最小连接30分钟，每15分钟检查一次 -->
        <property name="timeBetweenEvictionRunsMillis" value="900000"/>
        <property name="minEvictableIdleTimeMillis" value="1800000"/>

        <property name="validationQuery" value="SELECT user()"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="true"/>
        <property name="testOnReturn" value="false"/>

        <property name="poolPreparedStatements" value="true"/>
        <property name="maxOpenPreparedStatements" value="200"/>

        <property name="proxyFilters">
            <list>
                <ref bean="log-filter"/>
            </list>
        </property>

现象：服务每次运行20天作用就会出现部分接口插入数据异常，如下面的日志。

 基础环境：mysql、spring-data-jpa、hibernate、tomcat、druid，datasource配置如下：

        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>

        <!-- 开启监控 -->
        <property name="filters" value="stat"/>

        <property name="maxActive" value="${jdbc.pool.maxActive}"/>
        <property name="initialSize" value="${jdbc.pool.initialSize}"/>
        <property name="maxWait" value="${jdbc.pool.maxWait}"/>
        <property name="minIdle" value="${jdbc.pool.minIdle}"/>

        <!-- 连接Idle最小连接30分钟，每15分钟检查一次 -->
        <property name="timeBetweenEvictionRunsMillis" value="900000"/>
        <property name="minEvictableIdleTimeMillis" value="1800000"/>

        <property name="validationQuery" value="SELECT user()"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="true"/>
        <property name="testOnReturn" value="false"/>

        <property name="poolPreparedStatements" value="true"/>
        <property name="maxOpenPreparedStatements" value="200"/>

        <property name="proxyFilters">
            <list>
                <ref bean="log-filter"/>
            </list>
        </property>

现象：服务每次运行20天作用就会出现部分接口插入数据异常，如下面的日志。

初步推断：数据库连接长时间没有使用会被断掉，而jap不知道断掉了也不会自动重新连接。

{"errorInfo":{"cause":{"cause":{"cause":{"cause":null,"stackTrace":[
...
{"methodName":"run","fileName":"Thread.java","lineNumber":745,"className":"java.lang.Thread","nativeMethod":false}],"message":"Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.","localizedMessage":"Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.","suppressed":[]},"message":"could not extract ResultSet; nested exception is org.hibernate.exception.JDBCConnectionException: could not extract ResultSet","localizedMessage":"could not extract ResultSet; nested exception is org.hibernate.exception.JDBCConnectionException: could not extract ResultSet","suppressed":[]},"errorCode":1000}

核心提示：

could not extract ResultSet; nested exception is org.hibernate.exception.JDBCConnectionException: could not extract ResultSet

Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

初步推断：数据库连接长时间没有使用会被断掉，而jap不知道断掉了也不会自动重新连接。

 

Answer 1

谢谢，我试试看

Answer 2

druid连接池的空闲检测配置短一点，60秒检测一次

Answer 3

没有办法可以解决吗

Answer 4

mysql专有问题了吧。因为是数据库端自己关的，所以数据库连接池根本不知道。