Primary Key error while importing XML backup from Oracle database
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
Problem
While importing XML backup from JIRA instance which is using Oracle database, following stack trace is shown in log:
2017-11-21 13:41:37,980 active-objects-init-JiraTenantImpl{id='system'}-0 ERROR anonymous 821x223x1 1lsycvk 192.168.101.52,172.17.0.10 /secure/SetupImport.jspa [n.java.ao.sql] Exception executing SQL update <ALTER TABLE AO_5FB9D7_AOHIP_CHAT_LINK CHANGE COLUMN ID ID INTEGER AUTO_INCREMENT NOT NULL>
java.sql.SQLException: Cannot change column 'ID': used in a foreign key constraint 'fk_ao_5fb9d7_aohip_chat_user_hip_chat_link_id' of table 'jira.AO_5FB9D7_AOHIP_CHAT_USER'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)Diagnosis
Extract the XML backup and open the activeobjects.xml with a text editor to check whether the primary key for the tables and the autoincrement set is correctly.
Cause
Possibility related to JRASERVER-61007 - Getting issue details... STATUS bug.
Workaround
- Unzip the XML backup zip file and you will see entities.xml and activeobjects.xml files.
- Open the activeobjects.xml file and look for some rows that contain primaryKey="true" autoIncrement="false"
- If you find it, please replace all to primaryKey="true" autoIncrement="true" and save it.
- Zip back the file and try to import the data again by referring the links below:
- https://confluence.atlassian.com/adminjiraserver074/restoring-data-from-an-xml-backup-881684024.html
If the import failed after using the workaround above, please try to set the autoincrement for entries with sqltype=2 only.