JIRA System Restore "There was a problem restoring ActiveObjects data for the plugin..."

[Chris McInnes]

Hi Everyone,

I'm trying to move our JIRA Instance to a new server/hosting provider and encountering an issue when doing the "Restore JIRA data from backup" XML process.

There was a problem restoring ActiveObjects data for the plugin System Plugin: Tempo Teams(com.tempoplugin.tempo-teams) #6.3.0 plugin. Importing table AO_AEFED0_USER_INDEX failed. Please check the log for details.

I've setup the new server with exactly the same versions of JIRA (8.1.0) and all of the relevant plugins (Tempo 10.3.0).

In the log files I found this error

2019-06-03 12:46:31,178 JiraImportTaskExecutionThread-1 ERROR zincadmin 759x258x1 jfnmfj 0:0:0:0:0:0:0:1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Error during ActiveObjects restore
com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin System Plugin: Tempo Teams(com.tempoplugin.tempo-teams) #6.3.0 (table AO_AEFED0_USER_INDEX):


Caused by: java.sql.BatchUpdateException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

I've looked in the ActiveObjects.xml file (inside the ZIP Backup file) and this is the table structure.

<table name="AO_AEFED0_USER_INDEX">
<column name="ACTIVE" primaryKey="false" autoIncrement="false" sqlType="-7" precision="1"/>
<column name="DISPLAY_NAME" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/>
<column name="EMAIL" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/>
<column name="FROM_DATE" primaryKey="false" autoIncrement="false" sqlType="93" precision="23" scale="3"/>
<column name="ID" primaryKey="true" autoIncrement="true" sqlType="4" precision="10"/>
<column name="MEMBERSHIP_ID" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/>
<column name="ROLE_ID" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/>
<column name="ROLE_NAME" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/>
<column name="TEAM_ID" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/>
<column name="TEAM_NAME" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/>
<column name="TO_DATE" primaryKey="false" autoIncrement="false" sqlType="93" precision="23" scale="3"/>
<column name="UNIQUE_KEY" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/>
<column name="USER_KEY" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/>
<column name="USER_NAME" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/>
</table>

So I'm guessing it has something to do with the FROM_DATE / TO_DATE fields?

Here is a sample record

<data tableName="AO_AEFED0_USER_INDEX">
<column name="ACTIVE"/>
<column name="DISPLAY_NAME"/>
<column name="EMAIL"/>
<column name="FROM_DATE"/>
<column name="ID"/>
<column name="MEMBERSHIP_ID"/>
<column name="ROLE_ID"/>
<column name="ROLE_NAME"/>
<column name="TEAM_ID"/>
<column name="TEAM_NAME"/>
<column name="TO_DATE"/>
<column name="UNIQUE_KEY"/>
<column name="USER_KEY"/>
<column name="USER_NAME"/>
<row>
<boolean>true</boolean>
<string>Name Goes Here</string>
<string>firstname.surname@domain.com</string>
<timestamp>1752-12-31T14:00:00.000Z</timestamp>
<integer>5534</integer>
<integer xsi:nil="true"/>
<integer xsi:nil="true"/>
<string xsi:nil="true"/>
<integer xsi:nil="true"/>
<string xsi:nil="true"/>
<timestamp>9999-12-30T13:00:00.000Z</timestamp>
<string>00firstname.surname</string>
<string>firstname.surname</string>
<string>firstname.surname</string>
</row>

Any suggestions on how to find out which record is causing the issue or how to fix this?

Thanks

Chris

[Andy Heinzer · Accepted solution]

Hi Chris,

I understand you are trying to migrate your Jira server to a new system and in the process of doing so it appears that there is some plugin data from tempo that is not able to be converted/imported to SQL.

That specific SQL error message I believe pertains to using MS SQL databases.  Jira can be configured to use postgres, mysql, oracle, or Microsoft's SQL just for reference here.  Each database can have slightly different data types.  I would agree with you that this error is being caused by some date field.

There is a really good summary of this specific error and the underlying cause in https://www.mikesdotnetting.com/article/229/conversion-of-a-datetime2-data-type-to-a-datetime-data-type-resulted-in-an-out-of-range-value

While I'm not necessarily advocating for his solution, it does explain why this is happening.  In short, you have at least one value for a date in the datetime2 format that is invalid when being converted to a datetime format.  

From your example data, I think I can see one already:

<string>firstname.surname@domain.com</string>
<timestamp>1752-12-31T14:00:00.000Z</timestamp>
<integer>5534</integer>

That timestamp is not a valid value in the datetime data type.  Only dates from 1753-01-01 up to the year 9999 are accepted.  In that example it looks like you missed the cut off by a single day.  You could edit the xml file directly to give it a different date value, but it's unclear to me if that's an acceptable solution here.   Is that an actual date of some record in your system?  Or is this just some kind of sample data or some incorrect date entry here?   I'm just curious as a single record in your data could throw this error.  You could edit the xml file directly to change it though, save that file, add it back to the backup zip file and then attempt the import once more on the new system.  If that was the only record that was invalid, you should be good to go.  But if you have any other such invalid dates, you can expect to see this error again.

Alternatively, you might be able to side-step this problem entirely just by connecting the Jira server to a different database type, like postgresql and then doing the import there.  Postgres doesn't use this same datetime field that MS SQL will, so it might be able to avoid this error in doing so.

Andy

[Chris McInnes]

Thanks Andrew, that worked.

Every Entry in the AO_AEFED0_USER_INDEX table had a FROM_DATE of 1752.

I replaced that with 1753 and all good.

Would this be a bug in the export process? As I'm not sure how all these records ended up with an invalid value (1752) in the first place?

You're correct about the MSSQL Server, but I had created an exact clone of all versions/settings in the new hosting environment (including the same SQL Server Version and Collation).

Thanks

Chris

[Andy Heinzer]

Hi Chris,

Glad to hear that helped.  I wish I could tell you more about what is an expected value when exporting that data from that plugin.  I simply do not know what values are to be expected for that field in question. 

I would recommend trying to reach out to Tempo Support in regards to this issue to see if you can learn more about those date values and their expected values.  It seems odd to me, but honestly, I don't know very much about how Tempo is expected to work here.

Andy