Mentionable & assignable users - database collation issues
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
JIRA 8.19.1, JIRA 8.20.0, SERVER DC, MS SQL, MYSQL |
|---|
A new way of getting top mentionable and top assignable users was introduced in Jira 8.19.1 which is based on DB queries. Some DB collation misconfiguration which could be "hidden" before, may be now exposed when using the mentionable and assignable functionality.
Database collation healthcheck
Note that the Database collation health-check may not detect problems as it only checks the DB collation (not table or column collation settings). The DB collation is just a default to be used when creating new columns. Older tables with columns with different collation may still exists and cause issues.
Solution
Links
Which databases may have this problem?
Microsoft SQL:
Cannot resolve the collation conflict betweenException example
1Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Slovenian_CI_AS" in the equal to operation.MySQL:
Unknown error 1267Exception example
1 2Caused by: java.sql.SQLException: Unknown error 1267 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
Which query may have this problem?
Currently we are not sure how an instance can end up with tables and columns with different collation. Probably at some point in time the collation of database was changed. As a result of upgrade new tables were created with new collation that clashes with previous one, when we try to join tables.
In case of mentions the collation issue manifests in the following query:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select distinct CWD_USER.id,
...
from dbo.cwd_user CWD_USER
inner join dbo.app_user APPLICATION_USER
on APPLICATION_USER.lower_user_name = CWD_USER.lower_user_name
inner join dbo.cwd_directory DIRECTORY
on CWD_USER.directory_id = DIRECTORY.id and DIRECTORY.active = 1
inner join dbo.projectroleactor PROJECT_ROLE_ACTOR
on PROJECT_ROLE_ACTOR.roletypeparameter = APPLICATION_USER.user_key and
PROJECT_ROLE_ACTOR.pid = --pid-- and PROJECT_ROLE_ACTOR.roletype = 'role-type'
inner join dbo.schemepermissions SCHEME_PERMISSIONS
on cast(PROJECT_ROLE_ACTOR.projectroleid as varchar) = SCHEME_PERMISSIONS.perm_parameter and
SCHEME_PERMISSIONS.scheme = 0 and SCHEME_PERMISSIONS.permission_key = 'ASSIGNABLE_USER' and
SCHEME_PERMISSIONS.perm_type = 'projectrole'
where CWD_USER.active = 1
order by CWD_USER.lower_display_name ascHow to fix the collation problem?
You should change the illegal collation to the DB collation. Also in order to change the collation you may need to drop index first and recreate the index after changing collation.
MS SQL
To resolve this error, please follow the resolution link provided here:
Here is our guide to setting up a SQL Server connection for Jira:
It's easy to overlook the collation because you have to expand the little caveat about supported collations, but that will give you the information you need to fix this error.
In order to see all collation settings:
1
2
3
4
5
SELECT DISTINCT C.collation_name
FROM sys.tables AS T
INNER JOIN sys.columns C
ON T.object_id = C.object_id
WHERE collation_name IS NOT NULL;Example queries changing collation on some columns used in mentionable joins:
1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE dbo.projectroleactor
ALTER COLUMN ROLETYPEPARAMETER NVARCHAR(255) COLLATE Latin1_General_CI_AI;
ALTER TABLE dbo.app_user
ALTER COLUMN lower_user_name NVARCHAR(255) COLLATE Latin1_General_CI_AI;
ALTER TABLE dbo.app_user
ALTER COLUMN user_key NVARCHAR(255) COLLATE Latin1_General_CI_AI;
ALTER TABLE dbo.cwd_user
ALTER COLUMN lower_user_name NVARCHAR(255) COLLATE Latin1_General_CI_AI;MySQL
To resolve this error, please follow the resolution link provided here: How to Fix the Collation and Character Set of a MySQL Database manually
Was this helpful?