Prisma-client-js: Error in connector: Error querying the database: connection closed

[soosap]

I am trying to execute photon commands but I am left with this error. Whenever the server starts the first few photon invocations are executed without problems. Yet a few minutes later the following error is thrown:

Invalid `photon.()` invocation in /Users/soosap/project/node_modules/nexus-prisma/src/builder.ts:196:18                                                           │
Reason: Error in connector: Error querying the database: connection closed                 │
  at PhotonFetcher.<anonymous> (/Users/soosap/project/node_modules/@generated/photon/index.js:51:27)                                                            │
  at Generator.throw (<anonymous>)                                                       │ 
  at rejected (/Users/soosap/project/node_m│
odules/@generated/photon/index.js:6:65)                                                                              │
  at process.internalTickCallback (internal/process/next_tick.js:77:7)

I have the following setup in my server index.ts:

import { Photon } from '@generated/photon';
import schema from './schema';

const photon = new Photon();

const app = new GraphQLServer({
  schema,
  context: async (request) => {
    return {
      ...request,
      photon,
    };
  },
  middlewares: [ authenticate ],
});

app.start({
  port: PORT,
  playground: process.env.NODE_ENV === 'production' ? false : PLAYGROUND_ENDPOINT,
  endpoint: GRAPHQL_ENDPOINT,
});

[soosap]

level: 'error',
application: 'prisma',
date: 2019-11-02T21:34:51.999Z }
2019-11-02T21:34:52.489Z engine:log { message: 'postgres connection error: connection closed',

Turning on debug points me to a potential source of the problem, namely postgres. But not sure what exactly the problem is and why the connection closes.

[soosap]

Turns out this is a problem only if my postgres instance sits behind a reverse proxy. Switched to a local Docker for Mac postgres instance and it works as expected. Previously, I tried to connect to a postgres instance sitting in my Kubernetes cluster (i.e. a production deployment). I was hoping to do the migrations from my local machine pointing to the production k8s postgres instance exposed through a reverse proxy. Feel free to close, if there is nothing on your end that can be done here.

[pantharshit00]

Do you feel this was a bug in Prisma or it was an implication of your reverse proxy?

Also, what kind of reverse proxy you were using?

[soosap]

Hi sorry for the late reply. I am using HAProxy in Kubernetes. If I expose the postgres database through a reverse proxy, i.e. at [email protected], and at the same time try to connect to that endpoint from the prisma app running on my local machine... then a connection is first established, but after a couple of minutes the connection is lost. In such cases you would have to restart the prisma2 app and trigger a reconnect.

I gave up on the idea to have a dev postgres instance running in my kubernetes cluster. However, I am still able to use that technique to trigger migrations etc. locally for a postgres instance deployed in the cloud. The connection is not lost immediately, but only after a couple of minutes. For development I am now using a local Docker for Mac based postgres instance. With it I am not experiencing any connection-lost issues. For that reason, I think it is safe to assume that this is rather a reverse proxy configuration issue und less of a prisma2 problem.

[janpio]

@pantharshit00 Do we maybe still try to want to understand this better and find a possible workaround? @soosap might not be the only person using such a setup.

[pantharshit00]

If @soosap can share the configuration of the proxy, I can try to debug this. But I am pretty confident that this is not a photon issue

[janpio]

Exactly, which is why it makes sense to understand the situation better. Maybe there are other way Photon can actually help out here instead of just failing.

[soosap]

OK guys let me provide you with more information and log messages. Actually when I was trying today to connect to postgres.myco.co using Prisma2 (i.e. prisma2 dev) I get the following error message:

Error: Could not connect to database: Starting a postgresql pool with 9 connections.
[2019-11-28T19:00:17Z ERROR migration_engine] Error connecting to the database

On the other hand, third party clients (i.e. SQLPro) are capable of connecting to that very same host.

It also means that I no longer see a "Connection Lost" error message.

In terms of setup: I run an HAProxy on a Kubernetes cluster. However, I do not manage the HAProxy config myself. Instead I am using a kubernetes controller called voyager which dynamically stitches together the config file depending on services it finds in the cluster. To be more precise I am actually using a AWS ELB at the very front. That routes traffic to HAProxy and that eventually directs the traffic to the Postgres docker container. Let me know if there is anything else I can provide you with.

[pantharshit00]

@soosap Can you please add &sslaccept=accept_invalid and try this once?

Right now our connection policy is pretty strict and that might explain why other clients are able to connect while prisma is unable to perform a connection. See https://github.com/prisma/prisma2/issues/884

[soosap]

I switched from appscode/voyager to traefik for ingress routing in my kubernetes cluster. Using traefik I no longer have the Error in connector: Error querying the database: connection closed issue. Instead I receive the undefined target="exit" error that is popping up in a number of places. See https://github.com/prisma/photonjs/issues/357

I guess we can revisit this issue once #357 has been resolved.

[divyenduz]

@soosap Since #357 is fixed, can you please try this again and see what the actual error is? Thanks.

[soosap]

@divyenduz, the problem still exists, however, I have a different error message now:
Tried with: @prisma/[email protected]

Error:
Invalid `photon.()` invocation in /restaurant/server/src/app/newsletter/subscribeOneNewsletter.ts:30:35
undefined target="exit" timestamp="2020-01-25T17:29:00.274Z" fields={"message":"255"}
at PhotonFetcher.<anonymous> (/Users/soosap/Documents/restaurant/server/node_modules/@prisma/photon/index.js:65:27)
at Generator.throw (<anonymous>)
at rejected (/Users/soosap/Documents/restaurant/server/node_modules/@prisma/photon/index.js:6:65)
at process.internalTickCallback (internal/process/next_tick.js:77:7)

[pantharshit00]

Hi @soosap,

@prisma/photon has been renamed to @prisma/client(Release notes: https://github.com/prisma/prisma2/releases/tag/2.0.0-preview020). So can you please upgrade to the latest version and try this.

If you can still reproduce, please share your kubernetes config file so that I can try to locally reproduce this.

[janpio]

Ping @soosap

[fgnass]

We are experiencing exactly the same symptoms with a MySQL Galera cluster behind a TCP loadbalancer (no SSL):

Error in connector: Error querying the database: Connection to the server is closed.
     at PrismaClientFetcher.request (./node_modules/@prisma/client/runtime/index.js:1:47490

At the same time prisma2 migrate up --experimental runs just fine. An ideas what could be the cause?

[cimchd]

Same here (prisma2-preview024):

 Invalid `prisma.user.findOne()` invocation in
api_1            | /api/[...]
api_1            |
api_1            | Error in connector: Error querying the database: Connection to the server is closed.
api_1            |     at PrismaClientFetcher.request (/api/node_modules/@prisma/client/runtime/index.js:1:47799)
api_1            |     at runMicrotasks (<anonymous>)
api_1            |     at processTicksAndRejections (internal/process/task_queues.js:97:5)

Here the database (MariaDB) runs inside a docker container behind a ngninx reverse proxy, configured with docker-compose:

// docker-compose.yaml
version: '3.6'

services:
  nginx-proxy:
    image: jwilder/nginx-proxy
   [...]
  api:
    // node.js container with prisma2
   [...]
  db:
    image: mariadb:10.1
   [...]

First of all everything is fine and the PrismaClient works as expected. The error occurs after several minutes of inactivity. The database is still available (I can connect directly to it and perform some queries), so the docker configuration should be fine.

[pantharshit00]

@cimchd You can please share that complete docker-compose file so that I can try the exact setup locally 🙏 ?

[cimchd]

@cimchd You can please share that complete docker-compose file so that I can try the exact setup locally 🙏 ?

Sure, I will set up a test repo and give you link. Give me some time...

[cimchd]

@pantharshit00 We created a test repo in our team:
https://github.com/lboecker/prisma-client-js-issue-283

The error occured without our ngninx-proxy after 15 minutes, so we simplified the repo as much as possible. We only left an express-server container (api) and the MariaDB container (db) in it. Hope you can reproduce the error with this repo.

[cimchd]

@pantharshit00 @janpio
As long as we have this error: is there a quick workaround to restart the prisma db connection each 10-15 minutes, or something else?

[pantharshit00]

@cimchd Sorry for the late reply here.

I think we fixed a pooling issue recently. Please try again with 2.0.0.-beta.1

It has some breaking changes related to relation so you might need to introspect your database to get the updated schema

[cimchd]

@cimchd Sorry for the late reply here.

I think we fixed a pooling issue recently. Please try again with 2.0.0.-beta.1

It has some breaking changes related to relation so you might need to introspect your database to get the updated schema

We swicthed to the beta now and the error seems to be gone :-) Thank you for your support.

[pantharshit00]

Ok, going to close this one now. @soosap Please reply here if you are still facing this issue