Quartz JDBC Job Store - Maintenance/Cleanup

Question

I am currently in the processes of setting up Quartz in a load balanced environment using the JDBC job store and I am wondering how everyone manages the quartz job store DB.

For me Quartz (2.2.0) will be deployed as a part of a versioned application with multiple versions potentially existing on the one server at the one time. I am using the notation XXScheduler_v1 to ensure multiple schedulers play nice together. My code is working fine, with the quartz tables being populated with the triggers/jobs/etc as appropriate.

One thing I have noticed though is that there seems to be no database cleanup that occurs when the application is undeployed. What I mean is that the Job/Scheduler data seems to stay in the quartz database even though there is no longer a scheduler active.

This is less than ideal and I can imagine with my model the database would get larger than it needed to be with time. Am I missing how to hook-up some clean-up processes? Or does quartz expect us to do the db cleanup manually?

Cheers!

score 7 · Answer 1 · edited Aug 17 '22 at 00:58

I got this issue once, and here is what I did to rectify the issue. This will work for sure but in case it does not then we will have backup of table so you don't have anything to loose while trying this.

Take sql dump of following tables using method mentioned at : Taking backup of single table

    a) QRTZ_CRON_TRIGGERS
    b) QRTZ_SIMPLE_TRIGGERS
    c) QRTZ_TRIGGERS
    d) QRTZ_JOB_DETAILS

Delete data from above tables in sequence as

delete from QRTZ_CRON_TRIGGERS;
delete from QRTZ_SIMPLE_TRIGGERS;
delete from QRTZ_TRIGGERS;
delete from QRTZ_JOB_DETAILS;

Restart your app which will then freshly insert all deleted tasks and related entries in above tables (Provided your app has its logic right).

This is more like starting your app with all the tasks being scheduled for the first time. So you must keep in mind that tasks will behave as if these are freshly inserted.

NOTE: If this does not work then apply the backup you took for tables and try to debug more closely. As of now, I have not seen this method fail.

score 0 · Accepted Answer · answered Jul 17 '13 at 21:37

0

It's definitely not doing any DB cleanup when undeploying the application or shutting down the scheduler. You would have to build some cleanup code during application shutdown (i.e. building some sort of StartupServlet or context listener that would do the cleanup on the destroy() event lifecycle)

answered Jul 17 '13 at 21:37

lanimall

451
2
7

Although you're right about quartz not doing anything, it *cannot* be done during application shutdown. Shutdown could just be followed by startup that reuires the same tables... It should be somewhere after undeployment. – yair Jul 17 '13 at 21:57
Seems like he is using different application versions (V1,V2,etc...) against the same set of quartz tables (which is possible as long as the scheduler names are different...that's why he's naming them V1, V2, etc...). Hence, the cleanup does not have to be a cleanup of the tables...Just a cleanup of the scheduler related rows (tables/rows linked to the scheduler name he does not need anymore)...hence such cleanup (if written) should be do-able at application shutdown... – lanimall Jul 17 '13 at 22:22
(1) different schedulers use different tables. Otherwise `XXscheduler_V1` might execute jobs of `YYscheduler_V2`. The tables then should be dropped, not rows deleted. (2) application shutdown is *not* application undeployment. – yair Jul 17 '13 at 22:51
Thank you for your anwsers lanimall & yair, I was hoping the rows would get deleted for me /recreated on a need be bases. I will write our own cleanup code for older versions, on startup as suggested (on shutdown probably not suitable in a load balanced env.). – vica Jul 18 '13 at 00:54
@vica welcome to stack. if you accept my suggestion to do it on startup you should [accept my answer](http://stackoverflow.com/help/accepted-answer) (sorry lanimall, it's only fair). On the other hand, if both our answers were useful for you, you should upvote them both (regardless of which answer is accepted). Note the tooltip on the upvote button when hovering over it. – yair Jul 18 '13 at 01:11
@yair - Both answers were useful to me, so I accepted lanimall's as he is on much lower reputation than you are and from the comments he understood the model I am trying to maintain and tailored his answer to that better. Unfortunately I don't have enough reputation yet to 'upvote' anwsers. If I could have, I would have. Sorry, and thank you once again for your help. – vica Jul 18 '13 at 02:31
@vica decision is totally up to you. And welcome again to stack (most users are nicer than me ;) ). – yair Jul 18 '13 at 08:48
Thanks for the welcoming present! :) @yair I still want to point out that actually 2 schedulers (or more) on same application or different applications can be using the same database backend (as long as ached name is unique): that's why you'll notice the column "SCHED_NAME" on all the quartz tables (SCHED_NAME column is a sort of identifier column). That's exactly how XXscheduler_V1 does not execute jobs from YYscheduler_V2. I've personally tested it, and similar answer can be found at http://goo.gl/dH56C for example. Glad my answer helped. later. – lanimall Jul 19 '13 at 00:31

score 0 · Answer 3 · answered Jul 17 '13 at 21:55

You're not missing anything.

However, these quartz tables aren't different from any applicative DB objects you use in you data model. You add Employees table and in a later version you don't need it anymore. Who's responsible for deleting the old table? Only you. If you habe a DBA you might roll it on the DBA ;).

This kind of maintenance would typically be done using an uninstall script / wizard, upgrade script / wizard, or during the first startup of the application in its new version.

On a side note, typically different applications use different databases, or different schemas for the least, thus reducing inter-dependencies.

score 0 · Answer 4 · answered Jan 27 '20 at 14:57

To clean Quartz Scheduler internal data one needs more SQL:

delete from QRTZ_CRON_TRIGGERS;
delete from QRTZ_SIMPLE_TRIGGERS;
delete from QRTZ_TRIGGERS;
delete from QRTZ_JOB_DETAILS;
delete from QRTZ_FIRED_TRIGGERS;
delete from QRTZ_LOCKS;
delete from QRTZ_SCHEDULER_STATE;

Quartz JDBC Job Store - Maintenance/Cleanup

4 Answers4