Grails DB Outage leads to application going down

Question

Our application runs on Grails and we have MS SQL Database. We have an issue with our client. The problem is that their DB server suddenly goes down due to some reason or the other. It's only momentary and usually comes back in a couple of minutes or so. Whenever it is down and the users try to access the application, they see this error:

Caused by: org.hibernate.TransactionException: JDBC begin failed:
        at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:92)
        at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
        ... 84 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.getAutoCommit(SQLServerConnection.java:

I have googled and found a couple of links:

MySQL Connection Timeout Issue - Grails Application on Tomcat using Hibernate and ORM

http://andrzejgrzesik.info/2011/12/18/grails-automatically-reconnect-to-mysql-datasource/

But in both the above cases, it seems the application usually comes up on UI after the users attempt a few times. However, in my case, the UI never comes up and we continue to see the same errors despite after bringing the DB back. It's only after we restart the grails instance ( i.e tomcat ) that we see the application back.

Currenlty, our DataSourceConfig is pretty staright forward:

dataSource {
            dialect = org.hibernate.dialect.SQLServerDialect
            driverClassName = 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
            username = 'user'
            password = 'P@SSWorD'
            url = 'jdbc:sqlserver://hostName;DatabaseName=dbName;instanceName=instanceName'
            dbCreate = 'update' // Deliberately kept in update mode due to some reasons. Please advise if it has to be changed as well.
}

Appreciate your help as this issue is on our neck right now.

Try adding the data source properties: `testOn...` and `validationQuery` mentioned in the Stack Overflow question you linked to. See also: http://stackoverflow.com/questions/19454551/grails-2-3-0-and-mysql-connection-timeout — Andrew, Feb 05 '14 at 18:19
Sure I will Andrew. While I do that don't you think two cases have some difference, with mine being not able to reconnect even after bringing the SQL server up? Every time we end up restarting the grails app altogether. So, my perception is that the testOn will keep testing for DB availability and will make sure grails doesn't treat the application to be idle for long time. Whereas in my case it's not idleness but grails inability to connect back after the DB is back. I will still try those options though and let u know. Thanks. — rajugaadu, Feb 05 '14 at 18:37
It's likely that the reconnect failure is due to pooled connections that have become invalid due to the DB restart. The `testOnBorrow` pool property will detect stale connections and reconnect if needed. — Andrew, Feb 05 '14 at 19:48

Grails DB Outage leads to application going down

0 Answers0