subql-query not working with RDS database in master-slave architecture

lalitsharmaprofile commented 1 year ago

Prerequisites

[x] Are you running the latest version(s)? -> yes
[x] Did you check the debugging guide? --> I checked https://academy.subquery.network/academy/tutorials_examples/debug-projects.html#video-guide but doesn't seem it suggesting subquery graphql
[x] Did you check the FAQs and Discussions? --> yes, here is the latest link by the way:https://academy.subquery.network/faqs/faqs.html
[x] Are you reporting to the correct repository? --> yes, the npm package points to this repo https://www.npmjs.com/package/@subql/query
[x] Did you search for an existing issue or pull request? -> yes, here https://github.com/subquery/subql/issues?q=is%3Aissue+is%3Aopen+database+rds+recovery+listen

Description

[Description of the bug or feature] Running a subquery project for avalanche (https://github.com/subquery/avalanche-subql-starter) - c-type chain, mainnet. Using RDS database with master-slave architecture. I am storing data from indexer in master instance and trying to query data from subquery graphql using slave instance

NOTE:- the subql-query docker service working fine with RDS master-slave architecture when using version 1.6.1 [released on 2022-10-27] but giving error with latest version i.e, 1.9.0 [released on 2022-11-30]

Details

Seeing the following error for onfinality/subql-query:latest docker service:-

2022-12-01T06:55:19.515Z <nestjs> INFO Starting Nest application... 
2022-12-01T06:55:19.532Z <nestjs> INFO AppModule dependencies initialized 
2022-12-01T06:55:19.532Z <nestjs> INFO ConfigureModule dependencies initialized 
2022-12-01T06:55:19.532Z <nestjs> INFO GraphqlModule dependencies initialized 
2022-12-01T06:55:19.533Z <subql-query> INFO Started playground at http://localhost:3003 
/usr/local/lib/node_modules/@subql/query/dist/graphql/graphql.module.js:59
            throw new Error(`create apollo server failed, ${e.message}`);
                  ^

Error: create apollo server failed, cannot execute LISTEN during recovery
    at GraphqlModule.onModuleInit (/usr/local/lib/node_modules/@subql/query/dist/graphql/graphql.module.js:59:19)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async callModuleInitHook (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/hooks/on-module-init.hook.js:51:9)
    at async NestApplication.callInitHook (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/nest-application-context.js:178:13)
    at async NestApplication.init (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/nest-application.js:96:9)
    at async NestApplication.listen (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/nest-application.js:158:33)
    at async /usr/local/lib/node_modules/@subql/query/dist/main.js:29:5

These details can help to reproduce the environment the issue is occurring

Local Environment: [You can get this information from executing subql version.] --> @subql/cli/1.8.0 linux-x64 node-v17.2.0

Query Version: [What is the version of the query service?] --> Probably [1.9.0] - 2022-11-30, using this docker image https://hub.docker.com/layers/onfinality/subql-query/latest/images/sha256-d7419524924fd98b91b6ce9dfa8651ca26d34f3cbebaf8db6b5e40221d15b2d8?context=explore,

Indexer Version: [What is the version of the indexer service?] Probably [v1.10.5] (https://hub.docker.com/layers/onfinality/subql-node-avalanche/v1.10.5/images/sha256-02d9b8a4cb27af97a547f58e64444da619dcd82748d5c6ce8531573da37e1bf6?context=explore)

Network Details:

[Network] -> Mainnet
[Block height, a block height where the issue is happening] -> I don't think it is related to a block, occurs as soon as subquery-query node starts
[Dictionary endpoint, if used] --> not used

Steps to Reproduce

[First Step] -> clone https://github.com/subquery/avalanche-subql-starter
[Second Step] -> go to the folder
[Third Step] -> run $npm i from the folder opened in terminal
$npm run codegen
$npm run build
$npm run start:docker

Example project: [A link to a minimal example that can reproduce the issue] --> https://github.com/subquery/avalanche-subql-starter

Expected behavior: [What you expected to happen] It should work fine with RDS master slave database and i should be able to query the graphql service

Actual behavior: [What actually happened] subql-query docker instance giving error:-

2022-12-01T06:55:19.515Z <nestjs> INFO Starting Nest application... 
2022-12-01T06:55:19.532Z <nestjs> INFO AppModule dependencies initialized 
2022-12-01T06:55:19.532Z <nestjs> INFO ConfigureModule dependencies initialized 
2022-12-01T06:55:19.532Z <nestjs> INFO GraphqlModule dependencies initialized 
2022-12-01T06:55:19.533Z <subql-query> INFO Started playground at http://localhost:3003 
/usr/local/lib/node_modules/@subql/query/dist/graphql/graphql.module.js:59
            throw new Error(`create apollo server failed, ${e.message}`);
                  ^

Error: create apollo server failed, cannot execute LISTEN during recovery
    at GraphqlModule.onModuleInit (/usr/local/lib/node_modules/@subql/query/dist/graphql/graphql.module.js:59:19)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async callModuleInitHook (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/hooks/on-module-init.hook.js:51:9)
    at async NestApplication.callInitHook (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/nest-application-context.js:178:13)
    at async NestApplication.init (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/nest-application.js:96:9)
    at async NestApplication.listen (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/nest-application.js:158:33)
    at async /usr/local/lib/node_modules/@subql/query/dist/main.js:29:5

Any other information

Please note subql-avalanche-indexer docker service is able to save data to master instance as i can access via pgadmin / postgres via terminal and query the data.

NOTE:- the subql-query docker service working fine with RDS master-slave architecture when using version 1.6.1 [released on 2022-10-27] but giving error with latest version i.e, 1.9.0 [released on 2022-11-30]

Is there any other information you would like to add? No, That's all.

lalitsharmaprofile commented 1 year ago

Note: Just found that it is working on version onfinality/subql-query:v1.6.2-1 but error started from onfinality/subql-query:v1.6.2-2

lalitsharmaprofile commented 1 year ago

I found this code that is causing the error:- in file packages/query/src/graphql/graphql.module.ts

 if (!argv['disable-hot-schema']) {
      const pgClient = await this.pgPool.connect();
      await pgClient.query(`LISTEN "${hashName(dbSchema, 'schema_channel', '_metadata')}"`);

      pgClient.on('notification', (msg) => {
        if (msg.payload === 'schema_updated') {
          void this.schemaListener(dbSchema, options);
        }
      });
    }

It was added in this commit 642f039

If I run the subql-query service with --disable-hot-schema=true flag, it's working fine but the hot-schema-load feature will be disabled.

lalitsharmaprofile commented 1 year ago

Hi there, is the fix of adding a try-catch block sufficient? I mean it will disable the HSR.

subquery / subql