Duplicated entry - unique constraint violated

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。

問題

The logs may include stack traces similar to the ones below:

Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:GlobalPermissionEntry][group_id,BED-Jira-Access][permission,USE][id,10015] (SQL Exception while executing the following:INSERT INTO globalpermissionentry (ID, PERMISSION, GROUP_ID) VALUES (?, ?, ?) (Duplicate entry '10015' for key 'PRIMARY'))
    at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:214)
    at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:179)
Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:GadgetUserPreference][id,60081][userprefvalue,assignees][userprefkey,ystattype][portletconfiguration,13951] (SQL Exception while executing the following:INSERT INTO gadgetuserpreference (ID, PORTLETCONFIGURATION, USERPREFKEY, USERPREFVALUE) VALUES (?, ?, ?, ?) (ORA-00001: unique constraint (JIRAPROD.PK_GADGETUSERPREFERENCE) violated
))
    at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:136)
    at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:101)
Caused by: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:PortletConfiguration][position,0][id,14485][portalpage,12305][color,color1][gadgetXml,rest/gadgets/1.0/g/com.atlassian.jira.gadgets:admin-gadget/gadgets/admin-gadget.xml][columnNumber,0] (SQL Exception while executing the following:INSERT INTO portletconfiguration (ID, PORTALPAGE, PORTLET_ID, COLUMN_NUMBER, positionseq, GADGET_XML, COLOR) VALUES (?, ?, ?, ?, ?, ?, ?) (ORA-00001: unique constraint (JIRAPROD.PK_PORTLETCONFIGURATION) violated
2013-08-05 10:46:05,050 http-bio-8080-exec-2838 ERROR [500ErrorPage.jsp] Exception caught in 500 page org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,10002][userKey,oarteaga][lowerUserName,oarteaga] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry '10002' for key 1))
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,10002][userKey,oarteaga][lowerUserName,oarteaga] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry '10002' for key 1))
Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Membership][id,82263][membershipType,GROUP_USER][lowerParentName,nicr][parentId,80014][childId,42310][childName,lhooipen][lowerChildName,lhooipen][directoryId,10100][parentName,NICR] (SQL Exception while executing the following:INSERT INTO cwd_membership (ID, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ORA-00001: unique constraint (JIRAUSER.PK_CWD_MEMBERSHIP) violated


診断

It is possible to see in the error message the entity that is throwing the problem, together with its corresponding table e.g.:

org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Membership]

...
SQL Exception while executing the following:INSERT INTO cwd_membership

In this example, we can see that the problematic entity is 'Membership', and its corresponding table is 'cwd_membership'. With this, we can run the following queries in the database:

SELECT * FROM sequence_value_item WHERE seq_name = 'Membership';
SELECT max(id) FROM cwd_membership;


If the value returned in the second query is bigger than the value returned in the first one, please refer to the steps under Resolution to fix this problem. Otherwise, refer to Alternative Resolution for the solution

The names of the tables used are mostly equal to the entity's name. In some occasions though, the name is not exactly the same, so you may need to look for similar names as in the above example. Please take note of the case-sensitivity of the table name in your specific database. Sometimes you may need to use SEQUENCE_VALUE_ITEM instead of sequence_value_item.

May have to use numerical sorting for the SQL query, for example

MySQL query

SELECT max(CAST(id AS SIGNED)) FROM cwd_membership;

PostgreSQL query
SELECT max(id::integer) FROM cwd_membership;

原因

It is not known yet what causes this problem, although the most likely reason is an update attempt was not successfully finished and therefore caused this mismatch in the id counts.

ソリューション

データベースの変更を行う場合は 必ず事前にバックアップを取得してください。可能な場合はテスト サーバーで変更を試すことをおすすめします。

  1. Shutdown your JIRA application;
  2. Run the following query in the database:

    UPDATE sequence_value_item SET seq_id = (SELECT max(id)+100 FROM <TableName>) WHERE seq_name = '<EntityName>';

    Don't forget to commit your changes If you're using Oracle database.

     Be sure to replace '<EntityName>' and <TableName> with the ones shown in the error message.

  3. Jira アプリケーションを再起動します。

For example, the update statement below would be used to fix the problem in the example above:

UPDATE sequence_value_item SET seq_id = (SELECT max(id)+100 FROM cwd_membership) WHERE seq_name = 'Membership'; 

代替ソリューション

If the sequence value is higher than the maximum ID of the table in question (and the failing ID number already exists), try restarting Jira.

In some cases, JIRA will generate the next ID number internally, rather than referring to the database. If this in-memory value is not in line with what's in the database, database insertions will fail.

Restarting JIRA should allow for JIRA to re-read what's in the database and have the correct IDs to generate.

説明If you run into a situation in the logs where the logs throw an error stating: "Duplicated entry - unique constraint violated" this article can be used to resolve the issue
製品Jira
プラットフォームサーバー
最終更新日: 2021 年 10 月 27 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.