MS SQL Server collation errors in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
The generation of the Xray test run report in Jira may fail due to this error, which can be found in the <Jira-home>/log/atlassian-jira.log* files:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2024-10-24 16:44:55,036+0300 http-nio-8080-exec-11 ERROR user1 1004x1051838x2 9dj5oh 10.40.13.153 /rest/raven/1.0/report/testrunsreport/testruns/ [c.x.raven.repository.i] Error executing Test Evolution query
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve collation conflict between "SQL_Latin1_General_CP437_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in CASE operator occurring in SELECT statement column 5.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
....
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Unknown Source)Environment
Jira Software on Data Center.
Cause
This error arises when two or more tables in the Jira database have conflicting collations. It is a precisely identified database configuration issue that requires correction.
Daignosis
Execute the following SQL query to verify and confirm if there are any tables in the database with incorrect collation:
1
2
3
4
5
6
7
8
9
10
SELECT object_name(object_id) as TableName, name as ColumnName, collation_name
FROM sys.columns
WHERE collation_name != 'SQL_Latin1_General_CP437_CI_AI' AND collation_name != 'Latin1_General_CI_AI'
AND object_name(object_id) NOT LIKE 'sys%'
AND object_name(object_id) NOT LIKE 'queue%'
AND object_name(object_id) NOT LIKE 'file%'
AND object_name(object_id) NOT LIKE 'spt%'
AND object_name(object_id) NOT LIKE 'MSrep%'
AND object_name(object_id) NOT LIKE 'sqlagent%'
AND object_name(object_id) NOT LIKE 'plan_persist%'Solution
You should adjust the SQL Server instance-level collation to align with the Jira database collation, either to SQL_Latin1_General_CP437_CI_AI or Latin1_General_CI_AI.
Please refer to the instructions provided in How to fix the collation of Microsoft SQL Server database for Jira manually to implement a permanent solution.
Was this helpful?