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 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 yesterday 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 ...

155 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