search

upstash / issues

Issue Tracker for Upstash
https://upstash.com
2 stars 0 forks source link

Unable to connect using Debezium PostgreSQL Connector #50

Closed cward-snaplogic closed 1 year ago

cward-snaplogic commented 1 year ago

I am getting the following error when trying to connect to a PostgreSQL instance using the Debezium PostgreSQL Connector. The credentials provided in the configuration have been validated as being correct.

Connector configuration is invalid and contains the following 1 error(s): Error while validating connector config: Unable to parse URL

mdogan commented 1 year ago

Hi @cward-snaplogic; where's your PostgreSQL instance hosted? What's its URL? Can you send your connector configuration?

cward-snaplogic commented 1 year ago

The PostgreSQL instance is hosted with bit.io http://bit.io/, connector configuration is as follows

{ "connector.class": "io.debezium.connector.postgresql.PostgresConnector", "database.dbname": "cward/orders", "database.hostname": "db.bit.io", "database.password": "**", "database.port": "5432", "database.server.name": "postgres", "database.user": "cward", "key.converter": "org.apache.kafka.connect.json.JsonConverter", "key.converter.schemas.enable": true, "value.converter": "org.apache.kafka.connect.json.JsonConverter", "value.converter.schemas.enable": true }

Chris Ward Senior Solutions Architect, EMEA +44 (0) 758 4074953

SnapLogic.com | We're Hiring! https://www.snaplogic.com/about-us/jobs

This message is confidential. It may also be privileged or otherwise protected by work product immunity or other legal rules. If you have received it by mistake, please let us know by e-mail reply and delete it from your system; you may not copy this message or disclose its contents to anyone. The integrity and security of this message cannot be guaranteed on the Internet.

On 13 Mar 2023, at 08:34, Mehmet Dogan @.***> wrote:

Hi @cward-snaplogic https://github.com/cward-snaplogic; where's your PostgreSQL instance hosted? What's its URL? Can you send your connector configuration?

— Reply to this email directly, view it on GitHub https://github.com/upstash/issues/issues/50#issuecomment-1465713478, or unsubscribe https://github.com/notifications/unsubscribe-auth/AVFXHO52E2ABPZMCHMEDQ3LW33LZNANCNFSM6AAAAAAVWKJHIA. You are receiving this because you were mentioned.

sancar commented 1 year ago

To let you know, this seems to be coming from postgresql driver. The connector that we use depends on jdbc driver 42.3.5. And this is the error you see.

https://github.com/pgjdbc/pgjdbc/blob/REL42.3.5/pgjdbc/src/main/java/org/postgresql/Driver.java#L244

I get the same error while trying with bit.io

And problem seems to be dbname containing a '/' char.

The full error that connector prints 

 i.d.c.postgresql.PostgresConnector - Failed testing connection for jdbc:postgresql://db.bit.io:5432/mustafasancarkoyunlu/trial with user 'mustafasancarkoyunlu'
org.postgresql.util.PSQLException: Unable to parse URL
    at org.postgresql.Driver.connect(Driver.java:244)
    at io.debezium.jdbc.JdbcConnection.lambda$patternBasedFactory$1(JdbcConnection.java:244)
    at io.debezium.jdbc.JdbcConnection.connection(JdbcConnection.java:888)
    at io.debezium.connector.postgresql.PostgresConnector.validateConnection(PostgresConnector.java:90)
    at io.debezium.connector.common.RelationalBaseSourceConnector.validate(RelationalBaseSourceConnector.java:54)
    at org.apache.kafka.connect.runtime.AbstractHerder.validateConnectorConfig(AbstractHerder.java:459)
    at org.apache.kafka.connect.runtime.AbstractHerder.lambda$validateConnectorConfig$2(AbstractHerder.java:362)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at java.base/java.lang.Thread.run(Thread.java:833)

And when I try this jdbc:postgresql://db.bit.io:5432/mustafasancarkoyunlu/trial with using postgresql driver's related method with following simple code. It fails o parse.

Properties properties = Driver.parseURL("jdbc:postgresql://db.bit.io:5432/mustafasancarkoyunlu/trial", new Properties());
        if(properties == null) {
            System.out.println("Unable to parse");
        }

And it printed following error message:

 Mar 13, 2023 2:32:03 PM org.postgresql.Driver parseURL
WARNING: JDBC URL contains too many / characters: jdbc:postgresql://db.bit.io:5432/mustafasancarkoyunlu/trial

Note that my database.dbname was mustafasancarkoyunlu/trial

If I remove / from db.name it parses correctly.

So somehow we have to pass / in dbname because of bit.o but postgresql driver does not like it. So problem is found, I will investigate further for a solution:

sancar commented 1 year ago

By following this recommendation, I manage pass this error.

https://stackoverflow.com/questions/38415052/postgresql-jdbc-url-with-database-name-containing-forward-slash

So I pass mustafasancarkoyunlu%2Ftrial insteaf of mustafasancarkoyunlu/trial as dbname

Now, it returns another error. 

Connector configuration is invalid and contains the following 1 error(s): Postgres server wal_level property must be "logical" but is: replica

If you check the debezium site, they mention that the server needs to be configured with wal_level logical. https://debezium.io/documentation/reference/1.9/connectors/postgresql.html#postgresql-on-amazon-rds

So, it is something that needs to be configured on bit.io side. I have no experience with bit.io. Their doc and support should help you better here.

I hope it helps. Regards.

cward-snaplogic commented 1 year ago

Many thanks for the information Sancar. I can take it from here