Restoring Zephyr tests data for a single project through the database

[Rina Nir (AC)]

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

[Rina Nir (AC) · Accepted solution]

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)

[Dev Govindaswamy]

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

[Rina Nir (AC)]

Hello,

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

[Dev Govindaswamy]

Thanks Rina for getting back.

Dev

[Rina Nir (AC)]

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