We’ll do it live..

The big news is our new Spring Boot micro service went into production! It has been running in production for just over a week and we have had 0 issues with it, everything just works! It ended up being two crazy sprints to get it done, but we shipped it this week with no production issues after it went live. My first big project at the new company couldn’t have gone any better. The team really pulled together to get it done which makes it even more rewarding.

MySQL Database Connections…

We did have one near miss though. It turns out that MySQL drops database connections that have been idle for 8 hours. We discovered that the morning we were about to go live. I really think this should be more widely publicized. It seems like the sensible opinionated default of Spring Boot should be to have the connection pool test the connections so that people don’t end up in this state. Anyway luckily additional testing the morning of uncovered this and I was able to find out how to fix it with some digging around. Just so I remember for the future the magic settings are:

The settings…

spring.datasource.remove-abandoned=true
spring.datasource.remove-abandoned-timeout=600
spring.datasource.testOnBorrow=true
spring.datasource.testWhileIdle=true
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.validationQuery=SELECT 1


What it all means…

So breaking it down, testOnBorrow checks the connection before the connection pool gives it to you. This is my first fix that I started with as it it has the connection pool test the connection with the validation query prior to returning it back to the application. This is good enough to fix the issue, but if all connections are killed like if the database had gone down and came back up it is a little slow the first time the app goes for a connection as it has to run through everything in the pool before it allocates a new connection for you.

The second thing I found was the test while idle and the time between eviction runs. With that every minute we test idle connections. This is great for 2 reasons, it keeps the connections alive with MySQL and if someone gets messed up with one of the connections we can be proactive about cleaning it up.

The third set of options I discovered in all this is the remove-abandoned option. This protects you from leaking connections in the application. Above it basically says if we haven’t returned a connection within 600 seconds consider that connection to be abandoned and remove it from the pool. I think with all of these options you are going to make your application much more resilient to database connection issues.