unsupported collation: Latin1_General_CI_AI after update to 7.5.0 Edited

Hi,

Our Jira database is running on a sql 2014 and have the collation Latin1_General_CI_AI which is classified as a supported collation by Atlassian according to https://confluence.atlassian.com/adminjiraserver073/connecting-jira-applications-to-sql-server-2012-861253047.html

After reading abit about this issue on the community I run the query:

SELECT object_name(object_id) as TableName, name as ColumnName, collation_name
FROM sys.columns
WHERE collation_name != 'SQL_Latin1_General_CP437_CI_AI' AND collation_name != 'Latin1_General_CI_AI'
AND object_name(object_id) NOT LIKE 'sys%'
AND object_name(object_id) NOT LIKE 'queue%'
AND object_name(object_id) NOT LIKE 'file%'
AND object_name(object_id) NOT LIKE 'spt%'
AND object_name(object_id) NOT LIKE 'MSrep%'

which gave the result that 15 tables had changed to the default sql server collation (which is Finnish_Swedish_CI_AS). Why does this happen after an update since everything was set up with the correct collation from the beginning? I read that this was a bug in previous versions (7.0.2 for example) atleast according to this: 

https://jira.atlassian.com/browse/JRASERVER-59077

I read on the community that we need to do the following: https://confluence.atlassian.com/jirakb/how-to-fix-the-collation-of-a-microsoft-sql-server-jira-database-776646810.html

which is extra work and to be honest really bad. What if this would've been production?

Is there any other resolution?

1 answer

This widget could not be displayed.

Hi Jonathan,

The bug you referenced states that this should not show as unsupported and the health check is throwing an error that you can ignore. 

JIRA Supports the following collations with SQL Server 2012:

'SQL_Latin1_General_CP437_CI_AI' and 'Latin1_General_CI_AI'

Take a look at Oswaldo's comment from Jan 7th 2016:



Expanding on this, the difference only affects the storage on non-unicode text storage types (text, char, varchar) and JIRA's current schema does not specify columns of these types. Both SQL and Windows collations use the same rules for Unicode types.

That being said, JIRA should not automatically update the collation in that manner since it's set at the time of db creation so I would also check your MSSQL logs to see why the change was made.  Additionally, if a new database was created that would also need to be checked.

There is another bug ticket that is in awaiting development that you can find at JRASERVER-59378 with the following workaround:

Ignore the Error in the logs and confirm the supported collation with the JIRA Instance Health Add-on.

In regards to the collation that is incorrect at this time you will need to follow the steps in How to fix the collation of a Microsoft SQL Server JIRA database to correct this.

Cheers,

Branden

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

128 views 2 0
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