Uploaded image for project: 'Xporter for Jira'
  1. Xporter for Jira
  2. XPORTER-2446

Database constraints are not being removed during the upgrade process

    XporterXMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: Release 6.0.0, Release 6.0.1, Release 6.0.2, Release 6.0.3, Release 6.0.4, Release 6.0.6, Release 6.0.5
    • Fix Version/s: None
    • Component/s: Database
    • Labels:
      None
    • Sprint:
      Filtered Backlog

      Description

      Problem

      After upgrading to Xporter, user is unable to access any configuration page. 

      The following appears in the atlassian-jira{{.log}}

      2019-05-22 14:27:48,555 active-objects-init-JiraTenantImpl{id='system'}-0 ERROR anonymous     [n.java.ao.sql] Exception executing SQL update <ALTER TABLE jiraschema.AO_3C6513_XPORTER_TEMPLATE ALTER COLUMN FILENAME NVARCHAR(255) NOT NULL>2019-05-22 14:27:48,555 active-objects-init-JiraTenantImpl{id='system'}-0 ERROR anonymous     [n.java.ao.sql] Exception executing SQL update <ALTER TABLE jiraschema.AO_3C6513_XPORTER_TEMPLATE ALTER COLUMN FILENAME NVARCHAR(255) NOT NULL>com.microsoft.sqlserver.jdbc.SQLServerException: The object 'U_AO_3C6513_XPORTER980075700' is dependent on column 'FILENAME'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:845) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:752) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:680) 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:2245) at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2301) at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2273) 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: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.$Proxy465.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.$Proxy465.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.$Proxy1035.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:102) at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:97) 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:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)2019-05-22 14:27:48,571 active-objects-init-JiraTenantImpl{id='system'}-0 WARN anonymous     [n.j.ao.db.MsJdbcSQLServerDatabaseProvider] Error in schema creation: The object 'U_AO_3C6513_XPORTER980075700' is dependent on column 'FILENAME'.; attempting to roll back last partially generated table2019-05-22 14:27:48,571 active-objects-init-JiraTenantImpl{id='system'}-0 WARN anonymous     [c.a.activeobjects.osgi.TenantAwareActiveObjects] bundle [com.xpandit.plugins.jiraxporter] failed to create ActiveObjectscom.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:Database: - name:Microsoft SQL Server - version:11.00.2100 - minor version:0 - major version:11Driver: - name:Microsoft JDBC Driver 6.2 for SQL Server - version:6.2.1.0
      

      Diagnosis

      Environment

      • SQL Server 2012

      Diagnostic Steps

      We can see the error in the logs the following :

       

        [n.java.ao.sql] Exception executing SQL update <ALTER TABLE jiraschema.AO_3C6513_XPORTER_TEMPLATE ALTER COLUMN FILENAME NVARCHAR(255) NOT NULL>com.microsoft.sqlserver.jdbc.SQLServerException: The object 'U_AO_3C6513_XPORTER980075700' is dependent on column 'FILENAME'. at
      

       

      Which shows that the table was only partially generated and then rolled back as you can see in the log. There are issues in the database which is what's causing the issue.

      Cause

      The upgrade process didn't run on the database correctly and it's rolling back the table creation.

      Workaround

      Execute the following steps to get Xporter working again:

      1. Uninstall Xporter
      2. Drop the following constraints as described  here
        1. U_AO_3C6513_XPORTER646591493 (Table: AO_3C6513_XPORTER_SCHEME)
        2. U_AO_3C6513_XPORTER980075700 (Table: AO_3C6513_XPORTER_TEMPLATE)
        3. U_AO_3C6513_XPORTER1963927984 (Table: AO_3C6513_XPORTER_TEMPLATE)
      3. Downgrade the Xporter build number on Jira app registry by executing the following SQL Queries:
      select ps.* from propertystring ps
      join propertyentry pe on pe.id=ps.id
      where property_key like '%xporter%'
      

      The query above will return the id and the Xporter build number. Now you have the ID, so, you need to execute the following query. Before executing, replace the PUT_ID_HERE with the ID returned by the previous query.

      update propertystring set propertyvalue = '100058000' where id= PUT_ID_HERE
      

      After updating the Xporter build number, please make sure that the value is correct by executing the following query:

      select ps.* from propertystring ps join propertyentry pe on pe.id=ps.id where property_key like '%xporter%'
      
      1. Install Xporter latest version

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              rmbr Rui Rodrigues
              Reporter:
              rmbr Rui Rodrigues
              Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: