关于mysql报 loopWaitCount 0, wait millis 60001 错误的解决办法

最近遇到个比较奇怪的问题，系统上线一段时间之后，总是隔一段时间就出现Tomcat连接数据库报错，导致系统无法运行。

通过日志排查发现，里面报了一个错误，内容如下：

2023-04-17 00:01:05 [ ERROR ] [ AcquireJobsRunnableImpl.java :77(run)] exception during job acquisition: Could not open JDBC Connection for transaction; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: loopWaitCount 0, wait millis 60001, active 50
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: loopWaitCount 0, wait millis 60001, active 50
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:243)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
    at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)
    at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
    at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
    at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
    at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnableImpl.run(AcquireJobsRunnableImpl.java:54)
    at java.lang.Thread.run(Unknown Source)
Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: loopWaitCount 0, wait millis 60001, active 50
    at com.alibaba.druid.pool.DruidDataSource.pollLast(DruidDataSource.java:1375)
    at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1019)
    at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:902)
    at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4534)
    at com.alibaba.druid.filter.stat.StatFilter.dataSource_getConnection(StatFilter.java:661)
    at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4530)
    at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:884)
    at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:876)
    at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:92)
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:205)
    ... 8 more

 

这个异常可能是因为druid连接池溢出产生的异常。连接超时，是因为某个连接没有成功，而且没有开启druid的超时回收导致的。

原来的配置文件中如下：

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
        init-method="init" destroy-method="close">
        <!-- 基本属性 url、user、password -->
        <property name="url" value="${connection.url}" />
        <property name="username" value="${connection.username}" />
        <property name="password" value="${connection.password}" />
        <property name="dbType" value = "${connection.dbType}" />
        <property name="driverClassName" value="${connection.driverClassName}"/>

        <!-- 配置初始化大小、最小、最大 -->
        <property name="initialSize" value="${druid.initialSize}" />
        <property name="minIdle" value="${druid.minIdle}" />
        <property name="maxActive" value="${druid.maxActive}" />

        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait" value="${druid.maxWait}" />
        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}" />

        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="${druid.minEvictableIdleTimeMillis}" />

        <property name="validationQuery" value="${druid.validationQuery}" />
        <property name="testWhileIdle" value="${druid.testWhileIdle}" />
        <property name="testOnBorrow" value="${druid.testOnBorrow}" />
        <property name="testOnReturn" value="${druid.testOnReturn}" />

        <!-- 打开PSCache，并且指定每个连接上PSCache的大小 如果用Oracle，则把poolPreparedStatements配置为true，mysql可以配置为false。 -->
        <property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" />
        <property name="maxPoolPreparedStatementPerConnectionSize"
            value="${druid.maxPoolPreparedStatementPerConnectionSize}" />

        <!-- 配置监控统计拦截的filters -->
        <property name="filters" value="${druid.filters}" />

    </bean>

druid.initialSize=10
druid.minIdle=10
druid.maxActive=50
druid.maxWait=60000
druid.timeBetweenEvictionRunsMillis=60000
druid.minEvictableIdleTimeMillis=300000
druid.validationQuery=SELECT 'x'
druid.testWhileIdle=true
druid.testOnBorrow=false
druid.testOnReturn=false
druid.poolPreparedStatements=true
druid.maxPoolPreparedStatementPerConnectionSize=20
#druid.filters=wall,stat 设置wall导致无法创建sqlserver数据库，属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
druid.filters=stat

通过排查数据库配置，原来的配置没有配置垃圾回收机制，导致druid的超时回收，调整更改配置文件的xml和配置文件，分别增加如下内容

XML增加内容

<!-- 超时时间限制是否回收 -->
<property name="removeAbandoned" value="${druid.removeAbandoned}" />
<!-- 超时时间,单位为秒,如300秒=5分钟 -->
<property name="removeAbandonedTimeout" value="${druid.removeAbandonedTimeout}" />
<!-- 超时时间,单位为毫秒 -->
<property name="logAbandoned" value="${druid.logAbandoned}" />

 

配置文件增加内容

#druid recycle 内存回收
druid.removeAbandoned=true
druid.removeAbandonedTimeout=300
druid.logAbandoned=false

通过调整配置之后，Tomcat再也没有出现以上问题。