Error: "Data Foreign Key Constraint Validation...Data violations are detected on your schema" in Update Readiness Tool

Products

• Data Loss Prevention Enforce

Issue/Introduction

When running the Update Readiness Tool before an upgrade from Symantec Data Loss Prevention 14.6 to 15.0, 15,1, or 15.5, the tool returns results in its log file with the error below. 

 

Start: Data Foreign Key Constraint Validation - [date and time]

Data violations are detected on your schema, please use the below query(s) to retrieve the invalid data.

SELECT DISTINCT protocolFilterId AS "PROTOCOLFILTERID" FROM ENDPOINTPROTOCOLFILTER

WHERE protocolFilterId IS NULL OR protocolFilterId NOT IN (SELECT acv.protocolFilterId FROM

AgentConfigurationVersion acv WHERE acv.protocolFilterId IS NOT NULL);

End : Data Foreign Key Constraint Validation - elapsed 0s - FAILED (1 violation)

Resolution

1. Run the following command to create a data backup:
    
   create table EndpointProtocolFilter_nomatch as
   select * from EndpointProtocolFilter where protocolFilterId not in (select acv.protocolFilterId FROM
   AgentConfigurationVersion acv where acv.protocolFilterId IS NOT NULL);
    
2. Run the following command to confirm the record count:
   
   select count(*) from EndpointProtocolFilter where protocolFilterId not in (select acv.protocolFilterId
FROM AgentConfigurationVersion acv where acv.protocolFilterId IS NOT NULL);
    
3. Note the record count.
4. Run the following command to delete data that causes the upgrade to fail:
   
   DELETE FROM EndpointProtocolFilter WHERE protocolFilterId NOT IN (SELECT acv.protocolFilterId FROM AgentConfigurationVersion acv WHERE acv.protocolFilterId IS NOT NULL);
    
5. Confirm that the number of records deleted matches the record count. See step 3. If the record counts do not match, contact Symantec Support.
6. Run the following command to complete the delete operation:
   
   commit;
    
7. Run the following command to confirm that the number of records match:
   
   select count(*) from EndpointProtocolFilter where protocolFilterId not in (select acv.protocolFilterId
FROM AgentConfigurationVersion acv where acv.protocolFilterId IS NOT NULL);