quartz数据不完整导致不调度并且报错Couldn‘t store trigger

MisfireHandler: Error handling misfires: Couldn't store trigger '218111-TRIGGER' for '218111' job:The job (xx-JOBGROUP.218111) referenced by the trigger does not exist. org.quartz.JobPersistenceException: Couldn't store trigger '218111-TRIGGER' for '218111' job:The job (xx-JOBGROUP.218111) referenced by the trigger does not exist.

这是因为qrtz_triggers表中有2188111的调度时间信息,但是在qrtz_job_details表里缺少了2188111的数据,导致quartz调度更新触发器表的时候发现数据不完整而报错，也就没有调度。只要从qrtz_triggers和qrtz_cron_triggers表中删除掉该数据即可。

select * 
-- DELETE
from qrtz_triggers where TRIGGER_NAME = '218111'  ;

select * 
-- DELETE
from qrtz_cron_triggers where TRIGGER_NAME = '218111';

select * 
-- DELETE 
from qrtz_job_details where JOB_NAME =218111 ;

下面提供一个全面排查这种数据的sql

SELECT
		j.JOB_NAME 
	FROM
		qrtz_triggers j
	LEFT JOIN qrtz_job_details t ON j.JOB_NAME = t.JOB_NAME
	LEFT JOIN qrtz_cron_triggers ct ON CONCAT(j.JOB_NAME, '-TRIGGER') = ct.TRIGGER_NAME
 
	WHERE
		j.JOB_GROUP = 'xx-JOBGROUP'
	AND (
		t.JOB_NAME IS NULL
		OR ct.TRIGGER_NAME IS NULL 
	)
UNION
-- 查询qrtz_job_details调度计划详情表中有但是qrtz_triggers,qrtz_cron_triggers或r_job表中没有的流程
	SELECT
		j.JOB_NAME 
	FROM
		qrtz_job_details j
	LEFT JOIN qrtz_triggers t ON j.JOB_NAME = t.JOB_NAME
	LEFT JOIN qrtz_cron_triggers ct ON CONCAT(j.JOB_NAME, '-TRIGGER') = ct.TRIGGER_NAME
 
	WHERE
		j.JOB_GROUP = 'xx-JOBGROUP'
	AND (
		t.JOB_NAME IS NULL
		OR ct.TRIGGER_NAME IS NULL
	)