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

0 vote

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
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira

Mission-critical battery manufacturer fulfills FAA software requirements with Commit Policy Plugin

EaglePicher Technologies is a leading manufacturer of battery systems for diverse industries like defense, aviation, space or medical. As they operate in highly regulated industries, keeping a clear ...

182 views 0 2
Read article

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