SQLServerException: The ntext data type cannot be selected as DISTINCT because it is not comparable
The information on this page relates to Database Manipulation in JIRA. Consequently, Atlassian Support cannot guarantee to provide any support for the steps described on this page as database manipulation is not covered under Atlassian Support Offerings. Please be aware that this material is provided for your information only and that you use it at your own risk.
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
During Jira startup, we can see the following error message in Jira log files.
2022-02-22 18:03:57,449-0500 localhost-startStop-1 ERROR anonymous [c.a.s.core.lifecycle.DefaultLifecycleManager]
LifecycleAware.onStart() failed for component with class 'com.atlassian.servicedesk.plugins.base.internal.bootstrap.lifecycle.InternalBasePluginLauncher'
from plugin 'com.atlassian.servicedesk.internal-base-plugin'
com.querydsl.core.QueryException: Caught SQLServerException for select distinct "AO_319474_MESSAGE"."CLAIMANT", "AO_319474_MESSAGE"."CLAIMANT_TIME",
"AO_319474_MESSAGE"."CLAIM_COUNT", "AO_319474_MESSAGE"."CONTENT_TYPE", "AO_319474_MESSAGE"."CREATED_TIME", "AO_319474_MESSAGE"."EXPIRY_TIME",
"AO_319474_MESSAGE"."ID", "AO_319474_MESSAGE"."MSG_DATA", "AO_319474_MESSAGE"."MSG_ID", "AO_319474_MESSAGE"."MSG_LENGTH",
"AO_319474_MESSAGE"."PRIORITY", "AO_319474_MESSAGE"."QUEUE_ID", "AO_319474_MESSAGE"."VERSION" from "dbo"."AO_319474_MESSAGE" "AO_319474_MESSAGE"
where "AO_319474_MESSAGE"."QUEUE_ID" = ? and ("AO_319474_MESSAGE"."CLAIMANT" is null or "AO_319474_MESSAGE"."CLAIMANT" is not null and
("AO_319474_MESSAGE"."CLAIMANT_TIME" is null or "AO_319474_MESSAGE"."CLAIMANT_TIME" < ?))
order by "AO_319474_MESSAGE"."PRIORITY" desc, "AO_319474_MESSAGE"."CREATED_TIME" asc, "AO_319474_MESSAGE"."ID" asc offset ? rows fetch next ? rows only
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The ntext data type cannot be selected as DISTINCT because it is not comparable.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)Diagnosis
From the stack trace, we can observe that the affected table, on this sample, is AO_319474_MESSAGE.
Run the SQL queries below to verify if there are any column-related issues for the same database table:
select s.name, t.name, i.name, c.name from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
ic.column_id = c.column_id
where t.name = 'AO_319474_MESSAGE';
exec sp_columns AO_319474_MESSAGE;Cause
The issue is related to a known bug:
- According to Microsoft documentation: ntext, text, and image (Transact-SQL), the NTEXT was deprecated since SqlServer2005 and while still present in SqlServer2016, it will be removed later:
IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
- AO change: JIRA 7.4 makes AO write long String columns in SQL Server as type NVARCHAR instead of NTEXT. However, if the database schema is created with JIRA 7.3 or earlier, and then JIRA is upgraded, the problem can be reproduced.
Once the database tables are created, despite AO being upgraded it will not change column types. So if the table was created in early JIRA versions with NTEXT columns they will stay NTEXT columns forever. - Related to: JRASERVER-66618 - Getting issue details... STATUS
Solution
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
It is important not to perform these changes directly in a production database. Instead, we recommend setting up a staging server using an XML backup from the source instance and testing the changes there before apply them in a production environment.
From the SQL query results, fix the columns that are still using NTEXT, as on the sample above. We'll need to update the columns to use "NVARCHAR" with the following steps:
- Stop all your Jira nodes
- Database backup
- Run the following DB update to fix the affected column:
ALTER TABLE dbo.AO_319474_MESSAGE ALTER COLUMN MSG_DATA NVARCHAR(MAX) NULL;
- Start your nodes again.