It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

Shaakunthala Oct 30, 2015

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

2 votes
Answer accepted
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.

Shaakunthala Nov 01, 2015

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 Nov 01, 2015

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

0 votes
Nicolas JAUTEE Nov 03, 2015

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
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted in Jira Core

How to manage many similar workflows?

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

736 views 7 0
Join discussion

Community Events

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

Events near you