search

airbytehq / airbyte

The leading data integration platform for ETL / ELT data pipelines from APIs, databases & files to data warehouses, data lakes & data lakehouses. Both self-hosted and Cloud-hosted.
https://airbyte.com
Other
15.77k stars 4.04k forks source link

Source AlloyDB for PostgreSQL: Prohibited character conneting to external Postgresql DB at airbyte-bootloader #31021

Open ricardo-aspira opened 1 year ago

ricardo-aspira commented 1 year ago

Topic

External DB not working

Revelant information

I have an external Postgresql database that I want Airbyte connects to. It is a PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit.

I have added the airbyte-oss repo and trying to install it as follows:

helm repo add airbyte-oss https://airbytehq.github.io/helm-charts
helm upgrade --install airbyte -f ./values.yaml airbyte-oss/airbyte --namespace airbyte

Btw, I was able to connect to the database itself from a container running at the same namespace.

I am receiving the following error:

2023-10-03 15:23:45 INFO i.m.c.e.DefaultEnvironment(<init>):167 - Established active environments: [k8s, cloud]
2023-10-03 15:23:46 INFO i.m.r.Micronaut(lambda$start$2):98 - Startup completed in 1549ms. Server Running: http://airbyte-airbyte-bootloader:9002
2023-10-03 15:23:47 INFO i.a.f.ConfigFileClient(<init>):96 - path /flags does not exist, will return default flag values
2023-10-03 15:23:47 INFO i.a.c.s.RemoteDefinitionsProvider(<init>):74 - Creating remote definitions provider for URL 'https://connectors.airbyte.com/files/' and registry 'OSS'...
2023-10-03 15:23:47 INFO i.a.c.i.c.SeedBeanFactory(seedDefinitionsProvider):37 - Using local definitions provider for seeding
2023-10-03 15:23:47 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable LOCAL_CONNECTOR_CATALOG_PATH: 'seed/oss_registry.json'
2023-10-03 15:23:47 INFO i.a.c.p.v.DefaultDefinitionVersionOverrideProvider(<init>):52 - Initialized feature flag definition version overrides
2023-10-03 15:23:47 INFO i.a.c.p.ActorDefinitionVersionHelper(<init>):57 - ActorDefinitionVersionHelper initialized with override provider: DefaultDefinitionVersionOverrideProvider
2023-10-03 15:23:47 INFO i.a.b.Bootloader(load):99 - Initializing databases...
2023-10-03 15:23:47 INFO i.a.b.Bootloader(initializeDatabases):201 - Initializing databases...
2023-10-03 15:23:47 WARN i.a.d.c.DatabaseAvailabilityCheck(check):38 - Waiting for database to become available...
2023-10-03 15:23:47 INFO i.a.d.c.DatabaseAvailabilityCheck(lambda$isDatabaseConnected$1):75 - Testing airbyte configs database connection...
2023-10-03 15:24:18 ERROR i.a.d.c.DatabaseAvailabilityCheck(lambda$isDatabaseConnected$1):78 - Failed to verify database connection.
org.jooq.exception.DataAccessException: Error getting connection from data source HikariDataSource (HikariPool-2)
    at org.jooq_3.17.8.POSTGRES.debug(Unknown Source) ~[?:?]
    at org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:90) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.DefaultExecuteContext.connection(DefaultExecuteContext.java:560) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:296) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:301) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:322) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.SelectImpl.fetchLazyNonAutoClosing(SelectImpl.java:2861) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.ResultQueryTrait.fetchOne(ResultQueryTrait.java:509) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.Tools.attach(Tools.java:1526) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.DefaultDSLContext.fetchOne(DefaultDSLContext.java:4662) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.DefaultDSLContext.lambda$fetchValue$49(DefaultDSLContext.java:4682) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.Tools.attach(Tools.java:1526) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.DefaultDSLContext.fetchValue(DefaultDSLContext.java:4682) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.DefaultDSLContext.fetchValue(DefaultDSLContext.java:4696) ~[jooq-3.17.8.jar:?]
    at org.jooq.impl.DefaultDSLContext.fetchExists(DefaultDSLContext.java:4778) ~[jooq-3.17.8.jar:?]
    at io.airbyte.db.check.DatabaseAvailabilityCheck.lambda$isDatabaseConnected$0(DatabaseAvailabilityCheck.java:76) ~[io.airbyte.airbyte-db-db-lib-0.50.30.jar:?]
    at io.airbyte.db.Database.query(Database.java:23) ~[io.airbyte.airbyte-db-db-lib-0.50.30.jar:?]
    at io.airbyte.db.check.DatabaseAvailabilityCheck.lambda$isDatabaseConnected$1(DatabaseAvailabilityCheck.java:76) ~[io.airbyte.airbyte-db-db-lib-0.50.30.jar:?]
    at io.airbyte.db.check.DatabaseAvailabilityCheck.check(DatabaseAvailabilityCheck.java:47) ~[io.airbyte.airbyte-db-db-lib-0.50.30.jar:?]
    at io.airbyte.db.init.DatabaseInitializer.initialize(DatabaseInitializer.java:45) ~[io.airbyte.airbyte-db-db-lib-0.50.30.jar:?]
    at io.airbyte.bootloader.Bootloader.initializeDatabases(Bootloader.java:202) ~[io.airbyte-airbyte-bootloader-0.50.30.jar:?]
    at io.airbyte.bootloader.Bootloader.load(Bootloader.java:100) ~[io.airbyte-airbyte-bootloader-0.50.30.jar:?]
    at io.airbyte.bootloader.Application.main(Application.java:22) ~[io.airbyte-airbyte-bootloader-0.50.30.jar:?]
Caused by: java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 30001ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:181) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:146) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100) ~[HikariCP-5.0.1.jar:?]
    at org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:87) ~[jooq-3.17.8.jar:?]
    ... 21 more
Caused by: org.postgresql.util.PSQLException: Something unusual has occurred to cause the driver to fail. Please report this exception.
    at org.postgresql.Driver.connect(Driver.java:314) ~[postgresql-42.5.4.jar:42.5.4]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:359) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:470) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:733) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:712) ~[HikariCP-5.0.1.jar:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
    at java.lang.Thread.run(Thread.java:1589) ~[?:?]
Caused by: java.lang.IllegalArgumentException: Prohibited character
ricardo-aspira commented 10 months ago

Hello @Diana-Vait ,

How are you doing ? Do you have any expected date to this fix ?

Thanks a ton.

Diana-Vait commented 10 months ago

Hello @ricardosouzamorais, Thank you, I'm fine! Hope you are too. For today this issue is not in work yet and will be prioritized later. Regards!

ricardo-aspira commented 10 months ago

@Diana-Vait , what has it configured as AlloyDB to Postgresql ?

My problem was found when deploy helm chart with external Postgresql.

Diana-Vait commented 10 months ago

@ricardosouzamorais label for issue was updated

ricardo-aspira commented 10 months ago

@Diana-Vait ,

How are you doing?

It is nothing related to sources into connection flows. It is related to airbyte-airbyte-bootloader when Airbyte Postgres database is created.

So I think we should recategorize it and remove AlloyDB from title. What do you think?

Specifying my own values.yaml, if I remove the database entry on global section, remove the entries existingSecret and existingSecretPasswordKey from externalDatabase section, and add user and password entries, in plain test, on externalDatabase section, I could make it work.

Does it ring your bell ?

It does not work if I leave global/database and externalDatabase as follows:

...
global:
  ...
  env_vars: {}
  ##  database [object] -- Object used to overrite database configuration(to use external DB)
  ##  database.secretName -- secret name where DB creds are stored
  ##  database.secretValue -- secret value for database password
  ##  database.host -- Database host override
  ##  database.port -- Database port override
  database:
    secretName: "airbyte-secrets"
    secretValue: "DATABASE_PASSWORD"
    host: "10.1.123.123"
    port: "5432"
  state:
    ## state.storage.type Determines which state storage will be utilized.  One of "MINIO", "S3" or "GCS"
    storage:
      type: "MINIO"
...
externalDatabase:
  host: 10.1.123.123
  user: test
  password: ""
  existingSecret: airbyte-secrets
  existingSecretPasswordKey: DATABASE_PASSWORD
  database: test
  port: 5432
  jdbcUrl: "jdbc:postgresql://10.1.123.123:5432/test"
...

Thanks for your time.