grails oracle connection timeout

Question

I have below datasource config in grails.

dataSource_staging_oracle {
    dbCreate = "none"
    pooled = true
    url = "jdbc:oracle:thin:@//my-box-oracle.com:1521/DB1"
    driverClassName = "oracle.jdbc.OracleDriver"
    username = "USER_1"
    password = "encryptedpassword"
    passwordEncryptionCodec = PropertiesCodec
}
dataSource_prod_oracle {
    dbCreate = "none"
    pooled = true
    autoReconnect = true
    url = "jdbc:oracle:thin:@//my-oracle-prod-box.com:1521/DB2"
    driverClassName = "oracle.jdbc.OracleDriver"
    username = "user_2"
    password = "encrypted_password"
    passwordEncryptionCodec = PropertiesCodec
}

Application is able to fetch the DB connection and work fine. However after 1-2 hours of processing, I see connection closed exceptions.

 ERROR util.JDBCExceptionReporter  - Closed Connection

I believe this has to do with datasource config properties. What grails settings help me in refreshing the connections? I already have set autoReconnect and pooled to true

I use grails 2.3.3

Are you sure you want to focus on Grails and not attempt to fix the initial problem of your connections dying off after 1-2 hours? — christopher, Jun 22 '15 at 18:25
@christopher In grails land, isn't it the datasource properties that determine how the connection should be fetched, how long to keep, pooling etc? I am hoping there is something missing in my config which helps me refresh the connection. Its a read-only Oracle query I make from the application. — suman j, Jun 22 '15 at 18:31
I understand, but there is no Grails config that says "after two hours, kill this connection by default". You'd have to specify that. So it kind of implies that there is some underlying cause which is forcing the disconnection. — christopher, Jun 22 '15 at 18:53

score 1 · Answer 1 · edited May 23 '17 at 11:51

Datasource properties will inform the database what the middleware server will expect from the Database. If your Oracle server does not agree with the settings Grails would like to use, expect sometimes cryptic error messages.

Using pooled connections tells Oracle to hold a number of connections open for the application. If the application is running 1-2 hours and continuously processing, I expect the pool may be running out of valid connections. For some background please view the links included below.

Check out the Grails configuration docs, section 4.4, to see advanced datasource configuration options. Do you have a validation query defined? (eg select 1 from dual;)

Check out this AskTom post for a clear explanation of connection pooling.

Previous StackOverflow question with MySQL but similar error.

score 0 · Accepted Answer · answered Jul 30 '15 at 20:54

Below config helped me fix my problem.

dataSource_staging {
            dbCreate = "none"
            pooled = true
            autoReconnect = true
            url = "jdbc:oracle:thin:@//my-box-oracle.com:1521/DB1"
            driverClassName = "oracle.jdbc.OracleDriver"
            username = "USER_1"
            password = "encryptedpassword"
            passwordEncryptionCodec = PropertiesCodec
            properties {
                validationQuery = "SELECT 1 FROM DUAL"
                testOnBorrow = true
                testWhileIdle = true
                testOnReturn = false
                timeBetweenEvictionRunsMillis = 5000
                minEvictableIdleTimeMillis = 60000
                maxAge = 10 * 60000
                maxWait = 10000
                maxIdle = 25
                maxActive = 50
                initialSize = 5
            }
        }

grails oracle connection timeout

2 Answers2