Database migration to SQL Server fails because of duplicate entries
Problem
After migrating a JIRA database from MySQL/PostgreSQL/Cloud to Microsoft SQL Server, the import of an XML backup to JIRA fails with the following error:
Error importing data:org.xml.sax.SAXException:
com.atlassian.jira.exception.DataAccessException:
org.ofbiz.core.entity.GenericEntityException:
while inserting: [GenericEntity:Group][groupName,Résumé][createdDate,2016-11-09 10:39:35.0][lowerGroupName,résumé][active,1][directoryId,1][id,25011]
[updatedDate,2016-11-09 10:39:35.0][type,GROUP][local,0]
(SQL Exception while executing the following:
INSERT INTO dbo.cwd_group (ID, group_name, lower_group_name, active, local, created_date, updated_date, description, lower_description, group_type, directory_id)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
(Cannot insert duplicate key row in object 'dbo.cwd_group' with unique index 'uk_group_name_dir_id'. The duplicate key value is (résumé, 1).))There might also be other variations of this error if the import succeeds, but the Integration Checker fails anyway:
Referer URL: http://jiratest/secure/admin/IntegrityChecker!default.jspa
com.atlassian.jira.appconsistency.integrity.exception.IntegrityException: Error occurred while performing check.
com.atlassian.jira.appconsistency.integrity.exception.IntegrityException: Error occurred while performing check.
at com.atlassian.jira.appconsistency.integrity.check.WorkflowCurrentStepCheck.doCheck(WorkflowCurrentStepCheck.java:167)
at com.atlassian.jira.appconsistency.integrity.check.WorkflowCurrentStepCheck.preview(WorkflowCurrentStepCheck.java:54)
at com.atlassian.jira.appconsistency.integrity.IntegrityChecker.preview(IntegrityChecker.java:63)
at com.atlassian.jira.appconsistency.integrity.IntegrityChecker.previewWithIds(IntegrityChecker.java:35)
at com.atlassian.jira.web.action.admin.IntegrityChecker.doExecute(IntegrityChecker.java:71)
...
Caused by: com.atlassian.cache.CacheException: java.lang.IllegalStateException: There are more than one workflows associated with 'Purchases' in the database!
at com.atlassian.cache.memory.DelegatingCache$DelegatingLoadingCache.get(DelegatingCache.java:270)
at com.atlassian.jira.workflow.CachingWorkflowDescriptorStore.getWorkflow(CachingWorkflowDescriptorStore.java:68)
at com.atlassian.jira.workflow.JiraWorkflowFactory.getWorkflow(JiraWorkflowFactory.java:37)
at com.opensymphony.workflow.config.DefaultConfiguration.getWorkflow(DefaultConfiguration.java:89)
at com.atlassian.jira.workflow.OSWorkflowManager.getWorkflow(OSWorkflowManager.java:202)
at com.atlassian.jira.workflow.OSWorkflowManager.getWorkflow(OSWorkflowManager.java:548)
at com.atlassian.jira.appconsistency.integrity.check.WorkflowCurrentStepCheck.doCheck(WorkflowCurrentStepCheck.java:111)
... 188 more
Caused by: java.lang.IllegalStateException: There are more than one workflows associated with 'Purchases' in the database!
at com.atlassian.jira.workflow.OfBizWorkflowDescriptorStore.getWorkflowDescriptorGV(OfBizWorkflowDescriptorStore.java:155)
at com.atlassian.jira.workflow.OfBizWorkflowDescriptorStore.getWorkflow(OfBizWorkflowDescriptorStore.java:46)
at com.atlassian.jira.workflow.CachingWorkflowDescriptorStore$WorkflowCacheLoader.load(CachingWorkflowDescriptorStore.java:139)
at com.atlassian.jira.workflow.CachingWorkflowDescriptorStore$WorkflowCacheLoader.load(CachingWorkflowDescriptorStore.java:132)
...Cause
This problem occurs, because SQL Server uses case-insensitive and accent-insensitive collations, like SQL_Latin1_General_CP437_CI_AI, or Latin1_General_CI_AI (for details, see Connecting to SQL Server).
To illustrate this with an example, the values shown below will be treated as identical by SQL Server, although they're unique for other supported databases:
| Case-insensitive | Resume | resume |
|---|---|---|
| Accent-insensitive | resume | résumé |
During the import of your XML backup, both values are being inserted into a column that has unique constraints checks enabled, which results in a violation error.
Resolution
There's no automated solution for this problem. You will need to check the database you're migrating from, and update all the values that are considered identical by changing them to something unique (e.g. changing resume to resumed).
Once all the values are fixed, you can make another XML backup and import it to JIRA.