VARCHAR columns in AO_xxx tables on SQL Server are not migrated correctly to NVARCHAR columns when upgrading to JIRA 7.x

Summary

When it comes to JIRA 7.x, they start to use NVARCHAR columns instead of VARCHAR columns in AO_xxx tables (like AO_60DB71_SPRINT) on SQL Server for the purpose of avoiding AO-386. (FYI. JIRA 7.x start to use the fixed version of ao-plugin.)
However, only the case that we upgrade JIRA from the earlier version than JIRA 7.x, the columns are not migrated correctly.

Environment

• OS
  • Windows
• DB
  • SQL Server
• JIRA
  • Before upgrading : JIRA 6.x
  • After upgrading : JIRA 7.x

Steps to Reproduce

1. Install JIRA 6.x and JIRA Agile
2. Setup the JIRA instance to use SQL Server as the external DB
3. Upgrade the JIRA instance to JIRA 7.x

Expected Results

VARCHAR columns should be migrated correctly to NVARCHAR columns.

Actual Results

VARCHAR columns are still VARCHAR columns after the upgrading.

Notes

• This bug might lead to multibytes character problems.
• Causes JSWSERVER-15917

Workaround

• Alter the tables manually
• Export the data as XML from the instance and import it to a new instance

Example of workaround for AO_60DB71_LEXORANK table

Example of workaround for AO_60DB71_LEXORANK table. Similar approach can be done for other tables.

1. Stop JIRA.
2. Backup your database.
3. Delete all indexes from the table AO_60DB71_LEXORANK, except for the primary key.
4. Change the data type on the columns AO_60DB71_LEXORANK.RANK and AO_60DB71_LEXORANK.LOCK_HASH using these SQL queries:

   ALTER TABLE dbo.AO_60DB71_LEXORANK ALTER COLUMN RANK NVARCHAR(255) NOT NULL;
   ALTER TABLE dbo.AO_60DB71_LEXORANK ALTER COLUMN LOCK_HASH NVARCHAR(255) NULL;
   

5. Run the following SQL queries to flush caches and update statistics on the database:

   DBCC FREEPROCCACHE;
   SP_UPDATESTATS;
   

6. Start JIRA. The indexes for the table AO_60DB71_LEXORANK will be automatically recreated.