Upgrade to JIRA 6x fails due to duplicate entry into app_user table
症状
Upgrade to JIRA 6x fails due to duplicate entry into app_user table. The following appears in the atlassian-jira.log:
Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,10300][userKey,User01][lowerUserName,user01] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry 'User01' for key 'uk_user_key'))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:136)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:101)
at org.ofbiz.core.entity.GenericHelperDAO.create(GenericHelperDAO.java:64)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:487)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:467)
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:374)
... 29 more
原因
In JIRA 6x was introduced the ability of renaming users, the table app_user was created to store information related to user name changes. In JIRA versions previous to 5.2.8 there is a bug when deleting an User Directory, some information from the deleted User Directory are not deleted from the database, which causes this issue when upgrading to JIRA 6x.
ソリューション
データベースの変更を行う場合は 必ず事前にバックアップを取得してください。可能な場合はテスト サーバーで変更を試すことをおすすめします。
Delete the orphan entries into cwd_user_attributes and cwd_user tables that belong to the old directory that was deleted and were not remove from the database:
Jira をシャットダウンします。
Execute the following queries:
delete from cwd_user_attributes where directory_id not in (select id from cwd_directory); delete from cwd_user where directory_id not in (select id from cwd_directory);- Jira を再起動します。
Sometimes (especially when using Microsoft SQL Server or MySQL) a trailing space in a user name will cause the same type of error during upgrade.
Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,10300][userKey,User01][lowerUserName,user01] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry 'User01 ' for key 'uk_user_key'))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:136)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:101)
at org.ofbiz.core.entity.GenericHelperDAO.create(GenericHelperDAO.java:64)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:487)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:467)
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:374)
... 29 moreIn this case you'll need to remove the trailing white space from that user's name or check if there's another user name with the same name (but without a trailing space). You can achieve this a few different ways.
- Unzip a backup XML and replace all instances of 'User01 ' with 'User01' in the entities.xml file