MYSQL / Spring Hibernate: Connection has already been closed

Question

So, I want to do 32 write operations under one @Transactional. I have set maxActive connections as "100", maxIdle as "50" (which I believe are quite high), maxTimeout is set to 150000 and removeAbandonedTimeout = 3000

Here's the tomcat configuration:

driverClassName="com.mysql.jdbc.Driver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" testWhileIdle="true" validationQuery="SELECT 1" validationInterval="30000" timeBetweenEvictionRunsMillis="30000" removeAbandoned="true" removeAbandonedTimeout="3000" url="jdbc:mysql://127.0.0.1:3306/cas?autoReconnect=true"/>

Here's java code:

@Override
@Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED, timeout = 3600)
public void updateDBFromMap(String clientCode, String date, String time, EClientParameterMaintenance ebtData) throws Exception {
    if (ebtData.getCity() != null && !ebtData.getCity().isEmpty()) {
        String tableName;
        try {
            tableName = maintenanceService.getDailyTableName(clientCode, date, EventBifurcationType.CITY);
            for (String cityKey : ebtData.getCity().keySet()) {
                dailyFlushDao.updateCity(ebtData.getCity().get(cityKey), tableName, time);
            }
        } catch (Exception e) {
            LOG.error("Exception:", e);
            throw e;
        }

    } .... and 31 more such update operations

And here's the error:

- Connection has already been closed.
19:50:56,162  ERROR [FlushServiceImpl] - Exception:
org.springframework.orm.hibernate4.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [INSERT INTO fc_p_faclntlms1aoqglwzza0a_20161001 (time_period, prediction_count, footfall_count, unique_footfall_count, time_spent, fence_category_id, property_code, created) VALUES ('1900','2','2','2','60','19','FAPORYLMS1AOMXHHD3K4B',now()) ON DUPLICATE KEY UPDATE prediction_count=prediction_count+2, footfall_count=footfall_count+2,unique_footfall_count=unique_footfall_count+2,time_spent=time_spent+60;]; SQL state [null]; error code [0]; could not prepare statement; nested exception is org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:170)
        at org.springframework.orm.hibernate4.HibernateExceptionTranslator.convertHibernateAccessException(HibernateExceptionTranslator.java:57)
        at org.springframework.orm.hibernate4.HibernateExceptionTranslator.translateExceptionIfPossible(HibernateExceptionTranslator.java:44)
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
        at com.focus.cas.service.aspect.CASAspectHandler.logMethodExecutionTime(CASAspectHandler.java:122)
        at com.focus.cas.service.aspect.CASAspectHandler.profile(CASAspectHandler.java:43)
        at sun.reflect.GeneratedMethodAccessor103.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
        at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
        at com.focus.cas.dbaccess.mysql.flush.impl.DailyFlushDaoImpl$$EnhancerBySpringCGLIB$$1a5b5f55.updateFenceCategoryProperty(<generated>)
        at com.focus.cas.service.flush.impl.FlushServiceImpl.updateDBFromMap(FlushServiceImpl.java:1314)
        at com.focus.cas.service.flush.impl.FlushServiceImpl$$FastClassBySpringCGLIB$$d877b180.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:267)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
        at com.focus.cas.service.flush.impl.FlushServiceImpl$$EnhancerBySpringCGLIB$$f652020f.updateDBFromMap(<generated>)
        at com.focus.cas.service.flush.impl.FlushServiceImpl.flush(FlushServiceImpl.java:1046)
        at com.focus.cas.service.flush.impl.FlushServiceImpl.access$100(FlushServiceImpl.java:107)
        at com.focus.cas.service.flush.impl.FlushServiceImpl$FlushThread.run(FlushServiceImpl.java:1534)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:96)
        at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:205)
        at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1310)
        at org.hibernate.internal.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:389)
        at com.focus.cas.dbaccess.mysql.flush.impl.DailyFlushDaoImpl.updateFenceCategoryProperty(DailyFlushDaoImpl.java:269)
        at com.focus.cas.dbaccess.mysql.flush.impl.DailyFlushDaoImpl$$FastClassBySpringCGLIB$$f7b6a91f.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
        ... 30 more
Caused by: java.sql.SQLException: Connection has already been closed.
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:117)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
        at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
        at com.sun.proxy.$Proxy33.prepareStatement(Unknown Source)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:103)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
        ... 40 more
19:50:56,717  ERROR [TransactionInterceptor] - Application exception overridden by rollback exception
org.springframework.orm.hibernate4.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [INSERT INTO fc_p_faclntlms1aoqglwzza0a_20161001 (time_period, prediction_count, footfall_count, unique_footfall_count, time_spent, fence_category_id, property_code, created) VALUES ('1900','2','2','2','60','19','FAPORYLMS1AOMXHHD3K4B',now()) ON DUPLICATE KEY UPDATE prediction_count=prediction_count+2, footfall_count=footfall_count+2,unique_footfall_count=unique_footfall_count+2,time_spent=time_spent+60;]; SQL state [null]; error code [0]; could not prepare statement; nested exception is org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:170)
        at org.springframework.orm.hibernate4.HibernateExceptionTranslator.convertHibernateAccessException(HibernateExceptionTranslator.java:57)
        at org.springframework.orm.hibernate4.HibernateExceptionTranslator.translateExceptionIfPossible(HibernateExceptionTranslator.java:44)
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
        at com.focus.cas.service.aspect.CASAspectHandler.logMethodExecutionTime(CASAspectHandler.java:122)
        at com.focus.cas.service.aspect.CASAspectHandler.profile(CASAspectHandler.java:43)
        at sun.reflect.GeneratedMethodAccessor103.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
        at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
        at com.focus.cas.dbaccess.mysql.flush.impl.DailyFlushDaoImpl$$EnhancerBySpringCGLIB$$1a5b5f55.updateFenceCategoryProperty(<generated>)
        at com.focus.cas.service.flush.impl.FlushServiceImpl.updateDBFromMap(FlushServiceImpl.java:1314)
        at com.focus.cas.service.flush.impl.FlushServiceImpl$$FastClassBySpringCGLIB$$d877b180.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:267)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
        at com.focus.cas.service.flush.impl.FlushServiceImpl$$EnhancerBySpringCGLIB$$f652020f.updateDBFromMap(<generated>)
        at com.focus.cas.service.flush.impl.FlushServiceImpl.flush(FlushServiceImpl.java:1046)
        at com.focus.cas.service.flush.impl.FlushServiceImpl.access$100(FlushServiceImpl.java:107)
        at com.focus.cas.service.flush.impl.FlushServiceImpl$FlushThread.run(FlushServiceImpl.java:1534)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:96)
        at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:205)
        at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1310)
        at org.hibernate.internal.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:389)
        at com.focus.cas.dbaccess.mysql.flush.impl.DailyFlushDaoImpl.updateFenceCategoryProperty(DailyFlushDaoImpl.java:269)
        at com.focus.cas.dbaccess.mysql.flush.impl.DailyFlushDaoImpl$$FastClassBySpringCGLIB$$f7b6a91f.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

Fighting over it for the last 7 days. Almost all solutions have failed in my case.

And to add to that, it is showing this error after a few operations. Not on the first operation itself. — Rupitzer, Oct 08 '16 at 15:16
Maybe this link will help? http://stackoverflow.com/questions/21698675/analyzing-connection-closed-exception-in-spring-jpa-mysql-tomcat-app — OldProgrammer, Oct 08 '16 at 15:17
I have set removeAbandonedTimeout="3000". So, it is less likely to have failed because of that. Secondly, I am not using JPA Template. Hence, still stuck. — Rupitzer, Oct 08 '16 at 15:22
Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example. — Roman C, Oct 08 '16 at 15:38

score 1 · Answer 1 · answered Oct 08 '16 at 16:07

The setting you used has removeAbandonedTimeout set to 3 sec. Looking for the documentation for org.apache.tomcat.jdbc.pool.DataSourceFactory which you are using at https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html , this property should be set to the longest running query your applications might have. From the docs,

removeAbandoned
(boolean) Flag to remove abandoned connections if they exceed the removeAbandonedTimeout. If set to true a connection is considered abandoned and eligible for removal if it has been in use longer than the removeAbandonedTimeout Setting this to true can recover db connections from applications that fail to close a connection. See also logAbandoned The default value is false.

removeAbandonedTimeout
(int) Timeout in seconds before an abandoned(in use) connection can be removed. The default value is 60 (60 seconds). The value should be set to the longest running query your applications might have.

Thus you should check for the maximum time any query used by your application is taking and set this accordingly.

Oops my bad , but do check for the query time with removeAbandoned set to false and see if it is more than 3000 sec. — skm, Oct 08 '16 at 16:21
When I set it to "false", it is working but not sure why it is working. Because for each transactional block, it is taking close to 7-8 secs. So, I overshot that figure by a long way. Hence, not sure where it was failing and why removedAbandoned="false" is working fine. — Rupitzer, Oct 08 '16 at 16:47

MYSQL / Spring Hibernate: Connection has already been closed

1 Answers1