Migration of service projects fails with database collation errors.
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Learn what to do when your service projects are failing to migrate with the Jira Cloud Migration Assistant (JCMA) due to database collation errors.
Overview
Trying to migrate a service project using the JCMA fails, and the below error can be seen in the atlassian-jira.log file.
ERROR SourceSense /rest/migration/latest/check/<check-id> [migration.export.framework.DefaultExportFailureHandler] Exporting referenced entities by Issue <issue-key> failed
com.querydsl.core.QueryException: Caught SQLServerException for select "AO_56464C_APPROVAL"."ID", "AO_56464C_APPROVAL"."ISSUE_ID", "AO_56464C_APPROVAL"."APPROVE_CONDITION_TYPE", "AO_56464C_APPROVAL"."APPROVE_CONDITION_VALUE", "AO_56464C_APPROVAL"."COMPLETED_DATE", "AO_56464C_APPROVAL"."CREATED_DATE", "AO_56464C_APPROVAL"."DECISION", "AO_56464C_APPROVAL"."NAME", "AO_56464C_APPROVAL"."STATUS_ID", "AO_56464C_APPROVAL"."SYSTEM_DECIDED" from "dbo"."AO_56464C_APPROVAL" "AO_56464C_APPROVAL" inner join "dbo"."issuestatus" "ISSUESTATUS" on "ISSUESTATUS"."ID" = "AO_56464C_APPROVAL"."STATUS_ID" where "AO_56464C_APPROVAL"."ISSUE_ID" = ?
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.Configuration.translate(Configuration.java:459)
at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP437_CI_AI" and "Latin1_General_CI_AI" in the equal to operation.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621) The above error indicates that the wrong collation is set on the column or table, which is causing the project export to fail.
Solution
The changes below require a Database Administrator supervision, or a user who knows how to operate ALTER TABLE SQL queries.
Make sure to test it on a staging instance before applying it to your production instance.
Make sure to back up your database before running the queries.
Execute the SQL queries below to change the collation of column ID in the table issuestatus to SQL_Latin1_General_CP437_CI_AI:
ALTER TABLE ISSUESTATUS DROP CONSTRAINT PK_issuestatus;ALTER TABLE ISSUESTATUS ALTER COLUMN ID NVARCHAR(60) COLLATE SQL_Latin1_General_CP437_CI_AI;ALTER TABLE ISSUESTATUS ALTER COLUMN ID NVARCHAR(60) NOT NULL;ALTER TABLE ISSUESTATUS ADD CONSTRAINT PK_issuestatus PRIMARY KEY (ID); Once the SQL queries are executed, try to migrate the service projects again.