Backup restore fails with datetime2 conversion out-of-range error in Jira

Summary

When restoring a backup into Jira, the process fails with the message "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value".

Environment

All versions of Jira Core 7.x and 8.x.

Jira's database is either MS SQL Server database or Azure SQL. (other databases may present a slightly different error)

Diagnosis

Both the error on the screen and in the atlassian-jira.log should mention the datetime2 conversion message and the date it was trying to insert:

In the (redacted) example above, the date causing the error is 0018-09-04 11:24:00. That is, year 18 of CE.

Cause

Most database drivers won't accept dates before Unix Epoch Time (Jan 1 1970). MS SQL Server and Azure SQL will not accept dates before Jan 1 1753.

The backup was taken from an instance with invalid dates on comments or transition dates.

It's yet unknown how comments, editions or transitions may end up with out-of-range dates. Direct database updates are known to accept the invalid dates while the application database drivers won't, for instance.

Solution

1) Input date formats

First, confirm both the instance from where the backup was taken and the destination instance have the same input date formats.

Change the input formats of the destination instance to match those of the source's.

Retry the backup restore if the date formats were different and needed change.

2) Edit the backup file

If the backup restore fails even with the same date input formats, we have to edit the backup to correct the dates.

The example approach below corrects any date prior to the year 1000 CE to after 2000 CE:

1. Unzip the backup file. Two files will result: entities.xml and activeobjetcs.xml

   1 unzip backup.zip

2. Confirm if there are any out-of-range dates (it should output something grater than 0 if there are):

   1 2 cat entities.xml | egrep '="0[0-9]{3}-[0-9]{2}-[0-9]{2}' -c cat entities.xml | egrep '="1[0-7][0-9]{2}-[0-9]{2}-[0-9]{2}' -c

3. Replace the faulty lines:

   1 2 sed -r 's/="0([0-9]{3}-[0-9]{2}-[0-9]{2})/="2\1/g' entities.xml > entities2.xml; mv entities2.xml entities.xml;

   The command above will replace all dates from 0 to 999CE to 2000-something. If there are invalid dates after year 1000CE, you may execute the below command for each invalid date, choosing the new correct year. Replace yyyy by the invalid year in the output from step #2 and YYYY be the new desired year:

   1 2 sed -r 's/="yyyy-([0-9]{2}-[0-9]{2})/="YYYY-\1/g' entities.xml > entities2.xml; mv entities2.xml entities.xml;

4. Zip again the files into the backup and try the import again

   1 zip backup.zip entities.xml activeobjects.xml

5. Retry the backup restore with the new backup file.