Prisma client never recovers from P1001

[haggalin]

Bug description

Prisma client never recovers from PrismaClientInitializationError error code P1001, in a scenario where the database goes offline and then online again after some time. Haven't tested other error codes.

We use a serverless aurora v1 from AWS which has a feature called "AutoPause" i.e. the database pauses when there's no activity and then resumes when activity begins again.

How to reproduce

1. Verify that postgres is not running at localhost:5432
2. Run node test.js.
   Contents of test.js:

   /* eslint-disable */
   const { PrismaClient } = require('@prisma/client');
   const readline = require('readline')
   const url = 'postgresql://user:pass@localhost:5432/postgres';
   
   function createClient() {
       return new PrismaClient({
           datasources: {
               db: {
                   url
               }
           }
       });
   }
   
   let prisma = createClient();
   
   function askQuestion(query) {
       const rl = readline.createInterface({
           input: process.stdin,
           output: process.stdout,
       });
   
       return new Promise(resolve => rl.question(query, ans => {
           rl.close();
           resolve(ans);
       }))
   }
   
   
   async function main() {
       try {
           await prisma.$connect();
           //await prisma.$queryRaw`SELECT 1`
       } catch (error) {
           if (error?.errorCode !== 'P1001') {
               console.error(error);
               throw error;
           }
           await askQuestion(`Start up the postgres database at ${url}. Press ENTER when database is online.`);
           try {
               // Note: Workaround is to recreate the client:
               //prisma = createClient();
               await prisma.$connect();
               //await prisma.$queryRaw`SELECT 1`
           } catch (e) {
               console.error(e);
               console.log('\n' +
                   'Test failed');
               return;
           }
           console.log('Test passed!');
           return;
       }
       throw new Error('Test precondition failed: Database should be offline in the first query');
   
   }
   
   main()
       .catch((e) => {
           console.error(e);
           process.exit(1);
       })
       .finally(async () => {
           try {
               await prisma.$disconnect();
           } catch (e) {
               console.error('Disconnect failed');
           }
       });

3. When prompted start up the database in a separate process, e.g. with:

   docker run --name test_db -p 5432:5432 -e POSTGRES_DB=postgres -e POSTGRES_USER=user -e POSTGRES_PASSWORD=pass --rm -it postgres:10.12-alpine
   

4. Press enter to continue.

Output:

Start up the postgres database at postgresql://user:pass@localhost:5432/postgres. Press ENTER when database is online.
PrismaClientInitializationError: Can't reach database server at `localhost`:`5432`

Please make sure your database server is running at `localhost`:`5432`.
    at /home/hagalin/dev/lit/api/node_modules/@prisma/client/runtime/index.js:36260:21 {
  clientVersion: '3.4.1',
  errorCode: 'P1001'
}

Test failed
Disconnect failed

Note: Changed to use $connect() instead of executing a query because the error thrown from the query doesn't have an error code See this issue.

Expected behavior

Expected output from the test above:

Start up the postgres database at postgresql://user:pass@localhost:5432/postgres. Press ENTER when database is online.
Test passed!

Prisma client should be able to recover from this error.

Prisma information

No schema

Environment & setup

• Database: postgres 10.12
• Node.js version: v14.15.4

Prisma Version

prisma                  : 3.4.1
@prisma/client          : 3.4.1
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine 57771c0558568c7d08bd34c7248af5244ae16bd9 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Migration Engine        : migration-engine-cli 57771c0558568c7d08bd34c7248af5244ae16bd9 (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine    : introspection-core 57771c0558568c7d08bd34c7248af5244ae16bd9 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary           : prisma-fmt 57771c0558568c7d08bd34c7248af5244ae16bd9 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash    : 57771c0558568c7d08bd34c7248af5244ae16bd9
Studio                  : 0.438.0

[haggalin]

This is likely the case for other error types as well.

In one of our environments we get an error:

Timed out fetching a new connection from the connection pool. (More info: http://pris.ly/d/connection-pool, Current connection limit: 3)

which I can only recover from by restarting. I'm not sure I can reproduce this error in a small script like before.

[bschulz87]

I've also encountered such problems using Aurora Serverless Postgres and managed to use it with autopause with these connection url params: &pool_timeout=0&connect_timeout=120&sslmode=disable

My service is long running, but disconnects the database after a few minutes without receiving a request. Otherwise the DB would never pause.

[CaioOliveira793]

Seems that all #10153, #9967, #9902 and #9420 are related, the issue with prisma not being able to retry connections after the first try results in an error when the database is down.

Since it was reproduced in different envs, either databases or frameworks seem to not have influence on this.

[sreuter]

@janpio Any update on this by any chance? This is unfortunately becoming more and more of an issue for us. It also has been mentioned by various users across a multitude of issues (see above) by now. Any chance the team could look into this shortly?

[garrensmith]

@sreuter we will be looking at this soon. I can't promise when we will have a fix. But it is a focus for us.

[garrensmith]

#12066 fixes this.

You can test it out with prisma@dev and will be out in the next release. Would be great if you had the time to test this and let us know if this is indeed fixed for you now. Thanks!

[haggalin]

With prisma version:

prisma                  : 3.12.0-dev.38
@prisma/client          : 3.12.0-dev.38
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine 6f1b5b24baff9909403d1355bfee5155e02940b8 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Migration Engine        : migration-engine-cli 6f1b5b24baff9909403d1355bfee5155e02940b8 (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine    : introspection-core 6f1b5b24baff9909403d1355bfee5155e02940b8 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary           : prisma-fmt 6f1b5b24baff9909403d1355bfee5155e02940b8 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash    : 6f1b5b24baff9909403d1355bfee5155e02940b8
Studio                  : 0.458.0
Preview Features        : interactiveTransactions

I ran the test again and I can confirm that it works!

Great work!

[garrensmith]

Awesome. Thanks for confirming

[valerii15298]

@haggalin @garrensmith I am using prisma 3.12.0 and this problem still is happening to me. But in their release notes prisma claims to fix it in 3.12.0. Do not understand why it does not work then?

[janpio]

Most probably because your problem is slightly different from what is described here. Can you please open a new issue and then post the link to it here? Thanks.

[valerii15298]

@janpio here is new issue: #13117
Though I am not sure if it is really different.