Data xml Backup restore failed in JIRA Software

Question

we have restored the datra xml backup from prod jira instance to TEST jira instance. During restore had an issue and got the below error,

/secure/Dashboard.jspa [webresource] exception thrown in `addToUrl` during condition evaluation
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.4.5
- minor version:4
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1212
org.postgresql.util.PSQLException: ERROR: relation "public.AO_21F425_MESSAGE_AO" does not exist
Position: 28

.....

/secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.OfbizImportHandler] Create failed - retrying. Attempt [1] out of [5]
org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:OSPropertyEntry][propertyKey,xoauth_app_url][entityName,OAuthServiceProviderToken][entityId,21824][id,154264][type,6] (SQL Exception while executing the following:INSERT INTO public.propertyentry (ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype) VALUES (?, ?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint "pk_propertyentry"
Detail: Key (id)=(154264) already exists.)

We are using JIRA v7.13.2 and Database:
- name:PostgreSQL
- version:9.4.5
- minor version:4
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1212.

The restore got completed with this error, and unable to launch the Jira application.

Please help us to resolve this issue.

Prakash Selladurai_4uhjlc · Answer

Now we have upgraded our jira instance to v8.5.5 and both TEST and Prod jira using oracle database, but still we have the same issue the data xaml backup restore from PROD jira to TEST jira is not working and got the below error,

2021-02-17 04:58:12,464-0500 https-jsse-nio-9007-exec-13 ERROR f47aift 298x207597x2 6yinbo 10.129.61.68,127.0.0.1 /rest/message-field/1.0/config/getForCreate [c.j.j.p.messagefield.rest.RestExecutor] There was a SQL exception thrown by the Active Objects library:
Database:
- name:Oracle
- version:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
- minor version:2
- major version:12
Driver:
- name:Oracle JDBC driver
- version:12.2.0.1.0

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:Oracle
- version:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
- minor version:2
- major version:12
Driver:
- name:Oracle JDBC driver
- version:12.2.0.1.0
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:126)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.find(TenantAwareActiveObjects.java:286)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy1055.find(Unknown Source)
at com.jibrok.jira.plugins.messagefield.config.modelservice.FieldConfigServiceIml$1.doInTransaction(FieldConfigServiceIml.java:55)
at com.jibrok.jira.plugins.messagefield.config.modelservice.FieldConfigServiceIml$1.doInTransaction(FieldConfigServiceIml.java:52)
at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:21)
at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:50)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
at com.sun.proxy.$Proxy307.doInTransaction(Unknown Source)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:131)
at com.sun.proxy.$Proxy307.doInTransaction(Unknown Source)
at com.atlassian.sal.core.transaction.HostContextTransactionTemplate.execute(HostContextTransactionTemplate.java:18)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy1047.execute(Unknown Source)
at com.atlassian.activeobjects.internal.SalTransactionManager.inTransaction(SalTransactionManager.java:42)
at com.atlassian.activeobjects.internal.AbstractLoggingTransactionManager.doInTransaction(AbstractLoggingTransactionManager.java:16)
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.executeInTransaction(EntityManagedActiveObjects.java:204)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.executeInTransaction(TenantAwareActiveObjects.java:336)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy1055.executeInTransaction(Unknown Source)
at com.jibrok.jira.plugins.messagefield.config.modelservice.FieldConfigServiceIml.getAllFieldConfigs(FieldConfigServiceIml.java:52)
at com.jibrok.jira.plugins.messagefield.config.modelservice.Rest$7.doAction(Rest.java:333)
at com.jibrok.jira.plugins.messagefield.config.modelservice.Rest$7.doAction(Rest.java:326)
at com.jibrok.jira.plugins.messagefield.rest.RestExecutor.getResponse(RestExecutor.java:17)
at com.jibrok.jira.plugins.messagefield.rest.RestExecutor.getResponse(RestExecutor.java:12)
at com.jibrok.jira.plugins.messagefield.config.modelservice.Rest.getForCreate(Rest.java:387)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
... 19 filtered
at com.atlassian.plugins.rest.module.RestDelegatingServletFilter$JerseyOsgiServletContainer.doFilter(RestDelegatingServletFilter.java:159)
... 1 filtered
at com.atlassian.plugins.rest.module.RestDelegatingServletFilter.doFilter(RestDelegatingServletFilter.java:69)
... 36 filtered
at com.atlassian.jira.plugin.mobile.web.filter.MobileAppRequestFilter.doFilter(MobileAppRequestFilter.java:37)
... 4 filtered
at com.atlassian.jira.plugin.mobile.login.MobileLoginSuccessFilter.doFilter(MobileLoginSuccessFilter.java:54)
... 3 filtered
at com.atlassian.diagnostics.internal.platform.monitor.http.HttpRequestMonitoringFilter.doFilter(HttpRequestMonitoringFilter.java:55)
... 8 filtered
at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
... 57 filtered
at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:66)
... 1 filtered
at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:64)
... 16 filtered
at com.atlassian.plugins.rest.module.servlet.RestSeraphFilter.doFilter(RestSeraphFilter.java:37)
... 19 filtered
at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)
... 5 filtered
at com.idalko.jira.plugins.igrid.servicedesk.ServletRequestContextFilter.doFilter(ServletRequestContextFilter.java:23)
... 8 filtered
at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
... 4 filtered
at com.atlassian.web.servlet.plugin.LocationCleanerFilter.doFilter(LocationCleanerFilter.java:36)
... 7 filtered
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:239)
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:215)
at net.bull.javamelody.PluginMonitoringFilter.doFilter(PluginMonitoringFilter.java:88)
at net.bull.javamelody.JiraMonitoringFilter.doFilter(JiraMonitoringFilter.java:137)
... 22 filtered
at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
... 25 filtered
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: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:4646)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:10203)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3775)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:42)
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeQuery$5(DiagnosticPreparedStatement.java:59)
at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:69)
at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:55)
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeQuery(DiagnosticPreparedStatement.java:59)
at net.java.ao.EntityManager.find(EntityManager.java:671)
at net.java.ao.EntityManager.find(EntityManager.java:621)
at net.java.ao.EntityManager.find(EntityManager.java:532)
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:124)
... 346 more
Caused by: Error : 942, Position : 233, Sql = SELECT "FIELD_CONFIG_ID","LINK","ROLES","SHOW_IF_HAS_LINKS","LINKED_STATUSES","GROUPS","CODE","VIEW_FOR","SCREENS","AS_FLAG","CONTEXTS","TITLE","STATUSES","MESSAGE_TYPE","ID","VIEW_VERSION","CUSTOM_FIELD_ID","LINK_TYPES","BODY" FROM "AO_94BAAA_FIELD_CONFIG", OriginalSql = SELECT "FIELD_CONFIG_ID","LINK","ROLES","SHOW_IF_HAS_LINKS","LINKED_STATUSES","GROUPS","CODE","VIEW_FOR","SCREENS","AS_FLAG","CONTEXTS","TITLE","STATUSES","MESSAGE_TYPE","ID","VIEW_VERSION","CUSTOM_FIELD_ID","LINK_TYPES","BODY" FROM "AO_94BAAA_FIELD_CONFIG", Error Msg = ORA-00942: table or view does not exist

Prakash Selladurai_4uhjlc · Answer

Hi Liam, Still i have same issue with same database type in both TEST and PROD env in jirav8.5.5

Liam Van der Meer · Answer

Hi Prakash - just wondering, did you ever resolve this? We are facing a similar issues.