Restoring Zephyr tests data for a single project through the database

JIRA 7.3.1

Zephyr for JIRA 3.2.2.32202654

database is MySql


I needed to restore a single project from a one week backup. The Zephyr test steps and executions were not restored.

This is , apparently, the expected behaviour because Zephyr does not support a single project restore. Unfortunately, we did not have any other choice but to restore a single project and henced forced to find ways to restore the missing test data through the database.


We imported the database tables that are declared by JIRA as the tables of this plugin (full list below). The data was imported but now the test execution were not correctly linked to the test issues. If you open the screen of test cycles summary, then each executed test shows the following information:

ID=invalid

Status= correct status (Pass or Fail)

Summary= a number which is the id of the test issue (ie, 107189) - we checked and these are correct ids.

Other fields are displayed correctly: Executed By, Executed on, etc....


Any idea on what we need to do more so that Zephyr will process correctly the ID? It's clear that the info is there but somehow broken?


The list of restored tables:

 

AO_7DEABF_CHANGE_ZJEGROUP
AO_7DEABF_SCHEDULE_DEFECT
AO_7DEABF_ZQLFILTER
AO_7DEABF_COLUMN_LAYOUT_ITEM
AO_7DEABF_ZFJCOLUMN_LAYOUT
AO_7DEABF_STEP_DEFECT
AO_7DEABF_ZQLFAVORITE_ASOC
AO_7DEABF_CHANGE_ZJEITEM
AO_7DEABF_EXEC_CLUSTER_MESSAGE
AO_7DEABF_ZQLSHARE_PERMISSIONS
AO_7DEABF_CYCLE
AO_7DEABF_STEP_RESULT
AO_7DEABF_TESTSTEP
AO_7DEABF_SCHEDULE
AO_7DEABF_ATTACHMENT




1 answer

1 accepted

Hi,

 

As we solved that I will share the information here, for anybody in the same situation.

 

The problem is caused by this:

Zephyr data is linked to the correlating JIRA issue through a field called ISSUE_ID. This field referrs to the field ID in the JIRA table ISSUENUM.

Bad luck: when restoring a single project through JIRA's restore tools, JIRA gives issues new IDs. The result is that if you import Zephy tables from your backup database, the ISSUE_ID field in Zephyr tables point into 'ghost' IDs that no longer exist in the JIRA ISSUENUM table.

To fix that you need to mount have access to the old and current data base. Assuming you resatored your Zephyr tables from the old data base to the new database, you now need to run a script that 'fixes' the ghost ISSUE_ID in the Zephyr tables.

 

Basically doing the following:

// step 1: in the backup db, find what was was is the issue key (ie= PROJ-899 ) of the ghost ISSUE_ID
// step 2: in the prod db, find what is the current issue id for this issue key 
// step 3: in the prod db, replace all occurances in AO_7DEABF_* tables, column ISSUE_ID, where issue id is the ghost id, and replace it with the current issue id

A couple of key queries to use in the process:

 

/// Finding the issue key for an issue ID
/// the pkey of issue_id 110567 is AR-1090
mysql> select p.pkey,j.issuenum from project p,jiraissue j where p.id=j.project and p.pkey='AR' and j.ID=110567;
+------+----------+
| pkey | issuenum |
+------+----------+
| AR | 1090 |
+------+----------+
1 row in set (0.00 sec)

/// Finding the issue ID from an issue Key
mysql> select p.pkey,j.issuenum,j.id from project p,jiraissue j where p.id=j.project and j.issuenum=1090;
+------+----------+--------+
| pkey | issuenum | id |
+------+----------+--------+
| AR | 1090 | 110567 |
+------+----------+--------+
1 row in set (0.00 sec)

 

Thanks Rina for sharing this. If you have this scripted, could you share the script please.

Hello,

 

No- unfortunately I do no longer have access to this script

Thanks Rina for getting back.

 

Dev

no problem, if you need more details on that we can have a short chat where I can explain further. you can contact me at rina.nir@radbee.com

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,304 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot