JIRA reindex failure after database collation change from Chinese_Taiwan_Stroke_CI_AS to Latin1_Gene

[Wayne SW]

During the upgrade from version 7.2.7 to 8.20.0(7.2.7 -> 7.3.0 -> 7.13.0 -> 8.0.0 -> 8.5.0 -> 8.20.0), I successfully executed the reindex at each step. However, after updating to 8.20.0, the reindex failure. Upon inspecting the database collation using JIRA's Troubleshooting and Support tools, I identified collation issue, Before changing the collation, the reindex process would 99%, but ultimately fail, I processed to update the database collation, and during the reindex, I observed ERROR message in the Atlassian logs.

Could someone please advise if there are any issues with these actions?

[BHUSHAN PATIL · Accepted solution]

Hi @Wayne SW ,

Changing the database collation can significantly impact JIRA, especially considering its heavy reliance on the database for functionality. If you're using MS SQL, here are some crucial aspects to consider:

1. Compatibility: Ensure that your database version aligns with your JIRA version to guarantee compatibility and optimal performance.
   Supported platforms

2. Supported Collation: It's essential to verify that the new collation, in this case, 'Chinese_Taiwan_Stroke_CI_AS' and 'Latin1_Gene', is compatible with JIRA's requirements. Different collations can affect data storage and comparison methods, potentially causing issues if incompatible. Atlassian's documented supported collations include 'SQL_Latin1_General_CP437_CI_AI' and 'Latin1_General_CI_AI'.
   Connecting Jira applications to SQL Server 2019

3. Impact of Collation Change: Altering the collation can directly affect stored data, especially characters that might not be supported in the new collation. Mishandling this change could lead to data loss or corruption.

For more details you can follow below article:

• How to fix the collation of Microsoft SQL Server database for Jira manually
• How to Fix the Collation and Character Set of a MySQL Database manually

Also, if you feel my input help you please accept the answer.

Regards,
Bhushan

[Wayne SW]

Thank @BHUSHAN PATIL  for the reference to the provided article. I noticed a mistake in my previous title; indeed, the collation was change to Latin1_General_CI_AI.

Before the upgrade, I used JIRA version 7.2.7. Although collation issue occurred, they seemed not to impact usage, so I overlooked them. As described in my question, I could perform reindexing successfully before upgrading to 8.20.0, but it became failed after the upgrade. Honestly, I still don't know the reason for the reindexing failure.

Could you assist me with understanding this issue?

[BHUSHAN PATIL]

Hi @Wayne SW ,

According to error in attached screenshot is not really the whole re-index failing, it is reporting a single error for some issues due to customfield_19780. The rest of your issues are absolutely fine. It has only failed for that one custom field on the issues.

for this there are multiple causes for this issue.

• Sometime such problem was encountered when JIRA was trying to create an index for that custom field. Rather than failing the entire index operation for that issue, JIRA will drop the Custom Field from that index.
• sometime such exception might be false positive and occurring due to the actual server running out of space. for more details please refer following articles.

1. Custom field indexes dropped with errors in Jira server
2. Troubleshoot index problems in Jira server

• If this is rank field, then JIRA is unable to retrieve the maximum rank marker and minimum ranked value for a given Rank field.  (in our case customfield_19780)

Could you please confirm the type of custom field associated with 'customfield_19780'? or refer Reindexing fails with expected exactly 2 rows error in Jira server

Regards,
Bhushan

[Wayne SW]

Hi @BHUSHAN PATIL 

Thank you for your suggest.

I have successfully reindexed.