Notification emails are still being received despite deleting scheduled job

Issue:

An end-user deleted a scheduled job from the View -> Schedules page. However, despite the scheduled job having being deleted, the user keeps receiving the error notification emails for the report on a recurring basis (based on the deleted scheduled job definition). How can this be resolved ?

Solution:

Once the scheduled job was deleted from the front end UI, then under normal circumstances there should be no report executions and no notifications to trigger and hence no emails should have been received. However under abnormal (unidentified) circumstances, a scheduled job was deleted but the corresponding triggers remain. The steps documented below are intended to recover from this situation only as a workaround approach.

The idea is to trace down the scheduled job and the triggers and verify if they were actually deleted from the scheduling and trigger tables after the user deleted the schedule from the UI. The jasperserver.log may also capture information that would help with this trace.

1. The jireportjob table stores the schedule job information :

   select * from jireportjob 
   

   Note 1: If the schedule job has been deleted, then the scheduled job entry should not exist in this table.

   Note 2: The users email can be queried from jireportjobmailrecipient, to identify what schedule triggers email to the impacted user: 

   select * from jireportjob where mail_notification in (select destination_id from jireportjobmailrecipient where address = '<email>';
   

2. The jasperserver.log (located under WEB-INF/logs) can be inspected to see whether there is still a schedule trigger happening. For example, look for :

   "trigger: ReportJobs.trigger_343289_0, scheduled fire time: 2018-01-23 07:00:00.000, fired at: 2018-01-23 07:00:38.405"

   This entry captures the trigger name 'trigger_343289_0'

3. Check for existing corresponding trigger in the quartz triggers table based on the trigger name :

   select job_name, trigger_state from qrtz_triggers where trigger_name='trigger_343289_0';
   

4. If the trigger_state shows 'WAITING', then its an enabled trigger and it should be set to 'PAUSED' to disable it:

   update qrtz_triggers set trigger_state='PAUSED' where trigger_name='trigger_343289_0';
   

   Note 3: Setting to PAUSED is safer than deleting the trigger record, for such case where it can be re-enabled again if the wrong trigger was set.

   Note 4: This is equivalent to 'unchecking' the Enabled checkbox on the View -> Schedules page. However as the scheduled job was already deleted, it is not possible to uncheck the 'Enabled' checkbox.