Massively undo changes to issue statuses in Redmine 3
So, you or somebody else from your team accidentally closed/deleted/opened or something else several hunderds of issues. What to do now? How to quickly recover from such problem?
Well, unless you have backup of just few mintes ago, and your system is not heavily used, you can follow steps described here...
Good thing with Redmine is that it keeps changes to issues journalled in 2 of it's database tables (journals and journal_details), with structure as below:
CREATE TABLE IF NOT EXISTS `journals` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`journalized_id` int(11) NOT NULL DEFAULT '0',
`journalized_type` varchar(30) NOT NULL DEFAULT '',
`user_id` int(11) NOT NULL DEFAULT '0',
`notes` text,
`created_on` datetime NOT NULL,
`private_notes` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `journals_journalized_id` (`journalized_id`,`journalized_type`),
KEY `index_journals_on_user_id` (`user_id`),
KEY `index_journals_on_journalized_id` (`journalized_id`),
KEY `index_journals_on_created_on` (`created_on`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `journal_details` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`journal_id` int(11) NOT NULL DEFAULT '0',
`property` varchar(30) NOT NULL DEFAULT '',
`prop_key` varchar(30) NOT NULL DEFAULT '',
`old_value` text,
`value` text,
PRIMARY KEY (`id`),
KEY `journal_details_journal_id` (`journal_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
Where journalized_id is an ID of issue on which change was performed, and old_value was value before the change.
For example, we want to revert all issues which were in status "new" but are now "closed", back to "new" status, and delete journal entries so it won't confuse users.
First we want to findout journal entry by the date when incident with issue updating occured, and we can do it with following query (I have used 2016-12-08 as example date):
select * from journals where created_on>'2016-12-08 05:34:00' order by id desc
And in my case it returns around 200 rows:
On the image above, we can identify that journal ID we need is 2100, which we will now use in following query to find all issues whose original status was 1 (new), through following query:
select * from journal_details where old_value=1 and journal_id>2100
Which will list all those issues, now we will left-join this 2 queries and get a nice display of changed issues, like this:
select * from journal_details left join journals on (journal_details.journal_id=journals.id) where old_value=1 and journal_id>2100
Which gives results like below:
Next thing we are going to create temporary table, based on above query with following command:
CREATE TEMPORARY TABLE IF NOT EXISTS issuerec AS (select journal_details.id as jdid, journalized_id from journal_details left join journals on (journal_details.journal_id=journals.id) where old_value=1 and journal_id>2100)
And now, we will use that temporaty table to change issue status on issues page, and update journal!
update issues set closed_on=NULL, status_id=1 where id in (
select journalized_id from issuerec)
Now delete trace from journal_details so we don't confuse Redmine users
delete from journal_details where old_value=1 and journal_id>2100
And that's it.
To prevent issues like this occuring, you need to make sure that your users have proper permissions so they can't edit/close other people's issues...
