How to remove the inactive users filter subscriptions and their related jobs
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
Summary
When a user is deactivated, the filter subscriptions and the jobs related to them are still kept in Jira database. In such a scenario, when the Jira admin wants to keep the inactive user's internal reference, but wants to clean up the subscriptions and subscription jobs (hence alleviating the performance when there are several inactive users with subscriptions), a direct action in the database is required.
Procedure
Please note that all queries below are written for PostgreSQL. If you are using a different DBMS, please adjust accordingly. For any database modification please also record to validate the steps in a lower environment after an environment backup.
Check what inactive users have filter subscriptions with:
SELECT f.id as subscription_id, f.filter_i_d as filter_id, f.username as subscriber_username, appu.lower_user_name as subscriber_lower_user_name, cdu.active as subscriber_user_active
FROM filtersubscription f
join app_user appu on f.username = appu.user_key
join cwd_user cdu on cdu.lower_user_name = appu.lower_user_name
where cdu.active = 0;With the subscriptions listed, we can list the jobs related to them with:
select * from clusteredjob c
where exists (
select 1 from filtersubscription f
join app_user appu on f.username = appu.user_key
join cwd_user cdu on cdu.lower_user_name = appu.lower_user_name
where cdu.active = 0 and c.job_id like concat('%',f.id)
)If the entries are the expected to be removed, you can do it with:
-- Remove the jobs
delete from clusteredjob c
where exists (
select 1 from filtersubscription f
join app_user appu on f.username = appu.user_key
join cwd_user cdu on cdu.lower_user_name = appu.lower_user_name
where cdu.active = 0 and c.job_id like concat('%',f.id)
)
-- Remove the subscriptions
delete from filtersubscription ff where
ff.id in (
SELECT f.id
FROM filtersubscription f
join app_user appu on f.username = appu.user_key
join cwd_user cdu on cdu.lower_user_name = appu.lower_user_name
where cdu.active = 0
);After the deletions, proceed with a rolling restart.
If the subscriptions were removed already and you are facing a "No filter subscription for id xxxxx" in (Admin > System > Scheduler details) you can follow the instructions from this article.