mishmashmoo

Recently, I was struck with this error “node not found” upon logging into Quality Center, to be exact, 6 times.
Some investigation revealed what caused the error. This occurs when a folder is being deleted, and while the deletion is taking place, someone moves a folder into the folder under deletion.
This causes a corruption in the QC database, and creates, what we call ‘orphan’ records. That is, records that belong to an invalid father or id.
Luckily, I was able to solve the issue, but not before days of lost testing effort, due to the limited information on this issue, on our friend, the inter-web.

This error was appearing in the Test lab module. It happened 6 times, which means that there are 6 orphan records in the database somewhere. Somewhere being, either a folder or a lose test set.
The database structure of QC seems a little complex, but once you have a fair understanding of the relationships, its quite simple.

Firstly, we need to do a little investigation, so we can find our orphans and give them a home.
This query, will give you the test sets that are not in a folder. They could be in the “unattached” folder, although there may be records in here that do not have any folder at all.

SELECT * 
FROM cycle 
WHERE cy_folder_id NOT IN (SELECT cf_item_id FROM cycl_fold);

The cy_foler_id is the folder that these returned test cycles belong to. That folder, does not exist in the cycl_fold table, which means there is no folder attached.
This is problem 1.

To solve this, create a folder in QC test lab, by logging in, and creating the new folder under “Root”.
For arguments sake, we’ll call the folder “foobar”. We will now need to get the cy_folder_id of this folder we just created.
To get this run this query :

SELECT cf_item_id 
FROM cycl_fold 
WHERE cf_item_name='foobar'

This will return a number, for me it was 41092. Write that number down as we will need it.

Now run this query to move all of the orphan test cases into that folder.

UPDATE cycle SET
cy_folder_id = 41092 
WHERE cy_cycle_id NOT IN (SELECT cf_item_id FROM cycl_fold)

Now that’s done, step 2 is to find any orphan folders.
Try and picture this. Folder a, has 2 sub folders, b & c. This makes folder’s b & c children of folder a.
Folder b&c have sub folders c & d respectively, therefore c&d’s fathers are b&c. If folder A was to dissapear, folders b&c and all their children would be orphans. They would not belong to any parent folder and be a rogue record in a databases with no hierarchical mapping.
This is what creates the Node not found error.
QC’s database uses the father/son relationship for folder structure, and each father is given a number and each son is related to that number if it is their father….

Now that we have that explanation out of the way, we are going to run a query that looks for these folders.

SELECT *
FROM cycl_fold 
WHERE cf_father_id NOT IN (SELECT DISTINCT cf_item_id FROM cycl_fold)
AND cf_father_id !=0

This will give you the folders that are orphans. Now its time to give them a home.

UPDATE cycl_fold
SET cf_father_id = 41092
WHERE cf_father_id NOT IN (SELECT DISTINCT cf_item_id FROM cycl_fold)
AND cf_father_id !=0

That will give the orphan folders a father folder (our foobar folder).
The last step is to either restart QC service, or run a query to update the sequence table.
I opted to update the sequence table.

UPDATE sequences 
SET sq_seq_value = sq_seq_value + 1
WHERE sq_seq_name = 'cycle'

You should now be able to login to QC with no errors.

Enjoy!

admin quality center quality center, sql