I tried to upload a file to Address Hierarchy Module and got “HTTP Status 500 - Request processing failed; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch”. I know what I did wrong, so I trashed the module, redeployed it, but still get the same message. I guess the command to upload the unacceptable file to MySQL is ongoing somewhere in the system. Any ideas how to recover the module would be very much appreciated?
Maybe the problem is that there’s bad data in the addresshierarchy_* tables, and you need to clear them out.
It might help if you can share the actual sql error (which I guess is probably below the “could not execute batch” in the logs).
2 3 / 3 mikejg 1 min
Thanks @darius (your thoughtful advice given in many other posts has helped me lots along the way!). That there’s bad data in the modules’ tables sounds right. I attempted to import an Address Hierarchy from a CSV file (I think I got that mixed up idea from a Bahmni tutorial-doh) and it’s got stuck_*. So I guess the question is how do I get rid of it? Here is the rest of the error:
[details=Summary]type Exception report
message Request processing failed; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch
description The server encountered an internal error that prevented it from fulfilling this request.
exception
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978) Summary
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857) javax.servlet.http.HttpServlet.service(HttpServlet.java:624) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842) javax.servlet.http.HttpServlet.service(HttpServlet.java:731) org.openmrs.module.web.filter.ForcePasswordChangeFilter.doFilter(ForcePasswordChangeFilter.java:60) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:72) org.openmrs.module.owa.filter.OwaFilter.doFilter(OwaFilter.java:57) org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70) org.openmrs.module.web.filter.ModuleFilter.doFilter(ModuleFilter.java:54) org.openmrs.web.filter.OpenmrsFilter.doFilterInternal(OpenmrsFilter.java:108) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105) org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105) org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105) org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause
org.hibernate.exception.ConstraintViolationException: could not execute batch org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129) org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:136) org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:114) org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:163) org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:226) org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:484) org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351) org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:67) org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1227) org.hibernate.internal.SessionImpl.list(SessionImpl.java:1711) org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380) org.openmrs.module.addresshierarchy.db.hibernate.HibernateAddressHierarchyDAO.getAddressHierarchyEntryCountByLevel(HibernateAddressHierarchyDAO.java:65) org.openmrs.module.addresshierarchy.service.AddressHierarchyServiceImpl.getAddressHierarchyEntryCountByLevel(AddressHierarchyServiceImpl.java:411) org.openmrs.module.addresshierarchy.service.AddressHierarchyServiceImpl.setAddressHierarchyLevelParents(AddressHierarchyServiceImpl.java:643) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) com.sun.proxy.$Proxy237.setAddressHierarchyLevelParents(Unknown Source) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) org.openmrs.aop.LoggingAdvice.invoke(LoggingAdvice.java:121) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) com.sun.proxy.$Proxy238.setAddressHierarchyLevelParents(Unknown Source) org.openmrs.module.addresshierarchy.web.controller.ManageAddressHierarchyController.getOrderedAddressHierarchyLevels(ManageAddressHierarchyController.java:90) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:163) org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:446) org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:434) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857) javax.servlet.http.HttpServlet.service(HttpServlet.java:624) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842) javax.servlet.http.HttpServlet.service(HttpServlet.java:731) org.openmrs.module.web.filter.ForcePasswordChangeFilter.doFilter(ForcePasswordChangeFilter.java:60) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:72) org.openmrs.module.owa.filter.OwaFilter.doFilter(OwaFilter.java:57) org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70) org.openmrs.module.web.filter.ModuleFilter.doFilter(ModuleFilter.java:54) org.openmrs.web.filter.OpenmrsFilter.doFilterInternal(OpenmrsFilter.java:108) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105) org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105) org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105) org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause
java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (openmrs.address_hierarchy_level, CONSTRAINTparent_levelFOREIGN KEY (parent_level_id) REFERENCESaddress_hierarchy_level(address_hierarchy_level_id)) sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) java.lang.reflect.Constructor.newInstance(Constructor.java:423) com.mysql.jdbc.Util.handleNewInstance(Util.java:425) com.mysql.jdbc.Util.getInstance(Util.java:408) com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1162) com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778) com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262) com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:958) com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1135) org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:127) org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:114) org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:163) org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:226) org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:484) org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351) org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:67) org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1227) org.hibernate.internal.SessionImpl.list(SessionImpl.java:1711) org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380) org.openmrs.module.addresshierarchy.db.hibernate.HibernateAddressHierarchyDAO.getAddressHierarchyEntryCountByLevel(HibernateAddressHierarchyDAO.java:65) org.openmrs.module.addresshierarchy.service.AddressHierarchyServiceImpl.getAddressHierarchyEntryCountByLevel(AddressHierarchyServiceImpl.java:411) org.openmrs.module.addresshierarchy.service.AddressHierarchyServiceImpl.setAddressHierarchyLevelParents(AddressHierarchyServiceImpl.java:643) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) com.sun.proxy.$Proxy237.setAddressHierarchyLevelParents(Unknown Source) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) org.openmrs.aop.LoggingAdvice.invoke(LoggingAdvice.java:121) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) com.sun.proxy.$Proxy238.setAddressHierarchyLevelParents(Unknown Source) org.openmrs.module.addresshierarchy.web.controller.ManageAddressHierarchyController.getOrderedAddressHierarchyLevels(ManageAddressHierarchyController.java:90) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:163) org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:446) org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:434) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857) javax.servlet.http.HttpServlet.service(HttpServlet.java:624) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842) javax.servlet.http.HttpServlet.service(HttpServlet.java:731) org.openmrs.module.web.filter.ForcePasswordChangeFilter.doFilter(ForcePasswordChangeFilter.java:60) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:72) org.openmrs.module.owa.filter.OwaFilter.doFilter(OwaFilter.java:57) org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:70) org.openmrs.module.web.filter.ModuleFilter.doFilter(ModuleFilter.java:54) org.openmrs.web.filter.OpenmrsFilter.doFilterInternal(OpenmrsFilter.java:108) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105) org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105) org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:105) org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)[/details]
Hi @darius, how can I purge the bad data from my Address Hierarchy tables?
If you are trying to nuke everything and start again then I would log into the database and do:
drop table address_hierarchy_address_to_entry_map;
drop table address_hierarchy;
drop table address_hierarchy_level;
delete from global_property where property = 'addresshierarchy.database_version';
Then you can reinstall the module and start from scratch.
(Or are you trying to preserve partial work?)
2 Likes
That did the trick. Good on you @darius! I was just getting it configured so no harm done. Yet another lesson along the way…but getting there.
It is easier to use the truncate command to clear the tables. No need to reinstall the module afterwards and then upload the CSV file later. Would recommend doing this first after installing Bahmni to clear the data initially as well.
Hi. I have the same error. Please how can i log into the database? (poor code knowledge)