Large Objects may not be used in auto-commit mode
The issue was there was two different SessionFactories in my application. Only 1 of them had an associated transaction. I was saving the object with the large object using a sessionFactory that didn't had the transaction.
Since the I was using dbcp pooling the connection had the autoCommit property set to true.
I fixed it by changing the use of two different sessionFactories, now I'm using only one sessionFactory which work in a HibernateTransaction. This solves the problem since the transaction manager sets the autoCommit property to false.
Comments
-
Arun P Johny almost 2 years
I'm working on a application which uses spring and hibernate. We are using postgresql as the database.
When I try to insert a record into a table which has a OID column it is throwing the following error.
org.hibernate.exception.GenericJDBCException: could not insert: [com.greytip.cougar.model.misc.MailAttachment] at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2267) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2660) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:56) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:234) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000) at org.springframework.orm.hibernate3.SpringSessionSynchronization.beforeCommit(SpringSessionSynchronization.java:135) at org.springframework.transaction.support.TransactionSynchronizationUtils.triggerBeforeCommit(TransactionSynchronizationUtils.java:48) at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:882) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:692) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:678) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:319) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:116) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at $Proxy5.sendMail(Unknown Source) at com.greytip.cougar.module.ext.controller.TestMailController.testmail(TestMailController.java:48) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:259) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:476) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:431) at javax.servlet.http.HttpServlet.service(HttpServlet.java:690) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.greytip.cougar.system.security.IPAddressFilter.doFilter(IPAddressFilter.java:110) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:229) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:286) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.greytip.cougar.system.security.SchemaDetectFilter.doFilter(SchemaDetectFilter.java:147) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.greytip.cougar.system.security.AccessLogFilter.doFilter(AccessLogFilter.java:77) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.greytip.cougar.system.security.EmployeeSearchFilter.doFilter(EmployeeSearchFilter.java:77) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Unknown Source) Caused by: org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode. at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:241) at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:228) at org.postgresql.jdbc2.AbstractJdbc2Statement.setBlob(AbstractJdbc2Statement.java:2817) at org.apache.commons.dbcp.DelegatingPreparedStatement.setBlob(DelegatingPreparedStatement.java:180) at org.hibernate.type.BlobType.set(BlobType.java:49) at org.hibernate.type.BlobType.nullSafeSet(BlobType.java:117) at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:1997) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2243) ... 75 more
The exception says that a Large Objects may not be used in auto-commit mode, but when i saw the following log from spring and hibernate it says that the auto-commit mode is disabled.
Here is the log from spring transaction and hibernate jdbc connection manager
Log: 2009-07-14 12:17:31,546 [http-8080-5] DEBUG org.hibernate.transaction.JDBCTransaction - begin 2009-07-14 12:17:31,546 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection 2009-07-14 12:17:31,546 [http-8080-5] DEBUG org.hibernate.transaction.JDBCTransaction - current autocommit status: true 2009-07-14 12:17:31,546 [http-8080-5] DEBUG org.hibernate.transaction.JDBCTransaction - disabling autocommit 2009-07-14 12:17:31,546 [http-8080-5] DEBUG org.springframework.transaction.interceptor.TransactionInterceptor - Getting transaction for [com.greytip.cougar.service.MailManager.sendMail] 2009-07-14 12:17:31,578 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection 2009-07-14 12:17:31,593 [http-8080-5] DEBUG org.springframework.transaction.interceptor.TransactionInterceptor - Completing transaction for [com.greytip.cougar.service.MailManager.sendMail] 2009-07-14 12:17:31,593 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - registering flush begin 2009-07-14 12:17:31,609 [http-8080-5] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 25P01 2009-07-14 12:17:31,609 [http-8080-5] ERROR org.hibernate.util.JDBCExceptionReporter - Large Objects may not be used in auto-commit mode. 2009-07-14 12:17:31,609 [http-8080-5] ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session org.hibernate.exception.GenericJDBCException: could not insert: [com.greytip.cougar.model.misc.MailAttachment] at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2267) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2660) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:56) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:234) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000) at org.springframework.orm.hibernate3.SpringSessionSynchronization.beforeCommit(SpringSessionSynchronization.java:135) at org.springframework.transaction.support.TransactionSynchronizationUtils.triggerBeforeCommit(TransactionSynchronizationUtils.java:48) at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:882) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:692) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:678) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:319) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:116) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at $Proxy5.sendMail(Unknown Source) at com.greytip.cougar.module.ext.controller.TestMailController.testmail(TestMailController.java:48) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:259) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:476) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:431) at javax.servlet.http.HttpServlet.service(HttpServlet.java:690) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.greytip.cougar.system.security.IPAddressFilter.doFilter(IPAddressFilter.java:110) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:229) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:286) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.greytip.cougar.system.security.SchemaDetectFilter.doFilter(SchemaDetectFilter.java:147) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.greytip.cougar.system.security.AccessLogFilter.doFilter(AccessLogFilter.java:77) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.greytip.cougar.system.security.EmployeeSearchFilter.doFilter(EmployeeSearchFilter.java:77) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Unknown Source) Caused by: org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode. at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:241) at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:228) at org.postgresql.jdbc2.AbstractJdbc2Statement.setBlob(AbstractJdbc2Statement.java:2817) at org.apache.commons.dbcp.DelegatingPreparedStatement.setBlob(DelegatingPreparedStatement.java:180) at org.hibernate.type.BlobType.set(BlobType.java:49) at org.hibernate.type.BlobType.nullSafeSet(BlobType.java:117) at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:1997) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2243) ... 75 more 2009-07-14 12:17:31,640 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - registering flush end 2009-07-14 12:17:31,640 [http-8080-5] DEBUG org.hibernate.transaction.JDBCTransaction - rollback 2009-07-14 12:17:31,640 [http-8080-5] DEBUG org.hibernate.transaction.JDBCTransaction - re-enabling autocommit 2009-07-14 12:17:31,640 [http-8080-5] DEBUG org.hibernate.transaction.JDBCTransaction - rolled back JDBC Connection 2009-07-14 12:17:31,640 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources! 2009-07-14 12:17:31,640 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - performing cleanup 2009-07-14 12:17:31,640 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)] 2009-07-14 12:17:31,640 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources! 2009-07-14 12:17:31,656 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - performing cleanup 2009-07-14 12:17:31,656 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)] 2009-07-14 12:17:31,656 [http-8080-5] DEBUG org.hibernate.jdbc.ConnectionManager - transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources!
In this log the first few lines says that the method MailManager.sendMail() is running with a transaction block and the default auto-commit status is true, but the next line says that the auto-commit is disabled.
Can anybody tell me why postgresql is throwing "Large Objects may not be used in auto-commit mode" exception when actually the auto-commit mode is diabled.
Thank you.