starred/netbox
1
0
Fork 0
mirror of https://github.com/netbox-community/netbox.git synced 2026-01-12 05:20:31 +01:00
Code Issues 273 Packages Projects Releases 10 Wiki Activity

Missing index on dcim_region table #2319

New Issue
Closed
opened 2025-12-29 17:24:46 +01:00 by adam · 6 comments
adam commented 2025-12-29 17:24:46 +01:00
Owner
Copy Link

Originally created by @candlerb on GitHub (Jan 28, 2019).

Originally assigned to: @jeremystretch on GitHub.

Environment

  • Python version: 3.5.2
  • NetBox version: 2.5.3

Steps to Reproduce

This is a Netbox install which has been migrated over several versions to get to 2.5.3. All migrations have applied successfully.

When I run sqldiff, it says there is a missing index.

Expected Behavior

sqldiff would show no differences between the actual and expected database schema.

Observed Behavior

root@netbox:/opt/netbox/netbox# python3 manage.py sqldiff circuits dcim extras ipam secrets tenancy virtualization
BEGIN;
-- Application: dcim
-- Model: Region
CREATE INDEX "dcim_region_tree_id_lft_cc93ebcb_idx"
	ON "dcim_region" ("tree_id", "lft");

Aside: there are also sqldiff unknown database type errors, these are not relevant.

But it does appear that I'm missing an index on the dcim_region table.

I note that the original migration (netbox/dcim/migrations/0031_regions.py) creates separate indexes on 'tree_id' and 'lft', but not a composite index on them both.

Maybe django-mptt has since been upgraded but no corresponding migration was created?

Originally created by @candlerb on GitHub (Jan 28, 2019). Originally assigned to: @jeremystretch on GitHub. ### Environment * Python version: 3.5.2 * NetBox version: 2.5.3 ### Steps to Reproduce This is a Netbox install which has been migrated over several versions to get to 2.5.3. All migrations have applied successfully. When I [run](https://groups.google.com/forum/#!topic/netbox-discuss/qt5TmvkuBNk) sqldiff, it says there is a missing index. ### Expected Behavior sqldiff would show no differences between the actual and expected database schema. ### Observed Behavior ``` root@netbox:/opt/netbox/netbox# python3 manage.py sqldiff circuits dcim extras ipam secrets tenancy virtualization BEGIN; -- Application: dcim -- Model: Region CREATE INDEX "dcim_region_tree_id_lft_cc93ebcb_idx" ON "dcim_region" ("tree_id", "lft"); ``` > Aside: there are also sqldiff [unknown database type](https://github.com/django-extensions/django-extensions/issues/1292) errors, these are not relevant. But it does appear that I'm missing an index on the `dcim_region` table. I note that the original migration (`netbox/dcim/migrations/0031_regions.py`) creates separate indexes on 'tree_id' and 'lft', but not a composite index on them both. Maybe django-mptt has since been upgraded but no corresponding migration was created?
adam added the type: bugstatus: accepted labels 2025-12-29 17:24:46 +01:00
adam closed this issue 2025-12-29 17:24:46 +01:00
adam commented 2025-12-29 17:24:47 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Feb 13, 2019):

Please specify the exact steps needed to reproduce this bug.

@jeremystretch commented on GitHub (Feb 13, 2019): Please specify the exact steps needed to reproduce this bug.
adam commented 2025-12-29 17:24:47 +01:00
Author
Owner
Copy Link

@candlerb commented on GitHub (Feb 13, 2019):

My guess is that installing an older version of netbox (like 2.2.9) where mptt version was 0.8.7, and then upgrade, might cause this. Let me test.

@candlerb commented on GitHub (Feb 13, 2019): My guess is that installing an older version of netbox (like 2.2.9) where mptt version was 0.8.7, and then upgrade, might cause this. Let me test.
adam commented 2025-12-29 17:24:47 +01:00
Author
Owner
Copy Link

@candlerb commented on GitHub (Feb 13, 2019):

Turns out this issue is seen with a clean install of netbox v2.5.5

  • Create fresh lxd container or VM
  • Install netbox v2.5.5: follow usual instructions up to and including "run database migrations", but with 
    git clone -b v2.5.5 https://github.com/digitalocean/netbox.git .
  • Install and run sqldiff, use it to check the dcim model
pip3 install django-extensions
vi /opt/netbox/netbox/netbox/settings.py

# Installed applications
INSTALLED_APPS = [
...
    'django_extensions',
]

cd /opt/netbox/netbox
python3 manage.py sqldiff dcim

Results:

BEGIN;
-- Application: dcim
-- Model: Region
CREATE INDEX "dcim_region_tree_id_lft_cc93ebcb_idx"
	ON "dcim_region" ("tree_id", "lft");
-- Model: Interface
-- Comment: Unknown database type for field 'mac_address' (829)
ALTER TABLE "dcim_interface"
	ALTER "mac_address" TYPE macaddr;
COMMIT;

The issue about the unknown macaddr type is not important.

What I don't understand is why the dcim_region_tree_id_lft index is missing.

@candlerb commented on GitHub (Feb 13, 2019): Turns out this issue is seen with a clean install of netbox v2.5.5 * Create fresh lxd container or VM * Install netbox v2.5.5: follow usual instructions up to and including "run database migrations", but with ``` git clone -b v2.5.5 https://github.com/digitalocean/netbox.git . ``` * Install and run sqldiff, use it to check the dcim model ``` pip3 install django-extensions vi /opt/netbox/netbox/netbox/settings.py # Installed applications INSTALLED_APPS = [ ... 'django_extensions', ] cd /opt/netbox/netbox python3 manage.py sqldiff dcim ``` Results: ``` BEGIN; -- Application: dcim -- Model: Region CREATE INDEX "dcim_region_tree_id_lft_cc93ebcb_idx" ON "dcim_region" ("tree_id", "lft"); -- Model: Interface -- Comment: Unknown database type for field 'mac_address' (829) ALTER TABLE "dcim_interface" ALTER "mac_address" TYPE macaddr; COMMIT; ``` The issue about the unknown macaddr type is [not important](https://github.com/django-extensions/django-extensions/issues/1292). What I don't understand is why the `dcim_region_tree_id_lft` index is missing.
adam commented 2025-12-29 17:24:48 +01:00
Author
Owner
Copy Link

@DanSheps commented on GitHub (Mar 11, 2019):

Did some testing with this:

  • The index is missing
  • The query time decreases when adding the index
@DanSheps commented on GitHub (Mar 11, 2019): Did some testing with this: * The index is missing * The query time decreases when adding the index
adam commented 2025-12-29 17:24:48 +01:00
Author
Owner
Copy Link

@KhaledTo commented on GitHub (Jun 2, 2019):

This seems related to a bug on django_mptt.

@KhaledTo commented on GitHub (Jun 2, 2019): This seems related to a bug on [django_mptt](https://github.com/django-mptt/django-mptt/issues/682).
adam commented 2025-12-29 17:24:48 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Feb 19, 2020):

It seems that these indexes are not needed and have been removed in django-mptt 0.11.0, which will ship with NetBox v2.8. (A new migration will be introduced to reflect the change.) I think we can consider this resolved.

@jeremystretch commented on GitHub (Feb 19, 2020): It seems that [these indexes are not needed](https://github.com/django-mptt/django-mptt/pull/578) and have been removed in django-mptt 0.11.0, which will ship with NetBox v2.8. (A new migration will be introduced to reflect the change.) I think we can consider this resolved.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
beta
breaking change
complexity: high
complexity: low
complexity: medium
needs milestone
netbox
pending closure
plugin candidate
pull-request
Mirrored from GitHub Pull Request
 severity: high
severity: low
severity: medium
status: accepted
status: backlog
status: blocked
status: duplicate
status: needs owner
status: needs triage
status: revisions needed
status: under review
topic: GraphQL
topic: Internationalization
topic: OpenAPI
topic: UI/UX
topic: cabling
topic: event rules
topic: htmx navigation
topic: industrialization
topic: migrations
topic: plugins
topic: scripts
topic: templating
topic: testing
type: bug
type: deprecation
type: documentation
type: feature
type: housekeeping
type: translation
No Label status: accepted type: bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/netbox#2319
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?