Cannot resolve the collation conflict between "different collation" in the equal to operation

[Daniel Plaschy]

Hello,

For BitBucket, I use the "Atlassian crowd" type of user directory. BitBucket connect to our Jira user directory to get the users and groups list.

Since I updated BitBucket et Jira, I noticed the directory synchronisation was failed. Not sure it's directly connected to the update, maybe it was happening before. So currently BitBucket is unable to sync user from Jira.

Error in bitbucket log : 

2018-01-10 11:21:12,426 ERROR [Caesium-1-2] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 229377 ].
com.atlassian.crowd.exception.OperationFailedException: javax.xml.bind.UnmarshalException
- with linked exception:
[org.apache.http.ConnectionClosedException: Premature end of chunk coded message body: closing chunk expected]

Error in Jira log :

2018-01-10 11:21:12,350 http-nio-8080-exec-16 ERROR [o.a.c.c.C.[.[localhost].[/].[default]] Servlet.service() for servlet [default] in context with path [] threw exception
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 #temp2 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "French_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)

This is a Microsoft SQL Database
According to our DBA, this error is "normal". The SQL instance collation is  French_CI_AS and the JIRA database collation is SQL_Latin1_General_CP437_CI_AI
This is collation is supported, no error in the "Instance Health" :

Result

The database collation 'SQL_Latin1_General_CP437_CI_AI' and table collation 'SQL_Latin1_General_CP437_CI_AI' are supported by Jira.

But when JIRA creates a temp table, it uses the SQL instance collation, which is French_CI_AS, which generates this error.

It looks like it's possible to do a better SQL query to avoid this error.

Any suggestions or workaround ?
Other than installing a new SQL instance... the JIRA database is on a SQL servers cluster.

Thanks for your help !

BitBucket v5.6.1
Jira v7.6.1

[somethingblue]

Hi Daniel,

• What version of JIRA did you upgrade from?
• What is the Database Collation?
• What is the largest number of users you have in a group?

I have seen this in other Applications and in one instance there was a large number of inactive users causing this behavior. 

I also found a JIRA bug - JRASERVER-65461 - that sounds like it may be the cause of your problem.  The version of JIRA is not 7.6, however, the stack trace is the same:

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.)

Test the collations of the database and the JIRA instance.  According to the ticket "Collation mismatch is important because by default temporary tables in SQL server inherit collation from server instance."

If that does match your issue there are a couple ways to resolve this:

1. Make sure there aren't any groups with more than 2000 users
2. Make the database collation and the JIRA collation match

Please be sure to also vote on JRASERVER-65461 to add impact and you'll be notified when the ticket is updated.  I do know they are working to fix this, however, I cannot provide an ETA at this time so continue to watch the bug ticket for more info.

Let me know what you find.

Cheers,

Branden

[Daniel Plaschy]

Hello, thanks for the answer

• It was JIRA 7.4.2 before I updated to 7.6.1
• JIRA database collation is SQL_Latin1_General_CP437_CI_AI
• Yes of course we have largest group than 2000, for example I found one with 3778 members

Not sure what you mean by 

1. Make the database collation and the JIRA collation match

The JIRA database collation does not match the SQL server collation for sure.

Anyway, thank you for pointed me JRASERVER-65461, this is exactly our situation. So we have to wait for a bugfix to synchronise correctly ? That's an urgent situation for us...

I will try to workaround this by not synchronizing the groups. In fact, we use only the users, and then we create local JIRA groups

[Daniel Plaschy]

Well, is it possible to not synchronize the groups ? Only the users ?

...

[somethingblue]

Hi Daniel,

Take a look at How to Remove LDAP Groups from JIRA to see how to sync users only.  You may need to modify the filter for your environment.

Cheers,

Branden

[Daniel Plaschy]

Hello,

OK, I changed the LDAP filter, I excluded groups with more than 2000 users.

And everything works perfectly !
I hate workaround, but it will helps us to wait the bug fix...

Thanks a lot!
Regards