Database collation health check fails in Jira server with Oracle database

Still need help?

The Atlassian Community is here for you.

Ask the community

This article only applies to Atlassian's server products. Learn more about the differences between cloud and server.

Problem

This document relates specifically to Oracle Databases and the JIRA Database Collation Health Check in specific circumstances.
Please review the General guide: JIRA Database Collation Health Check page before this more specific troubleshooting article.

When running the Health Check in JIRA on an oracle, it shows that the session collation used in unsupported. The error thrown is similar to the following, with a language locale being reported:

The session collation of: 'FRENCH' is unsupported by JIRA.

Diagnosis

Environment

  • Oracle Database

Diagnostic Steps

  • The following query to check the database collation returns BINARY
SELECT * FROM nls_database_parameters WHERE parameter IN ('NLS_SORT'); 
  • The following query to check session collation (using the same user JIRA uses to connect to the database) returns BINARY
SELECT * FROM nls_session_parameters WHERE parameter IN ('NLS_SORT'); 
SELECT COALESCE(value, (SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_SORT')) FROM nls_session_parameters WHERE parameter = 'NLS_SORT' 
  • The collation returned by the health checker (eg. RUSSIAN) is related to the locale of the system which can be found in the application.xml from the Support Zip.
<user.language>ru</user.language>
<user.country>RU</user.country> 
  • JIRA startup log:
user.country            : US
user.country.format     : CH
user.language           : en
user.language.format    : fr 


Cause

The Oracle JDBC Driver is able to automatically detect the locale of the system. This is determined by the JVM locale. The JVM locale can be explicitly set, or it can be automatically detected based on the Operating system's language settings. The Oracle JDBC Driver then uses the locale information to set the NLS_SORT parameter when a database session (connection) is made between JIRA and Oracle.

Solution

The expected result in JIRA startup log:


user.country            : US
user.language           : en


Based on these JVM locale settings above (en/US) the Oracle JDBC driver will set a session NLS_SORT parameter to a BINARY value.

Linux

  • Option 1: set system language/region to en/US
  • Option 2: add additional JVM options to override global system settings:
-Duser.language=en -Duser.country=US 

Windows

  • Option 1: set system Language/Region to en/US (or for JIRA specific system account only)
  • Option 2: add additional JVM options to override global system settings:
-Duser.language=en -Duser.country=US 
Last modified on Sep 25, 2019

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.