Circuit problem "django.db.utils.IntegrityError"

[dejantep]

I have a circuit connected to interface and visible in interface overview of a device.
But when go to circuit detail page there are no terminations visible. If i try to add termination A i get following error

duplicate key value violates unique constraint "circuits_circuittermination_circuit_id_term_side_b13efd0e_uniq"
DETAIL: Key (circuit_id, term_side)=(81, A) already exists.

Python version: 3.6.9
NetBox version: 2.11.12

How would one correct this?

[candlerb]

I don't understand. How did you get a "circuit connected to interface" without using a circuit termination?

Otherwise, I wonder if it's a permissions thing. If you login to Netbox using a superuser account, are the terminations visible?

[dejantep]

I'm a superuser and circuit was connected a while ago. Log retention is 90 days so there is nothing to look for in change log
I'm not sure what could have happened.

Reason i stumbled upon this is that i updated circuit type on this particular circuit.

If i just disconnect from the interface will that remove values that django is complaining about?

[DanSheps]

I think the reasons for issues with DCIM are that we index them because we validate their uniqueness but do that in code.

[candlerb]

sqldiff seems to think that the definitions seen from talking to the database itself do not match the definitions in the models. But it could be a bug in sqldiff.

[dejantep]

Hello and thank you once again for your valuable inputs.

I did check for other anomalies and i'v got 3 rows on termination A. Nothing on termination Z
id | termination_a_id | id
----+------------------+-----
81 | | 106
82 | | 107
87 | | 117

and regarding foreign-key constraints i have this. My system has been around since version 2.7.x

    "circuits_circuitterm__cable_peer_type_id_bd122156_fk_django_co" FOREIGN KEY (_cable_peer_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED
    "circuits_circuitterm_provider_network_id_b0c660f1_fk_circuits_" FOREIGN KEY (provider_network_id) REFERENCES circuits_providernetwork(id) DEFERRABLE INITIALLY DEFERRED
    "circuits_circuittermination_circuit_id_257e87e7_fk" FOREIGN KEY (circuit_id) REFERENCES circuits_circuit(id) DEFERRABLE INITIALLY DEFERRED
    "circuits_circuittermination_site_id_e6fe5652_fk_dcim_site_id" FOREIGN KEY (site_id) REFERENCES dcim_site(id) DEFERRABLE INITIALLY DEFERRED

I cannot do that last part with sqldiff but thank you anyway.

I would like to fix those 3 errored rows. Would your update query solve that?

[candlerb]

Yes, you should be able to modify that update as appropriate to fill in the missing termination_a_id on each circuit.

However if the circuit_id points to a non-existent cable, that won't be fixed. To check for those:

select ct.id,ct.cable_id from circuits_circuittermination ct
  left join dcim_cable dc on ct.cable_id = dc.id
  where ct.cable_id is not null and dc.id is null;

I'm not sure what would be the best fix for those, but I think just setting the cable_id to null would be reasonable (on the assumption that the cable has been deleted).

Once that's done, in principle you can add the foreign key constraint by hand. It would however be nice to know how the database got into that bad state.

[dejantep]

Thanks a lot. i managed to correct it.

Happy holidays!!