0

HI I am using spring with jdbc template to connect to database with dbcp2 data source.

<bean id="dataSourcecool" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="RDS URL?zeroDateTimeBehavior=convertToNull" />
        <property name="username" value="xxxxx" />
        <property name="password" value="YYYYYYY" />
        <property name="defaultAutoCommit" value="true"/>
        <property name="initialSize" value="150"/>
        <property name="maxTotal" value="100"/>
        <property name="maxIdle" value="0"/>
        <property name="minIdle" value="0"/>
        <property name="maxWaitMillis" value="10000"/>
        <property name="validationQuery" value="SELECT NOW();"/>
        <property name="timeBetweenEvictionRunsMillis" value="10000"/>      
        <property name="removeAbandonedOnMaintenance" value="true"/>
        <property name="maxConnLifetimeMillis" value="10000"/>
        <property name="minEvictableIdleTimeMillis" value="1000"/>
    </bean>

The same configuration with 3 databases and all databases are present in 5 servers. My RDS has maximum connection of 800 and finding my exception as below Exception in my method::org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Error preloading the connection pool

can any one tell me what would have caused this error and let me know what database configurations i need to adopt here. Thanks in advance.

The complete stack trace is here

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Error preloading the connection pool
 at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:619)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:684)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:716)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:726)
 at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:863)
 MY CLASS
 at 
 My class
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:606)
 at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:220)
 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:134)
 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
 at org.springframework.web.servlet.FrameworkServlet.doPut(FrameworkServlet.java:883)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:653)
 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
 at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:442)
 at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:190)
 at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:640)
 at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
 at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Error preloading the connection pool
 at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2085)
 at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
 at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
 at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
 ... 45 more
Caused by: java.sql.SQLException: Data source rejected establishment of connection,  message from server: "Too many connections"
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1905)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1831)
 at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:2389)
 at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:757)
 at com.mysql.jdbc.Connection.createNewIO(Connection.java:1654)
 at com.mysql.jdbc.Connection.<init>(Connection.java:432)
 at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:400)
 at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
 at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:256)
 at org.apache.commons.pool2.impl.GenericObjectPool.create(GenericObjectPool.java:868)
 at org.apache.commons.pool2.impl.GenericObjectPool.addObject(GenericObjectPool.java:961)
 at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2081)
 ... 48 more
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Error preloading the connection pool
 at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:619)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:684)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:716)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:726)
 at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:863)
 My code
 
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:606)
 at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:220)
 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:134)
 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
 at..
Sharan
  • 37
  • 1
  • 1
  • 9

1 Answers1

0

The stack trace says clearly "Too many connections". You should reduce your initialSize value, try with something like 5 to see if it solves your problem. Note that maxTotal should be greater than initialSize. You should pay attention to maxIdle and minIdle settings, in fact the DBCP documentation says:

NOTE: If maxIdle is set too low on heavily loaded systems it is possible you will see connections being closed and almost immediately new connections being opened. This is a result of the active threads momentarily closing connections faster than they are opening them, causing the number of idle connections to rise above maxIdle. The best value for maxIdle for heavily loaded system will vary but the default is a good starting point.

Sizing a connection pool is not a trivial task, you should study your case. For more informations, you can read About Pool Sizing, Professional connection pool sizing, and see this related question.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
Robert Hume
  • 1,129
  • 2
  • 14
  • 25