How To Restore Resolution Date after Bulk Update with Script Runner

cdemattio January 23, 2018

Hi,

I recently used the Script Runner to bulk edit the Resolutions on a bunch of my issues.  When I ran the script, I was unaware that it also changed the Resolution Date of the issues, so now all of my Resolution Dates are messed up.  Is there any way to restore the previous Resolution Date using Script Runner?

NOTE: I have already explored the ChangeHistoryManager in the Script Runner console.  Unfortunately, it looks like updates to the Resolution made via the Script Runner bulk update do not appear in the change history.  Changes to the resolution that take place due to workflow activities do show up in the change history.

Thanks in advance for the help!

1 answer

1 vote
Alexey Matveev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 23, 2018

If you have backups you can restore your system. If you have backups but you can not restore your system in production then you could restore it to another server. Then write a script to put all resolution dates into a file for the issues which were bulk edited. Then in production you could wirte a script which would read the file and update resolution dates. 

Maybe you could use csv import to import the file. But I doubt you can change resolution date with csv import.

cjdemattio January 24, 2018

Thank you for the suggestion Alexey.  We do have backups, however, since I didn't discover the problem for a few days, I can't do a wholesale restore for fear of losing information posted since I made my changes.

Fortuntely, we do have a staging server who's last refresh took place just _before_ I made my updates to the resolutions.  So I do have a way to at least recover what the values used to be.  As a last resort, I may have to do what you suggested and iterate over that spreadsheet to push back to the old resolutions dates.

Before I do that, I'm really hoping that somebody from Adaptavist is going to chime in and tell me how to find the old resolution date in some changelog buried deep in JIRA.

Ameya August 22, 2019

The Fix is for Jira DC. We had issues that were affected in a critical project. Please test the same on lower environment before doing it Live.

The Steps:

 

  1. Spin up a copy of the Jira database backup from the latest version 
  2. Export the id and resolutiondate values to a CSV file:
    \copy id, resolutiondate FROM jiraissue WHERE project = "ID" AND resolutiondate IS NOT NULL) to '/tmp/output-dev.csv' with csv; 
  1. Manually add a header of id and resolutiondate to the CSV.
  2. Back up the potentially affected issues in the jiraissue table:
    \copy id, resolutiondate FROM jiraissue WHERE project = "PROJECT_ID" AND resolutiondate IS NOT NULL) to '/tmp/prod-backup.csv' with csv; 
  1. Create a temporary table in Jira to house the updated value:
    CREATE TABLE tmp_issues (id numeric(18,0), resolutiondate timestamptz); 
  1. Import issues from the CSV generated in step 2 to the table we just created:
    \copy tmp_issues(id, resolutiondate) FROM '/path/to/output-dev.csv' DELIMITER ',' CSV HEADER; 
  1. Confirm this data looks correct:
    SELECT * FROM tmp_issues limit 50;
    <two columns with id and resolutiondate>
    \d tmp_issues
                   Table "public.tmp_issues"
         Column     |           Type           | Modifiers 
    ----------------+--------------------------+----------- id             | numeric(18,0)            | 
     resolutiondate | timestamp with time zone | 
  1. Update the issues using our temp table:
    UPDATE jiraissue SET resolutiondate = tmp_issues.resolutiondate FROM tmp_issues WHERE jiraissue.id = tmp_issues.id; 
  1. Once this finishes, perform a rolling restart of Jira.
  2. Drop temporary table:
    DROP TABLE tmp_issues; 
  1. Clean up jiraissue table:
    VACUUM jiraissue; 
  2. Reindex the AFFECTED PROJECT
Ameya August 25, 2019

This issue happened with me in a critical project. The solution is for Jira DC 7.13 and PostgreSQL. Please try the same on Lower environment before doing it Live. It works.

The steps are:

  1. Spin up a copy of the Jira database backup from the latest version before the issue occured
  2. Export the id and resolutiondate values to a CSV file:
    \copy id, resolutiondate FROM jiraissue WHERE project = "PROJECT_ID" AND resolutiondate IS NOT NULL) to '/tmp/output-dev.csv' with csv; 
  1. Manually add a header of id and resolutiondate to the CSV.
  2. Back up the potentially affected issues in the jiraissue table:
    \copy id, resolutiondate FROM jiraissue WHERE project = "PROJECT_ID" AND resolutiondate IS NOT NULL) to '/tmp/prod-backup.csv' with csv; 
  1. Create a temporary table in Jira DB to house the updated value:
    CREATE TABLE tmp_issues (id numeric(18,0), resolutiondate timestamptz); 
  1. Import issues from the CSV generated in step 2 to the table we just created:
    \copy tmp_issues(id, resolutiondate) FROM '/path/to/output-dev.csv' DELIMITER ',' CSV HEADER; 
  1. Confirm this data looks correct:
    SELECT * FROM tmp_issues limit 50;
    <two columns with id and resolutiondate>
    \d tmp_issues
                   Table "public.tmp_issues"
         Column     |           Type           | Modifiers 
    ----------------+--------------------------+----------- id             | numeric(18,0)            | 
     resolutiondate | timestamp with time zone | 
  1. Update the issues using our temp table:
    UPDATE jiraissue SET resolutiondate = tmp_issues.resolutiondate FROM tmp_issues WHERE jiraissue.id = tmp_issues.id; 
  1. Once this finishes, perform a rolling restart of Jira.
  2. Drop temporary table:
    DROP TABLE tmp_issues; 
  1. Clean up jiraissue table:
    VACUUM jiraissue; 
  1. Perform a project reindex of Affected project.

Suggest an answer

Log in or Sign up to answer