Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Database: collation at the health check level is failing

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

 

1 answer

1 accepted

0 votes
Answer accepted

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:

Regards,
Earl

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

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:

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

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

We recreated these three stored procedures :)

Like Earl McCutcheon likes this

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Posted in Jira Software

Presenting the "Best of 2020" Jira Software roundup!

Catch up with Atlassian Product Managers in our 2020 Demo Den round-up! From Advanced Roadmaps to Code in Jira to Next-Gen Workflows, check out the videos below to help up-level your work in the new ...

3,666 views 5 15
Join discussion

Community Events

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

Events near you