We have a spring-boot application which uses embedded tomcat for deployment and default tomcat-jdbc connection pooling with MySQL back-end with no customization for MySQL or Tomcat side.
The app has a few schedulers that runs mostly during specific time in a day i.e. between the last cron run yesterday and 1st cron runs today, there is more than 9 hrs of gap. However, whenever the cron ran earlier, it has never come across idle connection issue.
Nowadays we see an error message
The last packet successfully received from the server was XXXXXXXX milliseconds ago. The last packet sent successfully to the server was XXXXXXXY milliseconds ago.
I can always try using testOnBorrow with validateQuery adn/or testWhileIdle etc as reqd to get this working but...
I'm trying to understand the lifecycle of the active connection in tomcat-jdbc connection pooling. Acc to the documentation, the default value for wait_timeout
for MySQL is 8 hrs, whereas default for idle_connection_timeout
on Tomcat_jdbc is nearly 6 secs.
The problem is not in configuration or setup. spring-boot
app uses spring-data
lib which makes use of the underlying connection pool
. The pool handles the connection(s) as per the connection pool implementation. The use of @Transactional
however decides when the underlying connection is opened. If there is none specified in spring-boot
app the default implementation of spring-data
opens it during crud operations; else it is opened during the method call in application annotated with @Transactional
.
In my case it was the latter.. After opening the connection a time taking non DB process was running which was making the connection to go idle after opening and was throwing exception while actually using it later.