Help to solve HTTP Status 500 - Request processing failed

address-hierarchy
Tags: #<Tag:0x00007efd25c836c8>

(Mike Gordon) #1

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?


(Darius Jazayeri) #2

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).


(Mike Gordon) #4

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]


(Mike Gordon) #5

Hi @darius, how can I purge the bad data from my Address Hierarchy tables?


(Darius Jazayeri) #6

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?)


(Mike Gordon) #7

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.


(Gideon Rynjah) #8

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.


(Isaias Arriola) #9

Hi. I have the same error. Please how can i log into the database? (poor code knowledge)