-
Bug
-
Resolution: Fixed
-
Low
-
3.8.3
-
None
-
Severity 3 - Minor
-
4
-
Description -
When adding over 2000 customers (as a group) for Service Desk Customers into the User and roles permission, will run into collation issue if MsSQL server collation is different to database collation.
Scenario for the bug to occur -
- Customer permissions need to be set with Customers who are added to the project
- MsSQL server collation is not supported by Jira, i.e different from the database collation of SQL_Latin1_General_CP437_CI_AI
Expected Results -
Customer organization screen is populated with all users from the group
Actual Results -
This happens when you click on the customer pane on the left hand side for Service Desk, you will see the error -
You will also notice in the atlassian-jira.log the following stack trace -
2018-01-18 17:04:30,124 http-nio-8752-exec-2 ERROR admin 1024x2848x1 vlov11 0:0:0:0:0:0:0:1 /rest/servicedesk/1/pages/people/customers/pagination/DBCON/search [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 jiraschema.cwd_user WHERE (lower_user_name IN ( select item from #temp4 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_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 jiraschema.cwd_user WHERE (lower_user_name IN ( select item from #temp4 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:401) at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findListIteratorByCondition(WrappingOfBizDelegator.java:278) at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.lambda$search$1(OfBizUserDao.java:854) .. .. .. Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT user_name FROM jiraschema.cwd_user WHERE (lower_user_name IN ( select item from #temp4 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.) at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:533)
This is caused by Temp tables being created in Server collation instead of following the Jira database collation, and the limit of 2000 records requiring a temp table to be created instead of loading into memory.
Workaround -
Options
- Separate groups into smaller chunks of 1999 users then add them in as Service Desk Customers
- Do not select Customers who are added to the project for customer permissions
- Change MsSQL server collation to be the same as database collation i.e - SQL_Latin1_General_CP437_CI_AI
- is related to
-
-
- Closed
-