JCMA migration error: 'We couldn't import issue <issue-key> Reason: QueryException: Caught BatchUpdateException for insert into "public"."jiraissue"'
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When using the Jira Cloud Migration Assistant app to migrate projects from server/DC to the cloud, migration plans can finish as Incomplete due to invalid characters present in the issue summaries.
Environment
- JCMA 1.6.6 and higher
- Jira Server/Data Center 7.6.0 and higher
- Jira Service Management Server/DC 3.9.0 and higher
Error
This error message below will surface when there is the \u0000 character or other within the same group present in the issue summaries.
<time-stamp> ERROR <project-key> project-import We couldn't import Issue <issue-key>. Reason: QueryException: Caught BatchUpdateException for insert into "public"."jiraissue" ("created", "creator", "description", "description_adf", "effective_subtask_parent_id", "id", "issuenum", "priority", "project", "pkey", "reporter", "resolution", "resolutiondate", "soft_archived", "issuestatus", "summary", "issuetype", "updated", "votes", "watches", "workflow_id")
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?). This caused <N> other items to fail.Solution
Detection
Identify the issues with the invalid character
This SQL query will help with that. It'll bring all the issues that contain that character in their summary.
PostgreSQL
SELECT summary
FROM jiraissue
WHERE summary ~ ('^('||$$[\09\0A\0D\x20-\x7F]|$$||$$[\09\0A\0D\xA1-\xFF]|$$||')*$');MySQL
SELECT summary
FROM jiraissue
WHERE NOT summary REGEXP "[\\x00-\\xFF]|^$";Oracle
SELECT summary
FROM jiraissue
WHERE NOT REGEXP_LIKE (summary,'[\\x00-\\xFF]|^$');Based on our tests that should not happen on sites that have Microsft SQL Server as their database.
Resolution
The fix is to edit the summary of the impacted issues making sure that the invalid characters are removed from it. For sites using MySQL and Oracle as their databases, you may see the � symbol on the UI.
New JCMA plan
Once you've fixed all offending issues with the impacted summaries, you should be good to create a new migration plan in JCMA and retry the migration containing the impacted projects.