How to fix Collation issue.

Question

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

Boris Georgiev [Appfire] · Answer

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!

lorenze larot · Answer

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

Boris Georgiev [Appfire] · Answer

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