Database: collation at the health check level is failing

[spantchakh]

Jira Team,

We are planning to upgrade Jira from Jira 6.1.7 to Jira 7.0.11 and then to 7.13.1. In this process we are facing some issues related to collation. We would like to have your support in addressing this issue.

We took a current Jira 6.1.7 production backup from our existing production server and restored on SQL server 2008 R2 and upgraded to 7.0.11. This upgrade went well and didn’t face any issues. Later we built a new SQL server 2016 for Jira upgrade from 7.0.11 to 7.13.1. We took a backup of Jira 7.0.11 database and restored on SQL 2016. We were able to connect to the database  but collation at the health check level is failing.

Later we found that our existing collation is SQL_Latin1_General_CP1_CI_AS and at the database level to SQL_Latin1_General_CP437_CI_AI. This change has been successful at the database level on the new SQL server 2016. But still were getting errors during the health check:

Problems detected

Error

Database

Error

Collation

What does this check do?

Checks if the database and tables are configured using the required collation.

Result

The table collation: 'SQL_Latin1_General_CP1_CI_AS' is unsupported by Jira. The Database collation: 'SQL_Latin1_General_CP437_CI_AI' is supported by Jira.

ALTER DATABASE CURRENT COLLATE SQL_Latin1_General_CP437_CI_AI ;

ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;

Upon researching online, we found that collation needs to be changed at column level too. We came across an article from Jira Wiki to fix the collation of SQL server database.

https://confluence.atlassian.com/kb/how-to-fix-the-collation-of-a-microsoft-sql-server-database-744326177.html

As said, In the article we tried to build a new database with the supported collation SQL_Latin1_General_CP437_CI_AI and executed Steps 1-5 successfully which are listed under creating the database tables. When we tried to run these scripts with sysadmin access, We are able to create  dbo schema tables but not the Jiraschema tables

[Earl McCutcheon · Accepted solution]

Hello Helen,

When Creating the Database, you need to create all the tables empty, with the correct table & column collation, and it sounds like there is a mismatch between theses from the previous modification, i.e. either the tables reflect the supported collation, but not their columns or vice versa.

Please refer to this article for steps to fix:

• https://confluence.atlassian.com/jirakb/how-to-fix-the-collation-of-a-microsoft-sql-server-jira-database-776646810.html

Regards,
Earl

[spantchakh]

Hi Earl,

Thank you for your response. But when I am trying to open this link, I am getting "Something went wrong. We're moving mountains to get it sorted."

Best, 

Helen

[Earl McCutcheon]

Hi Helen,

We were experiencing a communication issue and partial outage on confluence.atlassian.com earlier today right around the time you replied, Details here:

• https://status.atlassian.com/

The issue should be fully resolved, but if the page is still failing to load refresh your browser cache and try again, but you should be good to go now.

Regards,
Earl

[spantchakh]

Hi Earl,

Thank you for your help!

Question: We are having collation issues while upgrading from versions 6.1.7 to 7.13.1. We decided to build our database from scratch and pumped data into it. We followed the “How to fix the collation of a Microsoft SQL Server database” article from the Jira website. But this did not mentioned anything about the migration of stored procedures. There are three stored procedures currently under our production database.

sp_Priority_Closed

sp_SubmittedDefects_WithoutPriority

sp_SumittedIssues_ByPriority

As our database is very old we are not sure that these are user created or product based. Are these 3 stored procedures needed to run any functionality after migration? Or can we ignore them?

Please let us know.

Thanks,

Helen

[spantchakh]

We recreated these three stored procedures :)