Showing config context with multiple tags assigned fails with MultipleObjectsReturned

[krombel]

Environment

• Python version: 3.8.6
• NetBox version: 2.9.10

Steps to Reproduce

1. create a virtual machine
2. add two tags (which result in adding data to config context)
3. Open Config context of that VM

Expected Behavior

See config context

Observed Behavior

See an error

<class 'virtualization.models.VirtualMachine.MultipleObjectsReturned'>

get() returned more than one VirtualMachine -- it returned 2!

netbox_1         | Internal Server Error: /virtualization/virtual-machines/70/config-context/
netbox_1         | Traceback (most recent call last):
netbox_1         |   File "/usr/local/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner
netbox_1         |     response = get_response(request)
netbox_1         |   File "/usr/local/lib/python3.8/site-packages/django/core/handlers/base.py", line 179, in _get_response
netbox_1         |     response = wrapped_callback(request, *callback_args, **callback_kwargs)
netbox_1         |   File "/usr/local/lib/python3.8/site-packages/django/views/generic/base.py", line 73, in view
netbox_1         |     return self.dispatch(request, *args, **kwargs)
netbox_1         |   File "/opt/netbox/netbox/utilities/views.py", line 124, in dispatch
netbox_1         |     return super().dispatch(request, *args, **kwargs)
netbox_1         |   File "/usr/local/lib/python3.8/site-packages/django/views/generic/base.py", line 101, in dispatch
netbox_1         |     return handler(request, *args, **kwargs)
netbox_1         |   File "/opt/netbox/netbox/extras/views.py", line 146, in get
netbox_1         |     obj = get_object_or_404(self.queryset, pk=pk)
netbox_1         |   File "/usr/local/lib/python3.8/site-packages/django/shortcuts.py", line 76, in get_object_or_404
netbox_1         |     return queryset.get(*args, **kwargs)
netbox_1         |   File "/usr/local/lib/python3.8/site-packages/cacheops/query.py", line 353, in get
netbox_1         |     return qs._no_monkey.get(qs, *args, **kwargs)
netbox_1         |   File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 433, in get
netbox_1         |     raise self.model.MultipleObjectsReturned(
netbox_1         | virtualization.models.VirtualMachine.MultipleObjectsReturned: get() returned more than one VirtualMachine -- it returned 2!
netbox_1         | 192.168.80.7 - - [29/Nov/2020:18:45:03 +0000] "GET /virtualization/virtual-machines/70/config-context/ HTTP/1.0" 500 1855 "-" "<cut>"

Note: I wrote this already in #5314 (comment) and a change got introduced for 2.9 to fix it but in 2.10 it is still present.
I got asked to create a new issue.

[tobzsc]

I am also seeing this issue. In our case it is related to a device with two tags which provide config context data.

Exactly the same netbox version.

 File "/opt/netbox/venv/lib64/python3.6/site-packages/django/core/handlers/exception.py", line 47, in inner
   response = get_response(request)
 File "/opt/netbox/venv/lib64/python3.6/site-packages/django/core/handlers/base.py", line 179, in _get_response
   response = wrapped_callback(request, *callback_args, **callback_kwargs)
 File "/opt/netbox/venv/lib64/python3.6/site-packages/django/views/generic/base.py", line 73, in view
   return self.dispatch(request, *args, **kwargs)
 File "/opt/netbox/netbox/utilities/views.py", line 124, in dispatch
   return super().dispatch(request, *args, **kwargs)
 File "/opt/netbox/venv/lib64/python3.6/site-packages/django/views/generic/base.py", line 101, in dispatch
   return handler(request, *args, **kwargs)
 File "/opt/netbox/netbox/extras/views.py", line 146, in get
   obj = get_object_or_404(self.queryset, pk=pk)
 File "/opt/netbox/venv/lib64/python3.6/site-packages/django/shortcuts.py", line 76, in get_object_or_404
   return queryset.get(*args, **kwargs)
 File "/opt/netbox/venv/lib64/python3.6/site-packages/cacheops/query.py", line 353, in get
   return qs._no_monkey.get(qs, *args, **kwargs)
 File "/opt/netbox/venv/lib64/python3.6/site-packages/django/db/models/query.py", line 436, in get
   num if not limit or num < limit else 'more than %s' % (limit - 1),

[jeremystretch]

@lampwins can you take a look at this please?

[jeremystretch]

It seems like the root issue here is that retrieving the tags assigned to a VM/device requires an OUTER JOIN, resulting in a replication of the VM/device for each tag that's assigned when the context data is applied. Here's an example query for a VM with three tags applied and a ConfigContext assigned to each tag:

from django.db.models import OuterRef, Subquery, Q
from utilities.query_functions import EmptyGroupByJSONBAgg

qs = VirtualMachine.objects.annotate(
    config_context_data=Subquery(
        ConfigContext.objects.filter(
            Q(tags=OuterRef('tags')) | Q(tags=None)
        ).annotate(
            _data=EmptyGroupByJSONBAgg('data', ordering=['weight', 'name'])
        ).values("_data")
    )
).filter(id=5)
>>> print(qs.query)
SELECT "virtualization_virtualmachine"."id", "virtualization_virtualmachine"."created", "virtualization_virtualmachine"."last_updated", "virtualization_virtualmachine"."local_context_data", "virtualization_virtualmachine"."cluster_id", "virtualization_virtualmachine"."tenant_id", "virtualization_virtualmachine"."platform_id", "virtualization_virtualmachine"."name", "virtualization_virtualmachine"."status", "virtualization_virtualmachine"."role_id", "virtualization_virtualmachine"."primary_ip4_id", "virtualization_virtualmachine"."primary_ip6_id", "virtualization_virtualmachine"."vcpus", "virtualization_virtualmachine"."memory", "virtualization_virtualmachine"."disk", "virtualization_virtualmachine"."comments", (SELECT JSONB_AGG(U0."data" ORDER BY U0."weight", U0."name") AS "_data" FROM "extras_configcontext" U0 LEFT OUTER JOIN "extras_configcontext_tags" U1 ON (U0."id" = U1."configcontext_id") WHERE (U1."tag_id" = extras_taggeditem."tag_id" OR U1."tag_id" IS NULL)) AS "config_context_data" FROM "virtualization_virtualmachine" LEFT OUTER JOIN "extras_taggeditem" ON ("virtualization_virtualmachine"."id" = "extras_taggeditem"."object_id" AND ("extras_taggeditem"."content_type_id" = 72)) WHERE "virtualization_virtualmachine"."id" = 5 ORDER BY "virtualization_virtualmachine"."name" ASC, "virtualization_virtualmachine"."id" ASC

The SQL query (with some fields remove for brevity) returns the following:

netbox=> SELECT "virtualization_virtualmachine"."id", "virtualization_virtualmachine"."name", (SELECT JSONB_AGG(U0."data" ORDER BY U0."weight", U0."name") AS "_data" FROM "extras_configcontext" U0 LEFT OUTER JOIN "extras_configcontext_tags" U1 ON (U0."id" = U1."configcontext_id") WHERE (U1."tag_id" = extras_taggeditem."tag_id" OR U1."tag_id" IS NULL)) AS "config_context_data" FROM "virtualization_virtualmachine" LEFT OUTER JOIN "extras_taggeditem" ON ("virtualization_virtualmachine"."id" = "extras_taggeditem"."object_id" AND ("extras_taggeditem"."content_type_id" = 72)) WHERE "virtualization_virtualmachine"."id" = 5 ORDER BY "virtualization_virtualmachine"."name" ASC, "virtualization_virtualmachine"."id" ASC;
 id |   name   | config_context_data 
----+----------+---------------------
  5 | lab1-vm1 | [{"c": 3}]
  5 | lab1-vm1 | [{"b": 2}]
  5 | lab1-vm1 | [{"a": 1}]
(3 rows)

The best approach here isn't immediately clear. However, as this is a critical bug for users who employ config contexts, we'll have to disable the ConfigContextModelQuerySet optimization (introduced in #4559) in the next release if we can't identify a solution.

[tobzsc]

Would be happy to see a fix soon as we are heavily using config contexts for our device deployments.

[lampwins]

I finally figured out why this did not come up when this was first raised in #5314. It turns out this happens in two situations and I only found the first one in the fix for #5314. The first instance is when a single config context has two tags assigned and a device/vm has both of those same two tags assigned. That case is actually covered in this test case. The second, outstanding case, is when a device/vm has two tags assigned and those two tags are assigned to two different config context objects.