Loading...

XML

Word

Printable

Details

Type: Bug
Resolution: Unresolved
Priority: Medium
Fix Version/s: None
Affects Version/s: 7.3.8, 7.6.3
Component/s: System Administration - Others
Labels:
- fuego
- whl-fy24q3

Introduced in Version:
7.03
Support reference count:
102
Symptom Severity:
Severity 2 - Major
UIS:
38
Bug Fix Policy:
View Atlassian Server bug fix policy
Current Status:
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

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, Jira and temp table collation will not match.

Steps to Reproduce

Install SQL Server with a collation that is not supported (SQL_Latin1_General_CP437_CI_AI and Latin1_General_CI_AI are supported)
Create a database with the correct collation (e.g. SQL_Latin1_General_CP437_CI_AI)
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.

Attachments

Issue Links

is related to

JRASERVER-64919 Groups with more than 2000 users will fail to display correctly

Closed

JRASERVER-65461 When using SQL Server, groups with more than 2000 users fail to display and prevent filter sharing due to a mis-match in the server and database collation

Closed

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

relates to: MOVE-85218 Loading...

(13 mentioned in, 1 relates to)

Activity

People

Assignee:: Unassigned

Reporter:: Eric Storch (Inactive)

Votes:: 33 Vote for this issue

Watchers:: 55 Start watching this issue

Dates

Created:: 17/Jul/2017 3:27 PM

Updated:: 6 days ago 2:35 AM