<(CM) Support tip> All Related Record search broken

[Ethan_Wang]

Incident description :

Related Record search and All Related Record search are not returning the correct results

Cause                         :  

Root cause to this issue can be due to that the affected records are linked to URI=0 record (non-existing record) in TSRECLINK table.

Resolution                :

Steps to identify and remove the rows that causes the issue from TSRECLINK table :

Assuming the URI of the affected record is XXXX. 

The SQL query below can be used for finding all the related records to record where URI = XXXX :

SELECT * FROM TSRECLINK WHERE (rkRecUri1= XXXX OR rkRecUri2= XXXX );

and it should return something like : 

uri                 rkRecUri1           rkRecUri2           linkNumber rkLinkType

-------------------- -------------------- -------------------- ---------- ----------

URI1             XXXX               BBBB               0         0

URI2               XXXX              0                     0         0

URI3             AAAA               XXXX               0         0

URI4               XXXX              CCCC             0         0

** AAAA, BBBB, CCCC are URIs of Records (TSRECORD.URI)

Related Records search and ALL Related Records searches for record URI = XXXX should have returned the following records :

URI                 RECORDID

-------------------- --------------------------------------------------

AAAA               RecordID1

BBBB               RecordID2

CCCC               RecordID3

The row below is linking Record URI=XXXX to Record Uri =0 (non-existing record ) is causing the issue : 

uri                 rkRecUri1           rkRecUri2           linkNumber rkLinkType

-------------------- -------------------- -------------------- ---------- ----------

URI2               XXXX               0                       0         0

The steps below need to be followed   to get the row that is causing the issue removed from TSRECLINK table :

This should be tested in testing environment against a duplication of affected PROD database before making any change to PROD DB.

1, Back up the database or at least table TSRECLINK.

2, Execute the SQL below to identify the row to be removed :

SELECT * FROM TSRECLINK WHERE ( uri = URI2 AND rkRecUri1 = XXXX AND rkRecUri2 = 0 );

3, Remove the row :

DELETE FROM TSRECLINK WHERE ( uri = URI2 AND rkRecUri1 = XXXX AND rkRecUri2 = 0   );

4, Search again and no row should be returned :

SELECT * FROM TSRECLINK WHERE ( rkRecUri1 = XXXX AND rkRecUri2 = 0 );

5, Restart the CM Client, and then do All related record search for record URI=XXXX again and it should return the list of related records correctly.

 **Please note that,  it is not recommended to manipulate the data in database , hence it is highly recommended that a support case should be raised to get the issue reviewed before any action is taken.