How to recover the comments viewable by a Project Role which has been deleted

Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.

Scenario

  1. You have a project role named "OLD" which was created some time ago.
  2. Under circumstances, you decide to delete it and replace by a new project role named "NEW", or you have accidentally delete it.
  3. Some users have comment viewable by "OLD" project role on some issues, and now those comments disappear on the issues. (This is the expected behavior, since the project role does not exist anymore)
  4. You want to retrieve the comments and move it to the new project role "NEW"
  5. You know at least one issue's issue-key and comment detail which is related to the project role "OLD". 

 Before you begin:

(info) Always BACKUP your data before you perform any changes in your database and please ensure that you at least have a basic database knowledge before proceeding

Steps to recover the comments:

Step 1:

You should know any issue's issue key which is having this problem. For example, "TEST-1" in this case, then you can run the following query in your MySQL database:

select * from jiraaction where issueid = (select id from jiraissue where pkey = 'TEST-1')

This is for checking the rolelevel for the comment which has been viewable by project role "OLD" (The rolelevel and old comment for "OLD" still exists in your database even after you have deleted the project role) Please see the attachment below:

In this example, the rolelevel is 10101 

Step 2:

Find out the rolelevel from last query and run:

update jiraaction set rolelevel=(select id from projectrole where name='NEW') where rolelevel=10101

This is for updating all the comments which have been restricted viewable by "OLD" to the new project role "NEW".

Was this helpful?

Thanks for your feedback!

Why was this unhelpful?

Have a question about this article?

See questions about this article

Powered by Confluence and Scroll Viewport