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

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?

2 answers

1 accepted

Accepted Answer
2 votes
Chris Fuller Atlassian Team Oct 31, 2015

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.

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.

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

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

 

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Monday in Jira Ops

Jira Ops Early Access Program Update #1: Announcing our next feature and a new integration

Thanks for signing up for Jira Ops! I’m Matt Ryall, leader for the Jira Ops product team at Atlassian. Since this is a brand new product, we’ll be delivering improvements quickly and sharing updates...

427 views 0 8
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you