unsupported collation: Latin1_General_CI_AI after update to 7.5.0

Jonathan Nilsson

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?

somethingblue

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