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

Xporter migration fails on Oracle when more than 1000 project IDs are queried

    XporterXMLWordPrintable

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • Xporter DC 7.4.5
    • None
    • Server/Cloud Migration
    • None
    • Jira DC 9
      JCMA latest version

    Description

      Description
      When running a JCMA migration with Xporter on a Jira instance with more than 1000 project and a Oracle database, the migration export fails.
      Xporter builds a SQL query containing more than 1000 project IDs. Oracle databases have a limitation of 1000 expressions, which results in the error:

      ORA-01795: maximum number of expressions in a list is 1000
      

      Pre-conditions

      • Jira Data Center using Oracle database
      • Large number of projects (exceeding 1000 IDs involved in query)
      • Presence of scheduled reports (global and/or project-level)

      How to reproduce

      1. Create a JCMA migration plan for more than 1000 Projects
      2. Include Xporter app

      Actual result/Attachments
      Xporter migration fails with the following error:

      DEBUG ServiceRunner     [c.x.j.manager.impl.XporterCloudMigrationServiceImpl] Start creating the migration file.
      DEBUG ServiceRunner     [c.x.j.manager.impl.XporterCloudMigrationServiceImpl] Filename: migration
      DEBUG ServiceRunner     [c.x.j.manager.impl.XporterCloudMigrationServiceImpl] ProjectKeys: (1000 + Project Keys)
      DEBUG ServiceRunner     [c.x.j.manager.impl.XporterCloudMigrationServiceImpl] Loading Project Ids list.
      DEBUG ServiceRunner     [c.x.j.manager.impl.XporterCloudMigrationServiceImpl] Get all enabled project from project settings
      DEBUG ServiceRunner     [c.x.j.manager.impl.XporterCloudMigrationServiceImpl] Loading global Scheduled reports
      DEBUG ServiceRunner     [c.x.j.manager.impl.XporterCloudMigrationServiceImpl] Loading  project Scheduled reports
      ERROR ServiceRunner     [c.x.j.jira.listener.XporterCloudMigrationListener] An error has happened while creating the migration file: There was a SQL exception thrown by the Active Objects library:
          Database:
          	- name:Oracle
          	- version:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
          Version 19.29.0.0.0
          	- minor version:1
          	- major version:19
          Driver:
          	- name:Oracle JDBC driver
          	- version:19.3.0.0.0
          
          java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
          
      com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
      Database:
      	- name:Oracle
      	- version:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
      Version 19.29.0.0.0
      	- minor version:1
      	- major version:19
      Driver:
      	- name:Oracle JDBC driver
      	- version:19.3.0.0.0
      java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
      	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:153)
      	at com.atlassian.activeobjects.osgi.ActiveObjectsDelegate.find(ActiveObjectsDelegate.java:278)
      	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
      	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
      	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
      	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:186)
      	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:137)
      	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
      	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      	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:186)
      	at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
      	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:137)
      	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
      	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:241)
      	at jdk.proxy220/jdk.proxy220.$Proxy5086.find(Unknown Source)
      	at com.xpandit.jiraxporter.ao.service.XporterRepositoryImpl.get(Unknown Source)
      	at com.xpandit.jiraxporter.manager.impl.ScheduledReportsServiceImpl.getAdminAndProjects(Unknown Source)
      	at com.xpandit.jiraxporter.manager.impl.XporterCloudMigrationServiceImpl.createFile(Unknown Source)
      	at com.xpandit.jiraxporter.jira.listener.XporterCloudMigrationListener.createFileToCloud(Unknown Source)
      	at com.xpandit.jiraxporter.jira.listener.XporterCloudMigrationListener.onStartAppMigration(Unknown Source)
      	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
      	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
      	at com.atlassian.migration.app.DiscoverableListenerProxy.onStartAppMigration(DiscoverableListenerProxy.kt:56)
      	at com.atlassian.migration.app.DiscoverableListenerProxy.onRerunAppMigration(DiscoverableListenerProxy.kt:97)
      	at com.atlassian.migration.app.AbstractCloudMigrationRegistrar.executeRerun(AbstractCloudMigrationRegistrar.kt:268)
      	at com.atlassian.migration.app.AbstractCloudMigrationRegistrar.executeTransfer(AbstractCloudMigrationRegistrar.kt:182)
      	at com.atlassian.jira.migration.app.CloudMigrationRegistrar.runJob(CloudMigrationRegistrar.kt:405)
      	at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:134)
      	at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:106)
      	at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:90)
      	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:500)
      	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeLocalJobWithRetryOnFailure(CaesiumSchedulerService.java:463)
      	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:413)
      	at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:66)
      	at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:60)
      	at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:35)
      	at java.base/java.lang.Thread.run(Thread.java:1583)
      Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
      

      Expected result
      The Xporter migration should handle large datasets without exceeding Oracle SQL limits.

      Version

      • Xporter 7.4.4

      Workaround
      Reduce the number of projects included in the migration (below 1000 per batch)

      Attachments

        Activity

          People

            bernardo.cottim Bernardo Cottim
            mrap Mario Peixoto
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated: