search

prisma / prisma1

💾 Database Tools incl. ORM, Migrations and Admin UI (Postgres, MySQL & MongoDB) [deprecated]
https://v1.prisma.io/docs/
Apache License 2.0
16.54k stars 862 forks source link

Connection refused to Postgres database on k8s from 1.27 #4189

Closed joe-re closed 5 years ago

joe-re commented 5 years ago

Describe the bug

I tried to update prisma version from 1.26 to 1.27 or 1.28. But if I updated it, database connection was refused. I tried to make a repro environment but oddly I couldn't repro on docker-compose structure. That's only my local k8s structure. If I downgrade prisma version to 1.26 or below, database connection become normal. So I guess some kinda prisma updating has relation to this.. Could anyone give me any solutions?

To Reproduce

Sorry, it's only on k8s structure for now. Now I'm trying to repro on docker-compose, but I still have not been able to it.

Expected behavior

Database connection succeed as same as prev version.

Screenshots If applicable, add screenshots to help explain your problem.

Versions (please complete the following information):

Additional context Add any other context about the problem here.

pantharshit00 commented 5 years ago

The only thing this could have caused this is https://github.com/prisma/prisma/pull/4097

Can you please try adding a schema property to the config in accordance with your service and stage name(default is `default@default. Look into your database in the where the data is stored if you are not sure)

port: 4466
databases:
  default:
    connector: postgres
    migrations: true
    host: postgres
    port: 5432
    schema: default$default # it is service$stage
    user: prisma
    password: prisma
    rawAccess: true
joe-re commented 5 years ago

@pantharshit00 Thank you for your reply! Unfortunately if I specify schema, another error is caused.

If I specify uri parameter in PRISMA_CONFIG, this error disappear.

but prev connection error is comming back..

Mar 18, 2019 3:06:57 AM org.postgresql.Driver connect SEVERE: Connection error: org.postgresql.util.PSQLException: Connection to postgres:5432 refused. Check that the hostname and port are correct andthat the postmaster is accepting TCP/IP connections. at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:245) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) at org.postgresql.jdbc.PgConnection.(PgConnection.java:195) at org.postgresql.Driver.makeConnection(Driver.java:452) at org.postgresql.Driver.connect(Driver.java:254) at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101) at slick.jdbc.DataSourceJdbcDataSource.createConnection(JdbcDataSource.scala:68) at slick.jdbc.JdbcBackend$BaseSession.(JdbcBackend.scala:453) at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:46) at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:37) at slick.basic.BasicBackend$DatabaseDef.acquireSession(BasicBackend.scala:249) at slick.basic.BasicBackend$DatabaseDef.acquireSession$(BasicBackend.scala:248) at slick.jdbc.JdbcBackend$DatabaseDef.acquireSession(JdbcBackend.scala:37) at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:274) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: java.net.ConnectException: Connection refused (Connection refused) at java.net.PlainSocketImpl.socketConnect(Native Method) at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) at java.net.Socket.connect(Socket.java:589) at org.postgresql.core.PGStream.(PGStream.java:69) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:156) ... 16 more


And I confirmed that schema name is correct from psql console directly.

prisma=# select schema_name prisma-# from information_schema.schemata; schema_name

pg_toast pg_temp_1 pg_toast_temp_1 pg_catalog public information_schema management default$default (8 rows)



Sorry bother to you, but do you have any other suggestions..?
pantharshit00 commented 5 years ago

Only Postgres connectors specify a schema. If they do they also need to specify a database.

You will need to add the database name as well

port: 4466
databases:
  default:
    connector: postgres
    migrations: true
    host: postgres
    port: 5432
    user: prisma
    password: prisma
    rawAccess: true
    schema: default$default
    database: <your database name>
    rawAccess: true
joe-re commented 5 years ago

Set database name, but connection error is persistent..

port: 4466
databases:
  default:
    connector: postgres
    migrations: true
    host: postgres
    port: 5432
    user: prisma
    password: prisma
    rawAccess: true
    schema: default$default
    database: prisma
    rawAccess: true
No log level set, defaulting to INFO.
Mar 19, 2019 3:42:51 AM org.postgresql.core.v3.ConnectionFactoryImpl log
WARNING: ConnectException occurred while connecting to postgres:5432
java.net.ConnectException: Connection refused (Connection refused)
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
        at java.net.Socket.connect(Socket.java:589)
        at org.postgresql.core.PGStream.<init>(PGStream.java:69)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:156)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
        at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
        at org.postgresql.Driver.makeConnection(Driver.java:452)
        at org.postgresql.Driver.connect(Driver.java:254)
        at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101)
        at slick.jdbc.DataSourceJdbcDataSource.createConnection(JdbcDataSource.scala:68)
        at slick.jdbc.JdbcBackend$BaseSession.<init>(JdbcBackend.scala:453)
        at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:46)
        at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:37)
        at slick.basic.BasicBackend$DatabaseDef.acquireSession(BasicBackend.scala:249)
        at slick.basic.BasicBackend$DatabaseDef.acquireSession$(BasicBackend.scala:248)
        at slick.jdbc.JdbcBackend$DatabaseDef.acquireSession(JdbcBackend.scala:37)
        at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:274)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

Mar 19, 2019 3:42:51 AM org.postgresql.Driver connect
SEVERE: Connection error:
org.postgresql.util.PSQLException: Connection to postgres:5432 refused. Check that the hostname and port are correct andthat the postmaster is accepting TCP/IP connections.
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:245)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
        at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
        at org.postgresql.Driver.makeConnection(Driver.java:452)
        at org.postgresql.Driver.connect(Driver.java:254)
        at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101)
        at slick.jdbc.DataSourceJdbcDataSource.createConnection(JdbcDataSource.scala:68)
        at slick.jdbc.JdbcBackend$BaseSession.<init>(JdbcBackend.scala:453)
        at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:46)
        at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:37)
        at slick.basic.BasicBackend$DatabaseDef.acquireSession(BasicBackend.scala:249)
        at slick.basic.BasicBackend$DatabaseDef.acquireSession$(BasicBackend.scala:248)
        at slick.jdbc.JdbcBackend$DatabaseDef.acquireSession(JdbcBackend.scala:37)
        at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:274)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.net.ConnectException: Connection refused (Connection refused)
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
        at java.net.Socket.connect(Socket.java:589)
        at org.postgresql.core.PGStream.<init>(PGStream.java:69)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:156)
        ... 16 more
pantharshit00 commented 5 years ago

@joe-re Are you able to solve this?

joe-re commented 5 years ago

@pantharshit00 sorry, but no.. I have still got an error and used 1.26. I have thought that I'm going to investigate it and going to reproduce it as clearly code, but sorry I haven't had enough time now..

JoshuaToth commented 5 years ago

@joe-re I ran into a similar issue today with the same errors you describe here, although no version was working for me.

If you can ssh into the container and run nc -z -v -w5 <DB.HOST> <DB.PORT> i.e. nc -z -v -w5 13.32.240.99 80 You should have an output saying 'open'

Then you can determine if It's a connection issue or a prisma issue.

For me the cluster it was being hosted on wasn't allowed to make connections to the db server. (because I had deployed to the incorrect cluster) and all I got were those exact same errors. At first I thought it was a prisma issue but after a bit of scraping around I found the bad connection.

It would be nice if the prisma startup script could do something similar to determine if the connection was available before running prisma itself and dump to the logs. It would have saved a fair bit of time having to ssh onto the container during the 30s window it was up.

joe-re commented 5 years ago

@JoshuaToth Thanks for your information! I tried that and unfortunately I could get open information on both of v1.26 and v1.31 versions.

root@server-79c8dcbfb9-9mhkg:/opt/app# nc -z -v -w5 postgres 5432
postgres.dev-prisma.svc.cluster.local [10.103.35.96] 5432 (postgresql) open

But it was not always receiving open, sometimes messaging was likely refused.

root@server-79c8dcbfb9-9mhkg:/opt/app# nc -z -v -w5 postgres 5432
postgres.dev-prisma.svc.cluster.local [10.103.35.96] 5432 (postgresql) : Connection refused

Maybe, how to handle this error was changed from 1.27..?

joe-re commented 5 years ago

I fixed my problem to add selector field to my postgres container yaml.

apiVersion: v1
kind: Service
metadata:
  name: postgres
spec:
  ports:
  - port: 5432
    targetPort: 5432
    protocol: TCP
  selector:  # <- Added
    service: postgres

Probably that was some kind of networking problem of k8s. I have no idea why it’s capable to work under 1.26 prisma though..

Thank you so much every kind support and replying!