Server Manager - Update Database - Fatal Error - Central base fails to update - duplicate key value violates unique constraint efp traninf idx
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
2. 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 :
1. Identify the duplicated rows
```sql
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
2. Update the duplicated line.
```sql
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
3. Insert the corrected lines
```sql
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
4. Remove the wrong rows
```sql
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)
3. If there are no rows, there is no duplication in the table DSS\_OBJECT\_INFO.
2. Check duplication in the DSS\_OBJECT\_INFO
1. Run the following query :
```sql
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
- If the query returns at least one row, it means there is at least one duplication. Follow the section DSS_LINK_INFO - Remove duplication
- 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
Related Pages
Compute snapshot for this error due to duplicated objects: CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint uidx - efp - objects -statuses - duplicated objects
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