In our JIRA instance there's a lot of unused filter subscriptions that belong to resigned users. Since these users are resigned, each time filter subscriptions run, JIRA attempts to send e-mail to a bunch of non-existent e-mail addresses.
Therefore I plan to (bulk) delete these unwanted subscriptions from the database.
delete from filtersubscription where id in (...);
This will remove filter subscriptions, but where are the cron expressions stored? I don't see cron expressions or subscription interval stored in the same table. Don't they need to be deleted?
This depends on your version of JIRA, because the cron expression is actually stored in the scheduler's data structures:
As always, doing low-level SQL surgery on your database isn't exactly recommended, so using some other method (like a REST endpoint) is preferable if you can find one (unfortunately, I don't see one for filter subscriptions). You should definitely take a backup first.
I did the same thing on my JIRA instance, deleting subscriptions of unactive users directly in database on tables filtersubscription, qrtz_cron_triggers, and qrtz_triggers. Right after the clean, I see nothing wrong on the 2 qrtz tables, but after some hours, I can see that some entries were created on these tables, new qrtz_cron_triggers entries that match new qrtz_triggers entries too, but the qrtz_triggers entries refers to subscriptions that does not exist.
I use a following request to check overtime the evolution of these entries and it does not stop to increase :
SELECT QCT.ID AS CRON_ID, QCT.TRIGGER_ID, QCT.CRONEXPERSSION, QT.JOB, QT.MISFIRE_INSTR, TO_CHAR(QT.NEXT_FIRE, 'DD MM YYYY HH24:MI:SS') AS NEXT_FIRE, QT.START_TIME, QT.TRIGGER_GROUP, QT.TRIGGER_NAME, QT.TRIGGER_STATE, QT.TRIGGER_TYPE, QT.CALENDAR_NAME, QT.END_TIME FROM QRTZ_CRON_TRIGGERS QCT left JOIN QRTZ_TRIGGERS QT ON QT.ID = QCT.TRIGGER_ID WHERE trigger_name LIKE 'SUBSCRIPTION_%' AND SUBSTR(trigger_name,14,17) NOT IN (SELECT id FROM FILTERSUBSCRIPTION) OR TRIGGER_ID not in (select id from qrtz_triggers);
If you have ever seen this comportment and solved this I would be very thanksfull to see your feedback It looks like there is somewhere a reference to old filtersubscriptions that does not exist anymore and generates triggers and crons on these non-existent subscriptions.. but I can't figure out where.
I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events