The RANK column of the AO_60DB71_LEXORANK table in your JIRA application database must use the correct collation for ranking to work in JIRA Agile efficiently. If it does not, you will experience performance problems, including very slow re-indexing times. This problem only affects JIRA Agile 6.4 and later.

This article helps you diagnose whether you have the collation set correctly for the RANK column, and provides instructions on how to fix it, if required. If you need further help after reading this article, raise a support request.

See the instructions for your supported production database below:

Oracle

Diagnosis

The correct collation for the RANK column in an Oracle database is 'BINARY'. 

Check this for your JIRA application database, by running the following query:

SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_SORT';

Fix

These instructions will change the collation for the specific user that you use to connect to your JIRA application database. This is particularly useful if you have multiple databases and want to make this change for the JIRA application database only.

1. Connect as sys.

2. Grant the schema user the rights to perform the actions by running the following statement:

   grant create session, create trigger to <USER>;

3. Connect with the user that you have just granted rights to, by running the following statement:

   connect <USER>/<PASSWORD>

4. Create a trigger, by running the following statement:

   create or replace trigger after_logon
     after logon on schema
      begin
      execute immediate 'alter session set nls_sort = ''BINARY''';
      end after_logon;
   /

5. Validate that the collation has been set correctly, by running the following query:

   select * from nls_session_parameters where parameter = 'NLS_SORT';

   NLS_SORT should be set to 'BINARY'

6. Stop your application and drop the trigger:

   drop trigger after_logon

MySQL

Diagnosis

The correct collation for the RANK column in a MySQL database is 'utf8_bin'. 

Check this for your JIRA application database, by running the following query:

select collation_name from INFORMATION_SCHEMA.COLUMNS where table_name = 'AO_60DB71_LEXORANK' and column_name = 'RANK'

Fix

If your RANK field is not using the correct collation, running the following SQL statement to fix it:

alter table AO_60DB71_LEXORANK modify column rank varchar(255) collate utf8_bin;

Query your database again, as described in the 'Diagnosis' section, to confirm that the collation for the RANK column has been set correctly.

PostgreSQL

Diagnosis

The correct collation for the RANK column in a PostgreSQL database is either 'POSIX' or 'C'. 

Check this for your JIRA application database, as follows:

• If you are using PostgreSQL 8.4 or 9.0, run the following queries and check whether the RANK field has the correct collation:

  show LC_COLLATE;
  show LC_CTYPE;

• If you are using PostgreSQL 9.1 or later, run the following query and check whether the RANK field has the correct collation:

  select collation_name from INFORMATION_SCHEMA.COLUMNS where table_name = 'AO_60DB71_LEXORANK' and column_name = 'RANK';

Fix

If your RANK field is not using the correct collation, run the following SQL to fix it:

• If you are using PostgreSQL 8.4 or 9.0, you will need to do the following (note, this can take a long time for large databases):
  
  • Dump/backup the database with pg_dump.

  • Recreate the database with the correct collation:

    create database jira owner jira ENCODING 'UTF-8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;

  • Restore database from dump with pg_restore.

• If you are using PostgreSQL 9.1 or later, run one (not both) of the following SQL statements to fix it::

  alter table "AO_60DB71_LEXORANK" alter column "RANK" type character varying(255) collate pg_catalog."POSIX";
  or
  alter table "AO_60DB71_LEXORANK" alter column "RANK" type character varying(255) collate pg_catalog."C";

Query your database again, as described in the 'Diagnosis' section, to confirm that the collation for the RANK column has been set correctly.

Microsoft SQL Server

Diagnosis

The correct collation for the RANK column in a Microsoft SQL Server database is either 'SQL_Latin1_General_CP437_CI_AI' or 'SQL_Latin1_General_CI_AI'. 

Check this for your JIRA application database, by running the following query:

select collation_name from INFORMATION_SCHEMA.COLUMNS where table_name = 'AO_60DB71_LEXORANK' and column_name = 'RANK'

Fix

If your RANK field is not using the correct collation, run one (not both) of the following SQL statements to fix it:

alter table dbo.AO_60DB71_LEXORANK alter column rank varchar(255) collate SQL_Latin1_General_CP437_CI_AI;
or
alter table dbo.AO_60DB71_LEXORANK alter column rank varchar(255) collate SQL_Latin1_General_CI_AI;

Query your database again, as described in the 'Diagnosis' section, to confirm that the collation for the RANK column has been set correctly.