JDBCException due to stale connections in Grails

Issue
If your grails application remains idle for too long or the database connections remain idle for too long or the database is restarted you may see the following exceptions:

ERROR org.hibernate.util.JDBCExceptionReporter - No more data to read from socket
ERROR org.hibernate.util.JDBCExceptionReporter - OALL8 is in an inconsistent state
ERROR org.hibernate.util.JDBCExceptionReporter - Io exception: Broken pipe
ERROR org.hibernate.util.JDBCExceptionReporter - Already closed.

These issues are intermittent, that is, they may disappear when you try hitting again.
This issue occurs because the application tries to use a database connection that has become stale and should have been evicted.


Resolution
This problem can be fixed by overriding the default datasource with a DBCP BasicDataSource and setting some DBCP properties.

For my applicatin, i have encrypted my database password used a codec.
This is how my DataSource.groovy looks:
dataSource {
        
            loggingSql = false
            username = "USERNAME"
            password = "qDKJkZFGtPK2lj54M11OMQ00"
            dbCreate = "update"
            driverClassName = "oracle.jdbc.OracleDriver"
            passwordEncryptionCodec = "com.myapp.encryption.DESCustomCodec"
            url = "jdbc:oracle:thin:@ldap...."
        }

Grails uses the class specified in the property 'passwordEncryptionCodec' to decode the database password.

To fix the jdbc exceptions we are facing, we need to override the datasource bean in \grails-app\conf\springspring\resources.groovy as follows:

import org.apache.commons.dbcp.BasicDataSource
import org.codehaus.groovy.grails.commons.ConfigurationHolder as CH
import org.codehaus.groovy.grails.commons.ApplicationHolder as AH

beans = {
    ssoAuthenticationProvider(SSOAuthenticationProvider) {
        userDetailsService = ref("userDetailsService")
    }

        dataSource(BasicDataSource) {
        def application = AH.application

        if(CH.config.dataSource.passwordEncryptionCodec){
           def codec = application.classLoader.loadClass(
                              CH.config.dataSource.passwordEncryptionCodec)
           password = codec.decode(CH.config.dataSource.password)
        }
        else{
            password = CH.config.dataSource.password
        }
        username = CH.config.dataSource.username
        driverClassName = CH.config.dataSource.driverClassName
        url = CH.config.dataSource.url
        minEvictableIdleTimeMillis=1800000
        timeBetweenEvictionRunsMillis=1800000
        numTestsPerEvictionRun=3
        testOnBorrow=true
        testWhileIdle=true
        testOnReturn=true

        if(driverClassName.contains('oracle'))
           validationQuery="SELECT 1 FROM DUAL"
    }
}

If a passwordEcryptionCodec is specified, the corresponding class is loaded and the password is decoded and then assigned.  We then populate the username, driverClassName, and jdbc url of the new bean.
We also set the dbcp properties
minEvictableIdleTimeMillis: The minimum amount of time an object may sit idle in the pool before it is eligible for eviction by the idle object evictor
numTestsPerEvictionRun:  The number of objects to examine during each run of the idle object evictor thread
testOnBorrow: The indication of whether objects will be validated before being borrowed from the pool
testWhileIdle: The indication of whether objects will be validated by the idle object evictor
testOnReturn:The indication of whether objects will be validated before being returned to the pool.
validationQuery: The SQL query that will be used to validate connections from this pool before returning them to the caller

Find more DBCP options here.

Note: if you are using different databases for uat, dev and production, you may have to accordingly set the validationQuery per environment. ( For eg, the above query may not work for a dev environment which uses hsql database) Or you could choose to override the bean only in a production database. Whatever suits you best.