mysql超时断开连接 springboot配置。。。。。。。。。。

最新推荐文章于 2024-06-17 16:54:10 发布

gushenne@ian

最新推荐文章于 2024-06-17 16:54:10 发布

阅读量1.7k

点赞数 1

分类专栏： springboot mysql java 文章标签： java spring boot 数据库

本文链接：https://blog.csdn.net/gushenne/article/details/112258942

版权

java 同时被 3 个专栏收录

9 篇文章 0 订阅

订阅专栏

springboot

4 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

springboot2.1.17
alibaba druid1.2.4
mysql time_ out=1800s

druid配置连接池检测不生效
druid yml配置：

      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 150000
      # 配置一个连接在池中最大生存的时间，单位是毫秒
      maxEvictableIdleTimeMillis: 150000
      removeAbandoned: true
      removeAbandonedTimeout: 150
      log-abandoned: true
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: true
      testOnReturn: true

数据库访问量超过一定数量时候总是报错

The last packet sent successfully to the server was 2,562,822 milliseconds ago. is longer than the server configured value of 'wait_timeout'。。。。。。。

一开始以为是druid自检测yml配置不正确

后发现springboot默认连接池仅支持dbcp,dbcp2, tomcat, hikari

druid的话需要单独配置configuration

package com.mushi.mushiclient.config.druid;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;

/**
 * druid 配置属性
 * 
 * @author mushiwang
 */
@Configuration
public class DruidProperties
{
    @Value("${spring.datasource.druid.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.druid.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.druid.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.druid.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.druid.maxEvictableIdleTimeMillis}")
    private int maxEvictableIdleTimeMillis;

    @Value("${spring.datasource.druid.validationQuery}")
    private String validationQuery;

    @Value("${spring.datasource.druid.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.druid.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.druid.testOnReturn}")
    private boolean testOnReturn;

    public DruidDataSource dataSource(DruidDataSource datasource)
    {
        /** 配置初始化大小、最小、最大 */
        datasource.setInitialSize(initialSize);
        datasource.setMaxActive(maxActive);
        datasource.setMinIdle(minIdle);

        /** 配置获取连接等待超时的时间 */
        datasource.setMaxWait(maxWait);

        /** 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 */
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

        /** 配置一个连接在池中最小、最大生存的时间，单位是毫秒 */
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setMaxEvictableIdleTimeMillis(maxEvictableIdleTimeMillis);

        /**
         * 用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
         */
        datasource.setValidationQuery(validationQuery);
        /** 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。 */
        datasource.setTestWhileIdle(testWhileIdle);
        /** 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 */
        datasource.setTestOnBorrow(testOnBorrow);
        /** 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 */
        datasource.setTestOnReturn(testOnReturn);
        return datasource;
    }
}

package com.mushi.mushiclient.config.druid;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.util.Utils;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.servlet.*;
import javax.sql.DataSource;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

/**
 * druid 配置多数据源
 * 
 * @author mushiwang
 */
@Configuration
public class DruidConfig
{
    @Bean
    @ConfigurationProperties("spring.datasource.druid.master")
    public DataSource masterDataSource(DruidProperties druidProperties)
    {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.slave")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.slave", name = "enabled", havingValue = "true")
    public DataSource slaveDataSource(DruidProperties druidProperties)
    {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(DataSource masterDataSource, DataSource slaveDataSource)
    {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
        targetDataSources.put(DataSourceType.SLAVE.name(), slaveDataSource);
        return new DynamicDataSource(masterDataSource, targetDataSources);
    }

    /**
     * 去除监控页面底部的广告
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @Bean
    @ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")
    public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties)
    {
        // 获取web监控页面的参数
        DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
        // 提取common.js的配置路径
        String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
        String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");
        final String filePath = "support/http/resources/js/common.js";
        // 创建filter进行过滤
        Filter filter = new Filter()
        {
            @Override
            public void init(javax.servlet.FilterConfig filterConfig) throws ServletException
            {
            }
            @Override
            public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
                    throws IOException, ServletException
            {
                chain.doFilter(request, response);
                // 重置缓冲区，响应头不会被重置
                response.resetBuffer();
                // 获取common.js
                String text = Utils.readFromResource(filePath);
                // 正则替换banner, 除去底部的广告信息
                text = text.replaceAll("<a.*?banner\"></a><br/>", "");
                text = text.replaceAll("powered.*?shrek.wang</a>", "");
                response.getWriter().write(text);
            }
            @Override
            public void destroy()
            {
            }
        };
        FilterRegistrationBean registrationBean = new FilterRegistrationBean();
        registrationBean.setFilter(filter);
        registrationBean.addUrlPatterns(commonJsPattern);
        return registrationBean;
    }
}

package com.mushi.mushiclient.config.druid;

/**
 * 数据源
 * 
 * @author mushiwang
 */
public enum DataSourceType
{
    /**
     * 主库
     */
    MASTER,

    /**
     * 从库
     */
    SLAVE
}

package com.mushi.mushiclient.config.druid;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.Map;

/**
 * 动态数据源
 * 
 * @author mushiwang
 */
public class DynamicDataSource extends AbstractRoutingDataSource
{
    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources)
    {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey()
    {
        return DynamicDataSourceContextHolder.getDataSourceType();
    }
}

package com.mushi.mushiclient.config.druid;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据源切换处理
 * 
 * @author mushiwang
 */
public class DynamicDataSourceContextHolder
{
    public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);

    /**
     * 使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
     *  所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
     */
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    /**
     * 设置数据源的变量
     */
    public static void setDataSourceType(String dsType)
    {
        log.info("切换到{}数据源", dsType);
        CONTEXT_HOLDER.set(dsType);
    }

    /**
     * 获得数据源的变量
     */
    public static String getDataSourceType()
    {
        return CONTEXT_HOLDER.get();
    }

    /**
     * 清空数据源变量
     */
    public static void clearDataSourceType()
    {
        CONTEXT_HOLDER.remove();
    }
}

参考
https://blog.csdn.net/wlyang666/article/details/103378101
springboot 集成druid 报错Communications link failure

在这里插入图片描述
2021.1.9追加
目前还未生效依然报错

具体错

Caused by: java.sql.SQLException: connection disabled
	at com.alibaba.druid.pool.DruidPooledConnection.checkStateInternal(DruidPooledConnection.java:1170) ~[dru

查看github druid issue

使用druid连接池时数据库连接长时间未使用被数据库服务器主动关闭后使用连接报错（Error querying database. Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 9,103 milliseconds ago. The last packet sent successfully to the server was 9,110 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.），解决方案如下：
1、druid组件升级至1.1.16及以上版本

com.alibaba
druid-spring-boot-starter
1.1.16

2、数据库连接URL加入keepAlive=true参数，注意要删除连接URL中的autoReconnect=true参数
3、连接属性中的timeBetweenEvictionRunsMillis+minEvictableIdleTimeMillis要小于数据库服务器的wait_timeout值（可通过show global variables like 'wait_timeout'查看）

一个重点

wait_timeout>timeBetweenEvictionRunsMillis+minEvictableIdleTimeMillis

下面是很唠叨的解决过程和一些原理
。。。不一定对，没时间的可以不看。（第5点要稍微看看）

查阅内外网各种资料，通常会有以下解决方法：
1.增加mysql数据库的wait_timeout时间，默认8小时，最大可设置一年，拉满拉满，设置成一年就可以无忧了。
2.升级druid版本，低版本有bug，升级就完事了。
3.testWhileIdle、testOnBorrow、testOnReturn这三个参数设置成true，minEvictableIdleTimeMillis设置得小一点，设置成30000吧，就是30秒，这样每30秒就会检测一次连接池里的链接有没有效，无效的抛弃，很稳。
4.MySQL的jdbc连接串里加上属性autoReconnect=true，如果连接池的连接断了MySQL会自动重连，完美。
5.不知道是谁把MySQL的wait_timeout改成很短的时间（大概三十秒），改回默认的8小时就解决问题了，真讨厌。

以上方法我都排列组合地试过了，皆无效，没有用！

下面开始说正事了，先说一下原理

1.druid适合什么系统？
通俗点讲，druid设计的初衷是给大并发大流量的系统使用的数据库连接池，当一个冷系统，用了druid作为连接池，就可能会出现Communications link failure的问题，什么是冷系统？就是这个系统可能隔一两天甚至一个月都不会有人使用的那种，例如某些系统的管理后台，结论就是冷系统不适合用druid（太冷的系统其实不需要连接池...），那么能不能强行用呢，答案是可以的，但是要稍微搞清楚druid和MySQL的wait_timeout原理才能配置好连接池参数从而避免这类错误。

2.为什么会出现Communications link failure这种情况？
跟MySQL的wait_timeout参数有关，这个参数默认8小时（下面统一说8小时），可以随时改，如果想永久改就要改my.ini里的值。wait_timeout的作用是：MySQL会将空闲时间超过8小时的连接自动关闭掉，这个动作是偷偷地，不动声色地，所以，druid是不知道哪个连接被MySQL关闭了的，当有人进来用系统，如果druid分配了一个已经关闭掉的连接，就会报Communications link failure。

3.testWhileIdle、testOnBorrow、testOnReturn我都设置成true了，或者testWhileIdle=true，另外两个为false，为什么不生效？
不是不生效，你可能误解了这三个参数的作用，当程序从连接池获取数据库连接的时候，先通过运行设置的SQL“select 'x'”来检测连接是否有效，如果获取到的连接是已经被MySQL数据库主动关闭的连接，那么程序还是会用这个连接来跑SQL，结果就是会报错，查看源码，druid对连接是否能用是通过java.sql.Connection.isClosed() 这个方法来判断的，这个方法有个注释，说明了这个方法只能检测到程序自己关闭的程序，如果是数据库方面关闭的连接，这个方法是检测不出来的，注释原文如下：
“
Retrieves whether this Connection object has beenclosed. A connection is closed if the method closehas been called on it or if certain fatal errors have occurred.This method is guaranteed to return true only whenit is called after the method Connection.close hasbeen called.

This method generally cannot be called to determine whether aconnection to a database is valid or invalid. A typical clientcan determine that a connection is invalid by catching anyexceptions that might be thrown when an operation is attempted.

”
所以，偶尔出现Communications link failure是正常的，按照源码的逻辑，就算碰到了这种跑验证SQL就报错的连接，报错之后还会继续寻找有效连接，如果连接池里找不到，就会新建连接，所以正常情况下程序的运行是不会受影响的。但是，我发现某些情况下会有影响，造成的影响是，当前端操作某些查询时，会报这个错，然后前端就会一直在loading，不出结果，刷新之后会换一个连接池里的连接，如果又拿到一个死连，就又会一直loading，只能再刷新，如果连接池里的死连很多，就要刷新N次直到把死连都丢弃，拿到有效的连接才能正常查询得到正确结果。为什么会这样呢，目测是某些写得不好的低效率SQL导致的，这种情况要结合具体的程序和SQL来排查，这里就不展开了。

4.还有没有其他解决方法：
还真有，我试过将数据库的wait_timeout改成20秒，其他配置如上面所示，此时wait_timeout<timeBetweenEvictionRunsMillis+minEvictableIdleTimeMillis=60s，这样的话就能神奇地解决这个问题，但是wait_timeout设置得这么短，有点害怕，不敢这么搞，怕会频繁GC，也没有深究原理，反正就是能解决Communications link failure的问题。。。

5.一些要注意的点
MySQL的连接字符串里，如果你MySQL版本是5以上的，请不要加autoReconnect=true，不然就算你配置了我上面说的druid属性最终还是会报奇怪的错误。

2021.2.5更新

上述方法还是不行继续报错

网上有说是阿里云服务器部署的话数据库连接的ip是要改成阿里云服务器内网ip ，我之前用的是公网ip
有待考证。。。

2021.3.10
数据库连接ip应该写成阿里云服务器内网ip或者localhost 解决

gushenne@ian

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
mysql超时断开连接 springboot配置。。。。。。。。。。

springboot2.1.17alibaba druid1.2.4mysql time_ out=1800sdruid配置连接池检测不生效druid yml配置： timeBetweenEvictionRunsMillis: 60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 minEvictableIdleTimeMillis: 150000 # 配置一个连接在池中最大生存的时间，单位是毫秒 maxEvictableIdleT
复制链接

扫一扫