Upgrade to 6.3 Fails Due to Unable to create the Quartz trigger
By running the following SQL queries against the JIRA database:
SELECT * FROM qrtz_triggers ORDER BY next_fire ASC;
SELECT trigger_id,cronExperssion,trigger_name,trigger_group,next_fire FROM qrtz_cron_triggers qc INNER JOIN qrtz_triggers qt on qc.trigger_id = qt.id ORDER BY next_fire ASC;
These two queries help us to identify any tasks that have next_fire dates that are set in the past that exist in the qrtz_triggers and qrtz_cron_triggers tables
We see output similar to the following example:
|13210||"0 08 10 04 10 ? 2013"||"SUBSCRIPTION_13209"||"SEND_SUBSCRIPTION"||"2011-10-04 10:08:00-05"|
|13211||"0 06 10 07 10 ? 2013"||"SUBSCRIPTION_13210"||"SEND_SUBSCRIPTION"||"2013-10-07 10:06:00-05"|
|13212||"0 0 10 16 10 ? 2013"||"SUBSCRIPTION_13211"||"SEND_SUBSCRIPTION"||"2014-10-16 10:00:00-05"|
As seen in the table above, the entries with trigger_id 13210 and 13211 are set to fire (next_fire) in the past, given that the query was run 2014-10-15. These are problematic entries in the database which need to be removed manually. You will have separate results for each of the two queries below and you will need to note the SQL ID's for the invalid tasks and utilize them in the deletion queries to remove the invalid tasks.
Before making any changes to the JIRA database, always be sure to create a backup. If possible, utilize a testing/staging instance of JIRA to run through these steps before attempting them on a production/live instance of JIRA.
All SQL queries in this article are written for PostgreSQL and may require modification for other database platforms.
Please see our documentation on backing up JIRA data for detailed instructions.
Removing Problematic Trigger Entries
For each bad trigger identified, you will run the following SQL query for each trigger_id:
Please ensure a recent database backup exists before running any SQL statements to directly modify data. If possible, utilize a testing/staging instance of JIRA to verify database changes before tampering with a live/staging instance of JIRA.
Replace <bad_trigger_id> with the ID's that you identified from the previous queries. For instance in the previous example we noted that 13210 and 13211 were bad and that data was the result of the first query on the qrtz_triggers table. We would delete those ID's using the first query below. The results of the second query would correspond with the second query here.
delete FROM qrtz_triggers WHERE id = <bad_trigger_id>; delete FROM qrtz_cron_triggers WHERE trigger_id = <bad_trigger_id>;
Once these bad values have been removed from the database restart JIRA and proceed with upgrading JIRA.