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.

 

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).

IMPORTANT: Support, CMMs, and customers should be aware that feature flags are experimental. We’ll do our best to offer support if these feature flags break migrations. However, we can't guarantee fixes or incident responses. Feature flags don’t cover all edge cases. If using these feature flags is your only option, test your migrations thoroughly with the feature flags enabled. You should consider manual workarounds when facing issues with feature flags.

When the feature flags are enabled, JCMA will not show the migration progress of all cross-project boards and all filters. Instead, you might view the following:

  • All items on the migration details are marked with 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)

Summary

As the Jira Cloud Migration Assistant is still in development, not all data will be migrated by default.

You can get a comprehensive list of all objects that JCMA will/will not migrate here: What gets migrated with the Jira Cloud Migration Assistant.

In this article, we'll deal with some of those objects:

  • Cross-project boards

  • Boards not connected to the projects being migrated

  • Boards/filters that belong to inactive/deleted users

  • Filters on boards that are not migrated

Environment

Jira 7.6 and higher

Jira Cloud Migration Assistant version 1.7.1 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 linked at the end of this KB for further reference.

Before we can migrate all boards and filters, there are some sanity checks (and minor fixes) apart from the project permission check above, that are required to be done on Jira Server's database.

JCMA only assigns boards to projects only if their filter belongs to a single project. While migrating cross-project boards and filters, JCMA will set the location of those boards to the board admin (owner).

If the board admin or filter owner is inactive, JCMA may not assign the correct project location, so verifying those details may help you to migrate your boards and filters safely.

These are the things we must consider before we start migrating all boards and filters:

  1. Boards are associated with valid filters

  2. Board columns are associated with valid statuses

  3. Invalid JQLs in the Quick filter are fixed

  4. Valid groups present in shared permissions settings
  5. Personal data, such as email in any filters are removed

  6. Boards/Filters owned/created by inactive/deleted users assigned to valid owners

Dark Features / Feature Flags

Migrating cross-project boards and filters is implemented as Feature Flags (also known as Dark Features) and is not enabled in JCMA by default. See Enable Dark Feature in Jira for instructions on how to use feature flags.

Only once all the checks and changes (see the sections below) are done, apply the two Dark Features on the Jira Server instance:

Dark Features to migrate all boards and filters with JCMA
com.atlassian.jira.migration.export.all.filters
com.atlassian.jira.migration.export.multiprojects.boards

We no longer support the Dark Feature com.atlassian.jira.migration.export.shared.filters. It has been merged with com.atlassian.jira.migration.export.all.filters. Only apply the two Dark Features above.

To apply these Dark Features, on your Jira Server instance:

  • Navigate to the URL:

    ${Jira_URL}/secure/admin/jira/views/SiteDarkFeatures!default.jspa
  • Add the Dark Features above
  • Click Add (a Jira restart is not required)

Make sure to use the two Dark Features in only one JCMA plan (see the two suggested approaches below and choose one).

If you migrate all boards in the first plan while having the Dark Features above enabled, JCMA will migrate all your boards and link them with their project location, if possible. Otherwise, it will keep them under the board owner's location for the time being.

But as other migration plans run for other projects, the migration will adjust and place those boards in their proper locations.

In case you include these Dark Features in multiple plans, all the migrated boards will be updated again and push them into their user location, depending on what you have in the plan.

This means that the recommended path is one of the two below:

  1. Enable the two Dark Features
  2. Run a full JCMA migration plan (all Projects)
  3. Disable the two Dark Features

OR

  1. Do not enable the two Dark Features yet
  2. Migrate your data with JCMA (in one or several plans, whichever works best for your scenario)
  3. Once all data is migrated, only then enable the two 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 it's finished, disable the two Dark Features for Boards/Filters

Read this before going further

The changes below will require a DBA or a user that knows how to operate SELECT/DELETE/UPDATE SQL queries.

They involve changing your data at the database level, so please pay extra attention to the changes done.

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.

Boards linked with non-existing filters

If a Jira admin has deleted objects from the DB, or if the Jira version is old, this can result in loss of data integrity. It can also lead to inconsistencies, such as boards being linked to filters that do not exist anymore.

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 this issue and there are two naive approaches that are listed below as the main suggestions.

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 1 - Delete the offending boards

If you have the board IDs present in the "AO_60DB71_BOARDADMINS" table, you'll have to delete them first from there - otherwise, you'll see foreign key constraints errors when deleting the boards from the "AO_60DB71_RAPIDVIEW" table.

That's why the query to detect these boards above also fetches data from the "AO_60DB71_BOARDADMINS" table. That's so you'll know which IDs you need to delete from it if needed.

In this case, delete the Board from the AO_60DB71_BOARDADMINS table first.

PostgreSQL
DELETE FROM "AO_60DB71_BOARDADMINS" WHERE "RAPID_VIEW_ID" = <ID of the offending Board>;
Oracle, MySQL, MSSQL Server
DELETE FROM AO_60DB71_BOARDADMINS WHERE RAPID_VIEW_ID = <ID of the offending Board>;
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
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 );
Fix 2 - Create a dummy filter and associate the offending boards to it

Create a filter in Jira's UI. Then run the SQL SELECT below to get that filter ID - we'll use it to link the offending boards with it.

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

PostgreSQL, Oracle, MySQL, MSSQL
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
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 );

Boards columns linked with non-existing statuses

For reference: you can find errors such as this one on JCMA post-migration logs:

project-export Board '<board name>' has columns with missing statuses. Missing statuses are: <status 1>=[10010], <status 2>=[10011], <status 3>=[10012]. Remove the columns OR change the column statuses.
Detect

The below SQL allows admins to find all the board column statuses with non-existing 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
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 );
Fix 1 - Open the boards (listed in the output of the SQL SELECT query above) in Jira UI and delete them
Fix 2 - Remove those columns from the database directly
PostgreSQL - board column statuses
DELETE
  FROM "AO_60DB71_COLUMNSTATUS" col
 WHERE NOT EXISTS ( SELECT * FROM issuestatus WHERE id = col."STATUS_ID" );
PostgreSQL - board columns
DELETE
  FROM "AO_60DB71_COLUMN" col
 WHERE NOT EXISTS ( SELECT * FROM "AO_60DB71_COLUMNSTATUS" WHERE "COLUMN_ID" = col."ID" );
Oracle - board column statuses
DELETE
  FROM AO_60DB71_COLUMNSTATUS col
 WHERE NOT EXISTS ( SELECT * FROM issuestatus WHERE id = col.STATUS_ID );
Oracle - board columns
DELETE
  FROM AO_60DB71_COLUMN col
 WHERE NOT EXISTS ( SELECT * FROM AO_60DB71_COLUMNSTATUS WHERE COLUMN_ID = col.ID );
MySQL, MSSQL - board column statuses
DELETE col
  FROM AO_60DB71_COLUMNSTATUS col
 WHERE NOT EXISTS ( SELECT * FROM issuestatus WHERE id = col.STATUS_ID );
MySQL, MSSQL - board columns
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

It is worth checking for personal data in Quick Filters otherwise the import process may produce errors such as:

Invalid JQL error
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).

There isn't a database type of fix for this - you'll need to update the Quick Filter JQL and remove the unsupported characters.

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
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 '%@%';

Invalid groups in shared permissions

There are some cases in which a group is referenced in a shared permission configuration, but the group itself does not exist anymore in Jira. This query will list all those invalid groups.

Potential fixes for this are:

  1. Re-creating those groups in Jira Server

  2. Deleting those shared permission entries (editing the Filter/Board/Project and removing the references to the invalid groups under the permissions settings)

  3. Replacing the group in the shared permission (same as above, but instead of removing the references, replacing them with valid groups)

Detect
PostgreSQL, Oracle, MySQL, MSSQL Server
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 )

Personal data, such as email addresses in filters

It is worth checking for personal data in filters. Otherwise, the import process may produce errors such as:

Personal data in Filters
Project-import We couldn't import Filter 'FILTER NAME'. Reason: Failed to sanitize personal data from query

There isn't a database type of fix for this - you'll need to update the Filter JQL and remove the user personal data.

Detect
PostgreSQL, Oracle, MySQL, MSSQL
SELECT * FROM searchrequest WHERE reqcontent LIKE '%@%';

Boards/Filters owned by inactive/deleted users

Boards/filters owned by inactive or non-existing users will cause JCMA to throw errors such as:

Unsupported	CAP	Board	"FILTER_NAME" [985]	Cross-project board	Boards that reference multiple projects are not supported via migration. This board will not be migrated.	If you require this board, re-create the board in your cloud site. To pull out the JQL for board filters, run SQL queries from this page: https://confluence.atlassian.com/jirakb/troubleshooting-errors-and-non-migrated-entities-with-the-jira-cloud-migration-assistant-1021239878.html

Reference:  MIG-736 - Getting issue details... STATUS

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

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

See if the Boards are Multi-project or Single-project (non-private Boards)

Multi-project / Single-project Boards
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;


Known issues

MIG-169 - Getting issue details... STATUS

MIG-269 - Getting issue details... STATUS

MIG-1089 - Getting issue details... STATUS

Last modified on Mar 13, 2023

Was this helpful?

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