1

I have been trying to upgrade my project from grails 2.1.2 to grails 2.4.4. This project calls another module(upgraded to java 8) which uses ibatis for database connection. While the module as a standalone works fine, it gives me "Connection Closed" exception when accessed from the grails project. This applications had been working fine on grails 2.1.2 with java 6. However, the upgrade seems to be breaking something.

Exception: Caused by: java.sql.SQLException: PooledConnection has already been closed. at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:86) at com.sun.proxy.$Proxy35.prepareStatement(Unknown Source) at sun.reflect.GeneratedMethodAccessor354.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springsource.loaded.ri.ReflectiveInterceptor.jlrMethodInvoke(ReflectiveInterceptor.java:1270) at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:376) at com.sun.proxy.$Proxy37.prepareStatement(Unknown Source) at sun.reflect.GeneratedMethodAccessor354.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springsource.loaded.ri.ReflectiveInterceptor.jlrMethodInvoke(ReflectiveInterceptor.java:1270) at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:240) at com.sun.proxy.$Proxy37.prepareStatement(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springsource.loaded.ri.ReflectiveInterceptor.jlrMethodInvoke(ReflectiveInterceptor.java:1270) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)

I have searched for other similar questions online but I dont seem to be facing any of the issues mentioned. I do not see any Abandoned connections in my logs. Also from the DB side I see that there are two connections still alive(minimum idle connection is set to 2).

DataSource.config:

dataSource {
    pooled = true
    driverClassName = "oracle.jdbc.driver.OracleDriver"
    username = xxx
    password = yyy
    dialect = 'org.hibernate.dialect.Oracle10gDialect'
    dbCreate = "none"
    properties {
        maxActive = 15
        maxIdle = 5
        minIdle = 2
        initialSize = 8
        minEvictableIdleTimeMillis = 60000
        timeBetweenEvictionRunsMillis = 60000
        maxWait = 10000
        testOnBorrow = true
        validationQuery = "select 1 from dual"      
    }
}

EDIT 1: After some more debugging on my side I see that the issue happened after we upgraded our tomcat plugin to version 7.0.55. Earlier we were using 2.1.2. This new plugin uses jdbc-pool through JdbcInterceptor to create the DB connections. This connection is then being sent to the second module(which uses ibatis)

EDIT 2: SOLVED We tried to create a different datasource around the above mentioned datasource config pointing it to c3p0 and placed it in resources.groovy. This was injected to the ibatis module and we saw that this time round there was no "Connection Pool closed" error. It seems like some issue with the jdbc-pool, but we would like to know if there is some other workaround.

New config in resources.groovy:

 dataSource_new (ComboPooledDataSource) { bean ->

         idleConnectionTestPeriod = 1 * 60 * 60
         testConnectionOnCheckin = true
         bean.destroyMethod = 'close'
         user = xxx
         password = yyy
         driverClass = <same as in datasource>
         jdbcUrl = zzz
 }

BuildConfig.groovy: compile('com.mchange:c3p0:0.9.5.1')

Maria
  • 21
  • 6
  • Try by Removing `tomcat-dbcp` jar from tomcat lib folder and add all db related jars to tomcat lib folder.. – Prasanna Kumar H A Sep 06 '16 at 07:58
  • I am running this from the GGTS ide and hence there is no tomcat-lib folder that I see being used. The ivy-cache has commons-dbcp2jars(if that is what you're referring to). But this is automatically downladed by grails when compile and run. – Maria Sep 06 '16 at 09:38
  • @maria have you tried this plugin compile 'c3p0:c3p0:0.9.1.2' if please add this plugin to Buildconfig.groovy file Thank you. – Dipak Thoke Sep 09 '16 at 19:05
  • @DPT Did you mean to use that plugin along with the additional datasource that I had included in resources.groovy? – Maria Sep 12 '16 at 05:12
  • @maria not in resources.groovy you need add it to the Buildconfig.groovy – Dipak Thoke Sep 12 '16 at 05:17
  • @DPT that has been used in addition to the Datasource config that I mentioned as part of Edit 2. Also, we are now using the latest version com.mchange:c3p0:0.9.5.1 – Maria Sep 12 '16 at 06:36

0 Answers0