SpringBoot:GenericJDBCException: Unable to acquire

问题:

I am connecting to mysql database by creating DataSource dynamically with following properties, Its working fine, but after some time it keeps giving me the error "Unable to acquire JDBC Connection".

 package com.test.db;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import java.util.HashMap;

@Configuration
@EnableJpaRepositories(basePackages = "com.test.master", entityManagerFactoryRef = "userMasterEntityManager", transactionManagerRef = "userMasterTransactionManager")
public class MasterDBConfig {

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean userMasterEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource());
        em.setPackagesToScan(new String[] { "com.test.master" });

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "create");
        properties.put("hibernate.id.new_generator_mappings", "false");
        properties.put("hibernate.show_sql", "true");
        properties.put("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
        properties.put("hibernate.testOnBorrow", "true");
        properties.put("hibernate.validationQuery", "SELECT 1");
        properties.put("hibernate.testWhileIdle", "true");
//        properties.put("hibernate.timeBetweenEvictionRunsMillis", "3600000");
        properties.put("hibernate.connection.autoReconnect", "true");
        properties.put("hibernate.connection.autoReconnectForPools", "true");

        em.setJpaPropertyMap(properties);

        return em;
    }

    @Primary
    @Bean(name = "dataSource")
    public DataSourceRouter dataSource() {
        return new DataSourceRouter();
    }

    @Primary
    @Bean
    public PlatformTransactionManager userMasterTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(userMasterEntityManager().getObject());
        return transactionManager;
    }
}

Its been hosted in AWS with Tomcat environment. DB is in RDS of AWS.

UPDATE:
Caused by:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.GeneratedConstructorAccessor39.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:732)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:664)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:479)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:154)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)

回答1:

What I've learnt from your stacktrace, you have issue with too many connections which leads to unavailability to establish any new connections.The solution is quite simple - you have to setup connection pool(c3p0 is quite popular) and specify pool connections.
See this how to setup c3p0 connection and make basic tweak.

Also do not forget to include dependency for that

回答2:

For me, the problem solved by making sure that all the sessions are closed properly immediately after use.