JDBC Connection get lost after some idle time

Question

I am getting a communication link failure exception while executing some MySQL queries after some time. I am using the SQL connection object of package groovy.sql. The application works perfectly fine if I browse the application continuously but it won't work if I browse the application after some ideal time.

Below are the details:

• Tomcat 8
• Grails-3.3.11

Code snippet:

def dataSource = grails.util.Holders.applicationContext.getBean('dataSource')
Sql sql = new Sql(dataSource);
sql.rows(query)

Datasource properties:

properties:
            maxActive: 50
            maxIdle: 25
            minIdle: 5
            initialSize: 5
            maxWait: 10000
            removeAbandoned: true
            removeAbandonedTimeout: 400
            logAbandoned: true
            minEvictableIdleTimeMillis: 1800000
            timeBetweenEvictionRunsMillis: 1800000
            numTestsPerEvictionRun: 3
            testOnBorrow: true
            testWhileIdle: true
            testOnReturn: true
            validationQuery: "SELECT 1"

Thanks in advance.

Answer 1

I would try to put timeBetweenEvictionRunsMillis to -1 (deactivated). If it fixes your issue, it means you have a connection pool management issue.

Answer 2

What (if any) exception is being thrown? You might consider looking at https://www.ibm.com/docs/en/mpf/8.0.0?topic=sources-handling-stale-connections