Coder.com•8mo ago

Internal error checking workspace agent authorization (after db reinit from dump)

Hi. Today we tried to update Coder from v2.9.1 to v2.9.3 but the DB got corrupted (probably because of 2 pgsql instances running at the same time, rookie mistake). We have PG dumps each day, so I tried to run v2.9.3 version and import the backup dump (schema + data) from last known working version; the Coder works, I can access the dashboard, the data is there, but now all workspaces created show me the following error: Agent side:

2024-04-22 09:33:49.911 [info]  connecting to coderd
2024-04-22 09:33:49.934 [warn]  run exited with error ...
    error= GET https://coder.company.com/api/v2/workspaceagents/me/rpc?version=2.0: unexpected status code 500: Internal error checking workspace agent authorization.
                Error: pq: column "workspace_agents.created_at" must appear in the GROUP BY clause or be used in an aggregate function

2024-04-22 09:33:49.911 [info]  connecting to coderd
2024-04-22 09:33:49.934 [warn]  run exited with error ...
    error= GET https://coder.company.com/api/v2/workspaceagents/me/rpc?version=2.0: unexpected status code 500: Internal error checking workspace agent authorization.
                Error: pq: column "workspace_agents.created_at" must appear in the GROUP BY clause or be used in an aggregate function

Coder side:

2024-04-22 09:34:10.007 [warn]  coderd: GET  host=coder.company.com  path=/api/v2/workspaceagents/me/rpc  proto=HTTP/1.1  remote_addr=10.128.1.7  start="2024-04-22T09:34:10.006571993Z"  took=883.371µs  status_code=500  latency_ms=0  response_body="{\"message\":\"Internal error checking workspace agent authorization.\",\"detail\":\"pq: column \\\"workspace_agents.created_at\\\" must appear in the GROUP BY clause or be used in an aggregate function\"}\n"  request_id=d9c36ff4-82a1-4a67-ae6a-c18ce26ddab5

2024-04-22 09:34:10.007 [warn]  coderd: GET  host=coder.company.com  path=/api/v2/workspaceagents/me/rpc  proto=HTTP/1.1  remote_addr=10.128.1.7  start="2024-04-22T09:34:10.006571993Z"  took=883.371µs  status_code=500  latency_ms=0  response_body="{\"message\":\"Internal error checking workspace agent authorization.\",\"detail\":\"pq: column \\\"workspace_agents.created_at\\\" must appear in the GROUP BY clause or be used in an aggregate function\"}\n"  request_id=d9c36ff4-82a1-4a67-ae6a-c18ce26ddab5

I have tried investigating if the problem lies in some schema change, but that didn't help. I have also tried running v2.9.0, no change. If anybody has any idea, it would be very appreciated!

7 Replies

Codercord•8mo ago

<#1231901122399047690>

Category

Bug report

Product

Coder OSS (v2)

Platform

Linux

Logs

Please post any relevant logs/error messages.

modrinOP•8mo ago

More detailed query:

2024-04-22 09:54:00.818 GMT [183] ERROR:  column "workspace_agents.created_at" must appear in the GROUP BY clause or be used in an aggregate function at character 81

2024-04-22 09:54:00.818 GMT [183] STATEMENT:  -- name: GetWorkspaceAgentAndOwnerByAuthToken :one

    SELECT

        workspace_agents.id, workspace_agents.created_at, workspace_agents.updated_at, workspace_agents.name, workspace_agents.first_connected_at, workspace_agents.last_connected_at, workspace_agents.disconnected_at, workspace_agents.resource_id, workspace_agents.auth_token, workspace_agents.auth_instance_id, workspace_agents.architecture, workspace_agents.environment_variables, workspace_agents.operating_system, workspace_agents.instance_metadata, workspace_agents.resource_metadata, workspace_agents.directory, workspace_agents.version, workspace_agents.last_connected_replica_id, workspace_agents.connection_timeout_seconds, workspace_agents.troubleshooting_url, workspace_agents.motd_file, workspace_agents.lifecycle_state, workspace_agents.expanded_directory, workspace_agents.logs_length, workspace_agents.logs_overflowed, workspace_agents.started_at, workspace_agents.ready_at, workspace_agents.subsystems, workspace_agents.display_apps, workspace_agents.api_version, workspace_agents.display_order,

        workspaces.id AS workspace_id,

        users.id AS owner_id,

        users.username AS owner_name,

        users.status AS owner_status,

        workspaces.template_id AS template_id,

        workspace_builds.template_version_id AS template_version_id,

2024-04-22 09:54:00.818 GMT [183] ERROR:  column "workspace_agents.created_at" must appear in the GROUP BY clause or be used in an aggregate function at character 81

2024-04-22 09:54:00.818 GMT [183] STATEMENT:  -- name: GetWorkspaceAgentAndOwnerByAuthToken :one

    SELECT

        workspace_agents.id, workspace_agents.created_at, workspace_agents.updated_at, workspace_agents.name, workspace_agents.first_connected_at, workspace_agents.last_connected_at, workspace_agents.disconnected_at, workspace_agents.resource_id, workspace_agents.auth_token, workspace_agents.auth_instance_id, workspace_agents.architecture, workspace_agents.environment_variables, workspace_agents.operating_system, workspace_agents.instance_metadata, workspace_agents.resource_metadata, workspace_agents.directory, workspace_agents.version, workspace_agents.last_connected_replica_id, workspace_agents.connection_timeout_seconds, workspace_agents.troubleshooting_url, workspace_agents.motd_file, workspace_agents.lifecycle_state, workspace_agents.expanded_directory, workspace_agents.logs_length, workspace_agents.logs_overflowed, workspace_agents.started_at, workspace_agents.ready_at, workspace_agents.subsystems, workspace_agents.display_apps, workspace_agents.api_version, workspace_agents.display_order,

        workspaces.id AS workspace_id,

        users.id AS owner_id,

        users.username AS owner_name,

        users.status AS owner_status,

        workspaces.template_id AS template_id,

        workspace_builds.template_version_id AS template_version_id,

Seems like v2.10.1 fixed our problems; there might be some internal schema problem between v2.9 and v2.10, or just a coincidence 🤷‍♂️

Cian•8mo ago

We didn't make any schema changes from 2.9.0 -> 2.9.3 apart from a fix to remove a hardcoded public schema in a migration (https://github.com/coder/coder/issues/12619). That relates to a different table altogether and shouldn't actually change the resulting database schema. I tried reproducing your issue by running a fresh v2.9.1 install, upgrading to v2.9.3, and downgrading to v2.9.0 but couldn't reproduce the error you provided above. However, now that you're on v2.10 I would advise against downgrading.

modrinOP•8mo ago

We're running coder from the start of v2 and it sounds like it might have something to do with existing PG dump data not importing correctly on a fresh v2.9 install. But for now the issue is fine on v2.10

Cian•8mo ago

hmm.

modrinOP•8mo ago

Maybe it's random 🤷‍♂️ idk if it happens again I'll report with more info 😄 Can't exactly share the dump with company data 😅 Would love to cleanup the dump (about 65 MB) sometime as well, some old remnants (after v0 to v2 migration) might be interfering with updates 🤔

Cian•8mo ago

You can dump just the schema like we do here: https://github.com/coder/coder/blob/main/coderd/database/gen/dump/main.go#L53 I'm working on some extra tests around migration between versions right now, which is why this is of particular interest to me

Gaming

Programming

Internal error checking workspace agent authorization (after db reinit from dump)