Tomcat Jdbc Connection Pool active connection

Question

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.

1. If the default value is in use everywhere, then why issue has never surfaced before?
2. Or is it something that the connections in the tomcat-jdbc connection pool are made active every time the cron starts running and becomes idle thereafter?
3. Is it the state of the spring-boot app or the scheduler that makes any difference?

Answer 1

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.