How to migrate all boards and filters with the Jira Cloud Migration Assistant (JCMA)

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

 

Cross-project boards and filters can now be migrated to cloud using Jira Cloud Migration Assistant 1.10.0 and higher.

Learn more about this announcement

Learn more about cross-project boards and filters migration

Purpose

The purpose of this document is to provide instructions on how to use the updated feature flags for migrating cross-project boards and filters via Jira Cloud Migration Assistant (JCMA).

Instructions

In this guide, we'll deal with specific features:

  • Cross-project boards and filters

  • Any boards not directly associated with the projects being migrated

  • Any boards/filters that belong to inactive/deleted users

  • Filters used by boards that are not migrated (including personal filters)

There are two important sets of instructions to follow:

When the feature flags are enabled, JCMA will not be able to show the migration progress for cross-project boards or all filters. Instead, you might see the following:

  • All items on the migration details have a green tick

  • The migration status shows Migration running or Migration incomplete (when an import side error happened for the final CROSS_PROJECT_DATA step)

Environment

  • Jira 7.6 and higher

  • Jira Cloud Migration Assistant version 1.7.8 or higher

Sanity Checks

The admin user running the migration plan in JCMA must have the "Browse Project" Permission on all projects that are being migrated. See the MIG-1089 bug for further reference.

Before migrating all boards and filters, there are some sanity checks (and minor fixes) that are required to be done on Jira Server's database.

JCMA only locates boards with projects if the filter refers to a single project. While migrating cross-project boards and filters, JCMA will set the location of the board to the board admin (owner). If that board admin or filter owner is inactive, JCMA may not assign the correct project location, so verifying such details will help you to migrate your boards and filters correctly.

Check the following before migrating all boards and filters:

  1. Boards must be associated with valid filters

  2. Board columns must be associated with valid statuses

  3. Invalid JQL in the Quick Filter must be fixed

  4. Personal data, such as email addresses, must be removed from any filters

  5. Shared permissions settings must refer to valid groups

  6. Boards/filters owned/created by inactive/deleted users must be assigned to valid owners

Pre-migration checks

The changes below will require a DBA or a user that knows how to use SQL queries.

They involve changing your data at the database level, so be aware of the changes made.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

It is recommended that you perform each of the following checks on your server instance before attempting a migration of boards and filters to maximize the chance of success.

Boards linked to non-existent filters

If a Jira admin has deleted objects from the DB, or if the Jira version is old, there can be a loss of data integrity. This can also lead to inconsistencies, such as boards being linked to filters that no longer exist.

In addition, the Jira admin user who runs the migration may not have access to search/find and fix those filters and boards through the UI (unless they change the permission at DB level and restart Jira).

There are several ways to fix non-functioning (broken) boards listed below.

Detect

POSTGRESQL

SELECT DISTINCT 
       rv."ID"              AS "Board ID"
     , rv."NAME"            AS "Board Name"
     , rv."OWNER_USER_NAME" AS "Board Owner"
     , ba."RAPID_VIEW_ID"   AS "Foreign Key ID from the Board Admins table"
  FROM "AO_60DB71_RAPIDVIEW" rv
  LEFT JOIN searchrequest sr ON (rv."SAVED_FILTER_ID" = sr.id)
  LEFT JOIN "AO_60DB71_BOARDADMINS" ba ON (rv."ID" = ba."RAPID_VIEW_ID")
 WHERE NOT EXISTS ( SELECT *
                      FROM searchrequest
                      WHERE id = rv."SAVED_FILTER_ID" )
 ORDER BY rv."ID";

ORACLE, MYSQL, MSSQL

SELECT DISTINCT 
       rv.ID              AS "Board ID"
     , rv.NAME            AS "Board Name"
     , rv.OWNER_USER_NAME AS "Board Owner"
     , ba.RAPID_VIEW_ID   AS "Foreign Key ID from the Board Admins table"
  FROM AO_60DB71_RAPIDVIEW rv
  LEFT JOIN searchrequest sr ON (rv.SAVED_FILTER_ID = sr.id)
  LEFT JOIN AO_60DB71_BOARDADMINS ba ON (rv.ID = ba.RAPID_VIEW_ID)
 WHERE NOT EXISTS ( SELECT *
                      FROM searchrequest
                      WHERE id = rv.SAVED_FILTER_ID )
 ORDER BY rv.ID;
Fix: Create a dummy filter for broken boards

Create a filter using Jira's UI. Run the SQL SELECT below to get the filter ID and use it to link to each of the broken boards.

Once you have the filter ID, the UPDATE statement below will re-associate any broken boards to your new filter.

Any database

SELECT id FROM searchrequest WHERE filtername = '<dummy filter name>';

POSTGRESQL

UPDATE "AO_60DB71_RAPIDVIEW" rv
   SET "SAVED_FILTER_ID" = <filter ID from the SELECT above>
 WHERE NOT EXISTS ( SELECT id FROM searchrequest WHERE id = rv."SAVED_FILTER_ID" );

ORACLE, MYSQL, MSSQL SERVER

UPDATE AO_60DB71_RAPIDVIEW
   SET SAVED_FILTER_ID = <filter ID from the SELECT above>
 WHERE NOT EXISTS ( SELECT id FROM searchrequest WHERE id = AO_60DB71_RAPIDVIEW.SAVED_FILTER_ID );
Fix: Delete the boards with invalid filters using REST

A safe way to delete boards is via REST API.

For guidance on how to use REST APIs see Jira REST API examples.

Review Delete boards for the definition and parameters of how to delete a board.

  • The board ID is the same Board ID from the query to identify affected boards.

Example REST call using curl to delete a board

curl
curl -u username:password -X DELETE -H "Content-Type: application/json" https://jiraserver.com/rest/agile/1.0/board/{board ID}

Note, if you don’t have permissions to view the board you may receive an error.

To change yourself to the owner of the board, you can run the below SQL query:

EXAMPLE POSTGRESQL

UPDATE "AO_60DB71_RAPIDVIEW" 
SET "OWNER_USER_NAME" = '{my username}'
WHERE "ID" = {board ID}; 

If you’re not sure about the username, you can check the CWD_USER table.

EXAMPLE POSTGRESQL

SELECT user_name 
FROM cwd_user 
WHERE email_address = 'myuser@example.com';

After changing yourself to the owner you can try the REST call again.

Fix: Delete the boards with invalid filters via the database

If you have the board IDs present in the "AO_60DB71_BOARDADMINS" table, then delete them from there first; otherwise, you'll see foreign key constraint errors when deleting the boards from the "AO_60DB71_RAPIDVIEW" table (see below image).

The above query to detect broken boards also fetches data from the "AO_60DB71_BOARDADMINS" table to provide the IDs you need to delete from there if needed.

Note: this method is known to run into various database constraint errors. If that occurs it's recommended to try a different method. 

To delete the Board from the AO_60DB71_BOARDADMINS table (if required):

POSTGRESQL

DELETE
  FROM "AO_60DB71_BOARDADMINS"
WHERE "RAPID_VIEW_ID" IN (<ID of a Board>, <ID of another Board>, ...);

ORACLE, MYSQL, MSSQL SERVER

DELETE
  FROM AO_60DB71_BOARDADMINS
WHERE RAPID_VIEW_ID IN (<ID of a Board>, <ID of another Board>, ...);

To delete the Board from the AO_60DB71_RAPIDVIEW table:

POSTGRESQL

DELETE
  FROM "AO_60DB71_RAPIDVIEW" USING "AO_60DB71_RAPIDVIEW" rv
  LEFT JOIN searchrequest sr ON (rv."SAVED_FILTER_ID" = sr.id)
 WHERE "AO_60DB71_RAPIDVIEW"."ID" = rv."ID"
   AND NOT EXISTS ( SELECT *
                      FROM searchrequest
                     WHERE id = rv."SAVED_FILTER_ID" );

ORACLE

DELETE 
  FROM AO_60DB71_RAPIDVIEW rv
WHERE EXISTS ( SELECT rv.ID
                 FROM AO_60DB71_RAPIDVIEW rv
                 LEFT JOIN searchrequest sr ON (rv.SAVED_FILTER_ID = sr.id)
                WHERE NOT EXISTS ( SELECT *
                                     FROM searchrequest
                                    WHERE id = rv.SAVED_FILTER_ID ))

MYSQL, MSSQL SERVER

DELETE rv
  FROM AO_60DB71_RAPIDVIEW rv
  LEFT JOIN searchrequest sr ON (rv.SAVED_FILTER_ID = sr.id)
 WHERE NOT EXISTS ( SELECT *
                      FROM searchrequest
                      WHERE id = rv.SAVED_FILTER_ID );


Filter columns linked to non-existent statuses (optional)

The latest version of JCMA can migrate a filter with a missing status, which can leave any board relying on the filter in a broken state. If you prefer to find and fix all such occurrences, do the following.

Detect

Find all the board column statuses with non-existent status ids.

POSTGRESQL

SELECT rv."ID"   AS "Board ID"
     , rv."NAME" AS "Board Name"
     , c."NAME"  AS "Column Name"
     , i.pname   AS "Issue Status Name"
FROM "AO_60DB71_RAPIDVIEW" rv
JOIN "AO_60DB71_COLUMN" c ON (rv."ID" = c."RAPID_VIEW_ID")
JOIN "AO_60DB71_COLUMNSTATUS" cs ON (c."ID" = cs."COLUMN_ID")
LEFT JOIN issuestatus i ON (i.id = cs."STATUS_ID")
WHERE NOT EXISTS ( SELECT id 
                     FROM issuestatus 
                    WHERE id = cs."STATUS_ID" );

ORACLE, MYSQL, MSSQL SERVER

SELECT rv.ID   AS "Board ID"
     , rv.NAME AS "Board Name"
     , c.NAME  AS "Column Name"
     , i.pname AS "Issue Status Name"
FROM AO_60DB71_RAPIDVIEW rv
JOIN AO_60DB71_COLUMN c ON (rv.ID = c.RAPID_VIEW_ID)
JOIN AO_60DB71_COLUMNSTATUS cs ON (c.ID = cs.COLUMN_ID)
LEFT JOIN issuestatus i ON (i.id = cs.STATUS_ID)
WHERE NOT EXISTS ( SELECT id 
                     FROM issuestatus 
                    WHERE id = cs.STATUS_ID );
Using the UI
Open the Board (listed in the output of the SQL SELECT query above) in Jira UI and delete the status.
DB query

POSTGRESQL

DELETE
  FROM "AO_60DB71_COLUMNSTATUS" col
 WHERE NOT EXISTS ( SELECT * FROM issuestatus WHERE id = col."STATUS_ID" );
DELETE
  FROM "AO_60DB71_COLUMN" col
 WHERE NOT EXISTS ( SELECT * FROM "AO_60DB71_COLUMNSTATUS" WHERE "COLUMN_ID" = col."ID" );

ORACLE

DELETE
  FROM AO_60DB71_COLUMNSTATUS col
 WHERE NOT EXISTS ( SELECT * FROM issuestatus WHERE id = col.STATUS_ID );
DELETE
  FROM AO_60DB71_COLUMN col
 WHERE NOT EXISTS ( SELECT * FROM AO_60DB71_COLUMNSTATUS WHERE COLUMN_ID = col.ID );

MYSQL, MSSQL SERVER

DELETE col
  FROM AO_60DB71_COLUMNSTATUS col
 WHERE NOT EXISTS ( SELECT * FROM issuestatus WHERE id = col.STATUS_ID );
DELETE col
  FROM AO_60DB71_COLUMN col
 WHERE NOT EXISTS ( SELECT * FROM AO_60DB71_COLUMNSTATUS WHERE COLUMN_ID = col.ID );

Invalid JQL in the Quick Filter

Any special characters in a filter might cause the migration process to fail with:

project-import We couldn't import Board <Board ID>. Reason: JQL in quick filter is invalid: Invalid JQL: Error in the JQL Query: The character '@' is a reserved JQL character. You must enclose it in a string or use the escape '\u0040' instead. (line 1, character 34).

See also the below section on email addresses.

Detect

POSTGRESQL

SELECT rv."NAME" AS "Board Name"
     , qf."NAME" AS "Quick Filter Name"
 FROM "AO_60DB71_RAPIDVIEW" rv
INNER JOIN "AO_60DB71_QUICKFILTER" qf ON (rv."ID" = qf."RAPID_VIEW_ID")
 WHERE qf."LONG_QUERY" LIKE '%@%';

ORACLE, MYSQL, MSSQL SERVER

SELECT rv.NAME AS "Board Name"
     , qf.NAME AS "Quick Filter Name"
 FROM AO_60DB71_RAPIDVIEW rv
INNER JOIN AO_60DB71_QUICKFILTER qf ON (rv.ID = qf.RAPID_VIEW_ID)
 WHERE qf.LONG_QUERY LIKE '%@%';

Using the UI, update the JQL of the filter to remove unsupported characters.

Personal data, such as email addresses in filters

This pre-check is to prevent issues like this:

Error in the JQL Query: The character '@' is a reserved JQL character. You must enclose it in a string or use the escape '\u0040' instead. (line 1, character 52)

The '@' is a reserved character, but, on the server/DC, so long as you ensure that the Filters containing the '@' symbol have the email addresses enclosed by double quotes, this will allow the migration to occur and the filters to be functional on the Cloud site, there is no need to change it to the account ID for example.

You can run the following SQL query to find all filters with personal data (email address) to confirm if they are in the correct format, for example:

project = "ABC" AND reporter = "username@yourcompany.com"
Detect
SELECT * FROM searchrequest WHERE reqcontent LIKE '%@%';


Invalid groups in shared permissions (optional)

There are some cases in which a group is referenced in a filter’s shared permission configuration, but the group itself no longer exists in Jira. Although the filter will migrate, it may be broken or inaccessible. If you would rather mitigate the risk of migrating broken boards, check first. If you are migrating with scoped users & groups, a broken filter may also happen if the referenced group is not a part of the current plan.

This does not apply to a filter’s JQL containing a reference to a group.
Detect
SELECT sr.filtername AS filter_name , sp.entitytype AS bla , sp.param1 AS group_name FROM searchrequest sr JOIN sharepermissions sp ON (sr.id = sp.entityid) WHERE sp.entitytype = 'SearchRequest' AND sp.sharetype = 'group' AND NOT EXISTS ( SELECT id FROM cwd_group g WHERE g.group_name = sp.param1 )
Options for fixing
  1. Re-create the groups in Jira Server.

  2. Delete the shared permission entries.

    1. In the UI, edit the Filter/Board/Project and remove references to the invalid groups under the permissions settings.

  3. Replace the group in the shared permission.

    1. As above, but instead of removing the references, replace with valid groups.

Boards/Filters owned by inactive/deleted users

A migrated filter’s ownership may refer to a deactivated user on the cloud. The location of any associated cross-project boards will need to be fixed in a post-migration process. But fixing the owners pre-migration is a more feasible task. 

Boards owned by inactive users

Detect
PostgreSQL
SELECT rv."NAME"               AS "Board name"
     , cwu.lower_user_name     AS "Board inactive owner username"
     , cwu.lower_email_address AS "Board inactive owner email address"    
     , cwu.display_name        AS "Board inactive owner display name"
     , au.user_key             AS "Board inactive owner user key"
  FROM cwd_user cwu
 INNER JOIN app_user au         ON (cwu.lower_user_name = au.lower_user_name)
  JOIN "AO_60DB71_RAPIDVIEW" rv ON (rv."OWNER_USER_NAME" = cwu.lower_user_name OR rv."OWNER_USER_NAME" = au.user_key)
 WHERE cwu.active = 0
 ORDER BY rv."ID";


Oracle, MySQL, MSSQL


Oracle,MySQL,MSSQL
SELECT rv.NAME                 AS "Board name"
     , cwu.lower_user_name     AS "Board inactive owner username"
     , cwu.lower_email_address AS "Board inactive owner email address"    
     , cwu.display_name        AS "Board inactive owner display name"
     , au.user_key             AS "Board inactive owner user key"
  FROM cwd_user cwu
 INNER JOIN app_user au       ON (cwu.lower_user_name = au.lower_user_name)
  JOIN AO_60DB71_RAPIDVIEW rv ON (rv.OWNER_USER_NAME = cwu.lower_user_name OR rv.OWNER_USER_NAME = au.user_key)
 WHERE cwu.active = 0
 ORDER BY rv.ID;


Fix 1 - Open the boards (listed in the output of the SQL SELECT query above) in Jira UI (board settings) and change their owners manually. If this is not possible or feasible, then proceed to the DB fix approach below.
Fix 2 - Update those boards, changing their owners to active users, from the database directly

Make sure to choose an active Jira User to replace the inactive user as the board owner for the offending boards.

If you face the MySQL ERROR 1175, check: MySQL error code: 1175 during UPDATE in MySQL Workbench

Postgressql
UPDATE "AO_60DB71_RAPIDVIEW"
   SET "OWNER_USER_NAME" = '<user name that will be the new owner of these boards>'
 WHERE "ID" IN ( SELECT rv."ID"
                   FROM cwd_user cwu
                  INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
                   JOIN "AO_60DB71_RAPIDVIEW" rv ON (rv."OWNER_USER_NAME" = cwu.lower_user_name OR rv."OWNER_USER_NAME" = au.user_key)
                  WHERE cwu.active = 0 );
Oracle, MSSQL Server
UPDATE AO_60DB71_RAPIDVIEW
   SET OWNER_USER_NAME = '<user name that will be the new owner of these boards>'
 WHERE ID IN ( SELECT rv.ID
                 FROM cwd_user cwu
                INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
                 JOIN AO_60DB71_RAPIDVIEW rv ON (rv.OWNER_USER_NAME = cwu.lower_user_name OR rv.OWNER_USER_NAME = au.user_key)
                WHERE cwu.active = 0 );
MySQL
SET SQL_SAFE_UPDATES = 0;
 
UPDATE AO_60DB71_RAPIDVIEW
   SET OWNER_USER_NAME = '<user name that will be the new owner of these boards>'
 WHERE ID IN ( SELECT ID
                 FROM ( SELECT rv.ID
                          FROM cwd_user cwu
                         INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
                          JOIN AO_60DB71_RAPIDVIEW rv ON (rv.OWNER_USER_NAME = cwu.lower_user_name OR rv.OWNER_USER_NAME = au.user_key)
                         WHERE cwu.active = 0 ) AS t );
 
SET SQL_SAFE_UPDATES = 1;


Filters owned by inactive users

Detect
PostgreSQL,MySQL
SELECT DISTINCT sr.filtername  AS "Filter name"
     , sr.username             AS "Filter username"
     , sr.authorname           AS "Filter author"
     , cwu.lower_email_address AS "Filter inactive user email address"
     , cwu.display_name        AS "Filter inactive user display name"
     , au.user_key             AS "Filter inactive user user key"
     , CASE WHEN cwu.active = 0 THEN 'Inactive' ELSE 'Active' END AS "User status"
     , sr.reqcontent           AS "Filter JQL"
  FROM cwd_user cwu
 INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
  JOIN searchrequest sr ON ( (sr.username = cwu.lower_user_name OR sr.username = au.user_key) OR (sr.authorname = cwu.lower_user_name OR sr.authorname = au.user_key) )
 WHERE cwu.active = 0 ;
Oracle
SELECT DISTINCT sr.filtername  AS "Filter name"
     , sr.username             AS "Filter username"
     , sr.authorname           AS "Filter author"
     , cwu.lower_email_address AS "Filter inactive user email address"
     , cwu.display_name        AS "Filter inactive user display name"
     , au.user_key             AS "Filter inactive user user key"
     , CASE WHEN cwu.active = 0 THEN 'Inactive' ELSE 'Active' END AS "User status"
     , CAST(sr.reqcontent AS VARCHAR2(3000)) AS "Filter JQL"
  FROM cwd_user cwu
 INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
  JOIN searchrequest sr ON ( (sr.username = cwu.lower_user_name OR sr.username = au.user_key) OR (sr.authorname = cwu.lower_user_name OR sr.authorname = au.user_key) )
 WHERE cwu.active = 0 ;
MSSQL
SELECT DISTINCT sr.filtername  AS "Filter name"
     , sr.username             AS "Filter username"
     , sr.authorname           AS "Filter author"
     , cwu.lower_email_address AS "Filter inactive user email address"
     , cwu.display_name        AS "Filter inactive user display name"
     , au.user_key             AS "Filter inactive user user key"
     , CASE WHEN cwu.active = 0 THEN 'Inactive' ELSE 'Active' END AS "User status"
     , CAST(sr.reqcontent AS nvarchar(max)) AS "Filter JQL"
  FROM cwd_user cwu
 INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
  JOIN searchrequest sr ON ( (sr.username = cwu.lower_user_name OR sr.username = au.user_key) OR (sr.authorname = cwu.lower_user_name OR sr.authorname = au.user_key) )
 WHERE cwu.active = 0 ;


Fix 1 - Open the filters (listed in the output of the SQL SELECT query above) in Jira UI (board/filter settings) and change their owners
Fix 2 - Update those filters, changing their owners to active users, from the database directly

Make sure to choose an active Jira User to replace the inactive user as the filter owner for the offending filters.

You'll need to take the user_key of the user from the app_user table.

SELECT user_key FROM app_user WHERE lower_user_name = '<user name of the new owner of the filters>'

With the user_key value, populate it in the UPDATE statement below.

If you face the MySQL ERROR 1175, check: MySQL error code: 1175 during UPDATE in MySQL Workbench

PostgreSQL, Oracle, MSSQL
UPDATE searchrequest
   SET username = '<user_key that will be the new owner of these filters>'
     , authorname = '<user_key that will be the new owner of these filters>'
 WHERE id IN ( SELECT sr.id
                 FROM cwd_user cwu
                INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
                 JOIN searchrequest sr ON ( (sr.username = cwu.lower_user_name OR sr.username = au.user_key) OR (sr.authorname = cwu.lower_user_name OR sr.authorname = au.user_key) )
                WHERE cwu.active = 0 );
MySQL
SET SQL_SAFE_UPDATES = 0;
 
UPDATE searchrequest     
   SET username = '<user_key that will be the new owner of these filters>'
     , authorname = '<user_key that will be the new owner of these filters>'
 WHERE id IN ( SELECT id
                 FROM ( SELECT sr.id
                          FROM cwd_user cwu
                         INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
                          JOIN searchrequest sr ON ( (sr.username = cwu.lower_user_name OR sr.username = au.user_key) OR (sr.authorname = cwu.lower_user_name OR sr.authorname = au.user_key) )
                         WHERE cwu.active = 0 ) AS t );
 
SET SQL_SAFE_UPDATES = 1;


Boards owned by deleted users

Detect
PostgreSQL
SELECT rv."NAME"         AS "Board Name"
     , u.lower_user_name AS "Deleted Username"
 FROM "AO_60DB71_RAPIDVIEW" rv
 JOIN app_user u        ON (rv."OWNER_USER_NAME" = u.user_key)
 LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
WHERE NOT EXISTS ( SELECT *
                     FROM cwd_user
                    WHERE lower_user_name = u.lower_user_name );
Oracle, MySQL, MSSQL
SELECT rv.NAME           AS "Board Name"
     , u.lower_user_name AS "Deleted Username"
 FROM AO_60DB71_RAPIDVIEW rv
 JOIN app_user u        ON (rv.OWNER_USER_NAME = u.user_key)
 LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
WHERE NOT EXISTS ( SELECT *
                     FROM cwd_user
                    WHERE lower_user_name = u.lower_user_name );


Fix 1 - Open the boards (listed in the output of the SQL SELECT query above) in Jira UI (board settings) and change their owners
Fix 2 - Update those boards, changing their owners to active users, from the database directly

Make sure to choose an active Jira User to replace the inactive user as the board owner for the offending boards.

PostgreSQL
UPDATE "AO_60DB71_RAPIDVIEW"
   SET "OWNER_USER_NAME" = '<user name that will be the new owner of these boards>'
 WHERE "ID" IN ( SELECT rv."ID"
                   FROM "AO_60DB71_RAPIDVIEW" rv
                   JOIN app_user u        ON (rv."OWNER_USER_NAME" = u.user_key)
                   LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
                  WHERE NOT EXISTS ( SELECT *
                                       FROM cwd_user
                                      WHERE lower_user_name = u.lower_user_name ));
Oracle, MSSQL
UPDATE AO_60DB71_RAPIDVIEW
   SET OWNER_USER_NAME = '<user name that will be the new owner of these boards>'
 WHERE ID IN ( SELECT rv.ID
                 FROM AO_60DB71_RAPIDVIEW rv
                 JOIN app_user u        ON (rv.OWNER_USER_NAME = u.user_key)
                 LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
                WHERE NOT EXISTS ( SELECT *
                                     FROM cwd_user
                                    WHERE lower_user_name = u.lower_user_name ));
MySQL
UPDATE AO_60DB71_RAPIDVIEW rv
  JOIN app_user u        ON (rv.OWNER_USER_NAME = u.user_key)
  LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
   SET OWNER_USER_NAME = '<user name that will be the new owner of these boards>'
 WHERE NOT EXISTS ( SELECT *
                      FROM cwd_user
                     WHERE lower_user_name = u.lower_user_name );


Filters owned by deleted users

Detect
PostgreSQL, Oracle, MySQL, MSSQL
SELECT sr.filtername     AS "Filter name"
     , u.lower_user_name AS "Username"
  FROM searchrequest sr
  JOIN app_user u        ON (sr.authorname = u.lower_user_name)
  LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
 WHERE NOT EXISTS ( SELECT *
                      FROM cwd_user
                     WHERE lower_user_name = u.lower_user_name );


Fix 1 - Open the filters (listed in the output of the SQL SELECT query above) in Jira UI (board/filter settings) and change their owners
Fix 2 - Update those filters, changing their owners to active users, from the database directly

Make sure to choose an active Jira User to replace the inactive user as the filter owner for the offending filters.

You'll need to take the user_key of the user from the app_user table.

SELECT user_key FROM app_user WHERE lower_user_name = '<user name of the new owner of the filters>'

With the user_key value, populate it in the UPDATE statement below.

If you face the MySQL ERROR 1175, check: MySQL error code: 1175 during UPDATE in MySQL Workbench

PostgreSQL, Oracle, MSSQL
UPDATE searchrequest
   SET username = '<user_key that will be the new owner of these filters>'
     , authorname = '<user_key that will be the new owner of these filters>'
 WHERE id IN ( SELECT sr.id
                 FROM searchrequest sr
                 JOIN app_user u        ON (sr.authorname = u.lower_user_name)
                 LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
                WHERE NOT EXISTS ( SELECT *
                                     FROM cwd_user
                                    WHERE lower_user_name = u.lower_user_name ));
MySQL
UPDATE searchrequest sr
  JOIN app_user u        ON (sr.authorname = u.lower_user_name)
  LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
   SET username = '<user_key that will be the new owner of these filters>'
     , authorname = '<user_key that will be the new owner of these filters>'
 WHERE NOT EXISTS ( SELECT *
                      FROM cwd_user
                     WHERE lower_user_name = u.lower_user_name );


Determining if you have multi-project boards

To get an indication of how many multi-project boards (and single-project boards) exist, use the following.

Detect JQL across multiple projects

Any database

SELECT * FROM SEARCHREQUEST WHERE REQCONTENT LIKE '%project in%' OR REQCONTENT LIKE '%project%project%'
Detect permissions across multiple projects

POSTGRESQL

SELECT rv."ID"   AS board_id
     , rv."NAME" AS board_name
     , CASE WHEN COUNT(*) > 1 THEN 'Multi-project Board' ELSE 'Single-project Board' END AS board_type
     , COUNT(*)  AS project_count_on_filter
  FROM "AO_60DB71_RAPIDVIEW" rv 
 INNER JOIN searchrequest sr    ON (sr.id = rv."SAVED_FILTER_ID") 
 INNER JOIN sharepermissions sp ON (sp.entityid = sr.id) 
 INNER JOIN project p           ON (p.id = sp.param1::int) 
 WHERE sp.sharetype = 'project' 
 GROUP BY rv."ID", rv."NAME" 
 ORDER BY COUNT(*) DESC;

MYSQL

SELECT rv.ID     AS board_id
     , rv.NAME   AS board_name
     , CASE WHEN COUNT(*) > 1 THEN 'Multi-project Board' ELSE 'Single-project Board' END AS board_type
     , COUNT(*)  AS project_count_on_filter
  FROM AO_60DB71_RAPIDVIEW rv 
 INNER JOIN searchrequest sr    ON (sr.id = rv.SAVED_FILTER_ID)
 INNER JOIN sharepermissions sp ON (sp.entityid = sr.id) 
 INNER JOIN project p           ON (p.id = CAST(sp.param1 AS UNSIGNED)) 
 WHERE sp.sharetype = 'project' 
 GROUP BY rv.ID, rv.NAME
 ORDER BY COUNT(*) DESC;

MSSQL SERVER

SELECT rv.ID     AS board_id
     , rv.NAME   AS board_name
     , CASE WHEN COUNT(*) > 1 THEN 'Multi-project Board' ELSE 'Single-project Board' END AS board_type
     , COUNT(*)  AS project_count_on_filter
  FROM AO_60DB71_RAPIDVIEW rv 
 INNER JOIN searchrequest sr    ON (sr.id = rv.SAVED_FILTER_ID)
 INNER JOIN sharepermissions sp ON (sp.entityid = sr.id) 
 INNER JOIN project p           ON (p.id = CAST(sp.param1 AS NUMERIC)) 
 WHERE sp.sharetype = 'project' 
 GROUP BY rv.ID, rv.NAME
 ORDER BY COUNT(*) DESC;

ORACLE

SELECT rv.ID     AS board_id
     , rv.NAME   AS board_name
     , CASE WHEN COUNT(*) > 1 THEN 'Multi-project Board' ELSE 'Single-project Board' END AS board_type
     , COUNT(*)  AS project_count_on_filter
  FROM AO_60DB71_RAPIDVIEW rv 
 INNER JOIN searchrequest sr    ON (sr.id = rv.SAVED_FILTER_ID)
 INNER JOIN sharepermissions sp ON (sp.entityid = sr.id) 
 INNER JOIN project p           ON (p.id = CAST(sp.param1 AS NUMBER)) 
 WHERE sp.sharetype = 'project' 
 GROUP BY rv.ID, rv.NAME
 ORDER BY COUNT(*) DESC;

Migrating cross-project boards and all filters using Feature Flags

From JCMA 1.9.17 and below, this feature is implemented behind Feature Flags (also known as Dark Features) that need to be enabled. See also Enable Dark Feature in Jira for detailed instructions on how to use feature flags. Similarly, private filters outside of the scope of the migration plan are not included.

Once all of the checks and changes (see the sections above) are done, add the following Feature Flags on the Jira Server instance:

com.atlassian.jira.migration.export.all.filters
com.atlassian.jira.migration.export.multiprojects.boards

When used in tandem, these flags will migrate

  • all filters, even if they have no relation to the projects included (this includes personal filters as well as filters referring to other projects not in the current plan)

  • all boards that relate to projects in the plan and also refer to projects outside the plan (cross-project boards)

To apply these Feature Flags, on your Jira Server instance:

  • Navigate to the URL:

    ${Jira_URL}/secure/admin/jira/views/SiteDarkFeatures!default.jspa
  • Enter the text of the Flag above.

  • Click Add (a Jira restart is not required).

To avoid version incompatibilities & minimize migration time, you should use the two Dark Features in only one JCMA plan (see the suggested approaches below).

JCMA will migrate all single-project boards and link them with their project location, if possible. Additionally, it will keep cross-project boards (containing a filter that refers to multiple projects) under the board owner's location, even when the board depends on projects that have not yet been migrated.

If you include these Dark Features in multiple plans, all included boards and filters will be migrated again, and this may cause duplication if there have been any changes to the boards or their filters on the server or cloud side between running the plans - this is called config drift (see below).

This means that the recommended path is one of:

  1. Enable both Dark Features.

  2. Run a full JCMA migration plan (all projects).

  3. Once the plan has finished, disable both Dark Features.

OR

  1. Do not enable either Dark Feature.

  2. Migrate your projects with JCMA (in one or several plans, whichever works best for your scenario).

  3. Once all projects are migrated, then enable both Dark Features.

  4. Create a dummy project in Jira Server with a single Issue.

  5. Run a JCMA migration plan only with that dummy project.

  6. Once the plan has finished, disable both Dark Features.

Known issues

MIG-907 - Getting issue details... STATUS

Config drift

Any time a feature is migrated multiple times, there is a risk of config drift. Config drift usually occurs when configuration on the server side is changed between phases of migration - or even between the running of migration plans in JCMA. It can also occur if there are updates to the cloud side.

In the case of filters and boards, config drift can occur:

  • if you set both feature flags (above) for the duration of multiple plans (or migration phases)

  • if you change a filter or board relating to one project to be related to another between running plans (with or without feature flags)

The latter being unlikely, it is important to understand the risks of leaving the feature flags on, thereby allowing for a board or filter to be migrated multiple times.

Change between plans

Result

Special action

Filter (JQL) changed on server

No change to cloud

Delete filter (& board) on cloud before re-migrating

Filter (JQL) changed on cloud

No change to cloud


Board (config) changed on server

Board config updated on cloud


Board (config) changed on cloud

Board config changes lost on cloud

Re-implement changes on cloud

Last modified on Jul 23, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.