Error updating from version beta.3 to latest

[brcol187]

Hello, I tried updating my Hasura database on Heroku from beta.3 to latest and I followed the instructions here: https://docs.hasura.io/1.0/graphql/manual/deployment/heroku/updating.html exactly. It built and then crashed and now I get an error on heroku

(Application error An error occurred in the application and your page could not be served. If you are the application owner, check your logs for details. You can do this from the Heroku CLI with the command heroku logs --tail)

Examining the logs, it appears to be complaining about "hasura_uuid" violating a not-null constraint.

2019-11-20T19:37:27.588060+00:00 heroku[web.1]: State changed from crashed to starting 2019-11-20T19:37:27.413452+00:00 app[web.1]: {"internal":{"statement":"\n INSERT INTO hdb_catalog.hdb_version (version, upgraded_on) VALUES ($1, $2)\n ON CONFLICT ((version IS NOT NULL))\n DO UPDATE SET version = $1, upgraded_on = $2\n ","prepared":false,"error":{"exec_status":"FatalError","hint":null,"message":"null value in column \"hasura_uuid\" violates not-null constraint","status_code":"23502","description":"Failing row contains (null, 27, 2019-11-20 19:37:27.151554+00, {}, {})."},"arguments":["(Oid 25,Just (\"27\",Binary))","(Oid 1184,Just (\"\NUL\STX:\203\DC4R\155\194\",Binary))"]},"path":"$","error":"postgres query error","code":"unexpected"}

Please let me know if you are aware of any fixes!

Thanks.

[lakshmanpasala]

Facing similar issue while updated to v1.0.0 Any update on this?

[danrha]

Same happening to me updating v1.0.0 to v1.1.0 :(

[tirumaraiselvan]

@danrha Can you send me the results of the following 2 sql queries on your postgres instance:

1. select * from hdb_catalog.hdb_version
2. select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_schema = 'hdb_catalog' and table_name = 'hdb_version';

[Difrex]

Hi, @tirumaraiselvan, I have the same problem with upgrading from 1.0.0 to 1.1.0 and the result of the queries is:

1.

select * from hdb_catalog.hdb_version;
             hasura_uuid              | version |          upgraded_on          | cli_state |            console_sta
te             
--------------------------------------+---------+-------------------------------+-----------+-----------------------
---------------
 cc0447d8-5d56-4fea-84b7-a5f2fd4512ab | 28      | 2019-12-23 07:49:05.254051+00 | {}        | {"telemetryNotificatio
nShown": true}

2. select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_schema = 'hdb_catalog' and table_name = 'hdb_version';
   column_name  |        data_type         | character_maximum_length 
   ---------------+--------------------------+--------------------------
   hasura_uuid   | uuid                     |                         
   version       | text                     |                         
   upgraded_on   | timestamp with time zone |                         
   cli_state     | jsonb                    |                         
   console_state | jsonb                    |

[tirumaraiselvan]

@Difrex Thanks for the info. Everything looks fine here.

Can you pls perform the following query through psql or some client on your database and report the result:

BEGIN;
    INSERT INTO hdb_catalog.hdb_version (version, upgraded_on) VALUES (31, now())
    ON CONFLICT ((version IS NOT NULL))
    DO UPDATE SET version = 31, upgraded_on = now();
ROLLBACK;

[Difrex]

@tirumaraiselvan The result is:

ERROR: null value in column "hasura_uuid" violates not-null constraint DETAIL: Failing row contains (null, 31, 2020-03-17 12:36:14.446016+00, {}, {}).

[tirumaraiselvan]

@Difrex We seem to have isolated the issue! The statement above was expected to have succeeded.

Finally, could you tell your postgres version?

[Difrex]

@tirumaraiselvan PG version:

postgres --version
postgres (PostgreSQL) 12.1 (Debian 12.1-1.pgdg100+1)

[tirumaraiselvan]

@Difrex Thanks. Could you pls run this query and report the result:

SELECT * FROM pg_indexes WHERE schemaname = 'hdb_catalog' and tablename = 'hdb_version';

[Difrex]

@tirumaraiselvan Output:

 schemaname  |  tablename  |      indexname      | tablespace |                                                 indexdef                                        

-------------+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------
---------
 hdb_catalog | hdb_version | hdb_version_pkey    |            | CREATE UNIQUE INDEX hdb_version_pkey ON hdb_catalog.hdb_version USING btree (hasura_uuid)
 hdb_catalog | hdb_version | hdb_version_one_row |            | CREATE UNIQUE INDEX hdb_version_one_row ON hdb_catalog.hdb_version USING btree (((version IS NOT NULL)))

[tirumaraiselvan]

@Difrex I am not able to repro this still. I am guessing this might be a problem with Heroku pg: https://help.heroku.com/RKRZV2NO/how-do-i-fix-a-corrupted-index

Here is something that you can try on your current database which is on v1.0.0:

1. Stop Hasura v1.0.0

2. Run the following SQL

BEGIN;

DROP TABLE hdb_catalog.hdb_version;

CREATE TABLE hdb_catalog.hdb_version (
    hasura_uuid UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    version TEXT NOT NULL,
    upgraded_on TIMESTAMPTZ NOT NULL,
    cli_state JSONB NOT NULL DEFAULT '{}'::jsonb,
    console_state JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE UNIQUE INDEX hdb_version_one_row
ON hdb_catalog.hdb_version((version IS NOT NULL));

INSERT INTO hdb_catalog.hdb_version (version, upgraded_on) values (28, now());

COMMIT;

3. Start Hasura v1.0.0
4. Upgrade Hasura to v1.1.0

[tirumaraiselvan]

@Difrex Can you also send me the result of

SELECT column_name, column_default
FROM information_schema.columns
WHERE (table_schema, table_name) = ('hdb_catalog', 'hdb_version')
ORDER BY ordinal_position;

[Difrex]

@tirumaraiselvan

I am not able to repro this still. I am guessing this might be a problem with Heroku pg

Oh, I just found this bug in the Google and not looking to the repo name. I use a self-hosted Hasura installation. And it's running in docker(including the Pg).

My Dockerfile is:

FROM hasura/graphql-engine:v1.1.0.cli-migrations

# Workaround for k8s
RUN mkdir -p /usr/sbin/.hasura && chmod 777 /usr/sbin/.hasura

COPY ./migrations /hasura-migrations

Run the following SQL ...

This workaround works for upgrading

{"type":"startup","timestamp":"2020-03-18T07:14:31.126+0000","level":"info","detail":{"kind":"db_migrate","info":"Successfully migrated from catalog version 28 to version 31."}}

But the migrations not applied and API does not start. I got an error with a very very big SQL error.

Also, the result of the latest query is:

  column_name  |  column_default   
---------------+-------------------
 hasura_uuid   | gen_random_uuid()
 version       | 
 upgraded_on   | 
 cli_state     | '{}'::jsonb
 console_state | '{}'::jsonb

[tirumaraiselvan]

@Difrex Okay, so you have successfully upgraded. But migrations are not working? Could you open a new issue in https://github.com/hasura/graphql-engine/issues with relevant details (maybe shorten the sql to relevant portions only )?

[Difrex]

Okay, so you have successfully upgraded.

Yep, thank you!

But migrations are not working? Could you open a new issue in https://github.com/hasura/graphql-engine/issues with relevant details (maybe shorten the sql to relevant portions only )?

Before creating an issue I'll try to looking deep to the error message. Maybe migrations do not apply because I have custom functions.

[tirumaraiselvan]

Related to: https://github.com/hasura/graphql-engine/issues/4009

Particularly, this comment: https://github.com/hasura/graphql-engine/issues/4009#issuecomment-604846964

[tirumaraiselvan]

Closing this as problem was that the column default was getting dropped. The column default gets dropped when pgcrypto is dropped or public schema is dropped.

The solution is to install pgcrypto and set the default back. For e.g.

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

Also, tracking similar issue in main repo here: https://github.com/hasura/graphql-engine/issues/4009

[mehmetaydogduu]

I tried very hard considering the information here, but could't have a success upgrade from 1.1.1 to 1.2.0. Hasura layer is working as production so I cancelled process at this time.