Quartz Scheduler Migration from 1.6.3 to 2.1.6

I recently upgraded my app, www.tiemyfinger.com to use Quartz 2.1.6.  Prior to the upgrade it used version 1.6.3.  The Migration Guide provided by Quartz does a good job of listing the changes and necessary steps that need to be taken to complete the migration.  I did run into a couple of issues and here I document what I did to overcome them.  

Update the provided SQL script.

The Quartz team provides a SQL script that makes the necessary database changes, but I was unable to run it as provided.  I made a couple of changes and the following SQL script correctly updates a MySQL database.

--
-- DROP TABLES THAT ARE NO LONGER USED
--
DROP TABLE QRTZ_JOB_LISTENERS;
DROP TABLE QRTZ_TRIGGER_LISTENERS;
--
-- DROP COLUMNS THAT ARE NO LONGER USED
--
ALTER TABLE QRTZ_JOB_DETAILS DROP COLUMN IS_VOLATILE;
ALTER TABLE QRTZ_TRIGGERS DROP COLUMN IS_VOLATILE;
ALTER TABLE QRTZ_FIRED_TRIGGERS DROP COLUMN IS_VOLATILE;
--
-- ADD NEW COLUMNS THAT REPLACE THE 'IS_STATEFUL' COLUMN
--
ALTER TABLE QRTZ_JOB_DETAILS ADD COLUMN IS_NONCONCURRENT BOOL;
ALTER TABLE QRTZ_JOB_DETAILS ADD COLUMN IS_UPDATE_DATA BOOL;
UPDATE QRTZ_JOB_DETAILS SET IS_NONCONCURRENT = IS_STATEFUL;
UPDATE QRTZ_JOB_DETAILS SET IS_UPDATE_DATA = IS_STATEFUL;
ALTER TABLE QRTZ_JOB_DETAILS DROP COLUMN IS_STATEFUL;
ALTER TABLE QRTZ_FIRED_TRIGGERS ADD COLUMN IS_NONCONCURRENT BOOL;
ALTER TABLE QRTZ_FIRED_TRIGGERS ADD COLUMN IS_UPDATE_DATA BOOL;
UPDATE QRTZ_FIRED_TRIGGERS SET IS_NONCONCURRENT = IS_STATEFUL;
UPDATE QRTZ_FIRED_TRIGGERS SET IS_UPDATE_DATA = IS_STATEFUL;
ALTER TABLE QRTZ_FIRED_TRIGGERS DROP COLUMN IS_STATEFUL;
--
-- ADD NEW 'SCHED_NAME' COLUMN TO ALL TABLES
--
ALTER TABLE QRTZ_BLOB_TRIGGERS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
ALTER TABLE QRTZ_CALENDARS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
ALTER TABLE QRTZ_CRON_TRIGGERS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
ALTER TABLE QRTZ_FIRED_TRIGGERS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
ALTER TABLE QRTZ_JOB_DETAILS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
ALTER TABLE QRTZ_LOCKS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
ALTER TABLE QRTZ_PAUSED_TRIGGER_GRPS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
ALTER TABLE QRTZ_SCHEDULER_STATE ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
ALTER TABLE QRTZ_SIMPLE_TRIGGERS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
ALTER TABLE QRTZ_TRIGGERS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'TESTSCHEDULER';
--
-- DROP ALL PRIMARY AND FOREIGN KEY CONSTRAINTS, SO THAT WE CAN DEFINE NEW ONES
--
ALTER TABLE QRTZ_TRIGGERS DROP FOREIGN KEY QRTZ_TRIGGERS_ibfk_1;
ALTER TABLE QRTZ_BLOB_TRIGGERS DROP PRIMARY KEY;
ALTER TABLE QRTZ_BLOB_TRIGGERS DROP FOREIGN KEY QRTZ_BLOB_TRIGGERS_ibfk_1;
ALTER TABLE QRTZ_SIMPLE_TRIGGERS DROP PRIMARY KEY;
ALTER TABLE QRTZ_SIMPLE_TRIGGERS DROP FOREIGN KEY QRTZ_SIMPLE_TRIGGERS_ibfk_1;
ALTER TABLE QRTZ_CRON_TRIGGERS DROP PRIMARY KEY;
ALTER TABLE QRTZ_CRON_TRIGGERS DROP FOREIGN KEY QRTZ_CRON_TRIGGERS_ibfk_1;
ALTER TABLE QRTZ_JOB_DETAILS DROP PRIMARY KEY;
ALTER TABLE QRTZ_JOB_DETAILS ADD PRIMARY KEY (SCHED_NAME, JOB_NAME, JOB_GROUP);
ALTER TABLE QRTZ_TRIGGERS DROP PRIMARY KEY;
--
-- ADD ALL PRIMARY AND FOREIGN KEY CONSTRAINTS, BASED ON NEW COLUMNS
--
ALTER TABLE QRTZ_TRIGGERS ADD PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_TRIGGERS ADD FOREIGN KEY (SCHED_NAME, JOB_NAME, JOB_GROUP) REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME, JOB_NAME, JOB_GROUP);
ALTER TABLE QRTZ_BLOB_TRIGGERS ADD PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_BLOB_TRIGGERS ADD FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_CRON_TRIGGERS ADD PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_CRON_TRIGGERS ADD FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_SIMPLE_TRIGGERS ADD PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_SIMPLE_TRIGGERS ADD FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_FIRED_TRIGGERS DROP PRIMARY KEY;
ALTER TABLE QRTZ_FIRED_TRIGGERS ADD PRIMARY KEY (SCHED_NAME, ENTRY_ID);
ALTER TABLE QRTZ_CALENDARS DROP PRIMARY KEY;
ALTER TABLE QRTZ_CALENDARS ADD PRIMARY KEY (SCHED_NAME, CALENDAR_NAME);
ALTER TABLE QRTZ_LOCKS DROP PRIMARY KEY;
ALTER TABLE QRTZ_LOCKS ADD PRIMARY KEY (SCHED_NAME, LOCK_NAME);
ALTER TABLE QRTZ_PAUSED_TRIGGER_GRPS DROP PRIMARY KEY;
ALTER TABLE QRTZ_PAUSED_TRIGGER_GRPS ADD PRIMARY KEY (SCHED_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_SCHEDULER_STATE DROP PRIMARY KEY;
ALTER TABLE QRTZ_SCHEDULER_STATE ADD PRIMARY KEY (SCHED_NAME, INSTANCE_NAME);
--
-- ADD NEW SIMPROP_TRIGGERS TABLE
--
CREATE TABLE QRTZ_SIMPROP_TRIGGERS
 (          
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    STR_PROP_1 VARCHAR(512) NULL,
    STR_PROP_2 VARCHAR(512) NULL,
    STR_PROP_3 VARCHAR(512) NULL,
    INT_PROP_1 INT NULL,
    INT_PROP_2 INT NULL,
    LONG_PROP_1 BIGINT NULL,
    LONG_PROP_2 BIGINT NULL,
    DEC_PROP_1 NUMERIC(13,4) NULL,
    DEC_PROP_2 NUMERIC(13,4) NULL,
    BOOL_PROP_1 BOOL NULL,
    BOOL_PROP_2 BOOL NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
    REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
--
-- CREATE INDEXES FOR FASTER QUERIES
--
CREATE INDEX IDX_QRTZ_J_REQ_RECOVERY ON QRTZ_JOB_DETAILS(SCHED_NAME,REQUESTS_RECOVERY);
CREATE INDEX IDX_QRTZ_J_GRP ON QRTZ_JOB_DETAILS(SCHED_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_T_J ON QRTZ_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_T_JG ON QRTZ_TRIGGERS(SCHED_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_T_C ON QRTZ_TRIGGERS(SCHED_NAME,CALENDAR_NAME);
CREATE INDEX IDX_QRTZ_T_G ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP);
CREATE INDEX IDX_QRTZ_T_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_T_N_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_T_N_G_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_T_NEXT_FIRE_TIME ON QRTZ_TRIGGERS(SCHED_NAME,NEXT_FIRE_TIME);
CREATE INDEX IDX_QRTZ_T_NFT_ST ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME);
CREATE INDEX IDX_QRTZ_T_NFT_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME);
CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE_GRP ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_FT_TRIG_INST_NAME ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME);
CREATE INDEX IDX_QRTZ_FT_INST_JOB_REQ_RCVRY ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);
CREATE INDEX IDX_QRTZ_FT_J_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_FT_JG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_FT_T_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);
CREATE INDEX IDX_QRTZ_FT_TG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_GROUP);

Reschedule Existing Jobs

After making the necessary code and schema changes I found that none of my existing jobs were scheduled for execution.  So, I wrote a little program to retrieve and schedule all existing jobs.  The program was about 10 lines of code.  I would post the code here, but it uses the services of my application and is probably useless for other apps.

Besides these issues the Migration is relatively easy and Quartz still works as expected.

0 notes