mybatis-plus多数据源Connection is not available, request timed out after 30000ms.

背景:
最近在开发一个基础springboot的定时任务, 因为需要操作多个数据库,项目使用的mp(mybatis-plus), 正好该框架也有自己的多数据源, 于是就用上了. 使用了框架默认的hikari线程池配置,在百度上找了一些配置如下

spring:
  #连接池配置
  datasource:
    hikari:
      connection-timeout: 60000
      minimum-idle: 10
      maximum-pool-size: 20
      auto-commit: true
      idle-timeout: 600000
      max-lifetime: 28740000
      connection-test-query: SELECT 1
    type: com.zaxxer.hikari.HikariDataSource

按道理说这个配置看似正确, 然而项目在运行时开始报错了.

Could not open JDBC Connection for transaction; 
nested exception is java.sql.SQLTransientConnectionException: 
xxx - Connection is not available, request timed out after 30000ms.

问题排查:
根据报错, 是连接超时30S, 但是根据配置是60S, 第一时间想到是配置未生效.
hikari源码:
Hikari数据源创建器

package com.baomidou.dynamic.datasource.creator;

import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.hikari.HikariCpConfig;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import lombok.AllArgsConstructor;
import lombok.Data;

/**
 * Hikari数据源创建器
 *
 * @author TaoYu
 * @since 2020/1/21
 */
@Data
@AllArgsConstructor
public class HikariDataSourceCreator {

  private HikariCpConfig hikariCpConfig;

  public DataSource createDataSource(DataSourceProperty dataSourceProperty) {
    HikariConfig config = dataSourceProperty.getHikari().toHikariConfig(hikariCpConfig);
    config.setUsername(dataSourceProperty.getUsername());
    config.setPassword(dataSourceProperty.getPassword());
    config.setJdbcUrl(dataSourceProperty.getUrl());
    config.setDriverClassName(dataSourceProperty.getDriverClassName());
    config.setPoolName(dataSourceProperty.getPollName());
    return new HikariDataSource(config);
  }
}

HikariCp参数配置(这里不贴全, 需要自己去源码看)

com.baomidou.dynamic.datasource.spring.boot.autoconfigure.hikari.HikariCpConfig

图片.png

原来运行了那么长时间一直在用默认值, 所以在跑任务时候, 只有20多个定时任务(并发)就导致线程池不够用.
问题解决:
需要在每个数据源下增加hikari的配置, 如下

spring:
  datasource:
    dynamic:
      primary: master
      datasource:
        master:
          username: root
          password: root
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/master?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
          hikari:
            connection-timeout: 60000
            minIdle: 10
            maxPoolSize: 20
            isAutoCommit: true
            idle-timeout: 600000
            max-lifetime: 28740000
            connection-test-query: SELECT 1
        yyy:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/yyy?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
          username: root
          password: root
          hikari:
            connection-timeout: 60000
            minIdle: 10
            maxPoolSize: 20
            isAutoCommit: true
            idle-timeout: 600000
            max-lifetime: 28740000
            connection-test-query: SELECT 1