When restoring JIRA7 : ORA-00001: unique constraint (JIRAATGL7.PK_SEQUENCE_VALUE_ITEM) violated

Question

I got this exception when I restore my old Jira database on JIRA6.4.11 to a fresh database on JIRA7.4.2 (with a new version of Oracle).

2018-11-14 12:42:49,624 JiraImportTaskExecutionThread-1 ERROR anonymous 627x8x1 1rwz7dk 10.52.193.143,10.134.33.69 /secure/SetupImport.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Error importing data: org.xml.sax.SAXException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:SequenceValueItem][seqName,EntityPropertyIndexDocument][seqId,10100] (SQL Exception while executing the following:INSERT INTO SEQUENCE_VALUE_ITEM (SEQ_NAME, SEQ_ID) VALUES (?, ?) (ORA-00001: unique constraint (JIRAATGL7.PK_SEQUENCE_VALUE_ITEM) violated
    ))
    java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:SequenceValueItem][seqName,EntityPropertyIndexDocument][seqId,10100] (SQL Exception while executing the following:INSERT INTO SEQUENCE_VALUE_ITEM (SEQ_NAME, SEQ_ID) VALUES (?, ?) (ORA-00001: unique constraint (JIRAATGL7.PK_SEQUENCE_VALUE_ITEM) violated
    ))
org.xml.sax.SAXException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:SequenceValueItem][seqName,EntityPropertyIndexDocument][seqId,10100] (SQL Exception while executing the following:INSERT INTO SEQUENCE_VALUE_ITEM (SEQ_NAME, SEQ_ID) VALUES (?, ?) (ORA-00001: unique constraint (JIRAATGL7.PK_SEQUENCE_VALUE_ITEM) violated
))
java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:SequenceValueItem][seqName,EntityPropertyIndexDocument][seqId,10100] (SQL Exception while executing the following:INSERT INTO SEQUENCE_VALUE_ITEM (SEQ_NAME, SEQ_ID) VALUES (?, ?) (ORA-00001: unique constraint (JIRAATGL7.PK_SEQUENCE_VALUE_ITEM) violated
))

I cheked the max of the concerning table (EntityPropertyIndexDocument), but there is no inconsistancy as it is advised here .

Max is 10000 in the table and the value is 10100 in SequenceValueItem.

Anyway it is not the same exception as the mentionned link before.

I have no idea to solve this issue.

Please Help !

Daniel Eads · Answer

Hi Emmanuel,

old Jira database on JIRA6.4.11 to a fresh database on JIRA7.4.2

Migrating between versions like this won't work unfortunately. Especially with the jump from Jira 6.4 to Jira 7.0, the installer had to make many changes to the database schemas. We call these "migration tasks" and they need to run each time Jira versions change. The installer takes care of this for you when you do an upgrade.

Our migration hub has more information about the Jira 7.0 upgrade, but here's what I'd recommend for you at the most basic level as it sounds like you want to do both an Oracle version change as well as a Jira upgrade:

* Reconnect your old database to your old application version. We want a "known-working" configuration to start from (Jira 6.4 and presumably Oracle 11)
* Clone your database to your new Oracle DB (assuming Oracle 12) - do note that you should probably use version 12.2.0.1 of the Oracle ojdbc driver. Prior releases of the driver had some bugs that affected Jira. Repoint your Jira server to use the upgraded Oracle server. The driver goes in the lib directory of Jira's install folder. For safety, I usually rename the one that is there to "ojdbc7.jar.old" before copying in the correct version from Oracle's website - this makes sure Jira doesn't try to load a bad version.
* Start Jira and make sure everything is working correctly. At this point you should still be on Jira 6.4 with your upgraded Oracle database.
* Upgrade to Jira 7.0.11 - you can grab the installer from here by clicking the "View all versions" link and selecting the 7.0 releases. Start Jira after the upgrade is complete and make sure everything connects and works. Note that you'll need to copy in the correct Oracle driver every time you do an upgrade - the installer will wipe out the correct version you had already added.
* Shut down Jira and use the installer for the final version you want to upgrade to. Again remember that you'll need to copy in the correct Oracle driver into the lib folder.

Obviously each upgrade is a little more involved that a "single step" in a checklist, but this gives you the general idea of the safe path to upgrade from 6.x to 7.x as well as changing your Oracle version.

If you run into any additional trouble this route, we're happy to help!

Cheers,
Daniel

Earl McCutcheon · Answer

Hello Emmanuel,

Check out this KB instead it looks like it lines up better with the error, and since the SequenceValueItem is already a higher value the other article does not apply:

* Error importing backup due to unique constraint or cannot INSERT INTO NULL error.

Regards,
Earl

Henrique Bittencourt · Answer

Hi Emmanuel!

Seems like you already opened a support ticket with our support team!

* https://getsupport.atlassian.com/browse/GHS-135245

As Andrew suggested, the best approach for now (before moving further on the troubleshooting) is restoring the old database backup into a 7.0.11 version before moving to a higher version since jumping from a 6.4 directly to a 7.1+ isn't a supported upgrade path.

Please attempt to perform this upgrade against a new 7.0.11 instance of JIRA before proceeding with the upgrade to 7.4.x.

Hope this can help!

Best Regards.