DEFAULT.OrphanProcessJob threw an exception

Products

DX NetOps

Issue/Introduction

Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT /+label(ALARM_ORPHAN_CLEAR_DELETED_LINKED_RULES)/ INTO alarm_clear ( alarm_id, dcm_id, pollgroup_id, item_id, rule_id, severity, start_time, clear_time, alarm_type, profile_id, clear_reason_code ) SELECT a.alarm_id, a.dcm_id, a.pollgroup_id, a.item_id, a.rule_id, a.severity, a.start_time, 1715756400, a.alarm_type, a.profile_id, X FROM alarm a LEFT OUTER JOIN alarm_clear ac ON (a.alarm_id=ac.alarm_id) WHERE a.linked_rules IS NOT NULL AND ac.alarm_id is NULL]; [Vertica][VJDBC](3149) ERROR: Duplicate primary/unique key detected in join [(dauser.alarm x dauser.alarm_clear) using alarm_order_by_super_node0001 and alarm_clear_find_ongoing (PATH ID: X)]; value [alarm_id=XXXXXX]; nested exception is java.sql.SQLIntegrityConstraintViolationException: [Vertica][VJDBC](3149) ERROR: Duplicate primary/unique key detected in join [(<dbuser>.alarm x <dbuser>.alarm_clear) using alarm_order_by_super_node0001 and alarm_clear_find_ongoing (PATH ID: X)]; value [alarm_id=XXXXX]

Environment

Performance Management - All Versions

Cause

There are duplicate entries in the alarm_clear table

Resolution

Run the following

WITH duplicateRemoval as
1. ( select alarm_id ,ROW_NUMBER() OVER(PARTITION BY alarm_id ORDER BY alarm_id, epoch) ranked from alarm_clear order by alarm_id, epoch) select * from duplicateRemoval WHERE ranked > 1;

2. delete from alarm_clear where (alarm_id, epoch) in (select alarm_id, epoch from ( select alarm_id, epoch, ROW_NUMBER() OVER(PARTITION BY alarm_id ORDER BY alarm_id, epoch) ranked from alarm_clear order by alarm_id ) AS a where a.ranked > 1);

If multiple instances are returned in step 1, please review "Additional Information" below; then move to step 3

3. Run commit

Additional Information

You may need to run the cleanup (delete) command 2 times, if there are multiple instances in some cases; as the output below

The delete will only delete the ranked 1 entry. That will still leave 2 entries

alarm_id | ranked

----------+--------

XXXXXX2 | 3

XXXXXX2 | 2

XXXXXX3 | 3

XXXXXX3 | 2

XXXXXX4 | 2

Rerun the "with/select" command after doing the delete command, and see if we see the first 2 alarm IDs again with ranked 2.

If so, run the delete again.

Finally, rerun the "with/select" command again, and it should be empty

At this point you can run "commit", to commit the changes

If you with to back out, even after running delete, just exit vsql; as long as you hadn't run "commit" or drop a table