-
Bug
-
Resolution: Unresolved
-
Medium (View bug fix roadmap)
-
None
-
7.3.8, 7.6.3
-
7.03
-
117
-
Severity 2 - Major
-
5
-
-
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.
- 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 Failed to load
-
Page Failed to load
-
Page Failed to load
-
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...
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:
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