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.

Links

JIRA stats logs - top mentionable & assignable users stats

Mentionable & assignable users - tips & tricks

Database Collation health check fails with PostgreSQL in Jira server

JRASERVER-65634 - Getting issue details... STATUS

Which databases may have this problem?

• Microsoft SQL: Cannot resolve the collation conflict between

  Exception example

  Caused 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 1267

  Exception example

  Caused 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:

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 asc

How 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:

• How to fix the collation of Microsoft SQL Server database for Jira manually
• Set or Change the Server Collation.

Here is our guide to setting up a SQL Server connection for Jira:

• Connecting JIRA applications to SQL Server 2017

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:

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:

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