Hasura uses old url on Heroku, how to make Hasura use the right connection string?

[kurt-o-sys]

Hi all,
This is a follow-up of #8759
I used to have a working Hasura on Heroku, which changed about a month ago. Heroku did data migration to another instance (so I have to use another connection string). Although I did change the DATABASE_URL in my Heroku settings, Hasura still seems to use the old connection string somewhere. See logs below:

• Hasura uses the right connection string (@ec2-108-...) here:
  {"detail":{"info":{"database_url":"postgres://(redacted)@ec2-108-128-201-166.eu-west-1.compute.amazonaws.com:5432/d25u4bqll5rg1a","retries":1},"kind":"postgres_connection"},"level":"info","timestamp":"2022-08-17T04:20:16.030+0000","type":"startup"}. That shouldn't fail (I can connect to that url without any issues from another hasura instance or from the Heroku CLI).
• However, a bit further down in the logs, it uses the old connection string (@ec2-54-):
  {"detail":{"info":{"objects":[{"definition":"default","message":"connection to server at \"ec2-54-247-107-109.eu-west-1.compute.amazonaws.com\" (54.247.107.109), port 5432 failed: Connection timed out\n\tIs the server running on that host and accepting TCP/IP connections?\n"....

I already added env variables HASURA_GRAPHQL_METADATA_DATABASE_URL and HASURA_GRAPHQL_DATABASE_URL, still resulting in a connection failed.

What am I missing? How come Hasura is still using an old connection string? (It can't be hard coded, so where is does Hasura get that string from?)

Thanks, Kurt

$ /bin/sh -c graphql-engine\ \ \ \ \ --database-url\ \$DATABASE_URL\ \ \ \ \ serve\ \ \ \ \ --server-port\ \$PORT
{"detail":{"info":{"admin_secret_set":true,"auth_hook":null,"auth_hook_mode":null,"console_assets_dir":null,"cors_config":{"allowed_origins":"*","disabled":false,"ws_read_cookie":null},"enable_allowlist":false,"enable_console":true,"enable_maintenance_mode":false,"enable_metadata_query_logging":false,"enable_telemetry":true,"enabled_apis":["config","pgdump","metadata","graphql"],"enabled_log_types":["startup","webhook-log","http-log","websocket-log"],"events_fetch_batch_size":100,"experimental_features":[],"graceful_shutdown_timeout":60,"infer_function_permissions":true,"jwt_secret":[{"audience":null,"claims_format":"json","claims_namespace":"https://hasura.io/jwt/claims","header":null,"issuer":null,"key":"<JWK REDACTED>","type":"<TYPE REDACTED>"}],"live_query_options":{"batch_size":100,"refetch_delay":1},"log_level":"info","port":27978,"remote_schema_permissions":false,"server_host":"HostAny","stringify_numeric_types":false,"transaction_isolation":"ISOLATION LEVEL READ COMMITTED","unauth_role":"public","use_prepared_statements":true,"v1-boolean-null-collapse":false,"websocket_compression_options":"NoCompression","websocket_connection_init_timeout":"WSConnectionInitTimeout {unWSConnectionInitTimeout = Seconds {seconds = 3s}}","websocket_keep_alive":"KeepAliveDelay {unKeepAliveDelay = Seconds {seconds = 5s}}"},"kind":"server_configuration"},"level":"info","timestamp":"2022-08-17T04:20:16.030+0000","type":"startup"}
{"detail":{"info":{"database_url":"postgres://(redacted)@ec2-108-128-201-166.eu-west-1.compute.amazonaws.com:5432/d25u4bqll5rg1a","retries":1},"kind":"postgres_connection"},"level":"info","timestamp":"2022-08-17T04:20:16.030+0000","type":"startup"}
{"detail":{"message":"postgres connection failed, retrying(0)."},"level":"warn","timestamp":"2022-08-17T04:22:23.316+0000","type":"pg-client"}
{"detail":{"message":"postgres connection failed, retrying(1)."},"level":"warn","timestamp":"2022-08-17T04:24:30.676+0000","type":"pg-client"}
{"detail":{"info":"Already at the latest catalog version (47); nothing to do.","kind":"catalog_migrate"},"level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"startup"}
{"detail":{"info":"Schema sync enabled. Polling at Milliseconds {milliseconds = 1s}","kind":"schema-sync"},"level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"startup"}
{"detail":{"info":{"instance_id":"cbbd9d10-fe8b-4402-9b44-47a54083d90e","message":"listener thread started","thread_id":"ThreadId 37"},"kind":"schema-sync"},"level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"startup"}
{"detail":{"info":{"objects":[{"definition":"default","message":"connection to server at \"ec2-54-247-107-109.eu-west-1.compute.amazonaws.com\" (54.247.107.109), port 5432 failed: Connection timed out\n\tIs the server running on that host and accepting TCP/IP connections?\n","name":"source default","reason":"Inconsistent object: connection error","type":"source"},{"definition":{"enums":[],"input_objects":[],"objects":[{"description":null,"fields":[{"arguments":null,"description":null,"name":"code","type":"String"},{"arguments":null,"description":null,"name":"i18n","type":"jsonb"},{"arguments":null,"description":null,"name":"processData","type":"jsonb"}],"name":"MyQueryOutput","relationships":[]},{"description":null,"fields":[{"arguments":null,"description":null,"name":"code","type":"String"},{"arguments":null,"description":null,"name":"i18n","type":"jsonb"},{"arguments":null,"description":null,"name":"processData","type":"jsonb"}],"name":"RawFeedFlatten","relationships":[]}],"scalars":[]},"name":"custom_types custom_types","reason":"Inconsistent object: validation for the given custom types failed for the following reasons:\n  • the type \"jsonb\" for field \"i18n\" in  object type \"MyQueryOutput\" does not exist\n  • the type \"jsonb\" for field \"processData\" in  object type \"MyQueryOutput\" does not exist\n  • the type \"jsonb\" for field \"i18n\" in  object type \"RawFeedFlatten\" does not exist\n  • the type \"jsonb\" for field \"processData\" in  object type \"RawFeedFlatten\" does not exist\n","type":"custom_types"}]},"message":"Inconsistent Metadata!"},"level":"warn","timestamp":"2022-08-17T04:24:30.676+0000","type":"metadata"}
{"detail":{"info":{"instance_id":"cbbd9d10-fe8b-4402-9b44-47a54083d90e","message":"processor thread started","thread_id":"ThreadId 53"},"kind":"schema-sync"},"level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"startup"}
{"detail":{"info":"starting workers","kind":"event_triggers"},"level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"startup"}
{"detail":{"info":"preparing data","kind":"scheduled_triggers"},"level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"startup"}
{"detail":{"info":"Help us improve Hasura! The graphql-engine server collects anonymized usage stats which allows us to keep improving Hasura at warp speed. To read more or opt-out, visit https://hasura.io/docs/latest/graphql/core/guides/telemetry.html","kind":"telemetry"},"level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"startup"}
{"detail":{"info":{"message":"starting API server","time_taken":254.717127547},"kind":"server"},"level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"startup"}
{"detail":"Thread SchemeUpdate.listener (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}
{"detail":"Thread ourIdleGC (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}
{"detail":"Thread SchemeUpdate.processor (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}
{"detail":{"info":{"objects":[{"definition":"default","message":"connection to server at \"ec2-54-247-107-109.eu-west-1.compute.amazonaws.com\" (54.247.107.109), port 5432 failed: Connection timed out\n\tIs the server running on that host and accepting TCP/IP connections?\n","name":"source default","reason":"Inconsistent object: connection error","type":"source"},{"definition":{"enums":[],"input_objects":[],"objects":[{"description":null,"fields":[{"arguments":null,"description":null,"name":"code","type":"String"},{"arguments":null,"description":null,"name":"i18n","type":"jsonb"},{"arguments":null,"description":null,"name":"processData","type":"jsonb"}],"name":"MyQueryOutput","relationships":[]},{"description":null,"fields":[{"arguments":null,"description":null,"name":"code","type":"String"},{"arguments":null,"description":null,"name":"i18n","type":"jsonb"},{"arguments":null,"description":null,"name":"processData","type":"jsonb"}],"name":"RawFeedFlatten","relationships":[]}],"scalars":[]},"name":"custom_types custom_types","reason":"Inconsistent object: validation for the given custom types failed for the following reasons:\n  • the type \"jsonb\" for field \"i18n\" in  object type \"MyQueryOutput\" does not exist\n  • the type \"jsonb\" for field \"processData\" in  object type \"MyQueryOutput\" does not exist\n  • the type \"jsonb\" for field \"i18n\" in  object type \"RawFeedFlatten\" does not exist\n  • the type \"jsonb\" for field \"processData\" in  object type \"RawFeedFlatten\" does not exist\n","type":"custom_types"}]},"message":"Inconsistent Metadata!"},"level":"warn","timestamp":"2022-08-17T04:24:30.676+0000","type":"metadata"}
{"detail":"Thread asyncActionSubscriptionsProcessor (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}
{"detail":"Thread processScheduledTriggers (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}
{"detail":"Thread runTelemetry (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}
{"detail":"Thread processEventQueue (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}
{"detail":"Thread runCronEventsGenerator (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}
{"detail":"Thread checkForUpdates (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}
{"detail":"Thread asyncActionsProcessor (re)started","level":"info","timestamp":"2022-08-17T04:24:30.676+0000","type":"unstructured"}

[BenoitRanque]

Hasura can pull connection strings for user databases from one of two places: connection strings stored in the metadata, or connection strings available as env vars referenced in the metadata.

My best guess is that your metadata includes a reference to the old connection string via an environment variable that still has the older value. You should be able to check this in your console under database connections.

Most likely this is configured using another environment variable, not DATABASE_URL. Changing it to use that env var should resolve the problem. That or you are setting the connection string in the metadata directly (bad idea don't do it).

As to why this is happening: as you know, hasura v2 does not use a dedicated environment variable to connect the user's database.
Instead, users are able to configure multiple databases and specify arbitrary environment variables for the connection string of each one.

For backwards compatibility reasons, when hasura starts for the first time with the HASURA_GRAPHQL_DATABASE_URL env var set and no HASURA_GRAPHQL_METADATA_DATABASE_URL, hasura will use the former as the metadata database, and will add a new datasource named default using the HASURA_GRAPHQL_DATABASE_URL as the environment variable containing the string.

Next time the server starts, hasura will use that existing metadata. At this point, changing the env var does not matter: metadata has already been initialized, and the env var to use for the default database has been set.

This is of course easily fix by not relying on the backwards compatibly behavior and instead explicitly setting the env var yourself.

[kurt-o-sys]

Hi, that makes perfect sense. Thanks a lot.
So, in short, once the wrong value is in the metadata, you can't overwrite it with env variables.
That is an issue now: because the value in the metadata is wrong, I can't access the console (because it doesn't start) and I can't access the machine itself (because Heroku shuts down application that are not accessible/running well).
That being said: where can I find the metadata? I guess it's not in the db, since (1) the db config is in the metadata and (2) I can connect from another (new) instance of Hasura to the db (but with missing metadata). If I'd like to directly copy/work in the metadata (not via the console), how can I do that?

[BenoitRanque]

My bad, I'd missed that hasura was not starting. I'll dig into a this a bit further. Under no circumstances should an inability to connect to the user database prevent hasura from starting.

Being unable to connect to the metadata database however does prevent server start.

[BenoitRanque]

Did some testing and understood the problem, which is a bug.

To start up, hasura needs to connect to a metadata database.

There are 4 ways to provide the connection string for this database, in order of precedence:

• --metadata-database-url flag
• HASURA_GRAPHQL_METADATA_DATABASE_URL env var
• --database-url flag
• HASURA_GRAPHQL_DATABASE_URL env var

This is a bug, IMHO. The --database-url flag should take precedence over the HASURA_GRAPHQL_DATABASE_URL env var.

Because it does not, currently your instance tries to start up by connecting to that database, and ignores the flag.

There are a few ways to fix your problem:

• use the --metadata-database-url flag instead of --database-url
• delete the HASURA_GRAPHQL_METADATA_DATABASE_URL env var so the --database-url can take precedence.
• start up hasura without either --metadata-database-url or --database-url flags, thus falling back on env vars.

Please let us know if this resolves your issue

[kurt-o-sys]

OK, thanks a lot, I will check it later. I did set both HASURA_GRAPHQL_DATABASE_URL and HASURA_GRAPHQL_METADATA_DATABASE_URL env vars to the right url, but that doesn't seem to do anything.

However, it means I just change the Dockerfile and make --metadata-database-url and/or --database-url point to the right URL, right? (I do have the --database-url flag in there, so probably the missing --metadata-database-url and some wrong value of the current value is an issue. So I guess this should work (just added the 3rd line - need to try it later):

CMD graphql-engine \
    --database-url $DATABASE_URL \
    --metadata-database-url $DATABASE_URL \
    serve \
    --server-port $PORT

[BenoitRanque]

@kurt-o-sys The --database-url and --metadata-database-url are mutually exclusive, pick one or the other, ideally --metadata-database-url

Also, curious if you have any other custom behavior since you seem to be creating your own container with a default command. Any chance there is something funky happening and the env var is getting cached in the image? That is unlikely but if true would be a security concern.

Could you perhaps share more context? For example the full Dockerfile could be useful, and a description of your environment.

[kurtsys-howest]

I didn't create my own container, I have just changed a few small things trying to fix the metadata issue:

FROM hasura/graphql-engine:v2.11.0-beta.1

# temporary fix for https://github.com/hasura/graphql-engine/issues/8770
# only with hasura migrations-containers
#RUN sed -i 's/\(^\s\+\)PORT=/\1local PORT=/m'  /bin/docker-entrypoint.sh

# Heroku only allows to install extensions in the heroku_ext schema
ENV HASURA_GRAPHQL_DEFAULT_EXTENSIONS_SCHEMA=heroku_ext
# https://github.com/hasura/graphql-engine/issues/8734
ENV HASURA_GRAPHQL_METADATA_DATABASE_EXTENSIONS_SCHEMA=heroku_ext

ENV HASURA_GRAPHQL_ENABLE_CONSOLE=true

ENV HASURA_GRAPHQL_DEV_MODE=true

ENV HASURA_GRAPHQL_PG_CONNECTIONS=15

# Change $DATABASE_URL to your heroku postgres URL if you're not using
# the primary postgres instance in your app
CMD graphql-engine \
    --metadata-database-url $DATABASE_URL \
    serve \
    --server-port $PORT

I didn't do anything funky: I deployed Hasura on Heroku, and for some reason (I guess the migration of my postgres by Heroku/SalesForce) I couldn't connect to the db anymore. With the dockerfile above, I still have issues, I just can't seem to solve it, so it seems I just can't get my metadata anymore.
Whenever I connect from a fresh Hasura and connect to the postgres (on Heroku), I do get the data, but no metadata. Is there a way to take a copy of the metadata without dashboard?

[kurtsys-howest]

btw, the last comment from Heroku:

[BenoitRanque]

@kurtsys-howest could you please open a support ticket so we can schedule a meeting and look into this issue? We're likely missing some context.