Unable to Restore Data Backup due to 'Out of range value for column'
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Summary
Restoring a XML data backup fails. The following appears in the atlassian-jira.log:
Error importing data: org.xml.sax.SAXException: java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting:
[GenericEntity:CustomFieldValue][customfield,11908][issue,174768][id,2592980][updated,1668161192961][numbervalue,1.21110424954E12] (SQL Exception while executing the following:INSERT INTO customfieldvalue (ID,
ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Out of range value for column 'NUMBERVALUE' at row 1))
java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:CustomFieldValue][customfield,11908][issue,174768][id,2592980]
[updated,1668161192961][numbervalue,1.21110424954E12] (SQL Exception while executing the following:INSERT INTO customfieldvalue (ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE,
DATEVALUE, VALUETYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Out of range value for column 'NUMBERVALUE' at row 1))
Cause
The error indicates that the value stored in the column NUMBERVALUE of table customfieldvalue for customfield id 2592980 is out of range. This is due to an excessively large value entered into a number custom field.
Resolution
Option 1: Fix the field value via XML file
- Unzip the XML data backup zip file
- Open the entities.xml file
- Search for the offended NUMBERVALUE (in this case 1.21110424954E12)
- Edit entities.xml file and change the offended value to a reasonable value (e.g 1.21)
- Save the file and zip the entities.xml and activeobjects.xml files together.
- Attempt to import with the modified XML backup file.
Option 2: Increase NUMBERVALUE column space
Please note that the tables are defined based on the files located under <JIRA_INSTALL_DIR>/atlassian-jira/WEB-INF/classes/entitydefs.
The file entitymodel.xml would define the fields for the table, while fieldtype-<Jira-database-type>.xml would define the field type and precision.
Currently, the NUMBERVALUE field with floating-point is converted to DECIMAL(18,6) by default. In case you set up a new instance in the future, you may want to modify the field in the database to decimal (36,12) to be in sync with the current environment.
- Stop the running Jira instance
- Modify <installation-folder>/atlassian-jira/WEB-INF/classes/entitydefs/fieldtype-<database-type>.xml* changing the "floating-point" definition in the XML to DECIMAL(36,12)
Look for the fieldtype-<database-type>.xml file that matches your Jira database type. - Create a new fresh DB.
- Connect the new DB to the current Jira instance.
- Start Jira and start the installation over from the creation of the DB tables and attempt the import again.