Uploaded image for project: 'OpenNMS'
  1. OpenNMS
  2. NMS-10796

ProvisionD cannot delete nodes during import - ERROR: update or delete on table "node" violates foreign key constraint "fk_ipnettomedia_nodeid" on table "ipnettomedia"



    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Duplicate
    • Affects Version/s: 24.0.0
    • Fix Version/s: None
    • Security Level: Default (Default Security Scheme)
    • Labels:


      Since recently upgrading from Opennms 23.x to 24.0.0 (via fresh install) we started to notice that old VMs which had been deleted were still present in OpenNMS and alerting as down.

      Our setup has just one requisition which is maintained by the DNS Importer which does a zone transfer every 15 minutes and updates $OPENNMS_HOME/imports/pending/req.xml - This is still happening and the data in req.xml is accurate, however nodes which no longer exist in this file used to be automatically deleted and now they are not.

      The provisiond log shows the following error which I believe indicates that the delete is attempted but fails due to newly added foreigh-key constraints:

      2019-05-03 11:15:01,459 INFO  [importExecutor-6] o.o.n.p.s.l.Phase: failed to invoke lifecycle instance
      java.lang.reflect.InvocationTargetException: null
              at sun.reflect.GeneratedMethodAccessor648.invoke(Unknown Source) ~[?:?]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191]
      Caused by: org.springframework.dao.DataIntegrityViolationException: could not delete: [org.opennms.netmgt.model.OnmsNode#250]; SQL [delete from node where nodeId=?]; constraint [fk_ipnettomedia_nodeid]; nested exception is org.hibernate.exception.ConstraintViolationException: could not delete: [org.opennms.netmgt.model.OnmsNode#250]
      Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "node" violates foreign key constraint "fk_ipnettomedia_nodeid" on table "ipnettomedia"
        Detail: Key (nodeid)=(250) is still referenced from table "ipnettomedia".


      I believe I have tracked the introduction of this constraint to a change made to `opennms-base-assembly/src/main/filtered/etc/create.sql` as part of a huge commit for https://issues.opennms.org/browse/NMS-9273 (Commit ID: dbd1addf0a36ad8d9cb9c6f2888a0d185e268297)

      There was also a subsequent issue and PR to update the Liquibase changelog to reflect this in: https://issues.opennms.org/browse/HZN-1499

      I believe the way forward will be to update the SQL used by the importer for deleting/updating notes. If I have any success pinning this down and testing a fix I will submit a PR.


          Issue Links



              • Assignee:
                jondevit Jonathan Heard
              • Votes:
                0 Vote for this issue
                1 Start watching this issue


                • Created: