Restoring of Jira-server backup file is failing due to this ERROR: relation public.AO_21F425_MESSAGE

[Shivaprasad Hattaraki]

Jira server 7.13.0

I am getting Error while restoring the backup file.

I changed primary key from "false" to "true" in activeobjects.xml file but did not work.

Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_4789DD_HEALTH_CHECK_STATUS" does not exist
Position: 165
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at net.java.ao.EntityManager.find(EntityManager.java:671)
at net.java.ao.EntityManager.find(EntityManager.java:621)
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:140)
... 548 more
2019-07-09 08:45:28,460 http-nio-8080-exec-11 WARN shiv 525x521x2 66nb5j 172.27.26.48 /rest/webResources/1.0/resources [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.20
- 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
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:142)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.find(TenantAwareActiveObjects.java:296)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
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:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
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:179)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
at com.sun.proxy.$Proxy5860.find(Unknown Source)
at com.atlassian.whisper.plugin.impl.DefaultMessagesManager.lambda$hasMessages$8(DefaultMessagesManager.java:146)
at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:21)
at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:36)
... 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.$Proxy398.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:136)
at com.sun.proxy.$Proxy398.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:302)
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:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
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:179)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
at com.sun.proxy.$Proxy4785.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:302)
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:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
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:179)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
at com.sun.proxy.$Proxy5860.executeInTransaction(Unknown Source)
at com.atlassian.whisper.plugin.impl.DefaultMessagesManager.hasMessages(DefaultMessagesManager.java:143)
at com.atlassian.whisper.plugin.impl.DelegatedMessagesManager.hasMessages(DelegatedMessagesManager.java:90)
at com.atlassian.whisper.plugin.impl.IsWhisperEnabledCondition.isConditionTrue(IsWhisperEnabledCondition.java:24)
at com.atlassian.plugin.webresource.condition.SimpleUrlReadingCondition.addToUrl(SimpleUrlReadingCondition.java:30)
at com.atlassian.plugin.webresource.impl.UrlBuildingStrategy$NormalStrategy.addToUrl(UrlBuildingStrategy.java:39)
at com.atlassian.plugin.webresource.condition.DecoratingUrlReadingCondition.addToUrl(DecoratingUrlReadingCondition.java:48)
at com.atlassian.plugin.webresource.impl.CachedCondition.addToUrlSafely(CachedCondition.java:106)
at com.atlassian.plugin.webresource.impl.CachedCondition.evaluateSafely(CachedCondition.java:60)
at com.atlassian.plugin.webresource.impl.helpers.UrlGenerationHelpers.lambda$getConditions$1(UrlGenerationHelpers.java:280)
at com.atlassian.plugin.webresource.impl.helpers.BaseHelpers.findItChangesArguments(BaseHelpers.java:290)
at com.atlassian.plugin.webresource.impl.helpers.BaseHelpers.findItChangesArguments(BaseHelpers.java:294)
at com.atlassian.plugin.webresource.impl.helpers.BaseHelpers.find(BaseHelpers.java:263)
at com.atlassian.plugin.webresource.impl.helpers.BaseHelpers$BundleFinder.endAndGetResult(BaseHelpers.java:158)
at com.atlassian.plugin.webresource.impl.helpers.BaseHelpers$BundleFinder.end(BaseHelpers.java:166)
at com.atlassian.plugin.webresource.impl.helpers.UrlGenerationHelpers.getConditions(UrlGenerationHelpers.java:285)
at com.atlassian.plugin.webresource.impl.helpers.UrlGenerationHelpers.buildIncludedExcludedConditionsAndBatchingOptions(UrlGenerationHelpers.java:243)
at com.atlassian.plugin.webresource.impl.helpers.UrlGenerationHelpers.resolveExcluded(UrlGenerationHelpers.java:301)
at com.atlassian.plugin.webresource.assembler.DefaultWebResourceAssembler$2.exclude(DefaultWebResourceAssembler.java:191)
at com.atlassian.webresource.plugin.async.AsyncWebResourceLoaderImpl.resolveWebResourceSet(AsyncWebResourceLoaderImpl.java:111)
at com.atlassian.webresource.plugin.async.AsyncWebResourceLoaderImpl.resolve(AsyncWebResourceLoaderImpl.java:52)
at com.atlassian.webresource.plugin.rest.ListOfResources.post(ListOfResources.java:68)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
... 18 filtered
at com.atlassian.plugins.rest.module.RestDelegatingServletFilter$JerseyOsgiServletContainer.doFilter(RestDelegatingServletFilter.java:154)
... 1 filtered
at com.atlassian.plugins.rest.module.RestDelegatingServletFilter.doFilter(RestDelegatingServletFilter.java:68)
... 32 filtered
at com.atlassian.servicedesk.internal.web.ExternalCustomerLockoutFilter.doFilter(ExternalCustomerLockoutFilter.java:56)
... 13 filtered
at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
... 53 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.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.lambda$invokeFilterChain$0(CustomerContextSettingFilter.java:181)
at com.atlassian.servicedesk.internal.api.util.context.ReentrantThreadLocalBasedCodeContext.rteInvoke(ReentrantThreadLocalBasedCodeContext.java:137)
at com.atlassian.servicedesk.internal.api.util.context.ReentrantThreadLocalBasedCodeContext.runOutOfContext(ReentrantThreadLocalBasedCodeContext.java:90)
at com.atlassian.servicedesk.internal.utils.context.CustomerContextServiceImpl.runOutOfCustomerContext(CustomerContextServiceImpl.java:47)
at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.outOfCustomerContext(CustomerContextSettingFilter.java:174)
at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.doFilterImpl(CustomerContextSettingFilter.java:130)
at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.doFilter(CustomerContextSettingFilter.java:121)
... 4 filtered
at com.atlassian.jwt.internal.servlet.JwtAuthFilter.doFilter(JwtAuthFilter.java:32)
... 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)
... 26 filtered
at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
... 24 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: org.postgresql.util.PSQLException: ERROR: relation "public.AO_21F425_MESSAGE_AO" does not exist
Position: 28
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at net.java.ao.EntityManager.find(EntityManager.java:671)
at net.java.ao.EntityManager.find(EntityManager.java:621)
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:140)
... 350 more
2019-07-09 08:47:37,272 automation-queue-claimer:thread-1 WARN shiv 490x271x2 66nb5j 172.27.26.48 /rest/plugins/1.0/installed-marketplace [c.c.j.p.automation.queue.JiraAutomationQueueExecutor] JQL search indexes are currently not available (Indexing: true, Scheduler: SHUTDOWN). No automation items were claimed from the queue for processing on this node since most automation rules depend on JQL indexes being available.
2019-07-09 08:52:37,284 automation-queue-claimer:thread-1 WARN shiv 490x271x2 66nb5j 172.27.26.48 /rest/plugins/1.0/installed-marketplace [c.c.j.p.automation.queue.JiraAutomationQueueExecutor] JQL search indexes are currently not available (Indexing: true, Scheduler: SHUTDOWN). No automation items were claimed from the queue for processing on this node since most automation rules depend on JQL indexes being available.
2019-07-09 08:57:37,296 automation-queue-claimer:thread-1 WARN shiv 490x271x2 66nb5j 172.27.26.48 /rest/plugins/1.0/installed-marketplace [c.c.j.p.automation.queue.JiraAutomationQueueExecutor] JQL search indexes are currently not available (Indexing: true, Scheduler: SHUTDOWN). No automation items were claimed from the queue for processing on this node since most automation rules depend on JQL indexes being available.
2019-07-09 09:02:37,307 automation-queue-claimer:thread-1 WARN shiv 490x271x2 66nb5j 172.27.26.48 /rest/plugins/1.0/installed-marketplace [c.c.j.p.automation.queue.JiraAutomationQueueExecutor] JQL search indexes are currently not available (Indexing: true, Scheduler: SHUTDOWN). No automation items were claimed from the queue for processing on this node since most automation rules depend on JQL indexes being available.
2019-07-09 09:07:37,318 automation-queue-claimer:thread-1 WARN shiv 490x271x2 66nb5j 172.27.26.48 /rest/plugins/1.0/installed-marketplace [c.c.j.p.automation.queue.JiraAutomationQueueExecutor] JQL search indexes are currently not available (Indexing: true, Scheduler: SHUTDOWN). No automation items were claimed from the queue for processing on this node since most automation rules depend on JQL indexes being available.

[Shivaprasad Hattaraki · Accepted solution]

The problem was database Table os_historystep character length was less. 

so ran a query in database to fix it with 255 characters

Query


ALTER TABLE os_historystep ALTER COLUMN caller TYPE character varying(255);

[Prakash Selladurai]

HI @Shivaprasad Hattaraki ,

I too have the same problem and i just tried as you have mentioned to update the alter table column, but i got the below error,

SQL> ALTER TABLE os_historystep ALTER COLUMN caller TYPE character varying(255);
ALTER TABLE os_historystep ALTER COLUMN caller TYPE character varying(255)
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Also i checked the table scheme and its column are same.

SQL> describe os_historystep
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(18)
ENTRY_ID NUMBER(18)
STEP_ID NUMBER(9)
ACTION_ID NUMBER(9)
OWNER VARCHAR2(255 CHAR)
START_DATE DATE
DUE_DATE DATE
FINISH_DATE DATE
STATUS VARCHAR2(60 CHAR)
CALLER VARCHAR2(255 CHAR)

SQL>

[Andy Heinzer]

Hi,

I see you are getting a SQL error when trying to restore this backup file.   However in order to better help here, I would like to know more about this backup file.

• Where did it come from?
• Was this a backup from a Jira Cloud site? If so when was this backup created?
• Or was this a backup of a Jira Server site?  If the backup file came from a Jira Server, what version of Jira did this backup come from?

I ask these questions because there was an bug documented  for Jira server specifically about Jira Cloud exports in JRASERVER-66594.   However this was marked resolved more than a year ago.  The fix here was made to Jira Cloud itself and new backups were sometimes required to get past this issue.   There is also a workaround on that ticket where you an edit the xml backup in a specific way to get past this problem.

Workaround

Manually removing all data relating to AO_575BF5 from activeobjects.xml allows the import to complete successfully:

1. Extract the contents of the backup (zip) file.
2. Using your preferred text editor, open the file activeobjects.xml.
3. Look and delete all the tags containing a reference to the table AO_575BF5. You need to remove each complete entry related to it (see my example image-2018-01-04-19-26-39-993.png. The example is using a different table, but the principle is the same. The XML file might appear in a format difficult to read, so you might want to do the following:
   • For Windows Users: use a text editor that can format the XML to be humanly easier to read and edit, in my case I used Notepad++ with the XML Tools plugin, and used the function 'Pretty print' from the XML Plugin (see my example image-2018-01-04-19-12-05-438.png).
   • For Unix based OS, another option to format the XML file is with the command:

     xmllint --format activeobjects.xml > activeobjects_formatted.xml
     

     • You will need to rename the activeobjects_formatted.xml back to activeobjects.xml before zipping them.
4. Once all the entries related to the table AO_575BF5 have been removed, save the file and create a new zip file using the newly saved activeobjects.xml and the original entities.xml.
5. Attempt the Restore System again.

I hope this helps.

Please let me know the results.

Andy

[Shivaprasad Hattaraki]

The problem was database Table os_historystep character length was less. 

so ran a query in database to fix it with 255 characters

query: ALTER TABLE os_historystep ALTER COLUMN caller TYPE character varying(255);