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

This widget could not be displayed.
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.

This widget could not be displayed.

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
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted 16 hours ago in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

42 views 1 0
Join discussion

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