nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Shaakunthala_5h8fhb

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?

Accepted answers

crf

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.

All comments

crf

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

Shaakunthala_5h8fhb

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_5h8fhb

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

Nicolas JAUTEE1

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-administration

jira-core

database