Problem Description

This page outlines the issue with the Central base upgrade which fails while migrating using Server Manager.

Error	MODULMSG ; Job execution	SQL Error: ERROR:  duplicate key value violates unique constraint "efp_traninf_idx".
Error	MODULMSG ; Job execution	SQL Error: DETAIL:  Key (snapshot_id, object_id)=(26, 669831) already exists..
Error	MODULMSG ; Job execution	SQL Error: CONTEXT:  SQL statement "Insert into EFP_Tran_Info.
Error	MODULMSG ; Job execution	SQL Error: 		( Snapshot_ID, Object_ID, Object_Checksum ).
Error	MODULMSG ; Job execution	SQL Error: 	Select.
Error	MODULMSG ; Job execution	SQL Error: 		T.SNAPSHOT_ID, T.OBJECT_ID, F.OBJECT_CHECKSUM.
Error	MODULMSG ; Job execution	SQL Error: 	From.
Error	MODULMSG ; Job execution	SQL Error: 		DSS_OBJECT_INFO F, DSS_LINK_INFO L, DSS_OBJECT_INFO T.
Error	MODULMSG ; Job execution	SQL Error: 	Where.
Error	MODULMSG ; Job execution	SQL Error: 		T.SNAPSHOT_ID				= I_SNAPSHOT_ID.
Error	MODULMSG ; Job execution	SQL Error: 		And F.SNAPSHOT_ID			= T.SNAPSHOT_ID.
Error	MODULMSG ; Job execution	SQL Error: 		And L.SNAPSHOT_ID			= T.SNAPSHOT_ID.
Error	MODULMSG ; Job execution	SQL Error: 		And T.OBJECT_TYPE_ID		= 30002.
Error	MODULMSG ; Job execution	SQL Error: 		And L.LINK_TYPE_ID			= 11002.
Error	MODULMSG ; Job execution	SQL Error: 		And L.PREVIOUS_OBJECT_ID	= T.OBJECT_ID.
Error	MODULMSG ; Job execution	SQL Error: 		And L.NEXT_OBJECT_ID		= F.OBJECT_ID.
Error	MODULMSG ; Job execution	SQL Error: 		And not exists (select 1 from EFP_Tran_Exclusion E where E.Object_ID = F.OBJECT_ID)".
Error	MODULMSG ; Job execution	SQL Error: PL/pgSQL function apmcfp_compute_tran_checksum(integer) line 14 at SQL statement.
Error	MODULMSG ; Job execution	SQL Error: SQL statement "SELECT APMCFP_COMPUTE_TRAN_CHECKSUM (I_SNAPSHOT_ID)".
Error	MODULMSG ; Job execution	SQL Error: PL/pgSQL function apmcfp_compute_efp_checksum(integer) line 6 at PERFORM.
Error	MODULMSG ; Job execution	SQL Error: PL/pgSQL function mig_efp_checksum() line 26 at assignment (Severity 1, Msg No 1).
Error	MODULMSG ; Job execution	<CAST INSTALLATION FOLDER>\installscripts\caststorageservice\efp_central\MIGR\V81\Mig_Compute_Checksum.sql

The issue is raised due to an inconsistant and duplicate data in the table DSS_OBJECT_INFO for object type 30002 (APM_IFPUG_UserTransaction) or object type 30001 (APM_IFPUG_DataFunction).

Observed in CAST AIP

elease	Yes/No
8.3.x

Observed on RDBMS

RDBMS	Yes/No
CSS

Step by Step Scenario

Execute Servman
Upgrade the Central database.
Central base failed with the mentioned error.

Action Plan

This issue occurs because of duplication in the CENTRAL database in the table DSS_OBJECT_INFO or in the table DSS_LINK_INFO. In the Central Database, before migration, follow the below steps.

Check the duplication in the DSS_OBJECT_INFO

Run the following query :

SELECT doi.snapshot_id, 
       doi.object_id 
FROM   dss_object_info doi 
WHERE  doi.object_type_id IN ( 30002, 30001 ) 
GROUP  BY doi.snapshot_id, 
          doi.object_id 
HAVING Count(1) > 1

If the query returns at least one row, it means that there is at least one duplication.The solution is to remove the duplicate object on the central base before starting the migration.To remove the duplication, do as follows :

Identify the duplicated rows

select doi.SNAPSHOT_ID,
      doi.OBJECT_ID
  from DSS_OBJECT_INFO doi
 where doi.OBJECT_TYPE_ID in (30002, 30001)
 group by doi.SNAPSHOT_ID, doi.OBJECT_ID
having count(1) > 1

Update the duplicated line.

UPDATE dss_object_info doid 
SET    snapshot_id = snapshot_id * ( -1 ) 
WHERE  ( doid.snapshot_id, doid.object_id ) IN (SELECT doi.snapshot_id, 
                                                       doi.object_id 
                                                FROM   dss_object_info doi 
                                                WHERE 
              doi.object_type_id IN ( 30002, 30001 ) 
                                                GROUP  BY doi.snapshot_id, 
                                                          doi.object_id 
                                                HAVING Count(1) > 1)

Result of the update is

Insert the corrected lines

INSERT INTO dss_object_info 
SELECT ( -1 ) * snapshot_id, 
       object_id, 
       object_type_id, 
       Max(object_checksum) 
FROM   dss_object_info 
WHERE  snapshot_id < 0 
       AND ( snapshot_id, object_id ) IN (SELECT doi.snapshot_id, 
                                                 doi.object_id 
                                          FROM   dss_object_info doi 
                                          WHERE 
           doi.object_type_id IN ( 30002, 30001 ) 
                                          GROUP  BY doi.snapshot_id, 
                                                    doi.object_id 
                                          HAVING Count(1) > 1) 
group by snapshot_id, 
		 object_id, 
		 object_type_id

Remove the wrong rows

DELETE FROM dss_object_info 
WHERE  snapshot_id < 0 
       AND ( snapshot_id, object_id ) IN (SELECT doi.snapshot_id, 
                                                 doi.object_id 
                                          FROM   dss_object_info doi 
                                          WHERE 
           doi.object_type_id IN ( 30002, 30001 ) 
                                          GROUP  BY doi.snapshot_id, 
                                                    doi.object_id 
                                          HAVING Count(1) > 1)

If there are no rows, there is no duplication in the table DSS_OBJECT_INFO.

Check duplication in the DSS_OBJECT_INFO
1. Run the following query :
```
 SELECT previous_object_id, 
       snapshot_id, 
       link_type_id, 
       Count(1) 
FROM   dss_link_info 
WHERE  link_type_id = 11002 
GROUP  BY previous_object_id, 
          snapshot_id, 
          link_type_id 
HAVING Count(next_object_id) > 1 
```
2. If the query returns at least one row, it means there is at least one duplication. Follow the section DSS_LINK_INFO - Remove duplication
3. If there are no rows, there is no duplication in the table DDS_LINK_INFO
Once the duplication have been removed, execute the migration again.

DSS_LINK_INFO - Remove duplication

The queries below are to remove duplication in the dss_link_info table if needed based on the above action plan. These queries will return no significant information and the queries need to be followed in order to make sure that the duplication is removed.

set search_path to <YOUR CENTRAL DB>;

CREATE TABLE _sav_dss_link_info_11002 AS 
  SELECT * 
  FROM   dss_link_info 
  WHERE  link_type_id = 11002

DELETE 
FROM   <your central db>.dss_link_info cdli 
WHERE  ( 
              previous_object_id, next_object_id, link_type_id) IN 
       ( 
              SELECT cdli.previous_object_id, 
                     cdli.next_object_id, 
                     cdli.link_type_id 
              FROM   <your central db>.dss_link_info cdli 
              JOIN   <your central db>.dss_translation_table dtt1 
              ON     cdli.previous_object_id = dtt1.object_id 
              JOIN   <your local db>.dss_links ldl 
              ON     ldl.previous_object_id = dtt1.site_object_id 
              JOIN   <your central db>.dss_translation_table dtt2 
              ON     ldl.next_object_id = dtt2.site_object_id 
              AND    dtt2.object_id <> cdli.next_object_id 
              WHERE  cdli.link_type_id = 11002 
              AND    ldl.link_type_id = 11002 
              AND    cdli.previous_object_id IN 
                     ( 
                              SELECT   previous_object_id 
                              FROM     dss_link_info 
                              WHERE    link_type_id = 11002 
                              GROUP BY previous_object_id, 
                                       snapshot_id 
                              HAVING   count(1)>1) )

DELETE 
FROM   <your central db>.dss_links cdli 
WHERE  ( 
              previous_object_id, next_object_id, link_type_id) IN 
       ( 
              SELECT cdli.previous_object_id, 
                     cdli.next_object_id, 
                     cdli.link_type_id 
              FROM   <your central db>.dss_link_info cdli 
              JOIN   <your central db>.dss_translation_table dtt1 
              ON     cdli.previous_object_id = dtt1.object_id 
              JOIN   <your local db>.dss_links ldl 
              ON     ldl.previous_object_id = dtt1.site_object_id 
              JOIN   <your central db>.dss_translation_table dtt2 
              ON     ldl.next_object_id = dtt2.site_object_id 
              AND    dtt2.object_id <> cdli.next_object_id 
              WHERE  cdli.link_type_id = 11002 
              AND    ldl.link_type_id = 11002 
              AND    cdli.previous_object_id IN 
                     ( 
                              SELECT   previous_object_id 
                              FROM     dss_link_info 
                              WHERE    link_type_id = 11002 
                              GROUP BY previous_object_id, 
                                       snapshot_id 
                              HAVING   count(1)>1) )

----CREATE A WORK TABLE WITH LAST SNAPSHOT
 
set search_path to <your central db>
 CREATE TABLE _temp_dss_link_info1 AS 
  SELECT * 
  FROM   dss_link_info 
  WHERE  ( previous_object_id, snapshot_id, link_type_id ) IN 
         (SELECT previous_object_id, 
                 Max(snapshot_id), 
                 link_type_id 
          FROM   dss_link_info 
          WHERE 
                link_type_id = 11002 
                GROUP  BY previous_object_id, 
                          link_type_id) 
         AND previous_object_id IN (SELECT previous_object_id 
                                    FROM   dss_link_info 
                                    WHERE  link_type_id = 11002 
                                    GROUP  BY previous_object_id, 
                                              snapshot_id 
                                    HAVING Count(1) > 1)

set search_path to <your central db>
 CREATE TABLE _temp_dss_link_info2 AS 
  SELECT * 
  FROM   dss_link_info 
  WHERE  ( previous_object_id, next_object_id, link_type_id ) IN 
         (SELECT previous_object_id, 
                 next_object_id, 
                 link_type_id 
          FROM   _temp_dss_link_info1 
          WHERE 
                link_type_id = 11002) 
         AND previous_object_id IN (SELECT previous_object_id 
                                    FROM   dss_link_info 
                                    WHERE  link_type_id = 11002 
                                    GROUP  BY previous_object_id, 
                                              snapshot_id 
                                    HAVING Count(1) > 1)

set search_path to <your central db>
 
DELETE FROM dss_link_info 
WHERE  link_type_id = 11002 
       AND previous_object_id IN (SELECT previous_object_id 
                                  FROM   dss_link_info 
                                  WHERE  link_type_id = 11002 
                                  GROUP  BY previous_object_id, 
                                            snapshot_id 
                                  HAVING Count(1) > 1);

set search_path to <your central db>
 
insert into dss_link_info
select * from _temp_dss_link_info2;

Impact on Analysis Results and Dashboard

NA

Notes/comments

Ticket # 7666

Compute snapshot for this error due to duplicated datafunctions: CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint uidx_efp_objects_statuses - duplicated datafunctions

Server Manager - Update Database - Fatal Error - Central base fails to update - duplicate key value violates unique constraint efp traninf idx

DSS_LINK_INFO - Remove duplication