Relations public.Withdrawal, public.Epoch and metadata does not exist

[mkocur-ledger]

Summary

Hello!

First of all sorry that I duplicated this issue from graphql, but since it doesn't grab any attention, I'm thinking that maybe I should report it here instead. Still, I've seen a similar issue that was redirected to graphql... but the problem is about snapshot restore. I will close the one that is wrongly assigned.
(cardano-foundation/cardano-graphql#897)

I have restored a snapshot with db-sync:
(https://update-cardano-mainnet.iohk.io/cardano-db-sync/13.5/db-sync-snapshot-schema-13.5-block-10823207-x86_64.tgz)

[db-sync-node:Info:83] [2024-09-19 00:51:47.84 UTC] Reached EpochNo 508
[db-sync-node:Info:92] [2024-09-19 00:56:57.60 UTC] Asynchronously wrote a ledger snapshot to /config/state/134092758-670ca68c3d-507.lstate in 323.408766108s.
[db-sync-node:Info:83] [2024-09-19 01:02:12.81 UTC] Removing old epoch boundary file /config/state/130636777-c1ecf5af89-499.lstate
[db-sync-node:Info:83] [2024-09-19 01:02:28.43 UTC] Reached EpochNo 509
[db-sync-node:Info:83] [2024-09-19 01:02:30.04 UTC] Received block which is not in the db with HeaderFields {headerFieldSlot = SlotNo 134525754, headerFieldBlockNo = BlockNo 10823208, headerFieldHash = 1fbfe5fd0f3e99d6e1f4ccffd8ad7202e36e57c18cf8863bf6aec57eb1062db9}. Time to restore consistency.
[db-sync-node:Info:83] [2024-09-19 01:02:30.05 UTC] Starting at epoch 509
[db-sync-node:Info:83] [2024-09-19 01:02:32.80 UTC] Insert Conway Block: epoch 509, slot 134525754, block 10823208, hash 1fbfe5fd0f3e99d6e1f4ccffd8ad7202e36e57c18cf8863bf6aec57eb1062db9
[db-sync-node:Info:83] [2024-09-19 01:02:32.85 UTC] Setting ConsistencyLevel to Consistent
[db-sync-node:Info:92] [2024-09-19 01:05:35.24 UTC] Asynchronously wrote a ledger snapshot to /config/state/134524753-29011cc132-508.lstate in 202.425990791s.
[db-sync-node:Info:83] [2024-09-19 01:08:29.04 UTC] Insert Conway Block: epoch 509, slot 134563412, block 10825000, hash e6f71ef9d62932dd429b61051e63fd97929b605f9c33612817cc5205eb6b4ca2
[db-sync-node:Info:83] [2024-09-19 01:23:30.51 UTC] Inserted 1335280 EpochStake for EpochNo 510
[db-sync-node:Info:83] [2024-09-19 01:31:00.87 UTC] Insert Conway Block: epoch 509, slot 134664823, block 10830000, hash ec59c8053d5f30299ebbfb272083ead1e968f4aab60ab868203a9c8b93cd7201
[db-sync-node:Info:83] [2024-09-19 01:31:00.87 UTC] Voting Anchor Offchain metadata fetch: 0 results, 1000 fetch errors
[db-sync-node:Info:83] [2024-09-19 01:31:01.69 UTC] Pool Offchain metadata fetch: 0 results, 1000 fetch errors
[db-sync-node:Info:83] [2024-09-19 01:41:25.76 UTC] Insert Conway Block: epoch 509, slot 134767193, block 10835000, hash 11cace016758192b7459071aade17f575b305ebf374f6970c78f4ae936bead5e
[db-sync-node:Warning:83] [2024-09-19 01:47:59.59 UTC] prepareTxMetadata: Column 'json' in table 'metadata' was recorded as null, due to a Unicode NUL character found when trying to parse the json.
[db-sync-node:Warning:83] [2024-09-19 01:47:59.79 UTC] prepareTxMetadata: Column 'json' in table 'metadata' was recorded as null, due to a Unicode NUL character found when trying to parse the json.
[db-sync-node:Info:83] [2024-09-19 01:49:47.05 UTC] Insert Conway Block: epoch 509, slot 134867805, block 10840000, hash 4a858262fe88e24fabffd4f7e2b4ef6b51699cbd3aea350e63f748f98f78d175

Those 2 warnings at the end correspond with error I'm getting from token-metadata-registry:

    at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351) ~[spring-aop-6.1.3.jar!/:6.1.3]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:277) ~[spring-data-commons-3.2.2.jar!/:3.2.2]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170) ~[spring-data-commons-3.2.2.jar!/:3.2.2]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158) ~[spring-data-commons-3.2.2.jar!/:3.2.2]
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516) ~[spring-data-commons-3.2.2.jar!/:3.2.2]
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285) ~[spring-data-commons-3.2.2.jar!/:3.2.2]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:628) ~[spring-data-commons-3.2.2.jar!/:3.2.2]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.3.jar!/:6.1.3]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:168) ~[spring-data-commons-3.2.2.jar!/:3.2.2]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143) ~[spring-data-commons-3.2.2.jar!/:3.2.2]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.3.jar!/:6.1.3]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:70) ~[spring-data-commons-3.2.2.jar!/:3.2.2]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.3.jar!/:6.1.3]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.1.3.jar!/:6.1.3]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:385) ~[spring-tx-6.1.3.jar!/:6.1.3]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.1.3.jar!/:6.1.3]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.3.jar!/:6.1.3]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.1.3.jar!/:6.1.3]
    ... 38 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: relation "metadata" does not exist
  Position: 155

Furthermore, hasura/graphql-engine:v2.43.0.cli-migrations-v2 is reporting public.Withdrawal and public.Epoch relations missing:

{"detail":{"http_info":{"content_encoding":null,"http_version":"HTTP/1.1","ip":"10.42.10.97","method":"POST","status":200,"url":"/v1/graphql"},"operation":{"error":{"code":"unexpected","error":"database q
uery error","internal":{"arguments":["(Oid 114,Just (\"{\\\"x-hasura-role\\\":\\\"cardano-graphql\\\"}\",Binary))"],"error":{"description":null,"exec_status":"FatalError","hint":null,"message":"relation \
"public.Epoch\" does not exist","status_code":"42P01"},"prepared":true,"statement":"SELECT  coalesce(json_agg(\"root\" ORDER BY \"root.pg.number\" DESC NULLS FIRST), '[]' ) AS \"root\" FROM  (SELECT  row_
to_json((SELECT  \"_e\"  FROM  (SELECT  \"_root.base\".\"number\" AS \"number\"       ) AS \"_e\"      ) ) AS \"root\", \"_root.base\".\"number\" AS \"root.pg.number\" FROM  (SELECT  *  FROM \"public\".\"
Epoch\"  WHERE ('true')   ORDER BY \"number\" DESC NULLS FIRST LIMIT 1 ) AS \"_root.base\"    ORDER BY \"root.pg.number\" DESC NULLS FIRST  ) AS \"_root\"      "},"path":"$"},"query":{"type":null},"query_
execution_time":3.047279e-3,"request_id":"fc74d816-c063-47d9-ae71-cf1ac17e8c2b","request_mode":"error","request_read_time":6.125e-6,"response_size":826,"uncompressed_response_size":826,"user_vars":{"x-has
ura-role":"cardano-graphql"}},"request_id":"fc74d816-c063-47d9-ae71-cf1ac17e8c2b"},"level":"error","span_id":"db6b71dd35db4c48","timestamp":"2024-09-19T06:25:06.376+0000","trace_id":"4753ed8ad279d7b47c8bf
4e4ac8a7194","type":"http-log"}

{"timestamp":"2024-09-19T06:19:34.000+0000","level":"info","type":"startup","detail":{"kind":"migrations-startup","info":"env var HASURA_GRAPHQL_METADATA_DIR is not set, defaulting to /hasura-metadata"}}
{"timestamp":"2024-09-19T06:19:34.000+0000","level":"info","type":"startup","detail":{"kind":"migrations-apply","info":"directory /hasura-migrations does not exist, skipping migrations"}}
{"timestamp":"2024-09-19T06:19:34.000+0000","level":"info","type":"startup","detail":{"kind":"migrations-apply","info":"directory /hasura-metadata does not exist, skipping metadata"}}
{"timestamp":"2024-09-19T06:19:34.000+0000","level":"info","type":"startup","detail":{"kind":"migrations-shutdown","info":"killing temporary server"}}
{"timestamp":"2024-09-19T06:19:34.000+0000","level":"info","type":"startup","detail":{"kind":"migrations-shutdown","info":"graphql-engine will now start in normal mode"}}
{"detail":{"http_info":{"content_encoding":null,"http_version":"HTTP/1.1","ip":"10.42.10.97","method":"POST","status":200,"url":"/v1/graphql"},"operation":{"error":{"code":"unexpected","error":"database q
uery error","internal":{"arguments":["(Oid 114,Just (\"{\\\"x-hasura-role\\\":\\\"cardano-graphql\\\"}\",Binary))"],"error":{"description":null,"exec_status":"FatalError","hint":null,"message":"relation \
"public.Withdrawal\" does not exist","status_code":"42P01"},"prepared":true,"statement":"SELECT  json_build_object('aggregate', json_build_object('sum', json_build_object('amount', sum(\"_root\".\"root.pg
.amount\" ) ) ) ) AS \"root\" FROM  (SELECT  \"_root.base\".\"amount\" AS \"root.pg.amount\" FROM  (SELECT  *  FROM \"public\".\"Withdrawal\"  WHERE ('true')     ) AS \"_root.base\"      ) AS \"_root\"
   "},"path":"$"},"query":{"type":null},"query_execution_time":265.310693138,"request_id":"06d7e070-be6b-4a1a-98a5-5f10d6330822","request_mode":"error","request_read_time":5.408e-6,"response_size":669,"un
compressed_response_size":669,"user_vars":{"x-hasura-role":"cardano-graphql"}},"request_id":"06d7e070-be6b-4a1a-98a5-5f10d6330822"},"level":"error","span_id":"4c832a518ef4b722","timestamp":"2024-09-19T06:
25:05.375+0000","trace_id":"fdb208a2ea0746a898ea78d82148a64d","type":"http-log"}

The sync status:

curl \
  -X POST \
  -H "Content-Type: application/json" \
  -d '{"query": "{ cardanoDbMeta { initialized syncPercentage }}"}' \
  http://localhost:3100/graphql
{"data":{"cardanoDbMeta":{"initialized":true,"syncPercentage":100}},"extensions":{"tracing":{"version":1,"startTime":"2024-09-19T06:49:12.102Z","endTime":"2024-09-19T06:49:12.115Z","duration":13500570,"execution":{"resolvers":[{"path":["cardanoDbMeta"],"parentType":"Query","fieldName":"cardanoDbMeta","returnType":"CardanoDbMeta!","startOffset":502588,"duration":12826503},{"path":["cardanoDbMeta","initialized"],"parentType":"CardanoDbMeta","fieldName":"initialized","returnType":"Boolean!","startOffset":13396134,"duration":15571},{"path":["cardanoDbMeta","syncPercentage"],"parentType":"CardanoDbMeta","fieldName":"syncPercentage","returnType":"Percentage!","startOffset":13420908,"duration":5172}]}}}}
 curl \
  -X POST \
  -H "Content-Type: application/json" \
  -d '{"query": "{ cardano { tip { number slotNo epoch { number } } } }"}' http://localhost:3100/graphql
{"data":{"cardano":{"tip":{"number":10854265,"slotNo":"135162299","epoch":{"number":510}}}},"extensions":{"tracing":{"version":1,"startTime":"2024-09-19T06:49:55.759Z","endTime":"2024-09-19T06:49:55.779Z","duration":19672084,"execution":{"resolvers":[{"path":["cardano"],"parentType":"Query","fieldName":"cardano","returnType":"Cardano!","startOffset":2899698,"duration":16592105},{"path":["cardano","tip"],"parentType":"Cardano","fieldName":"tip","returnType":"Block!","startOffset":19533689,"duration":15467},{"path":["cardano","tip","number"],"parentType":"Block","fieldName":"number","returnType":"Int","startOffset":19563055,"duration":9800},{"path":["cardano","tip","slotNo"],"parentType":"Block","fieldName":"slotNo","returnType":"String","startOffset":19582450,"duration":3407},{"path":["cardano","tip","epoch"],"parentType":"Block","fieldName":"epoch","returnType":"Epoch","startOffset":19597505,"duration":5842},{"path":["cardano","tip","epoch","number"],"parentType":"Epoch","fieldName":"number","returnType":"Int!","startOffset":19617981,"duration":4217}]}}}}

I have restarted graphql, tmr, hasura, background components after the sync was completed to face the same issue.

db-sync: "13.5.0.2"
node: "9.1.1"
ogmios-server: "6.6.2"
graphql-engine: "v2.43.0.cli-migrations-v2"
graphql-server: "8.2.2"
graphql-background: "8.2.2-mainnet"
cf-token-metadata-registry-api: "1.0.2"
postgres: 15.6

Steps to reproduce the bug

No response

Actual Result

Some db data missing after snapshot restore.

Expected Result

No errors

Environment

Kubernetes
{
os: {
arch: 'x64',
cpus: { count: 48, model: 'Intel(R) Xeon(R) Gold 6312U CPU @ 2.40GHz' },
release: '5.15.0-117-generic',
totalMem: 269877899264,
type: 'Linux'
},
version: '8.0.0'
}

Platform

• Linux (Ubuntu)
• Linux (Other)
• macOS
• Windows

Platform version

Ubuntu 22.04.4 LTS

Runtime

• Node.js
• Docker

Runtime version

No response

[mkocur-ledger]

Most likely the bug is on hasura's side. When I run this query using psql and connection string from hasura pod, it works. Metadata was token-metadata-registry misconfiguration, pointing to wrong db_schema. Fixed configuration:

  NETWORK: mainnet
  DB_URL: "jdbc:postgresql://${DB_HOST}:${DB_PORT}/${DB_NAME}?currentSchema=${DB_SCHEMA}"
  TOKEN_METADATA_SYNC_JOB: true
  DB_SCHEMA: public