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

This document provides instructions on how to prepare the migration of all filters and boards with the Jira Cloud Migration Assistant, as described in this article.

Environment

Jira 7.6 and higher

Sanity Checks

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 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 owner. If the board owner or filter owner is inactive, on cloud, the board or filter becomes private and can no longer be accessed or modified.

Check the following before migrating all boards and filters:

Boards must be associated with valid filters
Board columns must be associated with valid statuses
Invalid JQL in the Quick Filter must be fixed
Personal data, such as email addresses, must be removed from any filters
Shared permissions settings must refer to valid groups
Boards/filters owned/created by inactive/deleted users must be assigned to valid owners
The admin users running the migration must have Browse project permissions on all projects that are selected for migration.
Owners of all boards and filters must have Browse project permissions for the projects associated with those boards and filters.

Pre-migration checks

The changes below will require a DBA or a user who 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 making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

To maximize the chance of success, it is recommended that you perform each of the following checks on your server instance before attempting a board and filter migration.

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 for and fix those filters and boards through the UI (unless they change the permissions at the 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 the board IDs are present in the "AO_60DB71_BOARDADMINS" table, delete them from there first; otherwise, you'll see foreign key constraint errors when deleting the boards from the "AO_60DB71_RAPIDVIEW" table (see the image below).

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 encounter various database constraint errors. If that occurs, it's recommended that you 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, as 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

Re-create the groups in Jira Server.
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.
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.

If a username has multiple entries across directories with at least one entry marked as inactive, SQL queries may incorrectly identify the user as inactive. To ensure accuracy, check the user’s active status by referring to How to list all Users and Groups in Jira.

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 - 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 - 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;

Once you have cleaned up your boards and filters, you can procceed to migrating your boards and filters.

Migration Support

Knowledge base

Products

Jira Software

Jira Service Management

Jira Work Management

Confluence

Bitbucket

Resources

Documentation

Community

Marketplace

Billing and licensing

Preparing boards and filters to be migrated with the Jira Cloud Migration Assistant (JCMA)

Still need help?

Purpose

Environment

Sanity Checks

Pre-migration checks

Boards linked to non-existent filters

Filter columns linked to non-existent statuses (optional)

Open the Board (listed in the output of the SQL SELECT query above) in Jira UI and delete the status.

Personal data, such as email addresses in filters

Invalid groups in shared permissions (optional)

Boards/Filters owned by inactive/deleted users

Boards owned by inactive users

Filters owned by inactive users

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

Boards owned by deleted users

Filters owned by deleted users

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

Determining if you have multi-project boards

Page

Viewport

Confluence

Preparing boards and filters to be migrated with the Jira Cloud Migration Assistant (JCMA)

Related content

Still need help?

Purpose

Environment

Sanity Checks

Pre-migration checks

Boards linked to non-existent filters

Filter columns linked to non-existent statuses (optional)

Open the Board (listed in the output of the SQL SELECT query above) in Jira UI and delete the status.

Personal data, such as email addresses in filters

Invalid groups in shared permissions (optional)

Boards/Filters owned by inactive/deleted users

Boards owned by inactive users

Filters owned by inactive users

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

Boards owned by deleted users

Filters owned by deleted users

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

Determining if you have multi-project boards

Related content