无法获得 JDBC 连接；嵌套异常是 java.sql.SQLException: Error preloading the connection pool in java

Could not get JDBC Connection; nested exception is java.sql.SQLException: Error preloading the connection pool in java

提问by Sharan

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

嗨，我正在使用带有 jdbc 模板的 spring 连接到带有 dbcp2 数据源的数据库。

<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

具有 3 个数据库的相同配置，并且所有数据库都存在于 5 个服务器中。我的 RDS 的最大连接数为 800，并在我的方法中找到以下异常：::org.springframework.jdbc.CannotGetJdbcConnectionException：无法获得 JDBC 连接；嵌套异常是 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..

回答by Robert Hume

The stack trace says clearly "Too many connections". You should reduce your initialSizevalue, try with something like 5to see if it solves your problem. Note that maxTotalshould be greater than initialSize. You should pay attention to maxIdleand minIdlesettings, in fact the DBCP documentationsays:

堆栈跟踪说清楚"Too many connections"。你应该降低你的initialSize价值，尝试类似的东西5，看看它是否能解决你的问题。注意maxTotal应该大于initialSize。你应该注意maxIdle和minIdle设置，其实DBCP文档说：

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.

注意：如果 maxIdle 在重负载系统上设置得太低，您可能会看到连接被关闭，并且几乎立即打开新连接。这是由于活动线程暂时关闭连接的速度比打开它们的速度快，导致空闲连接的数量超过 maxIdle。重载系统的 maxIdle 的最佳值会有所不同，但默认值是一个很好的起点。

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.

调整连接池的大小并不是一项微不足道的任务，您应该研究一下您的案例。有关更多信息，您可以阅读关于池大小调整、专业连接池大小调整，并查看此相关问题。