JIRA: Where's filter subscription cron expressions are stored?

[Shaakunthala]

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?

[crf · Accepted solution]

This depends on your version of JIRA, because the cron expression is actually stored in the scheduler's data structures:

• JIRA < 6.3: qrtz_cron_triggers; this is referenced by other tables like qrtz_triggers, so be careful!
• JIRA < 7.0: jquartz_cron_triggers; this is referenced by other tables like jquartz_triggers, so be careful!
• JIRA 7.0+: clusteredjob

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.

[Shaakunthala]

Wonderful! Thank you for the descriptive answer!! I've used tail -f with egrep '(select|insert|update|delete)' on SQL log, but couldn't catch this one for some reason. I'm not sure why.

[Shaakunthala]

// As always, doing low-level SQL surgery on your database isn't exactly recommended Correct! We'll use our staging environment first.

[Nicolas JAUTEE]

Hello Sameera,

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.