Graphql-engine: Upgrading from 1.1.1 to v1.3.0-beta.2

[fastengineer]

Deploying with dokku, on production. It gives the output.

=====> hasura-test web container output:
       {"type":"startup","timestamp":"2020-06-17T13:32:23.136+0000","level":"info","detail":{"kind":"server_configuration","info":{"live_query_options":{"batch_size":100,"refetch_delay":1},"transaction_isolation":"ISOLATION LEVEL READ COMMITTED","plan_cache_options":{"plan_cache_size":null},"enabled_log_types":["http-log","websocket-log","startup","webhook-log"],"server_host":"HostAny","enable_allowlist":false,"log_level":"info","auth_hook_mode":null,"use_prepared_statements":true,"unauth_role":"public","stringify_numeric_types":false,"enabled_apis":["metadata","graphql","config","pgdump"],"enable_telemetry":true,"enable_console":true,"auth_hook":null,"jwt_secret":null,"cors_config":{"allowed_origins":"*","disabled":false,"ws_read_cookie":null},"console_assets_dir":null,"admin_secret_set":true,"port":8080}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:23.136+0000","level":"info","detail":{"kind":"postgres_connection","info":{"retries":10,"database_url":"postgres://postgres:...@dokku-postgres-prodclone:5432/prodclone"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:23.136+0000","level":"error","detail":{"kind":"db_migrate","info":{"internal":{"statement":"CREATE TABLE hdb_catalog.hdb_action\n(\n  action_name TEXT PRIMARY KEY,\n  action_defn JSONB NOT NULL,\n  comment TEXT NULL,\n  is_system_defined boolean default false\n);\n\nCREATE TABLE hdb_catalog.hdb_action_permission\n(\n  action_name TEXT NOT NULL,\n  role_name TEXT NOT NULL,\n  definition JSONB NOT NULL DEFAULT '{}'::jsonb,\n  comment    TEXT NULL,\n\n  PRIMARY KEY (action_name, role_name),\n  FOREIGN KEY (action_name) REFERENCES hdb_catalog.hdb_action(action_name) ON UPDATE CASCADE\n);\n\nCREATE TABLE hdb_catalog.hdb_action_log\n(\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  -- we deliberately do not reference the action name\n  -- because sometimes we may want to retain history\n  -- after dropping the action\n  action_name TEXT,\n  input_payload JSONB NOT NULL,\n  request_headers JSONB NOT NULL,\n  session_variables JSONB NOT NULL,\n  response_payload JSONB NULL,\n  errors JSONB NULL,\n  created_at timestamptz NOT NULL default now(),\n  response_received_at timestamptz NULL,\n  status text NOT NULL,\n  CHECK (status IN ('created', 'processing', 'completed', 'error'))\n);\n\nCREATE TABLE hdb_catalog.hdb_custom_types\n(\n  custom_types jsonb NOT NULL\n);\n\nCREATE VIEW hdb_catalog.hdb_role AS\n(\n  SELECT DISTINCT role_name FROM (\n    SELECT role_name FROM hdb_catalog.hdb_permission\n    UNION ALL\n    SELECT role_name FROM hdb_catalog.hdb_action_permission\n  ) q\n);\n","prepared":false,"error":{"exec_status":"FatalError","hint":null,"message":"relation \"hdb_action\" already exists","status_code":"42P07","description":null},"arguments":[]},"path":"$","error":"database query error","code":"unexpected"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:24.414+0000","level":"info","detail":{"kind":"server_configuration","info":{"live_query_options":{"batch_size":100,"refetch_delay":1},"transaction_isolation":"ISOLATION LEVEL READ COMMITTED","plan_cache_options":{"plan_cache_size":null},"enabled_log_types":["http-log","websocket-log","startup","webhook-log"],"server_host":"HostAny","enable_allowlist":false,"log_level":"info","auth_hook_mode":null,"use_prepared_statements":true,"unauth_role":"public","stringify_numeric_types":false,"enabled_apis":["metadata","graphql","config","pgdump"],"enable_telemetry":true,"enable_console":true,"auth_hook":null,"jwt_secret":null,"cors_config":{"allowed_origins":"*","disabled":false,"ws_read_cookie":null},"console_assets_dir":null,"admin_secret_set":true,"port":8080}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:24.414+0000","level":"info","detail":{"kind":"postgres_connection","info":{"retries":10,"database_url":"postgres://postgres:...@dokku-postgres-prodclone:5432/prodclone"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:24.414+0000","level":"error","detail":{"kind":"db_migrate","info":{"internal":{"statement":"CREATE TABLE hdb_catalog.hdb_action\n(\n  action_name TEXT PRIMARY KEY,\n  action_defn JSONB NOT NULL,\n  comment TEXT NULL,\n  is_system_defined boolean default false\n);\n\nCREATE TABLE hdb_catalog.hdb_action_permission\n(\n  action_name TEXT NOT NULL,\n  role_name TEXT NOT NULL,\n  definition JSONB NOT NULL DEFAULT '{}'::jsonb,\n  comment    TEXT NULL,\n\n  PRIMARY KEY (action_name, role_name),\n  FOREIGN KEY (action_name) REFERENCES hdb_catalog.hdb_action(action_name) ON UPDATE CASCADE\n);\n\nCREATE TABLE hdb_catalog.hdb_action_log\n(\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  -- we deliberately do not reference the action name\n  -- because sometimes we may want to retain history\n  -- after dropping the action\n  action_name TEXT,\n  input_payload JSONB NOT NULL,\n  request_headers JSONB NOT NULL,\n  session_variables JSONB NOT NULL,\n  response_payload JSONB NULL,\n  errors JSONB NULL,\n  created_at timestamptz NOT NULL default now(),\n  response_received_at timestamptz NULL,\n  status text NOT NULL,\n  CHECK (status IN ('created', 'processing', 'completed', 'error'))\n);\n\nCREATE TABLE hdb_catalog.hdb_custom_types\n(\n  custom_types jsonb NOT NULL\n);\n\nCREATE VIEW hdb_catalog.hdb_role AS\n(\n  SELECT DISTINCT role_name FROM (\n    SELECT role_name FROM hdb_catalog.hdb_permission\n    UNION ALL\n    SELECT role_name FROM hdb_catalog.hdb_action_permission\n  ) q\n);\n","prepared":false,"error":{"exec_status":"FatalError","hint":null,"message":"relation \"hdb_action\" already exists","status_code":"42P07","description":null},"arguments":[]},"path":"$","error":"database query error","code":"unexpected"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:25.781+0000","level":"info","detail":{"kind":"server_configuration","info":{"live_query_options":{"batch_size":100,"refetch_delay":1},"transaction_isolation":"ISOLATION LEVEL READ COMMITTED","plan_cache_options":{"plan_cache_size":null},"enabled_log_types":["http-log","websocket-log","startup","webhook-log"],"server_host":"HostAny","enable_allowlist":false,"log_level":"info","auth_hook_mode":null,"use_prepared_statements":true,"unauth_role":"public","stringify_numeric_types":false,"enabled_apis":["metadata","graphql","config","pgdump"],"enable_telemetry":true,"enable_console":true,"auth_hook":null,"jwt_secret":null,"cors_config":{"allowed_origins":"*","disabled":false,"ws_read_cookie":null},"console_assets_dir":null,"admin_secret_set":true,"port":8080}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:25.781+0000","level":"info","detail":{"kind":"postgres_connection","info":{"retries":10,"database_url":"postgres://postgres:...@dokku-postgres-prodclone:5432/prodclone"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:25.781+0000","level":"error","detail":{"kind":"db_migrate","info":{"internal":{"statement":"CREATE TABLE hdb_catalog.hdb_action\n(\n  action_name TEXT PRIMARY KEY,\n  action_defn JSONB NOT NULL,\n  comment TEXT NULL,\n  is_system_defined boolean default false\n);\n\nCREATE TABLE hdb_catalog.hdb_action_permission\n(\n  action_name TEXT NOT NULL,\n  role_name TEXT NOT NULL,\n  definition JSONB NOT NULL DEFAULT '{}'::jsonb,\n  comment    TEXT NULL,\n\n  PRIMARY KEY (action_name, role_name),\n  FOREIGN KEY (action_name) REFERENCES hdb_catalog.hdb_action(action_name) ON UPDATE CASCADE\n);\n\nCREATE TABLE hdb_catalog.hdb_action_log\n(\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  -- we deliberately do not reference the action name\n  -- because sometimes we may want to retain history\n  -- after dropping the action\n  action_name TEXT,\n  input_payload JSONB NOT NULL,\n  request_headers JSONB NOT NULL,\n  session_variables JSONB NOT NULL,\n  response_payload JSONB NULL,\n  errors JSONB NULL,\n  created_at timestamptz NOT NULL default now(),\n  response_received_at timestamptz NULL,\n  status text NOT NULL,\n  CHECK (status IN ('created', 'processing', 'completed', 'error'))\n);\n\nCREATE TABLE hdb_catalog.hdb_custom_types\n(\n  custom_types jsonb NOT NULL\n);\n\nCREATE VIEW hdb_catalog.hdb_role AS\n(\n  SELECT DISTINCT role_name FROM (\n    SELECT role_name FROM hdb_catalog.hdb_permission\n    UNION ALL\n    SELECT role_name FROM hdb_catalog.hdb_action_permission\n  ) q\n);\n","prepared":false,"error":{"exec_status":"FatalError","hint":null,"message":"relation \"hdb_action\" already exists","status_code":"42P07","description":null},"arguments":[]},"path":"$","error":"database query error","code":"unexpected"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:27.265+0000","level":"info","detail":{"kind":"server_configuration","info":{"live_query_options":{"batch_size":100,"refetch_delay":1},"transaction_isolation":"ISOLATION LEVEL READ COMMITTED","plan_cache_options":{"plan_cache_size":null},"enabled_log_types":["http-log","websocket-log","startup","webhook-log"],"server_host":"HostAny","enable_allowlist":false,"log_level":"info","auth_hook_mode":null,"use_prepared_statements":true,"unauth_role":"public","stringify_numeric_types":false,"enabled_apis":["metadata","graphql","config","pgdump"],"enable_telemetry":true,"enable_console":true,"auth_hook":null,"jwt_secret":null,"cors_config":{"allowed_origins":"*","disabled":false,"ws_read_cookie":null},"console_assets_dir":null,"admin_secret_set":true,"port":8080}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:27.265+0000","level":"info","detail":{"kind":"postgres_connection","info":{"retries":10,"database_url":"postgres://postgres:...@dokku-postgres-prodclone:5432/prodclone"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:27.265+0000","level":"error","detail":{"kind":"db_migrate","info":{"internal":{"statement":"CREATE TABLE hdb_catalog.hdb_action\n(\n  action_name TEXT PRIMARY KEY,\n  action_defn JSONB NOT NULL,\n  comment TEXT NULL,\n  is_system_defined boolean default false\n);\n\nCREATE TABLE hdb_catalog.hdb_action_permission\n(\n  action_name TEXT NOT NULL,\n  role_name TEXT NOT NULL,\n  definition JSONB NOT NULL DEFAULT '{}'::jsonb,\n  comment    TEXT NULL,\n\n  PRIMARY KEY (action_name, role_name),\n  FOREIGN KEY (action_name) REFERENCES hdb_catalog.hdb_action(action_name) ON UPDATE CASCADE\n);\n\nCREATE TABLE hdb_catalog.hdb_action_log\n(\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  -- we deliberately do not reference the action name\n  -- because sometimes we may want to retain history\n  -- after dropping the action\n  action_name TEXT,\n  input_payload JSONB NOT NULL,\n  request_headers JSONB NOT NULL,\n  session_variables JSONB NOT NULL,\n  response_payload JSONB NULL,\n  errors JSONB NULL,\n  created_at timestamptz NOT NULL default now(),\n  response_received_at timestamptz NULL,\n  status text NOT NULL,\n  CHECK (status IN ('created', 'processing', 'completed', 'error'))\n);\n\nCREATE TABLE hdb_catalog.hdb_custom_types\n(\n  custom_types jsonb NOT NULL\n);\n\nCREATE VIEW hdb_catalog.hdb_role AS\n(\n  SELECT DISTINCT role_name FROM (\n    SELECT role_name FROM hdb_catalog.hdb_permission\n    UNION ALL\n    SELECT role_name FROM hdb_catalog.hdb_action_permission\n  ) q\n);\n","prepared":false,"error":{"exec_status":"FatalError","hint":null,"message":"relation \"hdb_action\" already exists","status_code":"42P07","description":null},"arguments":[]},"path":"$","error":"database query error","code":"unexpected"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:29.144+0000","level":"info","detail":{"kind":"server_configuration","info":{"live_query_options":{"batch_size":100,"refetch_delay":1},"transaction_isolation":"ISOLATION LEVEL READ COMMITTED","plan_cache_options":{"plan_cache_size":null},"enabled_log_types":["http-log","websocket-log","startup","webhook-log"],"server_host":"HostAny","enable_allowlist":false,"log_level":"info","auth_hook_mode":null,"use_prepared_statements":true,"unauth_role":"public","stringify_numeric_types":false,"enabled_apis":["metadata","graphql","config","pgdump"],"enable_telemetry":true,"enable_console":true,"auth_hook":null,"jwt_secret":null,"cors_config":{"allowed_origins":"*","disabled":false,"ws_read_cookie":null},"console_assets_dir":null,"admin_secret_set":true,"port":8080}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:29.144+0000","level":"info","detail":{"kind":"postgres_connection","info":{"retries":10,"database_url":"postgres://postgres:...@dokku-postgres-prodclone:5432/prodclone"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:29.144+0000","level":"error","detail":{"kind":"db_migrate","info":{"internal":{"statement":"CREATE TABLE hdb_catalog.hdb_action\n(\n  action_name TEXT PRIMARY KEY,\n  action_defn JSONB NOT NULL,\n  comment TEXT NULL,\n  is_system_defined boolean default false\n);\n\nCREATE TABLE hdb_catalog.hdb_action_permission\n(\n  action_name TEXT NOT NULL,\n  role_name TEXT NOT NULL,\n  definition JSONB NOT NULL DEFAULT '{}'::jsonb,\n  comment    TEXT NULL,\n\n  PRIMARY KEY (action_name, role_name),\n  FOREIGN KEY (action_name) REFERENCES hdb_catalog.hdb_action(action_name) ON UPDATE CASCADE\n);\n\nCREATE TABLE hdb_catalog.hdb_action_log\n(\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  -- we deliberately do not reference the action name\n  -- because sometimes we may want to retain history\n  -- after dropping the action\n  action_name TEXT,\n  input_payload JSONB NOT NULL,\n  request_headers JSONB NOT NULL,\n  session_variables JSONB NOT NULL,\n  response_payload JSONB NULL,\n  errors JSONB NULL,\n  created_at timestamptz NOT NULL default now(),\n  response_received_at timestamptz NULL,\n  status text NOT NULL,\n  CHECK (status IN ('created', 'processing', 'completed', 'error'))\n);\n\nCREATE TABLE hdb_catalog.hdb_custom_types\n(\n  custom_types jsonb NOT NULL\n);\n\nCREATE VIEW hdb_catalog.hdb_role AS\n(\n  SELECT DISTINCT role_name FROM (\n    SELECT role_name FROM hdb_catalog.hdb_permission\n    UNION ALL\n    SELECT role_name FROM hdb_catalog.hdb_action_permission\n  ) q\n);\n","prepared":false,"error":{"exec_status":"FatalError","hint":null,"message":"relation \"hdb_action\" already exists","status_code":"42P07","description":null},"arguments":[]},"path":"$","error":"database query error","code":"unexpected"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:31.830+0000","level":"info","detail":{"kind":"server_configuration","info":{"live_query_options":{"batch_size":100,"refetch_delay":1},"transaction_isolation":"ISOLATION LEVEL READ COMMITTED","plan_cache_options":{"plan_cache_size":null},"enabled_log_types":["http-log","websocket-log","startup","webhook-log"],"server_host":"HostAny","enable_allowlist":false,"log_level":"info","auth_hook_mode":null,"use_prepared_statements":true,"unauth_role":"public","stringify_numeric_types":false,"enabled_apis":["metadata","graphql","config","pgdump"],"enable_telemetry":true,"enable_console":true,"auth_hook":null,"jwt_secret":null,"cors_config":{"allowed_origins":"*","disabled":false,"ws_read_cookie":null},"console_assets_dir":null,"admin_secret_set":true,"port":8080}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:31.830+0000","level":"info","detail":{"kind":"postgres_connection","info":{"retries":10,"database_url":"postgres://postgres:...@dokku-postgres-prodclone:5432/prodclone"}}}
       {"type":"startup","timestamp":"2020-06-17T13:32:31.830+0000","level":"error","detail":{"kind":"db_migrate","info":{"internal":{"statement":"CREATE TABLE hdb_catalog.hdb_action\n(\n  action_name TEXT PRIMARY KEY,\n  action_defn JSONB NOT NULL,\n  comment TEXT NULL,\n  is_system_defined boolean default false\n);\n\nCREATE TABLE hdb_catalog.hdb_action_permission\n(\n  action_name TEXT NOT NULL,\n  role_name TEXT NOT NULL,\n  definition JSONB NOT NULL DEFAULT '{}'::jsonb,\n  comment    TEXT NULL,\n\n  PRIMARY KEY (action_name, role_name),\n  FOREIGN KEY (action_name) REFERENCES hdb_catalog.hdb_action(action_name) ON UPDATE CASCADE\n);\n\nCREATE TABLE hdb_catalog.hdb_action_log\n(\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  -- we deliberately do not reference the action name\n  -- because sometimes we may want to retain history\n  -- after dropping the action\n  action_name TEXT,\n  input_payload JSONB NOT NULL,\n  request_headers JSONB NOT NULL,\n  session_variables JSONB NOT NULL,\n  response_payload JSONB NULL,\n  errors JSONB NULL,\n  created_at timestamptz NOT NULL default now(),\n  response_received_at timestamptz NULL,\n  status text NOT NULL,\n  CHECK (status IN ('created', 'processing', 'completed', 'error'))\n);\n\nCREATE TABLE hdb_catalog.hdb_custom_types\n(\n  custom_types jsonb NOT NULL\n);\n\nCREATE VIEW hdb_catalog.hdb_role AS\n(\n  SELECT DISTINCT role_name FROM (\n    SELECT role_name FROM hdb_catalog.hdb_permission\n    UNION ALL\n    SELECT role_name FROM hdb_catalog.hdb_action_permission\n  ) q\n);\n","prepared":false,"error":{"exec_status":"FatalError","hint":null,"message":"relation \"hdb_action\" already exists","status_code":"42P07","description":null},"arguments":[]},"path":"$","error":"database query error","code":"unexpected"}}}

[codingkarthik]

@fastengineer I was not able to reproduce this issue, can you list the steps you used to upgrade?

[codingkarthik]

I was able to upgrade from v1.1.1 to v1.3.0-beta.2 by following the steps in here https://github.com/hasura/graphql-engine-heroku/issues/36#issuecomment-609016274

[fastengineer]

I was able to upgrade from v1.1.1 to v1.3.0-beta.2 by following the steps in here hasura/graphql-engine-heroku#36 (comment)

I am using the same comments. Is there a way to debug it?

[codingkarthik]

Can you run a couple of SQL queries to be able to debug this issue?

select * from hdb_catalog.hdb_version

\dt hdb_catalog.*; (using psql)

[tirumaraiselvan]

@fastengineer Did you run some other version of Hasura on this PG database ?

Seems like your db has a table hdb_catalog.hdb_action already which was introduced only in v1.2.

[djrudolph]

We are running Hasua on GKE using a hasura/graphql-engine:v1.1.1 image, it's running fine. When trying to upgrade to version 1.2.2, or any version above 1.1.1., the container is reporting a "database query error". We then need to rollback to v1.1.1. Any help is appreciated
eg:

{"type":"startup","timestamp":"2020-06-22T23:31:11.404+0000","level":"error","detail":{"kind":"db_migrate","info":{"internal":{"statement":"CREATE TABLE hdb_catalog.hdb_actionn(n action_name TEXT PRIMARY KEY,n action_defn JSONB NOT NULL,n comment TEXT NULL,n is_system_defined boolean default falsen);nnCREATE TABLE hdb_catalog.hdb_action_permissionn(n action_name TEXT NOT NULL,n role_name TEXT NOT NULL,n definition JSONB NOT NULL DEFAULT '{}'::jsonb,n comment TEXT NULL,nn PRIMARY KEY (action_name, role_name),n FOREIGN KEY (action_name) REFERENCES hdb_catalog.hdb_action(action_name) ON UPDATE CASCADEn);nnCREATE TABLE hdb_catalog.hdb_action_logn(n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),n -- we deliberately do not reference the action namen -- because sometimes we may want to retain historyn -- after dropping the actionn action_name TEXT,n input_payload JSONB NOT NULL,n request_headers JSONB NOT NULL,n session_variables JSONB NOT NULL,n response_payload JSONB NULL,n errors JSONB NULL,n created_at timestamptz NOT NULL default now(),n response_received_at timestamptz NULL,n status text NOT NULL,n CHECK (status IN ('created', 'processing', 'completed', 'error'))n);nnCREATE TABLE hdb_catalog.hdb_custom_typesn(n custom_types jsonb NOT NULLn);nnCREATE VIEW hdb_catalog.hdb_role ASn(n SELECT DISTINCT role_name FROM (n SELECT role_name FROM hdb_catalog.hdb_permissionn UNION ALLn SELECT role_name FROM hdb_catalog.hdb_action_permissionn ) qn);n","prepared":false,"error":{"exec_status":"FatalError","hint":"No function matches the given name and argument types. You might need to add explicit type casts.","message":"function gen_random_uuid() does not exist","status_code":"42883","description":null},"arguments":[]},"path":"$","error":"database query error","code":"unexpected"}}}

[codingkarthik]

@djrudolph From the error message, it seems that the pgcrypto extension is not added in your database. You can add the extension using CREATE EXTENSION IF NOT EXISTS pgcrypto; and then try to upgrade the graphql-engine.

[tirumaraiselvan]

@djrudolph It could also be that pgcrypto extension was moved to a different schema after hasura was running. You will need it in the postgres search_path during the upgrade:

https://hasura.io/docs/1.0/graphql/manual/deployment/postgres-requirements.html#pgcrypto-in-pg-search-path

[djrudolph]

@codingkarthik @tirumaraiselvan Thank you!

TL:DR Both your suggestions helped. We were able to upgrade from the hasura/graphql-engine:v1.1.1 image-> hasura/graphql-engine:v1.2.2

---

We did the following to update our db:

1. Used @codingkarthik suggestion for installing the pgcrypto extension.
2. Followed the link provided by @tirumaraiselvan which showed the required 1.1.1+ pg perms. needed for the 1.2.2 update.

But the container still failed when rolling out. Then...

1. Stumbled upon- https://github.com/hasura/graphql-engine-heroku/issues/71

ALTER TABLE hdb_catalog.hdb_version ALTER COLUMN hasura_uuid SET DEFAULT gen_random_uuid()

And the 1.2.2 container was able to roll-out. Thanks again!