Handle Foreign key contraints during import

[harsh-kotak]

Environment

• Python version: 3.9.18
• Nautobot version: 2.2.0
• nautobot-netbox-importer version: 2.0.0

Netbox data import

Foreign key contraints

I am trying to migrate to nautobot from netbox 3.6.7. The import fails with the below error. Is there a way to narrow down the issue to a particular Netbox cable ID rather than a nautobot UUID which does not help. We have hundreds of devices and associated cable info in netbox so not sure how to navigate this. Any inputs would be appreciated.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/nautobot/bin/nautobot-server", line 8, in <module>
    sys.exit(main())
  File "/opt/nautobot/lib64/python3.9/site-packages/nautobot/core/cli/__init__.py", line 286, in main
    execute_from_command_line([sys.argv[0], *unparsed_args])
  File "/opt/nautobot/lib64/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/opt/nautobot/lib64/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/nautobot/lib64/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/nautobot/lib64/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/opt/nautobot/lib64/python3.9/site-packages/nautobot_netbox_importer/management/commands/import_netbox.py", line 89, in handle
    adapter.import_to_nautobot()
  File "/opt/nautobot/lib64/python3.9/site-packages/nautobot_netbox_importer/diffsync/adapters/netbox.py", line 97, in import_to_nautobot
    self._atomic_import()
  File "/usr/lib64/python3.9/contextlib.py", line 79, in inner
    return func(*args, **kwds)
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/transaction.py", line 246, in __exit__
    connection.commit()
  File "/opt/nautobot/lib64/python3.9/site-packages/django/utils/asyncio.py", line 33, in inner
    return func(*args, **kwargs)
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/backends/base/base.py", line 266, in commit
    self._commit()
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
django.db.utils.IntegrityError: insert or update on table "dcim_consoleport" violates foreign key constraint "dcim_consoleport_cable_id_a9ae5465_fk_dcim_cable_id"
DETAIL:  Key (cable_id)=(b74c4439-6217-5ebf-9e8f-d8ae8ac1c497) is not present in table "dcim_cable".

[snaselj]

Hi @harsh-kotak,

thanks for reporting this, and sorry for the delayed response. The latest released version is not compatible with Nautobot v2.2 yet. However, you can try the latest version from the following branch:

https://github.com/nautobot/nautobot-app-netbox-importer/tree/u/snaselj-nautobot-v2.2

Regarding mapping from Nautobot to NetBox UIDs, it should be possible to find that in the detailed logs. If you find the first occurrence of the required Nautobot UUID (e.g. c103c78c-e4e4-5cea-88a9-daa6019f2216), you should see something like this:

...
08:07:29.866 DEBUG   nautobot-netbox-importer source.py                        import_record() : Importing record SourceModelWrapper<ipam.aggregate -> ipam.prefix> {'created': '2019-11-22T00:00:00Z', 'last_updated': '2021-10-28T14:44:03.178Z', 'custom_field_data': {}, 'prefix': '1.0.0.0/24', 'rir': 1, 'tenant': 1, 'date_added': None, 'description': 'test', 'id': 1}
...
08:07:29.869 DEBUG   nautobot-netbox-importer source.py                        import_record() : Imported c103c78c-e4e4-5cea-88a9-daa6019f2216 {'status_id': UUID('623acb70-22ce-5f69-bd7e-76bc921aa6c4'), 'location': None, 'role_id': None, 'created': datetime.datetime(2019, 11, 22, 0, 0, tzinfo=datetime.timezone.utc), 'last_updated': None, 'prefix': '1.0.0.0/24', 'rir_id': UUID('51689b38-a8fa-5601-b838-a732fae021cc'), 'tenant_id': UUID('9b6fe81e-92b4-5b82-8769-39a7627c566e'), 'description': 'test'}
...

Imported (second line) contains Nautobot UUID and data that was imported from NetBox. The previous Importing record (first line) contains the NetBox data to be imported. There could be other logs between these two lines.

Not the most straightforward way to find it, but it should work for now. We will consider adding a more user-friendly way to find this information in the future.

Cheers,
Jan

[helderbetiol]

Hi @snaselj,

I may be facing a quite similar issue here. My nautobot version, however, is 2.0.6 while my netbox version is 3.7.1. When importing, I get the following error also related to fk constraints and a cable_id not being present in the dcim_cable table:

2024-05-24 08:15:44 [info     ] Sync complete                  dst=<NautobotAdapter "Nautobot"> flags=<DiffSyncFlags.NONE: 0> src=<NetBoxAdapter "NetBox">
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
           ^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.ForeignKeyViolation: insert or update on table "dcim_interface" violates foreign key constraint "dcim_interface_cable_id_1b264edb_fk_dcim_cable_id"
DETAIL:  Key (cable_id)=(fa7ade1f-810c-555b-96f8-8d500622da28) is not present in table "dcim_cable".


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/nautobot-server", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/usr/local/lib/python3.11/site-packages/nautobot/core/cli/__init__.py", line 53, in main
    run_app(
  File "/usr/local/lib/python3.11/site-packages/nautobot/core/runner/runner.py", line 297, in run_app
    management.execute_from_command_line([runner_name, command] + command_args)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/source/nautobot_netbox_importer/management/commands/import_netbox.py", line 89, in handle
    adapter.import_to_nautobot()
  File "/source/nautobot_netbox_importer/diffsync/adapters/netbox.py", line 97, in import_to_nautobot
    self._atomic_import()
  File "/usr/local/lib/python3.11/contextlib.py", line 80, in inner
    with self._recreate_cm():
  File "/usr/local/lib/python3.11/site-packages/django/db/transaction.py", line 246, in __exit__
    connection.commit()
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 33, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 266, in commit
    self._commit()
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 241, in _commit
    with self.wrap_database_errors:
  File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
           ^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.IntegrityError: insert or update on table "dcim_interface" violates foreign key constraint "dcim_interface_cable_id_1b264edb_fk_dcim_cable_id"
DETAIL:  Key (cable_id)=(fa7ade1f-810c-555b-96f8-8d500622da28) is not present in table "dcim_cable".

When looking at the mappings.txt file, I find the following concerning this UUID:

fa7ade1f-810c-555b-96f8-8d500622da28 | SaveFailed | "{'_state': <django.db.models.base.ModelState object at 0xffff6db6f610>, 'id': UUID('fa7ade1f-810c-555b-96f8-8d500622da28'), 'created': datetime.datetime(2024, 5, 24, 8, 9, 6, 88363, tzinfo=<UTC>), 'last_updated': datetime.datetime(2024, 5, 24, 8, 9, 6, 88373, tzinfo=<UTC>), '_custom_field_data': {}, 'termination_a_type_id': None, 'termination_a_id': None, 'termination_b_type_id': 13, 'termination_b_id': UUID('8dfd2cf3-04d8-5d55-9cc1-7496b6f57d89'), 'type': 'cat6', 'status_id': UUID('318a7220-4327-48ca-876c-d1389a81758c'), 'label': '', 'color': '', 'length': None, 'length_unit': '', '_abs_length': None, '_termination_a_device_id': None, '_termination_b_device_id': UUID('51d4e2b2-cf93-5dc1-9848-a0e185b7f8b0'), '_pk': UUID('fa7ade1f-810c-555b-96f8-8d500622da28'), '_orig_status': None}" | "null value in column \"termination_a_id\" of relation \"dcim_cable\" violates not-null constraint\nDETAIL:  Failing row contains (fa7ade1f-810c-555b-96f8-8d500622da28, 2024-05-24 08:09:06.088363+00, 2024-05-24 08:09:06.088373+00, {}, null, 8dfd2cf3-04d8-5d55-9cc1-7496b6f57d89, cat6, , , null, , null, null, 51d4e2b2-cf93-5dc1-9848-a0e185b7f8b0, 318a7220-4327-48ca-876c-d1389a81758c, null, 13).\n"

Is there anything I can do to fix it? Or maybe should I create a separate issue?

Thanks!

[snaselj]

Hi @snaselj,

I may be facing a quite similar issue here. My nautobot version, however, is 2.0.6 while my netbox version is 3.7.1. When importing, I get the following error also related to fk constraints and a cable_id not being present in the dcim_cable table:

...

Is there anything I can do to fix it? Or maybe should I create a separate issue?

Thanks!

Hi @helderbetiol,

creating a separate issue would be helpful.

Than you!

[harsh-kotak]

Apologies for late response but the foreign key error is still present in new develop branch.

Traceback (most recent call last):
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
psycopg2.errors.ForeignKeyViolation: insert or update on table "extras_customfieldchoice" violates foreign key constraint "extras_customfieldch_custom_field_id_d57e0f43_fk_extras_cu"
DETAIL:  Key (custom_field_id)=(f0e4bcf5-9335-5265-9895-13d26b63e29a) is not present in table "extras_customfield".


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/nautobot/bin/nautobot-server", line 8, in <module>
    sys.exit(main())
  File "/opt/nautobot/lib64/python3.9/site-packages/nautobot/core/cli/__init__.py", line 293, in main
    execute_from_command_line([sys.argv[0], *unparsed_args])
  File "/opt/nautobot/lib64/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/opt/nautobot/lib64/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/nautobot/lib64/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/nautobot/lib64/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/opt/nautobot/lib64/python3.9/site-packages/nautobot_netbox_importer/management/commands/import_netbox.py", line 95, in handle
    adapter.import_to_nautobot()
  File "/opt/nautobot/lib64/python3.9/site-packages/nautobot_netbox_importer/diffsync/adapters/netbox.py", line 104, in import_to_nautobot
    self._atomic_import()
  File "/usr/lib64/python3.9/contextlib.py", line 79, in inner
    return func(*args, **kwds)
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/transaction.py", line 246, in __exit__
    connection.commit()
  File "/opt/nautobot/lib64/python3.9/site-packages/django/utils/asyncio.py", line 33, in inner
    return func(*args, **kwargs)
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/backends/base/base.py", line 266, in commit
    self._commit()
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/nautobot/lib64/python3.9/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
django.db.utils.IntegrityError: insert or update on table "extras_customfieldchoice" violates foreign key constraint "extras_customfieldch_custom_field_id_d57e0f43_fk_extras_cu"
DETAIL:  Key (custom_field_id)=(f0e4bcf5-9335-5265-9895-13d26b63e29a) is not present in table "extras_customfield".

[bryanculver]

@harsh-kotak Could you provide sample data as well as retesting with the latest release of Netbox Importer?

[Francisco-Pozo-Alight]

Hi,
I'm also facing the same issue. Is there any workaround to fix this?

[joshb89]

I'm also seeing this issue still. Is there any updates?

[andrewimeson]

My workaround it to disable the foreign key constraints (which are a type of trigger in Postgres) for the import

-- Disable constraints
DO $$
DECLARE
    table_record RECORD;
BEGIN
    FOR table_record IN (
        SELECT tablename
        FROM pg_tables
        WHERE tableowner = 'nautobot'
    )
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(table_record.tablename) || ' DISABLE TRIGGER ALL';
    END LOOP;
END $$;

-- Re-enable constraints
DO $$
DECLARE
    table_record RECORD;
BEGIN
    FOR table_record IN (
        SELECT tablename
        FROM pg_tables
        WHERE tableowner = 'nautobot'
    )
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(table_record.tablename) || ' ENABLE TRIGGER ALL';
    END LOOP;
END $$;

-- Re-check all constraints, will discard bad data that remains
DO $$
  declare r record;
BEGIN
FOR r IN  (
  SELECT FORMAT(
    'UPDATE pg_constraint SET convalidated=false WHERE conname = %L; ALTER TABLE %I VALIDATE CONSTRAINT %I;',
    tc.constraint_name,
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE'
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  WHERE  constraint_type = 'FOREIGN KEY'
    AND tc.constraint_schema = 'public'
)
  LOOP
    EXECUTE (r.x);
  END LOOP;
END;
$$;

a