Linux machine backup not compatible with Windows?

Gleb Kartashov

I have a backup from Jira server Linux machine also running Oracle SQL I believe. My machine is running on Windows Server + MS SQL Server. When trying to restore backups I'm getting incompatible date formats error (The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.) 

Is there any software to modify the backups to be acceptable by Windows Jira instance?

Andy Heinzer

Hi Gleb,

Sorry to hear about this problem.  This problem is something I have seen before in Jira server. 

What I think has happened here is that Jira was likely defaulting to use a two digit year (yy) for date fields.  Hence users would enter a date like 01/01/18 meaning the year 2018, but Oracle's SQL and Jira's date input settings are not being explicitly clear that this is meant to be 2018.  And as such Oracle's SQL database was just storing this as 0018 for a year.  This would not be a problem if you were importing into the same database type, but since you are switching to a MS SQL database, the data types for this database are different here and in turn have different limitations on what makes a valid date.  See datetime (Transact-SQL), for details about this field type.  From that page:

Date range   January 1, 1753, through December 31, 9999

So we can see clearly here that the year 0018 is not a valid date to represent here.  I think the best steps to take here would be to edit that data and then attempt the import once more.

What you can do here is take a copy of the backup.zip file, then extract the files in that zip.  In there you will find a file called entities.xml, this file contains the Jira issue data in an XML based format.   It is possible to then load this file into a text editor, makes changes to these date fields to use corrected 4 digit years, save that file, and add it back to the zip file.  With this amended zip file you can then import this data successfully.

If you're on a windows system, I'd recommend a text editor like notepad++ to do a mass find and replace.  If you're using a linux/unix system then I would probably look to use grep and/or sed commands to replace these invalid 00 year dates. I suspect you could search for the string such as

="0018-

and then replace this with something like

="2018-

for all instances, but this presumes that all the dates in Jira start since 2000 and later (probably a safe assumption in most cases, but good to test this with a backup of your data before committing to production environments).  At the very least Jira is going to store a date field like this for created, updated, and resolutiondate in that entities.xml file for every issue in Jira.  But if you have any other custom date fields those too could be storing the date this way for those customfields too.  Once all these invalid 00 years are repalced in that file, save the entities.xml file, add it to single zip file with the corresponding active-objects.xml, and then you can try to import this new zip file with the Jira setup wizard once more.  If you managed to get all the invalid dates then the import should go smoothly, but if we missed any fields, then the error logs will likely give us another error and tell us what value is again invalid.

 

As for how to prevent this problem in the future, well I find it is best when setting up Jira to force Jira to always use 4 digit years.  This is something as a Jira admin you can do, and I go into some details about how to do this over in this related thread https://community.atlassian.com/t5/Jira-questions/Custom-Date-Picker-field-throws-conversion-out-of-range-value/qaq-p/951250.  These steps are not as useful to correct the problem you have right now, because changing these setting only affect how Jira stores new data.  In your case we still need to find a way to adjust the data you have here and have it accurately reflect the year.

 

Try these steps and let me know if you have any questions or run into any problems here.

Andy