Upgrade error - duplicate key value violates unique constraint

[kbeaudreau]

Hello,

I am attempting to upgrade from version 2.8.3 (the ldap variant) to 2.10.5 (-ldap). I built the system in Docker. After dropping and creating the database, I ran pg_restore to populate my old data. When the system starts, it goes through its migrations and fails at virtualization.0016_replicate_interfaces with a unique constraint violation.

I don't know where to go for this. both the virtualization_vminterface and virtualization_vminterface_tagged_vlans are empty.

Any assistance is greatly appreciated.

🧬 loaded config '/etc/netbox/config/configuration.py'
🧬 loaded config '/etc/netbox/config/configuration.py'
🧬 loaded config '/etc/netbox/config/extra.py'
Operations to perform:
Apply all migrations: admin, auth, circuits, contenttypes, dcim, extras, ipam, secrets, sessions, taggit, tenancy, users, virtualization
Running migrations:
Applying virtualization.0016_replicate_interfaces...
Replicating 1861 VM interfaces...
Traceback (most recent call last):
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "virtualization_vminterfa_virtual_machine_id_name_cfb9c423_uniq"
DETAIL: Key (virtual_machine_id, name)=(1812, Network adapter 1) already exists.

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

Traceback (most recent call last):
File "./manage.py", line 10, in
execute_from_command_line(sys.argv)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/init.py", line 401, in execute_from_command_line
utility.execute()
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/init.py", line 395, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/base.py", line 330, in run_from_argv
self.execute(*args, **cmd_options)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/base.py", line 371, in execute
output = self.handle(*args, **options)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/base.py", line 85, in wrapped
res = handle_func(*args, **kwargs)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 243, in handle
post_migrate_state = executor.migrate(
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 227, in apply_migration
state = migration.apply(state, schema_editor)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/migration.py", line 124, in apply
operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/operations/special.py", line 190, in database_forwards
self.code(from_state.apps, schema_editor)
File "/opt/netbox/netbox/virtualization/migrations/0016_replicate_interfaces.py", line 37, in replicate_interfaces
VMInterface.objects.bulk_create(new_interfaces, batch_size=1000)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/opt/netbox/venv/lib/python3.8/site-packages/cacheops/query.py", line 374, in bulk_create
objs = self._no_monkey.bulk_create(self, objs, *args, **kwargs)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/models/query.py", line 506, in bulk_create
returned_columns = self._batched_insert(
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1271, in _batched_insert
inserted_rows.extend(self._insert(
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1254, in _insert
return query.get_compiler(using=using).execute_sql(returning_fields)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1397, in execute_sql
cursor.execute(sql, params)
File "/opt/netbox/venv/lib/python3.8/site-packages/cacheops/transaction.py", line 93, in execute
result = self._no_monkey.execute(self, sql, params)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "virtualization_vminterfa_virtual_machine_id_name_cfb9c423_uniq"
DETAIL: Key (virtual_machine_id, name)=(1812, Network adapter 1) already exists.

⏳ Waiting on DB... (0s / 30s)

[jeremystretch]

DETAIL: Key (virtual_machine_id, name)=(1812, Network adapter 1) already exists.

The virtual machine with ID 1812 apparently has more than one interface named "Network adapter 1". Deleting the duplicate interface(s) and re-running the migration should fix it.

[kbeaudreau]

Thanks! Since the application is unavailable due to this error, I need to delete it via postgres commands. are there multiple tables from which it should be deleted?

[kbeaudreau]

hmm...I may have found it.

in dcim_interface, I see 2 Network adapter 1 interfaces for virtual_machine_id=1812

[kbeaudreau]

so I think the suggestion by @jeremystretch is going to work, but there seems to be a lot of duplicates that I need to clean out before I can try again.

SELECT virtual_machine_id, count() FROM dcim_interface group by virtual_machine_id having count() > 1 order by virtual_machine_id;

this shows me all the machines with multiple interfaces but doesn't tell me which ones are duplicates. Still working on that query (not a sql person).

[kbeaudreau]

Success! After deleting all the duplicate interfaces, Netbox was able to continue with the migration at first startup.

For those interested, the query my DBAs gave me is:

SELECT CONCAT('DELETE FROM dcim_interface WHERE id=',max(id),';') FROM dcim_interface WHERE virtual_machine_id is not null GROUP BY virtual_machine_id, name HAVING count(*) > 1;

this prints a series of delete statements that I copy/pasted into the command line.

had to run it twice to ensure there were no machines with more than 2 duplicates (some had 3)