解决数据库连接池HikariCP问题

解决HikariCP的com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure的问题

om.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

servicename:bc-checkup message:2022-03-16 16:01:19.928 [Thread-42] WARN com.zaxxer.hikari.HikariConfig - HikariPool-38 - idleTimeout has been set but has no effect because the pool is operating as a fixed size pool. fields.host:10.161.51.246 log_type:wallet-bc-dw host.name:ip-10-161-51-246.ap-southeast-1.compute.internal @version:1 @timestamp:Mar 16, 2022 @ 16:01:19.959 _id:Fhe-kX8BCBBWjK6M5MOX _type:doc _index:wallet-bc-dw-logs-2022.03.11 _score: -

配置以下参数

    db-minimum-idle: 5
    db-maximum-pool-size: 10
    db-max-lifetime: 60000
    db-idle-timeout: 60000
    db-connection-timeout: 60000
    db-validation-timeout: 3000

java代码

public class DatabaseSourceServiceImpl implements DatabaseSourceService, BeanFactoryAware {
    
    @Value("${bc.mybatisplus.db-minimum-idle}")
    private Integer minimumIdle;

    @Value("${bc.mybatisplus.db-maximum-pool-size}")
    private Integer maximumPoolSize;
    @Value("${bc.mybatisplus.db-max-lifetime}")
    private Integer maxLifetime;

    @Value("${bc.mybatisplus.db-idle-timeout}")
    private Integer idleTimeout;

    @Value("${bc.mybatisplus.db-connection-timeout}")
    private Integer connectionTimeout;

    @Value("${bc.mybatisplus.db-validation-timeout}")
    private Integer validationTimeout;

    @Override public void addDataSource(String datasourceName) {
    
            //.....

            if (dbSource.getMinimumIdle() == null || dbSource.getMinimumIdle().intValue() <= 0) {
    
                dbSource.setMinimumIdle(minimumIdle); //从nacos取
            }
            configuration.setMinimumIdle(dbSource.getMinimumIdle());

            if (dbSource.getMaximumPoolSize() == null || dbSource.getMaximumPoolSize() <= 0) {
    
                dbSource.setMaximumPoolSize(maximumPoolSize);   //从nacos取
            }
            configuration.setMaximumPoolSize(dbSource.getMaximumPoolSize());

            if (dbSource.getMaxLifetime() == null || dbSource.getMaxLifetime() <= 0) {
    
                dbSource.setMaxLifetime(maxLifetime);  
            }
            configuration.setMaxLifetime(dbSource.getMaxLifetime());

            if (dbSource.getIdleTimeout() == null || dbSource.getIdleTimeout() <= 0) {
    
                dbSource.setIdleTimeout(idleTimeout);   
            }
            configuration.setIdleTimeout(dbSource.getIdleTimeout());

            if (dbSource.getConnectionTimeout() == null || dbSource.getConnectionTimeout() <= 0) {
    
                dbSource.setConnectionTimeout(connectionTimeout); 
            }

            configuration.setConnectionTimeout(dbSource.getConnectionTimeout());
            configuration.setConnectionTestQuery("SELECT 1");
            configuration.setValidationTimeout(validationTimeout);
            configuration.setAllowPoolSuspension(true);
            //configuration.setRegisterMbeans(true);
            configuration.setPoolName(datasourceName + "-Hikari-POOL");
            HikariDataSource dataSource = new HikariDataSource(configuration);
            try {
    
                dataSource.setLoginTimeout(5);
            } catch (SQLException e) {
    
                log.error(e.getMessage(), e);
            }
            //....

        }
}