Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. 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

XMLWordPrintable

      Steps to reproduce
      • Set up JIRA Server with SQL Server as the database type, make sure that server instance collation differs from jira database collation (We support 'SQL_Latin1_General_CP437_CI_AI' and 'Latin1_General_CI_AI' as case-insensitive, accent-insensitive, and language neutral collation types. From: Connecting to SQL Server 2012). Collation mismatch is important because by default temporary tables in SQL server inherit collation from server instance.
      • Add second directory which lower priority than existing one, this directory has to have group with more than 2000 members
      • Attempt to display the members of the group.
      Expected Result
      • Users display in a list
      Actual Result
      • An error is displayed. The following is displayed in atlassian-jira.log:
      ERROR [jira.web.servlet.InternalServerErrorServlet] {errorId=691765ae-05c8-4ccc-962a-ff611b83d846, interpretedMsg=, cause=java.lang.RuntimeException: 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 #temp5 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.), stacktrace=java.lang.RuntimeException: 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 #temp5 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)
      at com.atlassian.jira.web.action.admin.user.UserBrowser.getBrowsableItems(UserBrowser.java:173) [classes/:?]
      at com.atlassian.jira.web.action.admin.user.UserBrowser.doExecute(UserBrowser.java:122) [classes/:?]
      at webwork.action.ActionSupport.execute(ActionSupport.java:165) [webwork-1.4-atlassian-30.jar:?]
      at com.atlassian.jira.action.JiraActionSupport.execute(JiraActionSupport.java:63) [jira-api-7.3.7.jar:?]
      at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:39) [webwork-1.4-atlassian-30.jar:?]
      at webwork.interceptor.NestedInterceptorChain.proceed(NestedInterceptorChain.java:31) [webwork-1.4-atlassian-30.jar:?]
      at webwork.interceptor.ChainedInterceptor.intercept(ChainedInterceptor.java:16) [webwork-1.4-atlassian-30.jar:?]
      at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:35) [webwork-1.4-atlassian-30.jar:?]
      at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:225) [webwork-1.4-atlassian-30.jar:?]
      at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:154) [webwork-1.4-atlassian-30.jar:?]
      at com.atlassian.jira.web.dispatcher.JiraWebworkActionDispatcher.service(JiraWebworkActionDispatcher.java:147) [classes/:?]
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) [servlet-api.jar:?]
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230) [catalina.jar:8.5.6]
      ...
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.5.6]
      at java.lang.Thread.run(Thread.java:745) [?:1.8.0_121]
      
      Caused by: 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 #temp5 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)
      at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:401) [classes/:?]
      at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findListIteratorByCondition(WrappingOfBizDelegator.java:278) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.lambda$search$0(OfBizUserDao.java:830) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.db.DefaultOfBizTransaction.lambda$withTransaction$0(DefaultOfBizTransaction.java:57) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.db.DefaultOfBizTransaction.withTransaction(DefaultOfBizTransaction.java:37) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.db.DefaultOfBizTransaction.withTransaction(DefaultOfBizTransaction.java:51) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.db.DefaultOfBizTransaction.withTransaction(DefaultOfBizTransaction.java:55) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.db.DefaultOfBizTransactionManager.withTransaction(DefaultOfBizTransactionManager.java:9) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.search(OfBizUserDao.java:828) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.DelegatingUserDao.search(DelegatingUserDao.java:128) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.IndexedUserDao.search(IndexedUserDao.java:368) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.DelegatingUserDao.search(DelegatingUserDao.java:128) [classes/:?]
      at com.atlassian.jira.crowd.embedded.ofbiz.SwitchingUserDao.search(SwitchingUserDao.java:30) [classes/:?]
      at com.atlassian.crowd.directory.AbstractInternalDirectory.searchUsers(AbstractInternalDirectory.java:785) [crowd-persistence-2.10.2-rc04.jar:?]
      at com.atlassian.crowd.directory.AbstractForwardingDirectory.searchUsers(AbstractForwardingDirectory.java:159) [crowd-persistence-2.10.2-rc04.jar:?]
      at com.atlassian.crowd.manager.directory.DirectoryManagerGeneric.searchUsers(DirectoryManagerGeneric.java:329) [crowd-core-2.10.2-rc04.jar:?]
      at com.atlassian.crowd.manager.application.SingleDirectorySearchStrategy.searchUsers(SingleDirectorySearchStrategy.java:39) [crowd-core-2.10.2-rc04.jar:?]
      at com.atlassian.crowd.manager.application.InMemoryNonAggregatingSearchStrategy.findRemainingEntities(InMemoryNonAggregatingSearchStrategy.java:287) [crowd-core-2.10.2-rc04.jar:?]
      at com.atlassian.crowd.manager.application.InMemoryNonAggregatingSearchStrategy.removeNonCanonicalEntities(InMemoryNonAggregatingSearchStrategy.java:262) [crowd-core-2.10.2-rc04.jar:?]
      at com.atlassian.crowd.manager.application.InMemoryNonAggregatingSearchStrategy.searchNestedGroupRelationships(InMemoryNonAggregatingSearchStrategy.java:184) [crowd-core-2.10.2-rc04.jar:?]
      at com.atlassian.crowd.manager.application.ApplicationServiceGeneric.searchNestedGroupRelationships(ApplicationServiceGeneric.java:2280) [crowd-core-2.10.2-rc04.jar:?]
      at com.atlassian.crowd.embedded.core.CrowdServiceImpl.searchNestedGroupRelationships(CrowdServiceImpl.java:276) [embedded-crowd-core-2.10.2-rc04.jar:?]
      at com.atlassian.crowd.embedded.core.CrowdServiceImpl.search(CrowdServiceImpl.java:200) [embedded-crowd-core-2.10.2-rc04.jar:?]
      at com.atlassian.jira.web.bean.UserBrowserFilter.getUsersFilteredByGroup(UserBrowserFilter.java:162) [classes/:?]
      at com.atlassian.jira.web.bean.UserBrowserFilter.getUsersFilteredByGroupAndCriteria(UserBrowserFilter.java:149) [classes/:?]
      at com.atlassian.jira.web.bean.UserBrowserFilter.getFilteredUsers(UserBrowserFilter.java:129) [classes/:?]
      at com.atlassian.jira.web.action.admin.user.UserBrowser.getBrowsableItems(UserBrowser.java:170) [classes/:?]
      ... 279 more
      
      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 #temp5 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)
      at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:533) [entityengine-1.2.6.jar:?]
      at org.ofbiz.core.entity.GenericDAO.createEntityListIterator(GenericDAO.java:877) [entityengine-1.2.6.jar:?]
      at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:857) [entityengine-1.2.6.jar:?]
      at org.ofbiz.core.entity.GenericHelperDAO.findListIteratorByCondition(GenericHelperDAO.java:216) [entityengine-1.2.6.jar:?]
      at org.ofbiz.core.entity.GenericDelegator.findListIteratorByCondition(GenericDelegator.java:1237) [entityengine-1.2.6.jar:?]
      at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:398) [classes/:?]
      ... 305 more
      
      Caused by: java.sql.SQLException: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.
      at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) [jtds-1.3.1.jar:1.3.1]
      at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) [jtds-1.3.1.jar:1.3.1]
      at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) [jtds-1.3.1.jar:1.3.1]
      at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671) [jtds-1.3.1.jar:1.3.1]
      at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505) [jtds-1.3.1.jar:1.3.1]
      at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029) [jtds-1.3.1.jar:1.3.1]
      at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) [commons-dbcp2-2.1.jar:2.1]
      at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) [commons-dbcp2-2.1.jar:2.1]
      at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:527) [entityengine-1.2.6.jar:?]
      ... 310 more
      
      Notes

      None

      Workarounds

      None known

              morzechowski Michal Orzechowski (Inactive)
              tkanafa Tomasz Kanafa
              Votes:
              18 Vote for this issue
              Watchers:
              34 Start watching this issue

                Created:
                Updated:
                Resolved: