java.lang.IllegalStateException:Could not determine the mapping Oracle export, for DEFAULT_ACCOUNT

[John Rushe]

Hi,

Migrating JIRA 6.4 from Oracle 11g to Postgres (prior to upgrading JIRA).

I get the following error when importing data:

2017-11-10 19:15:50,865 JiraImportTaskExecutionThread-1 ERROR anonymous 1152x92x1 1r4wnov 192.168.10.32 /secure/SetupImport.jspa [jira.bc.dataimport.DefaultDataImportService] Error importing data: java.lang.IllegalStateException: Could not determine the proper mapping from Oracle export, for column:DEFAULT_ACCOUNT
java.lang.IllegalStateException: Could not determine the proper mapping from Oracle export, for column:DEFAULT_ACCOUNT
at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.getSqlType(ActiveObjectsTableCreator.java:158)
at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.isString(ActiveObjectsTableCreator.java:176)
at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.getQualifiers(ActiveObjectsTableCreator.java:167)
at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.getTypeInfo(ActiveObjectsTableCreator.java:126)
at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.toDdlField(ActiveObjectsTableCreator.java:107)
at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.toDdlTable(ActiveObjectsTableCreator.java:96)
at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.create(ActiveObjectsTableCreator.java:80)
at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.create(ActiveObjectsTableCreator.java:62)
at com.atlassian.dbexporter.importer.TableDefinitionImporter.doImportNode(TableDefinitionImporter.java:50)
at com.atlassian.dbexporter.importer.AbstractImporter.importNode(AbstractImporter.java:49)
at com.atlassian.dbexporter.DbImporter.importData(DbImporter.java:73)
at com.atlassian.activeobjects.backup.ActiveObjectsBackup.restore(ActiveObjectsBackup.java:164)
at com.atlassian.jira.bc.dataimport.DefaultDataImportService.restoreActiveObjects(DefaultDataImportService.java:572)
at com.atlassian.jira.bc.dataimport.DefaultDataImportService.performImport(DefaultDataImportService.java:773)
at com.atlassian.jira.bc.dataimport.DefaultDataImportService.doImport(DefaultDataImportService.java:332)
at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:65)
at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:28)
at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:177)
at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:148)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
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)

Has anyone come across a solution for this issue?

Thank you.

[John Rushe]

Hi Andy,

Apologies for the delay in coming back to you.

We have decided to revert to an Oracle->Oracle migration here as Oracle->Postgres was proving too problematic.

Thanks for your help.

John

[John Rushe]

Hi Andy,

The definition of the table in the oracle database is as follows:

On the source (Oracle) database:
select * from AO_C3C6E8_LINK_V1;

no rows selected

SQL>
SQL> desc AO_C3C6E8_LINK_V1
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_ID NOT NULL NUMBER(11)
ID NOT NULL NUMBER(11)
LINK_TYPE NOT NULL VARCHAR2(255)
SCOPE NOT NULL NUMBER(20)
SCOPE_TYPE NOT NULL VARCHAR2(255)
DEFAULT_ACCOUNT NOT NULL NUMBER(1)

SQL>

 Note that I made the change to the precision :

 <column name="DEFAULT_ACCOUNT" primaryKey="false" autoIncrement="false" sqlType="2" precision="1"/>

and re-ran the load . It failed again with the same error.

Regards

John 

[Andy Heinzer]

Hi John,

Sorry to hear that didn't resolve this.  For the next steps, I'd recommend setting up a staging server of Jira with a postgres database on the backend, and then installing this plugin to that otherwise empty Jira Server instance.  

I would then be interested to see if we can generate an XML backup of this instance and examine the activeobjects.xml file in that instance to see if we can get a better understanding of what kind of data structure is expected to exist in that AO table of the Jira database.   With that information, perhaps we can then tweak the other backup file to be restorable in your destination instance.

Please let me know the results.

Andy

[Andy Heinzer]

Hi John,

Sorry to hear that you have run into this problem, but we do have a helpful KB that explains some steps you can take to correct this.  Please see the KB Could not determine the proper mapping from Oracle export, for column:DEFAULT.

Please let us know if you run into any problems following those steps.

Regards,
Andy

[John Rushe]

Andy,

Thank you for your reply.

Please note that there error here if for column DEFAULT_ACCOUNT  as opposed to column:DEFAULT.

Is this the same column/Error or a different error?

Regards

John 

[Andy Heinzer]

Hi John,

I'd be interested to see if you can follow the diagnostic steps in the KB to see if you indeed have a column called DEFAULT_ACCOUNT instead of jira DEFAULT as is mentioned in the KB.   If so, then I believe the steps would be nearly identical if not very similar in order to make this work.

I believe this problem is happening because of the structural differences between Oracle databases and other database formats like postgres or mysql.

Let me know if you can still follow those steps successfully even with this slightly different name.

Regards,
Andy

[John Rushe]

Hi Andy,

Thank you for your reply.

The following are the two instances of "DEFAULT_ACCOUNT" in the activeobjects.xml file.

<table name="AO_C3C6E8_LINK_V1">

    <column name="ACCOUNT_ID" primaryKey="false" autoIncrement="false" sqlType="2" precision="11"/>

    <column name="DEFAULT_ACCOUNT" primaryKey="false" autoIncrement="false" sqlType="2"/>

    <column name="ID" primaryKey="true" autoIncrement="true" sqlType="2" precision="11"/>

    <column name="LINK_TYPE" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>

    <column name="SCOPE" primaryKey="false" autoIncrement="false" sqlType="2" precision="20"/>

    <column name="SCOPE_TYPE" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>

    <foreignKey fromTable="AO_C3C6E8_LINK_V1" fromColumn="ACCOUNT_ID" toTable="AO_C3C6E8_ACCOUNT_V1" toColumn="ID"/>

  </table>

<data tableName="AO_C3C6E8_LINK_V1">

    <column name="ACCOUNT_ID"/>

    <column name="ID"/>

    <column name="LINK_TYPE"/>

    <column name="SCOPE"/>

    <column name="SCOPE_TYPE"/>

    <column name="DEFAULT_ACCOUNT"/>

  </data>

Should I change the first occurrence from 

  <column name="DEFAULT_ACCOUNT" primaryKey="false" autoIncrement="false" sqlType="2"/>

to 

  <column name="DEFAULT_ACCOUNT" primaryKey="false" autoIncrement="false" sqlType="2" precision="1"/>

What change do you recommend in the second occurrence? Should I just change the ordering of the columns?

Thank you

john 

[Andy Heinzer]

Hi John,

I believe that the first change looks correct to me.   That section appears to need this precision parameter defined.

However for the data section, it isn't clear to me either exactly what has to be changed there.    It looks like this is actually a different plugin in this case, then the one that was used in the creation of this KB.  Both appear to be Tempo plugins, but the KB appears to be referencing Tempo Teams, and this new plugin looks to be for the Tempo Accounts plugin.

I'm expecting that there would have been a series of row entries there that help define the data in that table.   However without knowing exactly what is supposed to be in there, it makes it very difficult to know the correct data for that table.

The KB does have a note about this

The Mapping may vary to the version of the plugin. To create a new instance with the same version for JIRA and the plugin is advisable.

I think the next step would be to setup a test instance of Jira (same version of Jira and same version of that plugin).  Once this is in place I would be interested to see what data exists in that specific table of the database.   This could be helpful in order to understand if this table holds some predefined values or perhaps it contains more personal information about your plugin data/configuration.

It might also help to first compare these tables with a SQL command such as:

select * from "AO_C3C6E8_LINK_V1";

Do this on the source database first.  I want to see if this table has any values in it yet or not.

The example in the KB has the problem of this DEFAULT data being treated as an integer when we're really expecting this to be a boolean value in the activeobjects.xml file.  But if this specific table in your source instance doesn't actually have any data values in it, then there might not be any need to replace this data section yet.   Which means you can probably just change the first part and skip that data replacement step.

Please let me know if this helps.

Andy