Unable to to apply metadata, error in hdb_catalog.event_logs

[abdullah2993]

Version Information

Server Version: tried on v2.4.0 and v.2.7.0 CLI Version (for CLI related issue): v2.7.0

Environment

OSS

Description

Unable to apply metadata getting the following error from cli

 time="2022-07-14T11:19:51Z" level=fatal msg="error applying metadata \nPost \"***/v1/metadata\": EOF"

Server logs show the following:

{
    "type": "http-log",
    "timestamp": "2022-07-14T11:19:51.014+0000",
    "level": "error",
    "detail": {
        "operation": {
            "user_vars": {
                "x-hasura-role": "admin"
            },
            "error": {
                "internal": {
                    "statement": "ALTER TABLE hdb_catalog.event_log\nALTER COLUMN id\nSET DEFAULT hdb_catalog.gen_hasura_uuid();\n\nALTER TABLE hdb_catalog.event_invocation_logs\nALTER COLUMN id\nSET DEFAULT hdb_catalog.gen_hasura_uuid();\n\nALTER TABLE hdb_catalog.event_log RENAME COLUMN locked TO locked_boolean;\n\nALTER TABLE hdb_catalog.event_log ADD COLUMN locked TIMESTAMPTZ;\n\nUPDATE hdb_catalog.event_log\nSET locked = NOW()\nWHERE locked_boolean = 't';\n\nALTER TABLE hdb_catalog.event_log DROP COLUMN locked_boolean;\n",
                    "prepared": false,
                    "error": {
                        "exec_status": "FatalError",
                        "hint": null,
                        "message": "invalid input syntax for type timestamp with time zone: \"t\"",
                        "status_code": "22007",
                        "description": null
                    },
                    "arguments": []
                },
                "path": "$.args",
                "error": "database query error",
                "code": "unexpected"
            },
            "request_id": "59ac3fda-8e3a-4c9e-bc4d-a61f8944ba8e",
            "response_size": 789,
            "request_mode": "error"
        },
        "request_id": "59ac3fda-8e3a-4c9e-bc4d-a61f8944ba8e",
        "http_info": {
            "status": 500,
            "http_version": "HTTP/1.1",
            "url": "/v1/metadata",
            "ip": "20.25.176.170",
            "method": "POST",
            "content_encoding": null
        }
    }
}

for some reason hasura runs the following queries while applying metadata and they seem to fail

ALTER TABLE hdb_catalog.event_log
ALTER COLUMN id
SET DEFAULT hdb_catalog.gen_hasura_uuid();

ALTER TABLE hdb_catalog.event_invocation_logs
ALTER COLUMN id
SET DEFAULT hdb_catalog.gen_hasura_uuid();

ALTER TABLE hdb_catalog.event_log RENAME COLUMN locked TO locked_boolean;

ALTER TABLE hdb_catalog.event_log ADD COLUMN locked TIMESTAMPTZ;

UPDATE hdb_catalog.event_log
SET locked = NOW()
WHERE locked_boolean = 't';

ALTER TABLE hdb_catalog.event_log DROP COLUMN locked_boolean;

[abdullah2993]

It seems like it is trying to update the catalog even though the catalog is already on version 47 and the locked column is already a timestamp. It assumes that the column is boolean and try to set it to true which throws the above error.

[BenoitRanque]

@abdullah2993 this seems to be an issue with your catalog migration state. Could you open a support ticket at https://cloud.hasura.io/support so we can schedule a call and help you?

[BenoitRanque]

Update: issue was resolved after running the following SQL:

-- Table: hdb_catalog.hdb_source_catalog_version

-- DROP TABLE hdb_catalog.hdb_source_catalog_version;

CREATE TABLE IF NOT EXISTS hdb_catalog.hdb_source_catalog_version
(
    version text COLLATE pg_catalog."default" NOT NULL,
    upgraded_on timestamp with time zone NOT NULL
)

TABLESPACE pg_default;

ALTER TABLE hdb_catalog.hdb_source_catalog_version
    OWNER to postgres;
-- Index: hdb_source_catalog_version_one_row

-- DROP INDEX hdb_catalog.hdb_source_catalog_version_one_row;

CREATE UNIQUE INDEX hdb_source_catalog_version_one_row
    ON hdb_catalog.hdb_source_catalog_version USING btree
    ((version IS NOT NULL) ASC NULLS LAST)
    TABLESPACE pg_default;

INSERT INTO hdb_catalog.hdb_source_catalog_version (version, upgraded_on) VALUES ('2', NOW());

Explanation: database was missing hdb_catalog.hdb_source_catalog_version table. Creating it and setting the version fixed it. This may be a bug. Important here is that user is using the same database for metadata and data, which may be relevant.

Todo: attempt to reproduce the issue to figure out if this is a bug.

@abdullah2993 can you share more details as to how your database got into this state? We'd also like to know how you are connecting your metadata db: are you using HASURA_GRAPHQL_DATABASE_URL, or HASURA_GRAPHQL_METADATA_DATABASE_URL? Any other details that may help us resolve the issue are welcome.

[abdullah2993]

@BenoitRanque thank you again for getting it resolved. There is nothing special to report here we did the standard upgrades overtime as far as I can remember we are using the same instance since hasura v1.x.x as for env variables we use the heroku image provided by hasura which uses HASURA_GRAPHQL_METADATA_DATABASE_URL. Please do let me know if you need anymore specifics

[tuanalumi]

Update: issue was resolved after running the following SQL:

@BenoitRanque do we need to restart the server or anything? We're using self-hosted version.

[BenoitRanque]

Update: issue was resolved after running the following SQL:

@BenoitRanque do we need to restart the server or anything? We're using self-hosted version.

You'd probably need to restart the server yes. Assuming you're facing the exact same issue, which I do not know