XML Import to MS SQL fails due to a GenericEntityException - Date Format Error
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Environment
Jira Data Center and Server all versions
Symptoms
If an XML import is attempted to a JIRA instance running on MS SQL and the export that is being used is from a different database (MySQL, Oracle), the import can fail with an error denoting a format issue for the date.
The following may appear in the log files.
org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Worklog][id,39489][body,Time logged][author,<<authorname>>][startdate,0001-01-01 10:00:00.0][updated,2011-03-18 12:06:18.0][created,2011-03-18 12:06:18.0][issue,56509][timeworked,600][updateauthor,<<authorname>>] (SQL Exception while setting value: (Only dates between January 1, 1753 and December 31, 9999 are accepted.))Cause
This is caused by the date time formats used by the different databases and the XML exporter uses the format from the database which is not understood by the target database.
Workaround
- Open the XML backup in a text editor of your choice.
Edit those entries which has invalid date format which does not have the year component included. For example the below:
0012-04-12 00:50:39.0Should be changed to:
2012-04-12 00:50:39.0
Additionally please check the existing bug report below which addresses this issue : JRASERVER-25825 - Date validation needs to be tightened up - at the moment we rely on the backend database too much