springboot+druid+atomikos 8小时后数据库连接无法使用，多请求几次又可以使用了

qiaoyajun520 commented 5 years ago

Atomikos数据源： @Bean(name = "dataSourceDmc") public DataSource dataSourceDmc() { AtomikosDataSourceBean ds = new AtomikosDataSourceBean(); Properties prop = build(env, "spring.datasource.druid.dmcdb."); ds.setXaDataSourceClassName(dataSourceType); ds.setPoolSize(5); ds.setXaProperties(prop); return ds; }

yml配置 dmcdb:

JDBC配置

    name: DmcDBConfig
    url: jdbc:mysql://192.168.2.214:3306/test_dmc?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: reborn
    password: 123456
    driverClassName: com.mysql.cj.jdbc.Driver
    filters: stat,wall,slf4j
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 1
    testWhileIdle: true
    testOnBorrow: true
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20

druid版本：1.1.16 mysql 8.0.11 driver

报错信息： The last packet successfully received from the server was 58,839,119 milliseconds ago. The last packet sent successfully to the server was 58,839,120 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.

CQ-Harry commented 5 years ago

朋友，最后解决没有啊？我现在同样的问题。

qiaoyajun520 commented 5 years ago

朋友，最后解决没有啊？我现在同样的问题。

atomikos: maxPoolSize: 5 minPoolSize: 0 openTestQuery: false

druid:
  ## 连接池配置
  userdb:
    ## JDBC配置
    name: UserDBConfig
    url: jdbc:mysql://xxxx
    username: reborn
    password: 123456
    driverClassName: com.mysql.cj.jdbc.Driver
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,slf4j
    maxActive: 20
    initialSize: 1
    maxWait: 6000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 1
    validationQueryTimeout: 2000
    keepAlive: true
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
    removeAbandoned: false
    removeAbandonedTimeout: 80
    logAbandoned: true

@Primary
@Bean(name = "dataSourceUser")
public DataSource dataSourceUser() {
    AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
    Properties prop = build(env, "spring.datasource.druid.userdb.");
    ds.setXaDataSourceClassName(dataSourceType);
    ds.setMaxPoolSize(maxPoolSize);
    ds.setMinPoolSize(minPoolSize);
    ds.setXaProperties(prop);
    if (openTestQuery) {
        ds.setTestQuery("select 1");
    }
    return ds;
}

把AtomikosDataSourceBean 的最小连接池minPoolSize设为0，问题暂时解决了。

CQ-Harry commented 5 years ago

@qiaoyajun520 改成0依然不行，-_-

qiaoyajun520 commented 5 years ago

@qiaoyajun520 改成0依然不行，-_-

把你的配置信息贴出来看一下

CQ-Harry commented 5 years ago

@qiaoyajun520 改成0依然不行，-_-

把你的配置信息贴出来看一下

spring:
  redis:
    password:      # 密码（默认为空）
    # 连接池中连接池中的最大空闲连接的最小空闲连接
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    druid:
      iot:  #数据源1
        url: jdbc:mysql://127.0.0.1:3306/dev_authz?autoReconnect=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password:
      hzx:  #数据源2
        url: jdbc:mysql://127.0.0.1:3306/dev_hzx?autoReconnect=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password:
      smalltools:  #数据源3
        url: jdbc:mysql://127.0.0.1:3306/dev_smalltools?autoReconnect=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password:
      activiti:  #数据源4
        url: jdbc:mysql://127.0.0.1:3306/dev_hzx?autoReconnect=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password:
      initial-size: 10
      max-active: 100
      min-idle: 5
      max-wait: 600
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        #login-username: admin
        #login-password: admin
      filter:
        stat:
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: false
        wall:
          config:
            multi-statement-allow: true
      #      filters: config,wall,log4j

/**
     * 数据源创建模板
     */
    private static DataSource createDataSource(String dataSourceName, DruidProperties druidProperties) {
        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
        atomikosDataSourceBean.setUniqueResourceName(dataSourceName);
        atomikosDataSourceBean.setXaProperties(druidProperties.createProperties());
        atomikosDataSourceBean.setMinPoolSize(0);
        return atomikosDataSourceBean;
    }
 public Properties createProperties() {
        Properties properties = new Properties();
        properties.put("url", this.url);
        properties.put("username", this.username);
        properties.put("password", this.password);
        properties.put("driverClassName", this.driverClassName);
        properties.put("initialSize", this.initialSize);
        properties.put("maxActive", this.maxActive);
        properties.put("minIdle", this.minIdle);
        properties.put("maxWait", this.maxWait);
        properties.put("poolPreparedStatements", this.poolPreparedStatements);
        properties.put("maxPoolPreparedStatementPerConnectionSize", this.maxPoolPreparedStatementPerConnectionSize);
        properties.put("validationQuery", this.validationQuery);
        properties.put("testOnBorrow", this.testOnBorrow);
        properties.put("testOnReturn", this.testOnReturn);
        properties.put("testWhileIdle", this.testWhileIdle);
        properties.put("timeBetweenEvictionRunsMillis", this.timeBetweenEvictionRunsMillis);
        properties.put("minEvictableIdleTimeMillis", this.minEvictableIdleTimeMillis);
        properties.put("filters", this.filters);
        properties.put("keepAlive", true);
        return properties;
    }

qiaoyajun520 commented 5 years ago

我的全部配置，你参考下

yml

spring: aop: proxy-target-class: true auto: true datasource: type: com.alibaba.druid.pool.xa.DruidXADataSource atomikos: maxPoolSize: 5 minPoolSize: 0 openTestQuery: false druid:

连接池配置

  userdb:
    ## JDBC配置
    name: UserDBConfig
    url: jdbc:mysql://192.168.2.214:3306/reborn_user?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: reborn
    password: xxx
    driverClassName: com.mysql.cj.jdbc.Driver
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,slf4j
    maxActive: 20
    initialSize: 1
    maxWait: 6000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 1
    validationQueryTimeout: 2000
    keepAlive: true
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
    removeAbandoned: false
    removeAbandonedTimeout: 80
    logAbandoned: true
  dmcdb:
    ## JDBC配置
    name: DmcDBConfig
    url: jdbc:mysql://192.168.2.214:3306/test_dmc?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: reborn
    password: Reborn@2018
    driverClassName: com.mysql.cj.jdbc.Driver
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,slf4j
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 1
    validationQueryTimeout: 2000
    keepAlive: true
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
    removeAbandoned: false
    removeAbandonedTimeout: 80
    logAbandoned: true

DataSource

@Configuration public class DataSourceConfig { @Autowired private Environment env;

@Value("${spring.datasource.type}")
private String dataSourceType;

@Value("${spring.datasource.atomikos.maxPoolSize}")
private Integer maxPoolSize;
@Value("${spring.datasource.atomikos.minPoolSize}")
private Integer minPoolSize;
@Value("${spring.datasource.atomikos.openTestQuery}")
private Boolean openTestQuery;

@Primary
@Bean(name = "dataSourceUser")
public DataSource dataSourceUser() {
    AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
    Properties prop = build(env, "spring.datasource.druid.userdb.");
    ds.setXaDataSourceClassName(dataSourceType);
    ds.setMaxPoolSize(maxPoolSize);
    ds.setMinPoolSize(minPoolSize);
    ds.setXaProperties(prop);
    if (openTestQuery) {
        ds.setTestQuery("select 1");
    }
    return ds;
}

@Bean(name = "dataSourceDmc")
public DataSource dataSourceDmc() {
    AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
    Properties prop = build(env, "spring.datasource.druid.dmcdb.");
    ds.setXaDataSourceClassName(dataSourceType);
    ds.setMaxPoolSize(maxPoolSize);
    ds.setMinPoolSize(minPoolSize);
    ds.setXaProperties(prop);
    if (openTestQuery) {
        ds.setTestQuery("select 1");
    }
    return ds;
}

private Properties build(Environment env, String prefix) {
    Properties prop = new Properties();
    prop.put("name", env.getProperty(prefix + "name"));
    prop.put("url", env.getProperty(prefix + "url"));
    prop.put("username", env.getProperty(prefix + "username"));
    prop.put("password", env.getProperty(prefix + "password"));
    prop.put("driverClassName", env.getProperty(prefix + "driverClassName", ""));
    prop.put("filters", env.getProperty(prefix + "filters"));
    prop.put("maxActive", env.getProperty(prefix + "maxActive", Integer.class));
    prop.put("initialSize", env.getProperty(prefix + "initialSize", Integer.class));
    prop.put("maxWait", env.getProperty(prefix + "maxWait", Integer.class));
    prop.put("minIdle", env.getProperty(prefix + "minIdle", Integer.class));
    prop.put("timeBetweenEvictionRunsMillis",
            env.getProperty(prefix + "timeBetweenEvictionRunsMillis", Integer.class));
    prop.put("minEvictableIdleTimeMillis", env.getProperty(prefix + "minEvictableIdleTimeMillis", Integer.class));
    prop.put("validationQuery", env.getProperty(prefix + "validationQuery"));
    prop.put("validationQueryTimeout", env.getProperty(prefix + "validationQueryTimeout", Integer.class));
    prop.put("keepAlive", env.getProperty(prefix + "keepAlive", Boolean.class));
    prop.put("testWhileIdle", env.getProperty(prefix + "testWhileIdle", Boolean.class));
    prop.put("testOnBorrow", env.getProperty(prefix + "testOnBorrow", Boolean.class));
    prop.put("testOnReturn", env.getProperty(prefix + "testOnReturn", Boolean.class));
    prop.put("poolPreparedStatements", env.getProperty(prefix + "poolPreparedStatements", Boolean.class));
    prop.put("maxOpenPreparedStatements", env.getProperty(prefix + "maxOpenPreparedStatements", Integer.class));

// prop.put("removeAbandoned", env.getProperty(prefix + "removeAbandoned", Boolean.class)); // prop.put("removeAbandonedTimeout", env.getProperty(prefix + "removeAbandonedTimeout", Integer.class)); // prop.put("logAbandoned", env.getProperty(prefix + "logAbandoned", Boolean.class)); return prop; } }

qiaoyajun520 commented 5 years ago

我的全部配置，你参考下

yml

spring: aop: proxy-target-class: true auto: true datasource: type: com.alibaba.druid.pool.xa.DruidXADataSource atomikos: maxPoolSize: 5 minPoolSize: 0 openTestQuery: false druid:

连接池配置

  userdb:
    ## JDBC配置
    name: UserDBConfig
    url: jdbc:mysql://192.168.2.214:3306/reborn_user?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: reborn
    password: xxx
    driverClassName: com.mysql.cj.jdbc.Driver
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,slf4j
    maxActive: 20
    initialSize: 1
    maxWait: 6000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 1
    validationQueryTimeout: 2000
    keepAlive: true
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
    removeAbandoned: false
    removeAbandonedTimeout: 80
    logAbandoned: true
  dmcdb:
    ## JDBC配置
    name: DmcDBConfig
    url: jdbc:mysql://192.168.2.214:3306/test_dmc?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: reborn
    password: Reborn@2018
    driverClassName: com.mysql.cj.jdbc.Driver
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,slf4j
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 1
    validationQueryTimeout: 2000
    keepAlive: true
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
    removeAbandoned: false
    removeAbandonedTimeout: 80
    logAbandoned: true

DataSource

@Configuration public class DataSourceConfig { @Autowired private Environment env;

@Value("${spring.datasource.type}")
private String dataSourceType;

@Value("${spring.datasource.atomikos.maxPoolSize}")
private Integer maxPoolSize;
@Value("${spring.datasource.atomikos.minPoolSize}")
private Integer minPoolSize;
@Value("${spring.datasource.atomikos.openTestQuery}")
private Boolean openTestQuery;

@Primary
@Bean(name = "dataSourceUser")
public DataSource dataSourceUser() {
    AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
    Properties prop = build(env, "spring.datasource.druid.userdb.");
    ds.setXaDataSourceClassName(dataSourceType);
    ds.setMaxPoolSize(maxPoolSize);
    ds.setMinPoolSize(minPoolSize);
    ds.setXaProperties(prop);
    if (openTestQuery) {
        ds.setTestQuery("select 1");
    }
    return ds;
}

@Bean(name = "dataSourceDmc")
public DataSource dataSourceDmc() {
    AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
    Properties prop = build(env, "spring.datasource.druid.dmcdb.");
    ds.setXaDataSourceClassName(dataSourceType);
    ds.setMaxPoolSize(maxPoolSize);
    ds.setMinPoolSize(minPoolSize);
    ds.setXaProperties(prop);
    if (openTestQuery) {
        ds.setTestQuery("select 1");
    }
    return ds;
}

private Properties build(Environment env, String prefix) {
    Properties prop = new Properties();
    prop.put("name", env.getProperty(prefix + "name"));
    prop.put("url", env.getProperty(prefix + "url"));
    prop.put("username", env.getProperty(prefix + "username"));
    prop.put("password", env.getProperty(prefix + "password"));
    prop.put("driverClassName", env.getProperty(prefix + "driverClassName", ""));
    prop.put("filters", env.getProperty(prefix + "filters"));
    prop.put("maxActive", env.getProperty(prefix + "maxActive", Integer.class));
    prop.put("initialSize", env.getProperty(prefix + "initialSize", Integer.class));
    prop.put("maxWait", env.getProperty(prefix + "maxWait", Integer.class));
    prop.put("minIdle", env.getProperty(prefix + "minIdle", Integer.class));
    prop.put("timeBetweenEvictionRunsMillis",
            env.getProperty(prefix + "timeBetweenEvictionRunsMillis", Integer.class));
    prop.put("minEvictableIdleTimeMillis", env.getProperty(prefix + "minEvictableIdleTimeMillis", Integer.class));
    prop.put("validationQuery", env.getProperty(prefix + "validationQuery"));
    prop.put("validationQueryTimeout", env.getProperty(prefix + "validationQueryTimeout", Integer.class));
    prop.put("keepAlive", env.getProperty(prefix + "keepAlive", Boolean.class));
    prop.put("testWhileIdle", env.getProperty(prefix + "testWhileIdle", Boolean.class));
    prop.put("testOnBorrow", env.getProperty(prefix + "testOnBorrow", Boolean.class));
    prop.put("testOnReturn", env.getProperty(prefix + "testOnReturn", Boolean.class));
    prop.put("poolPreparedStatements", env.getProperty(prefix + "poolPreparedStatements", Boolean.class));
    prop.put("maxOpenPreparedStatements", env.getProperty(prefix + "maxOpenPreparedStatements", Integer.class));

// prop.put("removeAbandoned", env.getProperty(prefix + "removeAbandoned", Boolean.class)); // prop.put("removeAbandonedTimeout", env.getProperty(prefix + "removeAbandonedTimeout", Integer.class)); // prop.put("logAbandoned", env.getProperty(prefix + "logAbandoned", Boolean.class)); return prop; } }

CQ-Harry commented 5 years ago

@qiaoyajun520 能提供下spring-boot-starter-jta-atomikos、com.alibaba.druid，mysql-connector-java版本嘛？

qiaoyajun520 commented 5 years ago

@qiaoyajun520 能提供下spring-boot-starter-jta-atomikos、com.alibaba.druid，mysql-connector-java版本嘛？

CQ-Harry commented 5 years ago

@qiaoyajun520 谢谢了！

ioandy commented 5 years ago

@qiaoyajun520 @CQ-Harry, 我也遇到了同样的问题，查了好多资料都没弄好！有的说是atomikos版本低了，有bug，有的说要延长mysql的wait-timeout。。。我自己发先test-while-idle好像不起作用请问你们的问题都解决了吗，该如何解决？我的版本信息 Springboot: 1.5.17 spring-boot-starter-jta-atomikos: 1.5.17 对应的atomikos是3.9.3，网上有的说是版本低了，我手动改成了4.0.4 druid: 1.1.16 mysql: 5.7.20 mysql-connector-java:5.1.47

longdw commented 3 years ago

我也遇到同样的问题，隔了一晚上没用系统，第二天使用就无法连接数据库，请求多次后才行。话说你这个validationQueryTimeout是不是设置的有问题？这个单位是seconds，你设置2000了？检测连接可用的话要2000s才能有返回，是不是这个原因导致的？我设置了10000，照网上抄的，感觉应该是这里的问题，我修改为1了，今晚发布，观察下看看效果。

JeffreyDai commented 3 years ago

我的版本信息 Springboot: 1.5.17 spring-boot-starter-jta-atomikos: 1.5.21 对应的atomikos是3.9.3，网上有的说是版本低了，我手动改成了4.0.4 druid: 1.1.16 mysql: 5.7.20 mysql-connector-java:5.1.47

================================================ 按照上面的也不行。说说我是怎么解决的吧： spring.jta.atomikos.datasource.test-query: SELECT 1

加上这个配置就好了，只是控制台还是有错误日志，先让SELECT 1 打个先头兵，后面就是执行自己的业务，很正常。

只能这样将就了。

alibaba / druid