Column does not exists

[cronosy]

Greetings team.
I would like to know how to fix this kind of issue or if possible to validate why the postgresql is reporting the next error:

2023-10-23 09:56:21 EDT:10.253.203.172(56148):6535f7f3.421fa:root@vmdb_production:[270842]:ERROR: column lans.switch does not exist at character 183
2023-10-23 09:56:21 EDT:10.253.203.172(56148):6535f7f3.421fa:root@vmdb_production:[270842]:HINT: Perhaps you meant to reference the column "lans.switch_id".
2023-10-23 09:56:21 EDT:10.253.203.172(56148):6535f7f3.421fa:root@vmdb_production:[270842]:STATEMENT: SELECT DISTINCT "lans".* FROM "lans" INNER JOIN "switches" ON "lans"."switch_id" = "switches"."id" WHERE "switches"."type" IN ($1, $2, $3, $4, $5) AND "switches"."ems_id" = $6 AND (("lans"."switch" IS NULL AND "lans"."uid_ems" = 'dvportgroup-104787') OR ("lans"."switch" IS NULL AND "lans"."uid_ems" = 'dvportgroup-104787')) ORDER BY "lans"."id" ASC LIMIT $7

thnks
kind regards

[kbrock]

Hello @cronosy

What version of the code are you using?
app/models/lan.rb has belongs_to :switch, So lan.switch and lans.map(&:switch) should work, but lans.switch will not.

I was not able to find any code on master with lans.switch

[cronosy]

First, thanks for your help.
Second. I don't know how to validate the lan.rb code version i'm using because the ap that i'm running is a OVA downloaded from the page. And when navigate to /opt folder o vmdb there isn't a git repo configured. Please let me know how should I validate the information that you requests.

thks

[kbrock]

Do you know which ova image you downloaded? (platform and version)
From there we can go in and ensure the code looks right.

[cronosy]

Oh sure. I'm using the next ova: https://releases.manageiq.org/manageiq-vsphere-oparin-1.ova. in vmware vpshere.

[cronosy]

Hello guys any idea about the log?

[kbrock]

ok,

I don't know what screen this is.
I agree that lans.switch probably should be lans.switch_id. But it is curious because due to the INNER JOIN, lans.switch_id will never be null.

SELECT DISTINCT "lans".*
FROM "lans"
INNER JOIN "switches" ON "lans"."switch_id" = "switches"."id"
WHERE "switches"."type" IN ($1, $2, $3, $4, $5)
AND "switches"."ems_id" = $6
AND (
  (
    "lans"."switch" IS NULL
    AND "lans"."uid_ems" = 'dvportgroup-104787'
  ) OR (
    "lans"."switch" IS NULL
    AND "lans"."uid_ems" = 'dvportgroup-104787'
  )
)
ORDER BY "lans"."id" ASC
LIMIT 20

[Fryguy]

@cronosy Do you have a full stack trace so we can see where this error is coming from?

[cronosy]

No yet, To be honest i have not idea how to obtain it. Is there any manual that help me to obtain the request?

[cronosy]

Hello guys.
I've been reading the logs again and I suspect that is and information that is trying to get from vcenter:
ERROR: column lans.switch does not exist at character 183
HINT: Perhaps you meant to reference the column "lans.switch_id".
:STATEMENT: SELECT DISTINCT "lans".* FROM "lans" INNER JOIN "switches" ON "lans"."switch_id" = "switches"."id" WHERE "switches"."type" IN ($1, $2, $3, $4, $5) AND "switches"."ems_id" = $6 AND (("lans"."switch" IS NULL AND "lans"."uid_ems" = 'dvportgroup-xxx') OR ("lans"."switch" IS NULL AND "lans"."uid_ems" = 'dvportgroup-xxx')) ORDER BY "lans"."id" ASC LIMIT $7

I've modified the true value of the dvportgroup in this message but the rest of message is equal.

[Fryguy]

@kbrock do you know where this particular SQL query is coming from? It's hard to find without a stack trace pointing us to the code.

[Fryguy]

@cronosy Was this coming from a refresh, or was this from some other place, like a custom Expression (like a policy expression)?

[agrare]

@cronosy was this error from the refresh process? I've seen this happen when VMware has an invalid switch id on a network and we try to lookup the switch by ref and can't find it.

[cronosy]

Hi team. When you refer to refresh process i understand that if I add the infrastructure provider from scratch. Then I could say yes, I add the vmware infrastructure provider and start to register that message. So the user that I'm using to register the provider has limited access to vsphere. Could this limit the information obtained from provider?

[Fryguy]

Yes a limited user could cause this kind of thing, which is why we generally prefer admin creds. However, a limited user should still work.

Can you give details on the way this user is limited? This way perhaps we can recreate the issue.

[agrare]

This is primarily why we only "support" a full admin user, it is so easy to misconfigure something and cause a problem.

I recommend that you try changing the credentials to a full administrator and see if the issue goes away. If you have to have a restricted user the only feasible way is if the user has full recursive read access to an entire datacenter. This is because DVSwitches/PortGroups are owned at the datacenter level.