cannot ALTER TABLE "dcim_rack" because it has pending trigger events

[Brady]

Hello,

I recently upgraded Netbox 2.5.11 to 2.7.2 by 

# git pull origin master

followed by 

# ./upgrade.sh

After upgrade, I manually updated the configuration.py file to include the additional REDIS section. After the configuration file was updated, I restarted Netbox with:

# sudo supervisorctl restart netbox

Doing so brought the web interface back online, which suggested a database upgrade script needed to be run, which I did

#python3 manage.py migrate

Doing so resulted in an error related to the "dcim_rack" table with pending trigger events. I am not sure how to resolve these "pending trigger events". Has anyone else run into this and found a solution?

local-admin@netbox:/opt/netbox/netbox$ sudo python3 manage.py migrate

Operations to perform:

  Apply all migrations: admin, auth, circuits, contenttypes, dcim, extras, ipam, secrets, sessions, taggit, tenancy, users, virtualization

Running migrations:

  Applying extras.0019_tag_taggeditem... OK

  Applying circuits.0015_custom_tag_models... OK

  Applying circuits.0016_3569_circuit_fields... OK

  Applying circuits.0017_circuittype_description... OK

  Applying tenancy.0006_custom_tag_models... OK

  Applying virtualization.0009_custom_tag_models... OK

  Applying secrets.0006_custom_tag_models... OK

  Applying ipam.0025_custom_tag_models... OK

  Applying dcim.0070_custom_tag_models... OK

  Applying extras.0020_tag_data_squashed_0021_add_color_comments_changelog_to_tag... OK

  Applying dcim.0071_device_components_add_description_squashed_0088_powerfeed_available_power...

Updating cable device terminations...

Traceback (most recent call last):

  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

psycopg2.errors.ObjectInUse: cannot ALTER TABLE "dcim_rack" because it has pending trigger events

[Brady]

Additional information I meant to include in the original post, here is the message displayed when browsing to the web interface of the server hosting Netbox:

 Server Error

A database programming error was detected while processing this request. Common causes include the following:

 Database migrations missing - When upgrading to a new NetBox release, the upgrade script must be run to apply any new database migrations. You can run migrations manually by executing python3 manage.py migrate from the command line.

 Unsupported PostgreSQL version - Ensure that PostgreSQL version 9.4 or higher is in use. You can check this by connecting to the database using NetBox's credentials and issuing a query for SELECT VERSION().

---

The complete exception is provided below:

<class 'django.db.utils.ProgrammingError'>

column dcim_powerport._connected_poweroutlet_id does not exist

LINE 1: ... integer)) AS "_nat3" FROM "dcim_powerport" WHERE "dcim_powe...

Running PSQL 10

Brady

[Jeremy Stretch]

> cannot ALTER TABLE "dcim_rack" because it has pending trigger events

Among other things, this migration converts three fields on the dcim_rack table from integers to strings:

* type

* status

* outer_unit

You'll need to determine what the pending trigger event is. Do any of these fields contain values that are not listed in the constants inside the migration file? These are:

* RACK_TYPE_CHOICES

* RACK_STATUS_CHOICES

* RACK_DIMENSION_CHOICES

In the past, I've run into this issue when converting null values, however the migration should handle these without error.

--
You received this message because you are subscribed to the Google Groups "NetBox" group.
To unsubscribe from this group and stop receiving emails from it, send an email to netbox-discus...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/netbox-discuss/478a49a4-3e55-4ada-9bc7-90ef50c864a1%40googlegroups.com.

--

Jeremy Stretch
Sr. Network Automation Engineer
Network to Code, LLC

[Brady]

Hello,

The data in that dcim_rack table appears fairly standard.  We haven't populated any dimension values for the racks, and those all appear to be NULL currently.  I've attached a text file with the output from the PSQL query for the relevant parts of the dcim_rack table. 

I was able to view the constraints in the database table, and those appear to be met.  My apologies, I'm not sure how to view the constraints in the migration file, I'm new to that structure.  

Thank you for any guidance you can provide.  I have a snap of the VM, so my data is in a good place.  I feel I may be close, just need to jump a hurdle or two.

Brady 

On Wednesday, January 22, 2020 at 5:44:44 PM UTC-5, Brady wrote:

[Brady]

I now see the migration file you are referencing.  The "RACK_STATUS_CHOICES" and "RACK_TYPE_CHOICES" appear to be within bounds, but I haven't defined "RACK_DIMENSION_CHICES".  Is that value not being set possibly causing me heartburn?

I also notice the last action I see the migrate script calling is "Updating cable device terminations" from line 217.  We have a decent number of cables defined, so I'm taking a look at that table to see if anything stands out.  

Thank you

Brady

On Wednesday, January 22, 2020 at 5:44:44 PM UTC-5, Brady wrote:

[Jeremy Stretch]

> but I haven't defined "RACK_DIMENSION_CHICES".

Not sure I follow. RACK_DIMENSION_CHOICES is defined in the migration file. It's fine if you don't have these values set for any racks: null entries get converted to blank strings during the schema change.

> I also notice the last action I see the migrate script calling is "Updating cable device terminations" from line 217.

This is merely the most recent output before the error occurs. The SQL error "cannot ALTER TABLE "dcim_rack" because it has pending trigger events" suggests the issue lies with one of the changes to dcim_rack.

You can try running "python3 manage.py migrate -v 3" to see if it produces any more verbose output, but I'm not sure it will give you much.

--
You received this message because you are subscribed to the Google Groups "NetBox" group.
To unsubscribe from this group and stop receiving emails from it, send an email to netbox-discus...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/netbox-discuss/601b15fc-c49a-46ed-a5f5-0c7fad387f14%40googlegroups.com.

[daryl titcomb]

Hey all, we are having the same issue here, went to update from 2.4.2 and got this far and am now stuck

To unsubscribe from this group and stop receiving emails from it, send an email to netbox-...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/netbox-discuss/601b15fc-c49a-46ed-a5f5-0c7fad387f14%40googlegroups.com.

[Jeremy Stretch]

Try upgrading to v2.6.12 first, and then to v2.7.2. In theory, it shouldn't be necessary, but a lot has changed in the last year and a half.

To unsubscribe from this group and stop receiving emails from it, send an email to netbox-discus...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/netbox-discuss/30c1cbad-d40c-4e7f-906d-12a86adec67a%40googlegroups.com.

[daryl titcomb]

That's great that worked flawlessly, thank you so much

[Brady]

Hello,

Yes indeed, the incremental jump to 2.6.12 then 2.7.2 resolved my issues as well.  Thank you for your help!

Brady 

On Wednesday, January 22, 2020 at 5:44:44 PM UTC-5, Brady wrote:

[KP]

I have this same problem after upgrading to 2.7.4.  I upgraded today via git.  I don't recall the version I was running previously, but it was no more than a few months old.

I don't see a way to downgrade to 2.6.12 via git.  Is it safe to use the "Option A: Download a Release" method to get down to to 2.6.12 then " Option B: Clone the Git Repository (latest master release)" as I usually do to get back to 2.7.4?

[Brian Candler]

You can't simply revert to an older version, because database migrations can't be undone.

You need to restore the postgres database from the most recent backup you took just before the upgrade, then you can check out the corresponding git tag (e.g. v2.6.12)

[Brian Candler]

[Brian Candler]

[Chris Miller]

Brutal, I would rather not rollback my snapshot and start this process over, anything else those of us with "dcim_rack" issues can try?

[Chris Miller]

Just confirming that rolling back to 2.5, and stepping thru the 2.6.12 upgrade prior to 2.7.7 was successful for me. 

[Colin David]

Brady,

I am currently on v2.3.1 -- I installed REDIS, added it to the configuration file, then tried going the "Option B: Clone the Git Repository (latest master release)" route -- got up to 2.7.8, and when I went to log in, I got the same issue with the cannot ALTER TABLE.

I since reverted back via snapshot so I am back to v2.3.1 and fully functional.

I see Jeremy Stretch has suggested jumping to 2.6.12 and then going from there. It looks like the other route, "Option A: Download a Release," would be the way for me to go to get to 2.6.12. I see where I can download the tar.gz source for 2.6.12.

I am trying to figure out the exact steps I'm going to need to take. When I do a "python3 --version" on the server, it comes back with "Python 3.5.2" so I think I am good to go there. I think I'll still need to replace gunicorn with the Python3 version, though? I also think I'll need to install REDIS again as well, and pop that REDIS syntax into the configuration file.

When I get confused on is the "ln -sfn" command, and the way I'm being told to handle the copying of the configuration.py and gunicorn_config.py files. (Don't need to worry about media, reports, or ldap_config as I'm not using those).

The copy commands seem like they're asking me to replace the modified config files with the stock config file(s) from the newly downloaded and extracted tar.gz -- unless I'm looking at it the wrong way?

Any help would be greatly appreciated -- thanks!

[Jeremy Stretch]

What is the name of the migration that is failing?

--
You received this message because you are subscribed to the Google Groups "NetBox" group.
To unsubscribe from this group and stop receiving emails from it, send an email to netbox-discus...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/netbox-discuss/ee53334f-0029-4742-a815-24173b2538e4%40googlegroups.com.

[Jeremy Stretch]

After some testing it seems that a specific squashed migration (0071_device_components_add_description_squashed_0088_powerfeed_available_power) was triggering an exception under certain conditions. I've removed this file from the v2.7.9 release, which will cause NetBox to apply the individual migrations instead. This should mitigate the issue for anyone now upgrading to v2.7.9+ from an older release.

[Richard Hicks]

Running into the same error with the spam_ipaddress table on a migration from 2.5.5 to 2.7.10.

Full details:

fatal: [netbox.xxxx.com]: FAILED! => {"changed": false, "cmd": "./manage.py migrate --noinput", "msg": "stdout: Operations to perform:

  Apply all migrations: admin, auth, circuits, contenttypes, dcim, extras, ipam, secrets, sessions, taggit, tenancy, users, virtualization

Running migrations:

  Applying auth.0010_alter_group_name_max_length... OK

  Applying auth.0011_update_proxy_permissions... OK

  Applying extras.0017_exporttemplate_mime_type_length... OK

  Applying extras.0018_exporttemplate_add_jinja2... OK

  Applying extras.0019_tag_taggeditem... OK

  Applying circuits.0015_custom_tag_models... OK

  Applying circuits.0016_3569_circuit_fields... OK

  Applying circuits.0017_circuittype_description... OK

  Applying tenancy.0006_custom_tag_models... OK

  Applying virtualization.0009_custom_tag_models... OK

  Applying secrets.0006_custom_tag_models... OK

  Applying ipam.0025_custom_tag_models... OK

  Applying dcim.0069_deprecate_nullablecharfield... OK

  Applying dcim.0070_custom_tag_models... OK

  Applying extras.0020_tag_data_squashed_0021_add_color_comments_changelog_to_tag... OK

  Applying dcim.0071_device_components_add_description... OK

  Applying dcim.0072_powerfeeds... OK

  Applying dcim.0073_interface_form_factor_to_type... OK

  Applying dcim.0074_increase_field_length_platform_name_slug... OK

  Applying dcim.0075_cable_devices...

Updating cable device terminations...

 OK

  Applying dcim.0076_console_port_types... OK

  Applying dcim.0077_power_types... OK

  Applying dcim.0078_3569_site_fields... OK

  Applying dcim.0079_3569_rack_fields... OK

  Applying dcim.0080_3569_devicetype_fields... OK

  Applying dcim.0081_3569_device_fields... OK

  Applying dcim.0082_3569_interface_fields... OK

  Applying dcim.0082_3569_port_fields... OK

  Applying dcim.0083_3569_cable_fields... OK

  Applying dcim.0084_3569_powerfeed_fields... OK

  Applying dcim.0085_3569_poweroutlet_fields... OK

  Applying dcim.0086_device_name_nonunique... OK

  Applying dcim.0087_role_descriptions... OK

  Applying dcim.0088_powerfeed_available_power... OK

  Applying dcim.0089_deterministic_ordering... OK

  Applying dcim.0090_cable_termination_models... OK

  Applying dcim.0091_interface_type_other... OK

  Applying dcim.0092_fix_rack_outer_unit... OK

  Applying dcim.0093_device_component_ordering... OK

  Applying dcim.0094_device_component_template_ordering... OK

  Applying dcim.0095_primary_model_ordering... OK

  Applying dcim.0096_interface_ordering... OK

  Applying dcim.0097_interfacetemplate_type_other... OK

  Applying dcim.0098_devicetype_images... OK

  Applying dcim.0099_powerfeed_negative_voltage... OK

  Applying virtualization.0010_cluster_add_tenant_squashed_0012_vm_name_nonunique... OK

  Applying virtualization.0013_deterministic_ordering... OK

  Applying extras.0022_custom_links_squashed_0034_configcontext_tags... OK

  Applying extras.0035_deterministic_ordering... OK

  Applying extras.0036_contenttype_filters_to_q_objects... OK

  Applying extras.0037_configcontexts_clusters... OK

  Applying extras.0038_webhook_template_support... OK

  Applying ipam.0026_prefix_ordering_vrf_nulls_first_squashed_0032_role_description...

:stderr: /srv/netbox/releases/netbox-2.7.10/netbox/netbox/settings.py:31: UserWarning: Python 3.6 or higher will be required starting with NetBox v2.8 (current: Python 3.5.3)

  platform.python_version()

Traceback (most recent call last):

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/backends/utils.py\", line 84, in _execute

    return self.cursor.execute(sql, params)

psycopg2.errors.ObjectInUse: cannot ALTER TABLE \"ipam_ipaddress\" because it has pending trigger events

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

Traceback (most recent call last):

  File \"./manage.py\", line 10, in <module>

    execute_from_command_line(sys.argv)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/core/management/__init__.py\", line 381, in execute_from_command_line

    utility.execute()

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/core/management/__init__.py\", line 375, in execute

    self.fetch_command(subcommand).run_from_argv(self.argv)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/core/management/base.py\", line 323, in run_from_argv

    self.execute(*args, **cmd_options)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/core/management/base.py\", line 364, in execute

    output = self.handle(*args, **options)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/core/management/base.py\", line 83, in wrapped

    res = handle_func(*args, **kwargs)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/core/management/commands/migrate.py\", line 234, in handle

    fake_initial=fake_initial,

  File \"/srv/netbox/current/venv-py3/lib/python3.5/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 \"/srv/netbox/current/venv-py3/lib/python3.5/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 \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/migrations/executor.py\", line 245, in apply_migration

    state = migration.apply(state, schema_editor)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/migrations/migration.py\", line 124, in apply

    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/migrations/operations/fields.py\", line 249, in database_forwards

    schema_editor.alter_field(from_model, from_field, to_field)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/backends/base/schema.py\", line 535, in alter_field

    old_db_params, new_db_params, strict)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/backends/postgresql/schema.py\", line 124, in _alter_field

    new_db_params, strict,

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/backends/base/schema.py\", line 624, in _alter_field

    self.execute(self._delete_check_sql(model, constraint_name))

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/backends/base/schema.py\", line 137, in execute

    cursor.execute(sql, params)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/cacheops/transaction.py\", line 99, in execute

    result = self._no_monkey.execute(self, sql, params)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/backends/utils.py\", line 67, in execute

    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/backends/utils.py\", line 76, in _execute_with_wrappers

    return executor(sql, params, many, context)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/backends/utils.py\", line 84, in _execute

    return self.cursor.execute(sql, params)

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/utils.py\", line 89, in __exit__

    raise dj_exc_value.with_traceback(traceback) from exc_value

  File \"/srv/netbox/current/venv-py3/lib/python3.5/site-packages/django/db/backends/utils.py\", line 84, in _execute

    return self.cursor.execute(sql, params)

django.db.utils.OperationalError: cannot ALTER TABLE \"ipam_ipaddress\" because it has pending trigger events

", "path": "/srv/netbox/current/venv-py3/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin", "syspath": ["/tmp/ansible_django_manage_payload_e8xntep4/ansible_django_manage_payload.zip", "/usr/lib/python35.zip", "/usr/lib/python3.5", "/usr/lib/python3.5/plat-x86_64-linux-gnu", "/usr/lib/python3.5/lib-dynload", "/usr/local/lib/python3.5/dist-packages", "/usr/lib/python3/dist-packages"]}

On Friday, March 6, 2020 at 9:42:59 AM UTC-8, Jeremy Stretch wrote:

After some testing it seems that a specific squashed migration (0071_device_components_add_description_squashed_0088_powerfeed_available_power) was triggering an exception under certain conditions. I've removed this file from the v2.7.9 release, which will cause NetBox to apply the individual migrations instead. This should mitigate the issue for anyone now upgrading to v2.7.9+ from an older release.

To unsubscribe from this group and stop receiving emails from it, send an email to netbox-...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/netbox-discuss/ee53334f-0029-4742-a815-24173b2538e4%40googlegroups.com.

--

Jeremy Stretch
Sr. Network Automation Engineer
Network to Code, LLC