How to fix Collation issue.

Hello,

 

We have been using JIRA and everything is running well. We just notice that there has been a popup stating that "The Collation health check has been failed in your system" . We are running on version 7.0 for JIRA and MSSQL 2012 for our DB.  I had check the collation that we are using and it is set to "SQL_Latin1_General_CP437_CI_AI". 

I check on this document and is sure that this collation type is supported. https://confluence.atlassian.com/jira/connecting-jira-to-sql-server-2012-610763188.html?continue=https%3A%2F%2Fconfluence.atlassian.com%2Fjira%2Fconnecting-jira-to-sql-server-2012-610763188.html&application=cac

I also tried creating another DB where I'm planing to move my JIRA instance. I did make sure that I set the collation to "SQL_Latin1_General_CP437_CI_AI". when I created the DB. I then run the query below against the newly created database and found out that there are tables that are not in the same collation as what I had set when I created the DB.

 

use JIRADB03
select object_name(object_id) as tablename, name as columnname,collation_name
from sys.columns
where collation_name is not null
order by object_name(object_id),column_id

 

I'm not sure if I missed something or if there is any other document that I should be referencing for this issue. Any response is greatly appreciated. 

Thanks,

Lorenze 

1 answer

Thanks for the Response @Boris Georgiev [Botron]. Does this KB still applies even if running the query below does not return any result?

 

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 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%'

Thanks,

Lorenze

are you still seeing the errors in the log or through the health check UI ?

My bad, Its actually returning result, I had it run against another DB.  Another quick question, I'm not sure if this is possible though.

I'm planning to create a new DB base on this KB. https://confluence.atlassian.com/jira/connecting-jira-to-sql-server-2012-610763188.html.  

-Create an xml backup of our system. 

-clone the existing server and reconfigure the DB connection to point to the new DB.

-do an import from the xml back.up.

Do you think this plan will work? Sorry for asking too much.

 

Thanks,

Lorenze

This should work - yes. Just make sure your JIRA instance has enough RAM because if you're importing large backup it might fail with out of memory if your heap is low.

I've imported ~10GB XML backups and everything is fine with the proper memory settings.

https://confluence.atlassian.com/jira/increasing-jira-memory-5896.html

FIRST test the procedure on a test/staging server before doing this on production!

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,233 views 14 19
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot