Redmine based platforms, and every now and then I get a report about an issue that disappeared under unknown circumstances. Logs show that it is usually caused by someone with too many privileges who accidentally deleted the issue, but that’s another story.
Let’s see how we can restore an accidentally (or not) removed issue. This guide assumes you have both database and filesystem backups that contain the removed issue. The procedure is shown for MySQL, but a similar approach can be used with other databases.
Disclaimer: I have used this method on Redmine 3.2 and 3.3 instances, but I’m not sure if it works on older Redmine versions. Anyways, I take no responsibility for any unexpected consequences, make sure you understand every step and double check before executing anything!
Create a new database, which you will use to restore the backup into:
;
And then import your latest database dump inside it:
$ cat /path/to/dump.sql | mysql -uroot -p restoreissue
Now let’s assume the following parameters:
redmine
is your production databaseRestoring database objects can be achieved by executing the following SQL statements:
SET @ISSUEID = xxx;
INSERT INTO redmine.issues
(SELECT *
FROM restoreissue.issues
WHERE id = @ISSUEID);
INSERT INTO redmine.journals
(SELECT *
FROM restoreissue.journals
WHERE journalized_id = @ISSUEID
AND journalized_type = 'Issue');
INSERT INTO redmine.journal_details
(SELECT d.*
FROM restoreissue.journals j
INNER JOIN restoreissue.journal_details d
ON j.id = d.journal_id
WHERE j.journalized_id = @ISSUEID
AND j.journalized_type = 'Issue');
INSERT INTO redmine.attachments
(SELECT * FROM restoreissue.attachments
WHERE container_id = @ISSUEID
AND container_type = 'Issue');
After running these statements, your issue should be accessible again using a browser.
This step is only required if your issue had files attached.
Let’s assume your Redmine instance is located at /opt/redmine
. Restore /opt/redmine/files
from your backup somewhere else, and copy your attachments to their original location adapting the following commands to your case (e.g. by using scp
from a snapshot of your VM instead of cp
):
cd /your/backup/path
mysql -uroot -p -N -B -e \
"SET @ISSUEID = xxx;
SELECT CONCAT(disk_directory, '/', disk_filename)
FROM restoreissue.attachments
WHERE container_id = @ISSUEID
AND container_type = 'Issue'" | while read F; do
cp -a "$F" /opt/redmine/files/${F}
done
Do not forget to remove your temporary database and your restored files.