Duplicate entry key uk_lower_user_name when upgrading to JIRA 6.x
症状
The following appears in the atlassian-jira.log when upgrading to JIRA 6.x
Exception thrown during upgrade: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,11388][userKey,cwat-akepplin][lowerUserName,cwat-akepplin] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry 'cwat-akepplin' for key 'uk_lower_user_name'))
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,11388][userKey,cwat-akepplin][lowerUserName,cwat-akepplin] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry 'cwat-akepplin' for key 'uk_lower_user_name'))
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:379)
at com.atlassian.jira.entity.EntityEngineImpl.createValue(EntityEngineImpl.java:28)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build6040.mapUsername(UpgradeTask_Build6040.java:72)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build6040.doUpgrade(UpgradeTask_Build6040.java:63)
at com.atlassian.jira.upgrade.UpgradeManagerImpl.doUpgradeTaskSuccess(UpgradeManagerImpl.java:673)
at com.atlassian.jira.upgrade.UpgradeManagerImpl.runUpgradeTasks(UpgradeManagerImpl.java:526)
at com.atlassian.jira.upgrade.UpgradeManagerImpl.doUpgrade(UpgradeManagerImpl.java:456)
at com.atlassian.jira.upgrade.UpgradeManagerImpl.doUpgradeIfNeeded(UpgradeManagerImpl.java:399)
at com.atlassian.jira.upgrade.UpgradeManagerImpl.doUpgradeIfNeededAndAllowed(UpgradeManagerImpl.java:333)
at com.atlassian.jira.upgrade.UpgradeLauncher.checkIfUpgradeNeeded(UpgradeLauncher.java:100)
at com.atlassian.jira.upgrade.UpgradeLauncher.start(UpgradeLauncher.java:49)診断
Run the following SQL query to see if there are any uppercase or mixed case entries in lower_user_name column of cwd_user table
select lower_user_name from cwd_user;
原因
There are uppercase or mixed case entried in the lower_user_name column of cwd_user table where it should not. Probably caused by misconfiguration during database schema creation on the collation type.
ソリューション
Backup
The following changes are irreversible. Please ensure that the JIRA database backup is taken before proceeding to the resolution steps. It is also recommended that JIRA is taken out of service before starting the database backup.
After completing the resolution steps described below, please check the JIRA instance and the GreenHopper configuration including user tests before bringing JIRA online. For e.g.
- Do all the boards show up correctly
- Are all the earlier sprints intact
- Are the Story Points, Epic, Ranking and Business Value are correct in issues by doing a random check
- Are the Sprint Reports and Burn-down charts intact (both Classic boards and Rapid boards)
手順:
- Create a database backup
- Convert the lower_user_case column to lowercase username
update cwd_user set lower_user_name=lower(user_name);
update cwd_membership set lower_child_name=lower(child_name);