Cannot insert duplicate key in object when adding content to a JIRA application

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

When adding a new version, comment, issue, custom field, or 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 application user interface.
  • Jira's database is a cluster with a loadbalancer 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 node with different content.


Resolution

Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.

  1. Find the specific table that is having issues with data insertion and find the highest id in that table: 
    1. 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;
  2. Find the appropriate entry in the sequence_value_item table and take note of the id
    1. 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';
  3. Verify that the id value from the sequence_value_item table is higher than or closer to the max(id) value in the projectversion table
  4. If it is not; Update the sequence_value_item table and set the relevant row's(in this case 'Version') 'seq_id' value to a value greater than the actual maximum ID in the table you're getting errors for.
  5. Restart your JIRA application after this update for it to take effect.


  • For the second cause , if Jira database is behind a loadbalancer for redundancy/high availability make sure that requests routing and session affinity is well configured and the user's requests land on the same node. 

(info) Direct data manipulation is highly discouraged, in part because it is easy to cause errors. A better route is to use JIRA application Remote API to insert data.

Last modified on Mar 6, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.