Uploaded image for project: 'Jira Server and Data Center'
  1. Jira Server and Data Center
  2. JRASERVER-65634

Collation error in Jira when the database and server/default collation do not match in SQL Server

    XMLWordPrintable

    Details

      Description

      Summary

      Jira uses temp tables to perform some of its functions. When creating temp tables, SQL server uses the instance-level collation, and Jira currently does not specify collation on table creation. If the instance-level collation is different from the database-level collation (SQL_Latin1_General_CP437_CI_AI and Latin1_General_CI_AS are supported), Jira and temp table collation will not match.

      Steps to Reproduce

      1. Install SQL Server with a collation that is not supported (SQL_Latin1_General_CP437_CI_AI and Latin1_General_CI_AS are supported)
      2. Create a database with the correct collation (e.g. SQL_Latin1_General_CP437_CI_AI)
      3. Error shows in logs in reference to temp tables.

      Expected Results

      Should report collation is correct "SQL_Latin1_General_CP437_CI_AI"

      Actual Results

      The below exception is thrown in the atlassian-jira.log file:

      2017-06-20 18:10:46,004 https-jsse-nio-443-exec-12 ERROR Scott.Lepech 1090x309x1 15yequj 10.7.35.65 /rest/projecttemplates/1.0/create-templates/item/35 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)
      com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)
      

      Workaround

      Change the instance-level collation to match the Jira database collation to SQL_Latin1_General_CP437_CI_AI or Latin1_General_CI_AS. You can use this guide to do so: Set or Change the Server Collation.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                estorch Eric Storch
              • Votes:
                11 Vote for this issue
                Watchers:
                15 Start watching this issue

                Dates

                • Created:
                  Updated: