Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-47267

Inconsistent Oracle column type when upgrading from JIRA 6.x to JIRA 7.x

XMLWordPrintable

      Summary

      When upgrading from JIRA 6.x to JIRA 7.x on Oracle database, there will be inconsistency errors on column type for column PARAMETERS. The error is affecting the clusteredjob table which stores cron expression for JIRA 7.0+.

      In JIRA 6.x, the column type is LONG RAW while in JIRA 7.x is BLOB

      Environment

      JIRA connected to supported Oracle database

      Steps to Reproduce

      1. Install JIRA 6.4.12 with Oracle 12c database
      2. Inspect the column type for column "PARAMETERS" of table "clusteredjob"
      3. Upgrade to JIRA 7.0.0 via Rapid Upgrade Method
      4. Check the logs for the error message

      Expected Results

      No error for the UpgradeTask

      Actual Results

      The following errors can be found in catalina.out:

      2015-11-24 17:47:11,692 localhost-startStop-1 INFO      [o.o.c.entity.jdbc.DatabaseUtil] Database Driver Name is Oracle JDBC driver
2015-11-24 17:47:11,692 localhost-startStop-1 INFO      [o.o.c.entity.jdbc.DatabaseUtil] Database Driver Version is 11.2.0.2.0
2015-11-24 17:47:12,921 localhost-startStop-1 ERROR      [o.o.c.entity.jdbc.DatabaseUtil] WARNING: Column "PARAMETERS" of table "clusteredjob" of entity "ClusteredJob" is of type "LONG RAW" in the database, but is defined as type "BLOB" in the entity definition.

      2015-11-24 17:49:43,216 localhost-startStop-1 ERROR      [c.a.j.p.dvcs.scheduler.DvcsScheduler] Unexpected error during launch
org.ofbiz.core.util.GeneralRuntimeException: Error creating GenericValue (SQL Exception while getting value:  (Invalid column type: getBLOB not implemented for class oracle.jdbc.driver.T4CLongRawAccessor))
	at org.ofbiz.core.entity.EntityListIterator.next(EntityListIterator.java:253)
	at org.ofbiz.core.entity.EntityListIterator.next(EntityListIterator.java:49)
	at com.atlassian.jira.ofbiz.DefaultOfBizListIterator$LookaheadIterator.<init>(DefaultOfBizListIterator.java:228)
	at com.atlassian.jira.ofbiz.DefaultOfBizListIterator$LookaheadIterator.<init>(DefaultOfBizListIterator.java:220)
	at com.atlassian.jira.ofbiz.DefaultOfBizListIterator.iterator(DefaultOfBizListIterator.java:212)
	at com.atlassian.jira.ofbiz.WrappingOfBizListIterator.iterator(WrappingOfBizListIterator.java:146)
	at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.forEach(SelectQueryImpl.java:245)
	at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.consumeWith(SelectQueryImpl.java:227)
	at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.singleValue(SelectQueryImpl.java:199)
	at com.atlassian.jira.scheduler.OfBizClusteredJobDao.find(OfBizClusteredJobDao.java:93)
	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.getJobDetails(CaesiumSchedulerService.java:212)
	at com.atlassian.scheduler.core.DelegatingSchedulerService.getJobDetails(DelegatingSchedulerService.java:97)
	at com.atlassian.scheduler.compat.clustered.ClusteredCompatibilityPluginScheduler.getJobInfo(ClusteredCompatibilityPluginScheduler.java:121)
	at com.atlassian.scheduler.compat.AutoDetectingCompatibilityPluginScheduler.getJobInfo(AutoDetectingCompatibilityPluginScheduler.java:83)
	at com.atlassian.jira.plugins.dvcs.scheduler.DvcsScheduler.scheduleJob(DvcsScheduler.java:95)
	at com.atlassian.jira.plugins.dvcs.scheduler.DvcsScheduler.onStart(DvcsScheduler.java:79)
	at com.atlassian.jira.plugins.dvcs.scheduler.DvcsScheduler$1.run(DvcsScheduler.java:59)
	at com.atlassian.jira.plugins.dvcs.scheduler.SchedulerLauncher.runSingleJob(SchedulerLauncher.java:144)
	at com.atlassian.jira.plugins.dvcs.scheduler.SchedulerLauncher.onLifecycleEvent(SchedulerLauncher.java:133)
	at com.atlassian.jira.plugins.dvcs.scheduler.SchedulerLauncher.onStart(SchedulerLauncher.java:73)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager$4.consume(DefaultLifecycleManager.java:310)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager$4.consume(DefaultLifecycleManager.java:306)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager.notifyLifecyleAware(DefaultLifecycleManager.java:344)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager.notifyOnStartIfStartedAndEnabled(DefaultLifecycleManager.java:304)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager.access$300(DefaultLifecycleManager.java:50)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager$3.evaluate(DefaultLifecycleManager.java:261)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager$3.evaluate(DefaultLifecycleManager.java:257)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager.notifyLifecycleAwares(DefaultLifecycleManager.java:286)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager.notifyStartableLifecycleAwares(DefaultLifecycleManager.java:255)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager.startIfApplicationSetup(DefaultLifecycleManager.java:241)
	at com.atlassian.sal.core.lifecycle.DefaultLifecycleManager.start(DefaultLifecycleManager.java:230)
	at com.atlassian.sal.jira.lifecycle.JiraLifecycleManager.onJiraStart(JiraLifecycleManager.java:68)
	... 3 filtered
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.atlassian.event.internal.SingleParameterMethodListenerInvoker.invoke(SingleParameterMethodListenerInvoker.java:36)
	at com.atlassian.event.internal.AsynchronousAbleEventDispatcher$1$1.run(AsynchronousAbleEventDispatcher.java:48)
	at com.google.common.util.concurrent.MoreExecutors$DirectExecutorService.execute(MoreExecutors.java:299)
	at com.atlassian.event.internal.AsynchronousAbleEventDispatcher.dispatch(AsynchronousAbleEventDispatcher.java:107)
	at com.atlassian.event.internal.EventPublisherImpl.invokeListeners(EventPublisherImpl.java:160)
	at com.atlassian.event.internal.EventPublisherImpl.publish(EventPublisherImpl.java:79)
	at com.atlassian.plugin.event.impl.DefaultPluginEventManager.broadcast(DefaultPluginEventManager.java:84)
	at com.atlassian.jira.upgrade.PluginUpgradeLauncher.start(PluginUpgradeLauncher.java:35)
	at com.atlassian.jira.startup.ActiveServicesLauncher.start(ActiveServicesLauncher.java:57)
	at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$175(DefaultJiraLauncher.java:140)
	at com.atlassian.jira.startup.DefaultJiraLauncher$$Lambda$14/681048280.run(Unknown Source)
	at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:356)
	at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:226)
	at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:126)
	at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$173(DefaultJiraLauncher.java:92)
	at com.atlassian.jira.startup.DefaultJiraLauncher$$Lambda$1/105253432.run(Unknown Source)
	at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:34)
	at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:90)
	at com.atlassian.jira.startup.LauncherContextListener.contextInitialized(LauncherContextListener.java:84)
	... 5 filtered
	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: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while getting value:  (Invalid column type: getBLOB not implemented for class oracle.jdbc.driver.T4CLongRawAccessor)
	at org.ofbiz.core.entity.jdbc.SqlJdbcUtil.getValue(SqlJdbcUtil.java:706)
	at org.ofbiz.core.entity.EntityListIterator.currentGenericValue(EntityListIterator.java:169)
	at org.ofbiz.core.entity.EntityListIterator.next(EntityListIterator.java:246)
	... 63 more
Caused by: java.sql.SQLException: Invalid column type: getBLOB not implemented for class oracle.jdbc.driver.T4CLongRawAccessor
	at oracle.jdbc.driver.Accessor.getBLOB(Accessor.java:1270)
	at oracle.jdbc.driver.OracleResultSetImpl.getBLOB(OracleResultSetImpl.java:1623)
	at oracle.jdbc.driver.OracleResultSetImpl.getBlob(OracleResultSetImpl.java:585)
	at org.apache.commons.dbcp.DelegatingResultSet.getBlob(DelegatingResultSet.java:550)
	at org.apache.commons.dbcp.DelegatingResultSet.getBlob(DelegatingResultSet.java:550)
	at org.ofbiz.core.entity.jdbc.SqlJdbcUtil.getBlobAsByteArray(SqlJdbcUtil.java:741)
	at org.ofbiz.core.entity.jdbc.SqlJdbcUtil.getValue(SqlJdbcUtil.java:701)
	... 65 more

      Workaround

      1. Identify the latest XML backup from the $JIRA_HOME/export directory
      2. Create a new user as per the Connecting JIRA to Oracle guide
      3. Install a new JIRA 7.0.0 instance and connect it to the new Oracle user
      4. Import the XML backup

            [JRASERVER-47267] Inconsistent Oracle column type when upgrading from JIRA 6.x to JIRA 7.x

            Sarah A added a comment -

            https://getsupport.atlassian.com/browse/GHS-139721

            Sarah A added a comment - https://getsupport.atlassian.com/browse/GHS-139721

            crf added a comment -

            ian.juliff436704020:

            this will effect the majority of ORACLE users. Please could you let me know when this is likely to be fixed?

            This would affect all Oracle users that have first upgraded to 6.4.8 or later prior to upgrading to 7.0. If you are still on 6.4.7 or earlier, then you should be able to move directly to 7.0 without a problem. I am working on the fix for it now.

            robert.heckel:

            I really would like to at least perform an automated upgrade from our test instance so that I can see how the upgrade to 7 would affect us, rather than a fresh install then import as our support engineer offered.

            Mandatory disclaimer: I have not yet tested these commands. You should always back up your database before performing any kind of direct SQL surgery on it.

            I can offer two simpler workarounds that I would expect to work. The first is to drop the table before starting JIRA 7.0 for the first time:

            DROP TABLE clusteredjob;

            This table was not used in JIRA 6.4.x and was only added to make it possible to export from JIRA Cloud to a 6.4.x instance. It will be empty unless you did that, and you do not need any data that it might still contain. JIRA 7.0 will recreate the table with the correct type, and the upgrade tasks will will repopulate the table as needed.

            Alternatively, you can attempt to alter the column type in place. I don't see any real advantage to doing that, however:

            ALTER TABLE clusteredjob MODIFY (parameters BLOB);

            crf added a comment - ian.juliff436704020 : this will effect the majority of ORACLE users. Please could you let me know when this is likely to be fixed? This would affect all Oracle users that have first upgraded to 6.4.8 or later prior to upgrading to 7.0. If you are still on 6.4.7 or earlier, then you should be able to move directly to 7.0 without a problem. I am working on the fix for it now. robert.heckel : I really would like to at least perform an automated upgrade from our test instance so that I can see how the upgrade to 7 would affect us, rather than a fresh install then import as our support engineer offered. Mandatory disclaimer: I have not yet tested these commands. You should always back up your database before performing any kind of direct SQL surgery on it. I can offer two simpler workarounds that I would expect to work. The first is to drop the table before starting JIRA 7.0 for the first time: DROP TABLE clusteredjob; This table was not used in JIRA 6.4.x and was only added to make it possible to export from JIRA Cloud to a 6.4.x instance. It will be empty unless you did that, and you do not need any data that it might still contain. JIRA 7.0 will recreate the table with the correct type, and the upgrade tasks will will repopulate the table as needed. Alternatively, you can attempt to alter the column type in place. I don't see any real advantage to doing that, however: ALTER TABLE clusteredjob MODIFY (parameters BLOB);

            RobertH added a comment -

            ^ That. I really would like to at least perform an automated upgrade from our test instance so that I can see how the upgrade to 7 would affect us, rather than a fresh install then import as our support engineer offered. Obviously nothing against them, as they have to work within the confines of the application.

            RobertH added a comment - ^ That. I really would like to at least perform an automated upgrade from our test instance so that I can see how the upgrade to 7 would affect us, rather than a fresh install then import as our support engineer offered. Obviously nothing against them, as they have to work within the confines of the application.

            Ian Juliff added a comment -

            I dont believe this is an edge case, this will effect the majority of ORACLE users. Please could you let me know when this is likely to be fixed?

            Ian Juliff added a comment - I dont believe this is an edge case, this will effect the majority of ORACLE users. Please could you let me know when this is likely to be fixed?

              ohernandez@atlassian.com Oswaldo Hernandez (Inactive)
              cchan Chung Park Chan
              Affected customers:
              8 This affects my team
              Watchers:
              22 Start watching this issue

                Created:
                Updated:
                Resolved: