Fail during migrating CLOB type column with missing index in Oracle DB

Description

Summary

Environment

Tested

Very likely

Jira 7.7.0 (AO 1.5.0)

Any Jira using AO version > 0.28.5

Oracle 12c

Any Oracle supported version

Steps to Reproduce

  1. Start Jira

  2. Install Portfolio 2.13.1

  3. Stop Jira

  4. In database table AO_D9132D_SCENARIO_ISSUE_LABEL:

    1. Remove index from LABEL column

    2. Remove LABEL column

    3. Add LABEL column with CLOB type (don't add index)

  5. Start Jira

Expected Results

Jira runs successfully despite different column type.

Actual Results

Error can be seen in the log:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 2018-06-02 01:56:45,899 JIRA-Bootstrap ERROR [c.a.r.c.b.jira.lifecycle.PluginStartUpTaskServiceBridge63] Unexpected error during task 'Active Objects initialization'. 2018-06-02 01:56:45,900 JIRA-Bootstrap ERROR [c.a.r.c.b.jira.lifecycle.PluginStartUpTaskServiceBridge63] bundle [com.atlassian.jpo] com.atlassian.activeobjects.internal.ActiveObjectsInitException: bundle [com.atlassian.jpo] at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:95) at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:86) at com.atlassian.sal.core.executor.ThreadLocalDelegateCallable.call(ThreadLocalDelegateCallable.java:38) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library: Database: - name:Oracle - version:Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production - minor version:1 - major version:12 - name:Oracle JDBC driver - version:12.2.0.1.0 java.sql.SQLException: ORA-01418: specified index does not exist at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:53) at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:77) at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory$1.doInTransaction(AbstractActiveObjectsFactory.java:72) at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:21) at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:34) ... 2 filtered at java.lang.reflect.Method.invoke(Method.java:497) at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26) at com.sun.proxy.$Proxy426.doInTransaction(Unknown Source) ... 2 filtered at java.lang.reflect.Method.invoke(Method.java:497) at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:136) at com.sun.proxy.$Proxy426.doInTransaction(Unknown Source) at com.atlassian.sal.core.transaction.HostContextTransactionTemplate.execute(HostContextTransactionTemplate.java:18) ... 2 filtered at java.lang.reflect.Method.invoke(Method.java:497) 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.$Proxy1024.execute(Unknown Source) at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:72) at com.atlassian.activeobjects.internal.DelegatingActiveObjectsFactory.create(DelegatingActiveObjectsFactory.java:32) at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:91) ... 6 more Caused by: java.sql.SQLException: ORA-01418: specified index 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.T4CStatement.doOall8(T4CStatement.java:213) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:896) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119) at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1661) at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1626) at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1613) at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:282) at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234) at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234) at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2246) at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2302) at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2274) at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:91) at net.java.ao.EntityManager.migrate(EntityManager.java:128) at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:51) ... 44 more Caused by: Error : 1418, Position : 16, Sql = DROP INDEX JIRA."index_ao_d9132d_sce307133478", OriginalSql = DROP INDEX JIRA."index_ao_d9132d_sce307133478", Error Msg = ORA-01418: specified index does not exist at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498) ... 65 more

AO tries to change column type. This opration fails, because it tries to remove missing index in the middle of migration. Temporary column is left in the table that makes it impossible to repeat the process. Next and subsequent Jira starts will report error:

1 2 ... Error : 1430, Position : 54, Sql = ALTER TABLE JIRA."AO_D9132D_SCENARIO_ISSUE_LABEL" ADD "LEBAL" VARCHAR(255), OriginalSql = ALTER TABLE JIRA."AO_D9132D_SCENARIO_ISSUE_LABEL" ADD "LEBAL" VARCHAR(255), Error Msg = ORA-01430: column being added already exists in table

Workaround

While having both original and temporary columns created by AO you can finish migration manually by:

  1. Dropping original column. e.g.

    1 ALTER TABLE AO_D9132D_SCENARIO_ISSUE_LABEL DROP COLUMN LABEL;
  2. Renaming temporary column back to actual column

    1 ALTER TABLE AO_D9132D_SCENARIO_ISSUE_LABEL RENAME COLUMN LEBAL TO LABEL;

Notes

It can happen when migrating from Cloud to Server.
Code behind the problem: source

Environment

None

Testing Notes

Add notes...

Status

Assignee

Unassigned

Reporter

Przemyslaw Czuj

Labels

None

Add-on Type

Server

Team

None

CC

None

Risk factor

None

QA Kickoff Status

None

QA Demo Status

None

Components

Affects versions

0.28.5

Priority

Minor
Configure