java.sql.SQLException: ORA-01440: column to be modified must be empty to decrease precision or scale

XMLWordPrintable

    • 7.02
    • 11
    • Severity 2 - Major
    • 1

      Problem Summary

      After restoring a JIRA Cloud backup on JIRA Server running on Oracle database, the following exceptions (or similar) appeared repeatedly on JIRA's logs:

      2016-11-01 14:29:49,766 active-objects-init-JiraTenantImpl{id='system'}-0 ERROR anonymous 865x1176x1 1mt60nj 64.102.41.116 /secure/admin/XmlRestore.jspa [n.java.ao.sql] Exception executing SQL update <ALTER TABLE "AO_E8B6CC_SYNC_AUDIT_LOG" MODIFY ("FLIGHT_TIME_MS" NUMBER(11))>
java.sql.SQLException: ORA-01440: column to be modified must be empty to decrease precision or scale

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:933)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
	at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1640)
	at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1603)
	at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:308)
	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:2238)
	at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2294)
	at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2266)
	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)
	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:498)
	at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
	at com.sun.proxy.$Proxy422.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.$Proxy422.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:317)
	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:207)
	at com.sun.proxy.$Proxy3405.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)
	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)

      The issue seems to be only reproducible on a specific customer's backup and cannot be replicated on a newly exported Cloud backup.

      Environment

      • Oracle 12C database - using driver 12.0.0.1 on testing
      • JIRA Server 7.2.x

      Cause

      The exception seems to be an Oracle-specific problem and is caused by differences in Cloud and Server version of DVCS plugin. The column FLIGHT_TIME_MS of table AO_E8B6CC_SYNC_AUDIT_LOG uses data type INT, whereas Cloud uses LONG. It appears that Oracle cannot perform precision downgrade if the column is empty.

      Additional information about changing size of a non-char column can be found in this Oracle Administrator Guide article.

      Workaround

      Run the below queries on JIRA's database.

      Make sure to have an up-to-date backup of the database before proceeding.

      ALTER TABLE AO_E8B6CC_SYNC_AUDIT_LOG
ADD FLIGHT_TIME_MS_TEMP NUMBER(11);

UPDATE AO_E8B6CC_SYNC_AUDIT_LOG
SET FLIGHT_TIME_MS_TEMP=FLIGHT_TIME_MS;

UPDATE AO_E8B6CC_SYNC_AUDIT_LOG
SET FLIGHT_TIME_MS = null;

ALTER TABLE AO_E8B6CC_SYNC_AUDIT_LOG MODIFY FLIGHT_TIME_MS NUMBER(11);

UPDATE AO_E8B6CC_SYNC_AUDIT_LOG
SET FLIGHT_TIME_MS = FLIGHT_TIME_MS_TEMP;

ALTER TABLE AO_E8B6CC_SYNC_AUDIT_LOG
DROP COLUMN FLIGHT_TIME_MS_TEMP;

       

            Assignee:
            Unassigned
            Reporter:
            Anna Cardino (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated: