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

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

1 answer

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

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

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

...

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

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Jira

Looking for anyone who made the switch to Data Center

The Jira Marketing team is putting together an ebook on migrating to Data Center. We're looking for pro tips on how you staffed your project team and organized your Proof of Concept. Share yo...

36 views 0 2
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you