v4.0-beta1 migration "users.0008_flip_objectpermission_assignments..." fails

[a084ed22]

Deployment Type

Self-hosted

NetBox Version

v4.0-beta1

Python Version

3.11

Steps to Reproduce

I'm testing the upgrade between v3.7.5 and v4.0-beta1 on a separate system where I restored the database we're running and I'm encountering the following issue:

Running migrations:
  Applying dcim.0186_location_facility... OK
  Applying extras.0108_convert_reports_to_scripts... OK
  Applying extras.0109_script_model... OK
  Applying extras.0110_remove_eventrule_action_parameters... OK
  Applying extras.0111_rename_content_types... OK
  Applying extras.0112_tag_update_object_types... OK
  Applying extras.0113_customfield_rename_object_type... OK
  Applying tenancy.0015_contactassignment_rename_content_type... OK
  Applying users.0005_alter_user_table... OK
  Applying users.0006_custom_group_model... OK
  Applying users.0007_objectpermission_update_object_types... OK
  Applying users.0008_flip_objectpermission_assignments...Traceback (most recent call last):
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedObject: constraint "users_objectpermissi_objectpermission_id_2f7cc117_fk_users_obj" for table "users_group_object_permissions" does not exist

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

Traceback (most recent call last):
  File "/opt/netbox/netbox/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/core/management/base.py", line 413, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/migrations/operations/special.py", line 37, in database_forwards
    database_operation.database_forwards(
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/migrations/operations/special.py", line 106, in database_forwards
    self._run_sql(schema_editor, self.sql)
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/migrations/operations/special.py", line 133, in _run_sql
    schema_editor.execute(statement, params=None)
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: constraint "users_objectpermissi_objectpermission_id_2f7cc117_fk_users_obj" for table "users_group_object_permissions" does not exist

Somewhat relatedly, would it be possible and/or a good idea to have some form of consistency check before a major upgrade such as this, to ensure the correct presence and format of all the database objects with their expected names?

Expected Behavior

Update to the beta version should work without encountering issues due missing/differently named objects

Observed Behavior

Migrations fail due to unexpectedly missing database objects

[jeremystretch]

This issue was reported under #15605 and has since been resolved in the feature branch. You can check out the branch now, or wait for the next release.

[jeremystretch]

I just noticed that you edited your post: Your stack trace does not match the issue title. Please update the title, and provide more detail about how to reproduce your issue. (Merely upgrading from v3.7.5 does not cause the error.)

[a084ed22]

Sorry, in trying to reproduce the issue from #15605 cleanly I encountered an issue I caused myself. I changed the stack trace later after resetting my test environment. I've changed the title accordingly now.

Cross-comparing the cleaned-up environment with a stock installation, it seems the former is missing users_objectpermissi_objectpermission_id_2f7cc117_fk_users_obj and users_objectpermissi_objectpermission_id_78a9c2e6_fk_users_obj.

[jeremystretch]

I haven't been able to determine how your database arrived in this state. The users_objectpermission_groups table (renamed to users_group_object_permissions in v4.0) is a many-to-many mapping of ObjectPermissions to Groups. It should have a foreign key constraint for each of objectpermission_id and group_id in the original (v3.7) state:

netbox=> \d users_objectpermission_groups
                      Table "public.users_objectpermission_groups"
       Column        |  Type   | Collation | Nullable |             Default              
---------------------+---------+-----------+----------+----------------------------------
 id                  | bigint  |           | not null | generated by default as identity
 objectpermission_id | bigint  |           | not null | 
 group_id            | integer |           | not null | 
Indexes:
    "users_objectpermission_groups_pkey" PRIMARY KEY, btree (id)
    "users_objectpermission_g_objectpermission_id_grou_3b62a39c_uniq" UNIQUE CONSTRAINT, btree (objectpermission_id, group_id)
    "users_objectpermission_groups_group_id_fb7ba6e0" btree (group_id)
    "users_objectpermission_groups_objectpermission_id_2f7cc117" btree (objectpermission_id)
Foreign-key constraints:
    "users_objectpermissi_group_id_fb7ba6e0_fk_auth_grou" FOREIGN KEY (group_id) REFERENCES auth_group(id) DEFERRABLE INITIALLY DEFERRED
    "users_objectpermissi_objectpermission_id_2f7cc117_fk_users_obj" FOREIGN KEY (objectpermission_id) REFERENCES users_objectpermission(id) DEFERRABLE INITIALLY DEFERRED

When building a new database using the migrations present in NetBox v3.7, we can confirm that these constraints are created as expected. However, both you and @tobiasge (see #15726) appear to be missing them.

While we could work around this in the v4.0 migration, the fact that the constraint appears to be missing is problematic on its own.

[a084ed22]

I have done a few tests and I could manually add the missing FK constraints to get the migration to apply cleanly. I'm however also not sure about how the state of things ended up as it is, nor I can imagine how common an issue it is.

Would it be worthwhile to have some form of pre-upgrade check that verifies the correctness of the database?

[bogi788]

I've also run into

netbox-1               | django.db.utils.ProgrammingError: constraint "users_objectpermissi_objectpermission_id_2f7cc117_fk_users_obj" for table "users_group_object_permissions" does not exist
netbox-1               |   Applying users.0008_flip_objectpermission_assignments...

while testing the migration from 3.7.5 to 4.0. I also appear to be missing the foreign key constraint:

netbox=# \d users_objectpermission_groups
                                   Table "public.users_objectpermission_groups"
       Column        |  Type   | Collation | Nullable |                          Default                          
---------------------+---------+-----------+----------+-----------------------------------------------------------
 id                  | integer |           | not null | nextval('users_objectpermission_groups_id_seq'::regclass)
 objectpermission_id | bigint  |           | not null | 
 group_id            | bigint  |           | not null | 
Indexes:
    "users_objectpermission_groups_pkey" PRIMARY KEY, btree (id)
    "users_objectpermission_g_objectpermission_id_grou_3b62a39c_uniq" UNIQUE CONSTRAINT, btree (objectpermission_id, group_id)
    "users_objectpermission_groups_group_id_fb7ba6e0" btree (group_id)
    "users_objectpermission_groups_objectpermission_id_2f7cc117" btree (objectpermission_id)
Foreign-key constraints:
    "users_objectpermissi_group_id_fb7ba6e0_fk_users_gro" FOREIGN KEY (group_id) REFERENCES users_group(id) DEFERRABLE INITIALLY DEFERRED

I could add the constraint using alter table users_objectpermission_groups add constraint users_objectpermissi_objectpermission_id_2f7cc117_fk_users_obj foreign key (objectpermission_id) REFERENCES users_objectpermission(id) DEFERRABLE INITIALLY DEFERRED;, but then ran into

netbox-1               | django.db.utils.ProgrammingError: constraint "users_objectpermissi_objectpermission_id_78a9c2e6_fk_users_obj" for table "users_user_object_permissions" does not exist
netbox-1               |   Applying users.0008_flip_objectpermission_assignments...

I assume there is another foreign key missing from users_objectpermission_users:

netbox=# \d users_objectpermission_users
                                   Table "public.users_objectpermission_users"
       Column        |  Type   | Collation | Nullable |                         Default                          
---------------------+---------+-----------+----------+----------------------------------------------------------
 id                  | integer |           | not null | nextval('users_objectpermission_users_id_seq'::regclass)
 objectpermission_id | bigint  |           | not null | 
 user_id             | integer |           | not null | 
Indexes:
    "users_objectpermission_users_pkey" PRIMARY KEY, btree (id)
    "users_objectpermission_u_objectpermission_id_user_3a7db108_uniq" UNIQUE CONSTRAINT, btree (objectpermission_id, user_id)
    "users_objectpermission_users_objectpermission_id_78a9c2e6" btree (objectpermission_id)
    "users_objectpermission_users_user_id_16c0905d" btree (user_id)
Foreign-key constraints:
    "users_objectpermission_users_user_id_16c0905d_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES users_user(id) DEFERRABLE INITIALLY DEFERRED

Adding this as well using alter table users_objectpermission_users add constraint users_objectpermissi_objectpermission_id_78a9c2e6_fk_users_obj foreign key (objectpermission_id) REFERENCES users_objectpermission(id) DEFERRABLE INITIALLY DEFERRED; allowed the migration to finish.

[jeremystretch]

Thank you for the confirmation. It seems we have at least three people who have encountered this issue, so I imagine it's fairly common for people with databases that originated prior to v2.9.

While we can work around the immediate problem by making the renaming of the constraint conditional (ignoring the operation if the constraint does not exist), I still need to determine why it's missing.

[a084ed22]

I can check on the archived pre-upgrade db dumps I have, that go back to 2.11.12, if it is of any help. I believe that was the original version our instance started as.

[opericgithub]

I tried upgrading to feature branch from 3.7.5 (in the past we were on 2.8 and then migrated subsequently to the latest 2.x and then to every 3.x). I also got the following error:

Applying database migrations (python3 netbox/manage.py migrate)...
Operations to perform:
  Apply all migrations: account, auth, circuits, contenttypes, core, dcim, django_rq, extras, ipam, sessions, social_django, taggit, tenancy, users, virtualization, vpn, wireless
Running migrations:
  Applying dcim.0186_location_facility... OK
  Applying extras.0108_convert_reports_to_scripts... OK
  Applying extras.0109_script_model... OK
  Applying extras.0110_remove_eventrule_action_parameters... OK
  Applying extras.0111_rename_content_types... OK
  Applying extras.0112_tag_update_object_types... OK
  Applying extras.0113_customfield_rename_object_type... OK
  Applying tenancy.0015_contactassignment_rename_content_type... OK
  Applying users.0005_alter_user_table... OK
  Applying users.0006_custom_group_model... OK
  Applying users.0007_objectpermission_update_object_types... OK
  Applying users.0008_flip_objectpermission_assignments...Traceback (most recent call last):
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
  File "/opt/netbox/venv/lib/python3.10/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedObject: constraint "users_objectpermissi_objectpermission_id_2f7cc117_fk_users_obj" for table "users_group_object_permissions" does not exist

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

Traceback (most recent call last):
  File "/opt/netbox/netbox/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/core/management/base.py", line 413, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/migrations/operations/special.py", line 37, in database_forwards
    database_operation.database_forwards(
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/migrations/operations/special.py", line 106, in database_forwards
    self._run_sql(schema_editor, self.sql)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/migrations/operations/special.py", line 133, in _run_sql
    schema_editor.execute(statement, params=None)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute
    return super().execute(sql, params)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/netbox/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
  File "/opt/netbox/venv/lib/python3.10/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: constraint "users_objectpermissi_objectpermission_id_2f7cc117_fk_users_obj" for table "users_group_object_permissions" does not exist

[jeremystretch]

This took a bit of digging but I believe I've uncovered the root issue. This can be replicated by following these steps:

1. Begin with an empty database.
2. Check out and set up NetBox v2.11.12 but do not run any migrations.
3. Run manage.py migrate secrets (to work around a suspected missing dependency item).
4. Run manage.py migrate users 0010.
5. Enter DB shell (manage.py dbshell) and verify that both M2M tables have both required FK constraints.
   • \d users_objectpermission_groups
   • \d users_objectpermission_users
6. Apply the users.0011_standardize_models migration (manage.py migrate users 0011).
7. Use the DB shell to inspect the M2M tables again. The FK constraint for objectpermission_id is missing from both M2M tables.

I'm not sure why this happens. The users.0011_standardize_models migration merely changes the type of the id column for the ObjectPermission and Token models:

    operations = [
        migrations.AlterField(
            model_name='objectpermission',
            name='id',
            field=models.BigAutoField(primary_key=True, serialize=False),
        ),
        migrations.AlterField(
            model_name='token',
            name='id',
            field=models.BigAutoField(primary_key=True, serialize=False),
        ),
    ]

My guess is we've hit some bug in Django 3.2, but I haven't attempted to locate one yet. This at least identifies the root cause of the migration issue, so I'm comfortable working around it as I mentioned above.

Thank you everyone for your assistance in tracking this down!

[jeremystretch]

@a084ed22 @bogi788 @opericgithub @tobiasge I just submitted PR #15765 which I believe resolves this. Please try it out if you're able and let me know if you still encounter any issues running the v4.0 migrations.

[Betawurst]

@jeremystretch I had the same problem. After I applied the PR, the migration went through without any further problems