Fix "Cannot insert duplicate key in object" error when creating data in Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
Summary
This article discusses situations where you receive the error "Cannot insert duplicate key in object" in Jira Data Center and provides detailed steps on how to resolve this.
Environment
Jira Data Center on any version
Diagnosis
When adding a new version, comment, issue, custom field, or any other Jira application entity, an error occurs like:
2009-08-30 00:28:44,749 pool-97-thread-2 ERROR [jira.action.admin.OfbizImportHandler] Exception importing entity: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Version][id,11201][project,10121][sequence,1][description,5.3.1][name,5.3.1] (SQL Exception while executing the following:INSERT INTO jiraschema1.projectversion (ID, PROJECT, vname, DESCRIPTION, SEQUENCE, RELEASED, ARCHIVED, URL, RELEASEDATE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) (Violation of PRIMARY KEY constraint 'PK_projectversion'. Cannot insert duplicate key in object 'jiraschema1.projectversion'.))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:123)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:88)
at org.ofbiz.core.entity.GenericHelperDAO.create(GenericHelperDAO.java:63)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:480)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:460)
at org.ofbiz.core.entity.GenericValue.create(GenericValue.java:77)
at com.atlassian.jira.action.admin.OfbizImportHandler$1.run(OfbizImportHandler.java:235)
at com.atlassian.jira.util.concurrent.BoundedExecutor$1.run(BoundedExecutor.java:39)
at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1061)
Cause
Entries were inserted manually into the Jira application database instead of via the application user interface.
Jira's database is a cluster with a load balancer that doesn't honor session affinity or doesn't replicate in real-time, where queries from the same user session land on different database server nodes with different content.
Solution
Database correction
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Find the specific table that is having issues with data insertion, and find the highest ID in that table:
In the above example, the error was "SQL Exception while executing the following:INSERT INTO jiraschema1.projectversion..." which signifies that this is an issue with a row being inserted into the projectversion table.
SELECT MAX(id) FROM projectversion;
Find the appropriate entry in the sequence_value_item table and take note of the ID.
In the above example, the specific row is where seq_name = Version. For tables other than projectversion the seq_name will be different. You can find the correct corresponding value by checking the database table name defined in
WEB-INF/classes/entitydefs/entitymodel.xml.SELECT * FROM sequence_value_item WHERE seq_name = 'Version';
Check if the ID value from the sequence_value_item table is higher than the max(id) value in the projectversion table.
If it is not, update the sequence_value_item table and set the relevant row's 'seq_id' value to a value greater than the actual maximum ID in the table you're getting errors for. In this case, the relevant row is 'Version'.
UPDATE sequence_value_item SET seq_id = (SELECT max(id)+100 FROM projectversion) WHERE seq_name = 'Version';
Restart your Jira application after this update for it to take effect.
Loadbalancer configuration
If the Jira database is behind a load balancer for redundancy/high availability, make sure that request routing and session affinity are well configured and that the user's requests land on the same node.
Direct data manipulation is highly discouraged, in part because it is easy to cause errors. A better route is to use the Jira Remote API to insert data.
Was this helpful?