MS SQL Server collation errors when migrating Advanced Roadmaps Plans with JCMA
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
When trying to run the Jira Cloud Migration Assistant (JCMA) to migrate Advanced Roadmaps Plans from a Server instance to Jira Cloud, the AR Plans may fail to migrate due to this error (found in the <Jira-local-home>/log/atlassian-jira.log* files):
2022-10-27 19:10:48,589-0700 pool-91-thread-16 ERROR john.doe 590x690654x5 dghki7 xx.x.xx.xxx /rest/plugins/1.0/available/featured [c.a.j.m.tracking.polling.Poller] 1q2w3e4r-0000-0000-5t6y-1q2w3e4r5t6y-jiraCrossProjectUpload#1q2w3e$4r5t6y7u: task worker 1qas2ws3ed4rf5tg6yh failed to process Task(migrationId=1qa2ws3ed4rf5tg, migrationTask=com.atlassian.jira.migration.orchestratorclient.tasks.CrossProjectTransferMigrationTask@71fd300d[heartbeatUrl=https://api-private.atlassian.com/migrations/v1/migration/1q2w3e4r5t-0285-3572/transfer/1q2w3e3e4r5t6y7u/heartbeat/xxxxx,migrationTaskType=jiraCrossProjectUpload,transferId=1q2w3e4r5t,migrationTaskType=jiraCrossProjectUpload,taskId=jiraCrossProjectUpload#1q2w3e4r5t6y7u8i9o0p], cloudSite=AO_6FF49D_CLOUD_SITE {CLOUD_ID = 1qa2ws3ed4rf5tg6yh7uj})
com.querydsl.core.QueryException: Caught SQLServerException for select "AO_A415DF_AOPERMISSION"."HOLDER_ID", "AO_A415DF_AOPERMISSION"."TARGET_ID"
from "jiraschema"."AO_A415DF_AOPERMISSION" "AO_A415DF_AOPERMISSION"
inner join "jiraschema"."cwd_group" "CWD_GROUP" on "CWD_GROUP"."group_name" = "AO_A415DF_AOPERMISSION"."HOLDER_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)
at com.atlassian.jira.migration.export.db.advancedroadmaps.AdvancedRoadmapsPluginPermissionReader.getPluginPermissions$lambda-0(AdvancedRoadmapsPluginPermissionReader.kt:26)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP437_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" 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)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
This code path (in the stack trace above) will confirm if you're being impacted by this problem.
at com.atlassian.jira.migration.export.db.advancedroadmaps.AdvancedRoadmapsPluginPermissionReader.getPluginPermissions$lambda-0(AdvancedRoadmapsPluginPermissionReader.kt:26)
Environment
- JCMA 1.7.4 and higher
- Jira Server/Data Center 7.6.0 and higher
- Advanced Roadmaps
Cause
- JCMA 1.7.4 and upwards included new functionalities and checks related to Advanced Roadmaps migration with JCMA.
Additional reference
Solution
You can fix this by going with the workaround suggestions listed in the bug linked above or with the fix below. This fix will change the collation of the group_name column on the table cwd_group.
Read this before going further
The changes below will require a DBA or a user that knows how to operate ALTER TABLE SQL queries.
It will involve changes at the database level, so please pay extra attention to the alterations done.
Make sure to test it on a staging environment before applying it to your production instance.
ALTER TABLE cwd_group ALTER COLUMN group_name NVARCHAR(510) COLLATE SQL_Latin1_General_CP437_CI_AI;
Once the fix is done, create a new migration plan in JCMA.