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
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:
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?
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.
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events