Facing Too many connection issue on HIkariCP

Question

I have a Java JDBC application, after 100 queries of Select Jetty server crashed and return below error:

     ERROR com.zaxxer.hikari.pool.HikariPool - dev-server - Exception during pool initialization.
java.sql.SQLNonTransientConnectionException: Too many connections
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:526)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1606)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:95)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:443)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:514)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:111)
        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:72)
        at com.jrg.platform.commons.hikari.HikariDataSourceSupport.getDataSource(HikariDataSourceSupport.java:70)
        at com.jrg.platform.commons.hikari.HikariDataSourceSupport.getConnection(HikariDataSourceSupport.java:82)
        at com.jrg.platform.services.internal.pcms.data.campaign.CampaignAlertDaoImpl.getCampaignAlerts(CampaignAlertDaoImpl.java:40)
        at com.jrg.platform.services.internal.pcms.service.CampaignAlertServiceImpl.campaignAlerts(CampaignAlertServiceImpl.java:43)
        at com.jrg.platform.services.internal.pcms.resource.CampaignAlertResource.getCampaignAlerts(CampaignAlertResource.java:52)
        at com.jrg.platform.services.internal.pcms.resource.CampaignAlertResource_$$_jvstf5a_6._d5getCampaignAlerts(CampaignAlertResource_$$_jvstf5a_6.java)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Unable to find the solution. I am just getting the connection from HikariDataSourceSupport and performing the query. the sample code of getting connection is given below:

default HikariDataSource getDataSource(E e, boolean readOnly) throws IOException {
        String key = this.getKey(e, readOnly);
        Map<String, HikariDataSource> sources = this.getDataSources();
        if(!sources.containsKey(key)) {
            synchronized(this.getMonitor()) {
                if(!sources.containsKey(key)) {
                    if(logger.isDebugEnabled()) {
                        logger.debug("creating new DataSource for {}", key);
                    }

                    Config config = this.getConfig(e, readOnly);
                    if(!config.hasPathOrNull("jdbcUrl")) {
                        throw new EnvironmentNotConfigured(e, readOnly);
                    }

                    sources.put(key, new HikariDataSource(new HikariConfig(ConfigUtils.asProperties(config))));
                }
            }
        }

        return (HikariDataSource)sources.get(key);
    }

    default Connection getConnection(E env, boolean readOnly) throws SQLException, IOException {
        return this.getDataSource(env, readOnly).getConnection();
    }

Do you `close` your connection, so that it can be returned in the pool? — Marmite Bomber, Jul 13 '18 at 12:52
while getting connection i am doing in finally block to close. but happening again. if you know any other approach according to my code then it will be asset to me. — Irfan Nasim, Jul 13 '18 at 12:56
It sounds like you are creating too many connections for the current MySQL configuration, and maybe you are creating too many data sources (which in turn are creating too many connections). Without a [mcve] that is hard to tell. — Mark Rotteveel, Jul 13 '18 at 14:46
It sound like you are are closing the POOL every time. That exception only occurs during pool initialization, and is impossible to encounter after 100 queries. Therefore, you must be creating and closing POOLS rather than CONNECTIONS. — brettw, Jul 14 '18 at 00:30
@brettw I'd even guess he is creating pools every time, but never closing those pools. — Mark Rotteveel, Jul 14 '18 at 07:23

score 0 · Answer 1 · answered Aug 15 '18 at 08:38

There was problem in configuration HikariCP. When request generated and try to get connection, the create connection method was creating the new datasource because the last state was destroy due to view scope of the bean.

now i have converted that class into to singalton and created the implementation of that interface to inject in the code. it is saving the state of previous data source connection. Now it is working perfect with minimum idle connections.

the code can be seen here: APP.java

 bind(HikariLucktasticDataSourceSupportImpl.class)
                        .to(HikariDataSourceSupport.class)
                        .named("hdsSupport")
                        .in(Singleton.class);

in DAO Layer:

   @Inject
    @Named("hdsSupport")
    private HikariDataSourceSupport hdsSupport;

Facing Too many connection issue on HIkariCP

1 Answers1