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

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

XMLWordPrintable

    • 7.03
    • 111
    • Severity 2 - Major
    • 23
    • Hide
      Atlassian Update – 24 May 2023

      Hi everyone,

      We’ve investigated the bug and decided to lower its priority.

      Reason:

      After investigating the reported cases we could conclude that the majority of them are actually not about this particular bug but rather about when two or more tables in the Jira database have mismatching collations, which is an accurately reported database configuration error that should be corrected. This bug only applies when temporary tables are used with the Microsoft SQL Server engine and the database default collation is different from the Jira database's own collation.

      Next steps:

      If you come across this error, first check if this bug applies or if there is a configuration error.

      This bug applies if the database engine used is Microsoft SQL Server and the error message contains a reference to a temp table, recognisable by the table name beginning with a hash (#) sign. Like ( select item from #temp1 ) in this example:

      2017-06-20 18:10:46,004 https-jsse-nio-443-exec-12 ERROR User.Name 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.)

      If you discover that this bug is having any direct functional impact on your Jira, kindly contact support and provide a more detailed description of the issue. Doing so will enable us to investigate and reevaluate its priority.

      If you do not find a reference to a temp table in the error, then we suggest to review that your database collation settings are set up correctly as described for your particular database engine on the page Connecting Jira applications to a database or contact support to identify the root cause in your particular case.

      Best regards
      Magnus Karlsson
      Jira Developer

      Show
      Atlassian Update – 24 May 2023 Hi everyone, We’ve investigated the bug and decided to lower its priority. Reason: After investigating the reported cases we could conclude that the majority of them are actually not about this particular bug but rather about when two or more tables in the Jira database have mismatching collations, which is an accurately reported database configuration error that should be corrected. This bug only applies when temporary tables are used with the Microsoft SQL Server engine and the database default collation is different from the Jira database's own collation. Next steps: If you come across this error, first check if this bug applies or if there is a configuration error. This bug applies if the database engine used is Microsoft SQL Server and the error message contains a reference to a temp table, recognisable by the table name beginning with a hash (#) sign. Like ( select item from #temp1 ) in this example: 2017-06-20 18:10:46,004 https-jsse-nio-443-exec-12 ERROR User.Name 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.) If you discover that this bug is having any direct functional impact on your Jira, kindly contact support and provide a more detailed description of the issue. Doing so will enable us to investigate and reevaluate its priority. If you do not find a reference to a temp table in the error, then we suggest to review that your database collation settings are set up correctly as described for your particular database engine on the page Connecting Jira applications to a database or contact support to identify the root cause in your particular case. Best regards Magnus Karlsson Jira Developer

      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, 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_AI 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_AI. You can use this guide to do so: Set or Change the Server Collation.

              Unassigned Unassigned
              estorch Eric Storch (Inactive)
              Votes:
              33 Vote for this issue
              Watchers:
              57 Start watching this issue

                Created:
                Updated: