How can I stop MySQL from running out of connections when using Spring Boot Live Reload? CannotGetJdbcConnectionException: “Too many connections”

Question

After a few code changes, Spring Boot gets into a funky state, and floods my console logs with stack traces saying it can't get a DB connection. How can I have Spring Boot avoid this problem with Live Reload? It works if I kill it and run it again with mvn spring-boot:run (but that takes longer).

Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"

I have Spring DevTools installed in my pom.xml for live-reload.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-devtools</artifactId>
  <optional>true</optional>
</dependency>

This is my DataSource upon startup. The default testOnBorrow is true.

2018-12-03 16:09:39.530 INFO 14804 --- [ restartedMain] c.s.s.TheApp : DATASOURCE = org.apache.tomcat.jdbc.pool.DataSource@72d10b82{ConnectionPool[defaultAutoCommit=null; defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=null; driverClassName=com.mysql.jdbc.Driver; maxActive=100; maxIdle=100; minIdle=10; initialSize=10; maxWait=30000; testOnBorrow=true; testOnReturn=false; timeBetweenEvictionRunsMillis=5000; numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=60000; testWhileIdle=false; testOnConnect=false; password=********; url=jdbc:mysql://localhost:3306/appdb?useSSL=false; username=root; validationQuery=/* ping */ SELECT 1; validationQueryTimeout=-1; validatorClassName=null; validationInterval=3000; accessToUnderlyingConnectionAllowed=true; removeAbandoned=false; removeAbandonedTimeout=60; logAbandoned=false; connectionProperties=null; initSQL=null; jdbcInterceptors=null; jmxEnabled=true; fairQueue=true; useEquals=true; abandonWhenPercentageFull=0; maxAge=0; useLock=false; dataSource=null; dataSourceJNDI=null; suspectTimeout=0; alternateUsernameAllowed=false; commitOnReturn=false; rollbackOnReturn=false; useDisposableConnectionFacade=true; logValidationErrors=false; propagateInterruptState=false; ignoreExceptionOnPreLoad=false; useStatementFacade=true; }

This could well be an issue caused by your code. Most time it is. Without seeing that we are rather hobbled when it comes to useful comment — RiggsFolly

Karol Dowbecki Karol Dowbecki · Accepted Answer · 2018-12-03T21:40:13

Your pool is sized from 10 to 100 connections based on your org.apache.tomcat.jdbc.pool.DataSource. 100 is a lot of connections. If you opened 100 connections it means that you are:

Not closing Connection objects once acquired from the pool. A Connection object must be closed so the pool can reuse it. One way to ensure close() being callied is to always use try-with-resource:
```
try (Connection conn = dataSource.getConnection()) {
  ...
}
```
Opening all of them in a short period of time that you don't get the chance to trigger 30 sec timeout (e.g. maxWait=30000 option that you set). Connection objects are meant to be reused, see HikariCP wiki: About Pool Sizing.

Unfortunately the only way to tell what's happening is to debug the application and see why are you running out of the connections.

How can I stop MySQL from running out of connections when using Spring Boot Live Reload? CannotGetJdbcConnectionException: “Too many connections”

1 Answers