OpenMRS 1.8 database migration to 2.10

Tags: #<Tag:0x00007f0f270d7e50>

Application Name: openmrs/openmrs-reference-application-distro:2.10 Version Number: 2.10

Question: I got a broken update openmrs installation and I’m trying to do a migration with it. I installed a 2.10 version using the docker-compose instructions but now I can’t restore the database.

I got the mysql dump from the 1.8 version and manually modified the data in agreement with this wiki article. I When I restarted tomcat, the app is failing to recognize the database. An excerpt of the log can be found at the bottom of this question.

I cant run any update modules in the former installation as it is broken at this moment. But I can’t figure out why the dump is not working. I wonder if there is a configuration file in the reference application that I need to modify if I manually modify the database.

The steps I did where:

  1. docker-compose up
  2. mysql drop database openmrs
  3. mysql create database openmrs
  4. mysql < dump.sql
  5. docker-compose stop && docker-compose start

After Y restart the reference app, the following errores can be seen in .OpenMRS/openmrs.log

Any thoughts?

ERROR - SqlExceptionHelper.logExceptions(146) |2020-07-19 21:57:01,321| Table ‘openmrs.hl7_in_queue’ doesn’t exist ERROR - SqlExceptionHelper.logExceptions(146) |2020-07-19 21:57:01,321| Table ‘openmrs.appframework_component_state’ doesn’t exist ERROR - PageController.handlePath(156) |2020-07-19 21:57:01,328| could not extract ResultSet org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91) at org.hibernate.loader.Loader.getResultSet(Loader.java:2066) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839) at org.hibernate.loader.Loader.doQuery(Loader.java:910) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) at org.hibernate.loader.Loader.doList(Loader.java:2554) at org.hibernate.loader.Loader.doList(Loader.java:2540) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) at org.hibernate.loader.Loader.list(Loader.java:2365) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1718) at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380) at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:402) at org.openmrs.module.appframework.repository.AllComponentsState.getComponentStateFromDB(AllComponentsState.java:54) at org.openmrs.module.appframework.repository.AllComponentsState.getComponentState(AllComponentsState.java:47) at org.openmrs.module.appframework.repository.AllComponentsState$$FastClassBySpringCGLIB$$dc4e6efb.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649) at org.openmrs.module.appframework.repository.AllComponentsState$$EnhancerBySpringCGLIB$$737cbf13.getComponentState() at org.openmrs.module.appframework.service.AppFrameworkServiceImpl.disabledByComponentState(AppFrameworkServiceImpl.java:153) at org.openmrs.module.appframework.service.AppFrameworkServiceImpl.getAllEnabledApps(AppFrameworkServiceImpl.java:141) at org.openmrs.module.appframework.service.AppFrameworkServiceImpl.getAllEnabledExtensions(AppFrameworkServiceImpl.java:219) at org.openmrs.module.appframework.service.AppFrameworkServiceImpl.getExtensionsForCurrentUser(AppFrameworkServiceImpl.java:295) at sun.reflect.GeneratedMethodAccessor648.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201) at com.sun.proxy.$Proxy198.getExtensionsForCurrentUser(Unknown Source) at sun.reflect.GeneratedMethodAccessor648.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.cache.interceptor.CacheInterceptor$1.invoke(CacheInterceptor.java:52) at org.springframework.cache.interceptor.CacheAspectSupport.execute(CacheAspectSupport.java:303) at org.springframework.cache.interceptor.CacheInterceptor.invoke(CacheInterceptor.java:61) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.openmrs.aop.LoggingAdvice.invoke(LoggingAdvice.java:122) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) at com.sun.proxy.$Proxy199.getExtensionsForCurrentUser(Unknown Source) at org.openmrs.module.referenceapplication.page.controller.HomePageController.controller(HomePageController.java:53) at sun.reflect.GeneratedMethodAccessor657.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.openmrs.ui.framework.UiFrameworkUtil.invokeMethodWithArguments(UiFrameworkUtil.java:112) at org.openmrs.ui.framework.UiFrameworkUtil.executeControllerMethod(UiFrameworkUtil.java:71) at org.openmrs.ui.framework.page.PageFactory.handleRequestWithController(PageFactory.java:219) at org.openmrs.ui.framework.page.PageFactory.processThisFragment(PageFactory.java:160) at org.openmrs.ui.framework.page.PageFactory.process(PageFactory.java:116) at org.openmrs.ui.framework.page.PageFactory.handle(PageFactory.java:86) at org.openmrs.module.uiframework.PageController.handlePath(PageController.java:117) at org.openmrs.module.uiframework.PageController.handleUrlWithDotPage(PageController.java:84) at sun.reflect.GeneratedMethodAccessor650.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:177) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:446) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:434) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857) at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842) at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:742) at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:484) at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:409) at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:337) at org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:168) at org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:303) at org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1228) at org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1011) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:955) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857) at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842) at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.openmrs.module.web.filter.ForcePasswordChangeFilter.doFilter(ForcePasswordChangeFilter.java:60) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.openmrs.web.filter.GZIPFilter.doFilterInternal(GZIPFilter.java:65) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:73) at org.openmrs.module.owa.filter.OwaFilter.doFilter(OwaFilter.java:68) at org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:71) at org.openmrs.module.referenceapplication.filter.RequireLoginLocationFilter.doFilter(RequireLoginLocationFilter.java:93) at org.openmrs.module.web.filter.ModuleFilterChain.doFilter(ModuleFilterChain.java:71) at org.openmrs.module.web.filter.ModuleFilter.doFilter(ModuleFilter.java:57) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.openmrs.web.filter.OpenmrsFilter.doFilterInternal(OpenmrsFilter.java:109) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.openmrs.web.filter.StartupFilter.doFilter(StartupFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:494) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1137) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637) at org.apache.tomcat.util.net.AprEndpoint$SocketWithOptionsProcessor.run(AprEndpoint.java:2512) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘openmrs.appframework_component_state’ doesn’t exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) … 154 more ERROR - TimerSchedulerTask.run(50) |2020-07-19 21:57:01,328| FATAL ERROR: Task [class org.openmrs.scheduler.tasks.TaskThreadedInitializationWrapper] failed due to exception [org.hibernate.exception.SQLGrammarException]

Thanks for this @danieljay, could you try using pastebin.com and share your full error logs, i have relatively similar issue.

Hi Sharif! yes, I uploaded it to sprung: http://sprunge.us/ypH6GB

You did the same? Uploading a mysql dump from one version to another one?

I’m wondering if anyone have seen this issue before. As part of my documentation of this process I’m doing an incremental approach. First I’m exporting the rows from the origin database and I’m inserting them in the target database. This worked fine for locations and some other tables, while the patients are not being recognized. I’m trying another approach to uploading them via the rest api. Afterwards I will try to add the visits and encounters, finally I will upload the concept dictionary.