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.53k stars 4k forks source link

deployment bug while setting up connection. Thanks. #38686

Closed sluo2024 closed 3 months ago

sluo2024 commented 3 months ago

Platform Version

0.61.0

What step the error happened?

On deploy

Relevant information

we are setting up connection between mysql and snowflake. the airbyte server ip is 35.183.X.X and mysql source was setup properly. when we setup the connection and select the destintion as snowflake. the following error message showed up. Internal message: java.sql.SQLSyntaxErrorException: SELECT command denied to user ''@'10.24.1.50' for column 'name' in table 'all_trans' Failure origin: source Failure type: system_error first the username seems not tranfered and IP does not belong to any of our servers. so it is truely wield we recreated the source, destination and connection multiple times. and the error always shows up. other mysql DB sources we set up for comparison just worked fine. would you please help to advise your opinion? Thanks!

Relevant log output

Discovering schema failed
Something went wrong in the connector. See the logs for more details.

Learn more
Internal message: java.sql.SQLSyntaxErrorException: SELECT command denied to user ''@'10.24.1.50' for column 'name' in table 'all_trans'
Failure origin: source
Failure type: system_error

Stacktrace
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
    at com.mysql.cj.jdbc.DatabaseMetaData$2.forEach(DatabaseMetaData.java:2167)
    at com.mysql.cj.jdbc.DatabaseMetaData$2.forEach(DatabaseMetaData.java:2096)
    at com.mysql.cj.jdbc.IterateBlock.doForAll(IterateBlock.java:56)
    at com.mysql.cj.jdbc.DatabaseMetaData.getColumns(DatabaseMetaData.java:2273)
    at com.zaxxer.hikari.pool.ProxyDatabaseMetaData.getColumns(ProxyDatabaseMetaData.java:107)
    at com.zaxxer.hikari.pool.HikariProxyDatabaseMetaData.getColumns(HikariProxyDatabaseMetaData.java)
    at io.airbyte.cdk.integrations.source.jdbc.AbstractJdbcSource.discoverInternal$lambda$5(AbstractJdbcSource.kt:275)
    at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.bufferedResultSetQuery(DefaultJdbcDatabase.kt:39)
    at io.airbyte.cdk.integrations.source.jdbc.AbstractJdbcSource.discoverInternal(AbstractJdbcSource.kt:273)
    at io.airbyte.cdk.integrations.source.jdbc.AbstractJdbcSource.discoverInternal(AbstractJdbcSource.kt:405)
    at io.airbyte.cdk.integrations.source.jdbc.AbstractJdbcSource.discoverInternal(AbstractJdbcSource.kt:86)
    at io.airbyte.cdk.integrations.source.relationaldb.AbstractDbSource.discoverWithoutSystemTables(AbstractDbSource.kt:313)
    at io.airbyte.cdk.integrations.source.relationaldb.AbstractDbSource.discover(AbstractDbSource.kt:95)
    at io.airbyte.integrations.source.mysql.MySqlSource.discover(MySqlSource.java:305)
    at io.airbyte.cdk.integrations.base.ssh.SshWrappedSource.discover$lambda$1(SshWrappedSource.kt:57)
    at io.airbyte.cdk.integrations.base.ssh.SshTunnel$Companion.sshWrap(SshTunnel.kt:550)
    at io.airbyte.cdk.integrations.base.ssh.SshWrappedSource.discover(SshWrappedSource.kt:57)
    at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:169)
    at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:116)
    at io.airbyte.integrations.source.mysql.MySqlSource.main(MySqlSource.java:648)

Logs

2024-05-27 20:45:11 platform > Docker volume job log path: /tmp/workspace/767795ad-9d9f-4328-b3c1-80bcb30f9808/0/logs.log
2024-05-27 20:45:11 platform > Executing worker wrapper. Airbyte version: 0.61.0
2024-05-27 20:45:11 platform > Attempt 0 to save workflow id for cancellation
2024-05-27 20:45:11 platform > Using default value for environment variable SIDECAR_KUBE_CPU_LIMIT: '2.0'
2024-05-27 20:45:11 platform > Using default value for environment variable SOCAT_KUBE_CPU_LIMIT: '2.0'
2024-05-27 20:45:11 platform > Using default value for environment variable SIDECAR_KUBE_CPU_REQUEST: '0.1'
2024-05-27 20:45:11 platform > Using default value for environment variable SOCAT_KUBE_CPU_REQUEST: '0.1'
2024-05-27 20:45:11 platform > Checking if airbyte/source-mysql:3.4.1 exists...
2024-05-27 20:45:11 platform > airbyte/source-mysql:3.4.1 was found locally.
2024-05-27 20:45:11 platform > Creating docker container = source-mysql-discover-767795ad-9d9f-4328-b3c1-80bcb30f9808-0-yajvb with resources io.airbyte.config.ResourceRequirements@7dde1f35[cpuRequest=,cpuLimit=,memoryRequest=,memoryLimit=,additionalProperties={}] and allowedHosts null
2024-05-27 20:45:11 platform > Preparing command: docker run --rm --init -i -w /data/767795ad-9d9f-4328-b3c1-80bcb30f9808/0 --log-driver none --name source-mysql-discover-767795ad-9d9f-4328-b3c1-80bcb30f9808-0-yajvb --network host -v airbyte_workspace:/data -v oss_local_root:/local -e DEPLOYMENT_MODE=OSS -e WORKER_CONNECTOR_IMAGE=airbyte/source-mysql:3.4.1 -e AUTO_DETECT_SCHEMA=true -e LAUNCHDARKLY_KEY= -e SOCAT_KUBE_CPU_REQUEST=0.1 -e SOCAT_KUBE_CPU_LIMIT=2.0 -e FIELD_SELECTION_WORKSPACES= -e USE_STREAM_CAPABLE_STATE=true -e WORKER_ENVIRONMENT=DOCKER -e AIRBYTE_ROLE=dev -e APPLY_FIELD_SELECTION=false -e WORKER_JOB_ATTEMPT=0 -e OTEL_COLLECTOR_ENDPOINT=http://host.docker.internal:4317 -e FEATURE_FLAG_CLIENT=config -e AIRBYTE_VERSION=0.61.0 -e WORKER_JOB_ID=767795ad-9d9f-4328-b3c1-80bcb30f9808 airbyte/source-mysql:3.4.1 discover --config source_config.json
2024-05-27 20:45:11 platform > Reading messages from protocol version 0.2.0
2024-05-27 20:45:12 platform > INFO main i.a.i.s.m.MySqlSource(main):647 starting source: class io.airbyte.integrations.source.mysql.MySqlSource
2024-05-27 20:45:12 platform > INFO main i.a.c.i.b.IntegrationCliParser$Companion(parseOptions):145 integration args: {discover=null, config=source_config.json}
2024-05-27 20:45:12 platform > INFO main i.a.c.i.b.IntegrationRunner(runInternal):124 Running integration: io.airbyte.cdk.integrations.base.ssh.SshWrappedSource
2024-05-27 20:45:12 platform > INFO main i.a.c.i.b.IntegrationRunner(runInternal):125 Command: DISCOVER
2024-05-27 20:45:12 platform > INFO main i.a.c.i.b.IntegrationRunner(runInternal):126 Integration config: IntegrationConfig{command=DISCOVER, configPath='source_config.json', catalogPath='null', statePath='null'}
2024-05-27 20:45:13 platform > WARN main c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword order - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2024-05-27 20:45:13 platform > WARN main c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword airbyte_secret - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2024-05-27 20:45:13 platform > WARN main c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword always_show - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2024-05-27 20:45:13 platform > WARN main c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword display_type - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2024-05-27 20:45:13 platform > WARN main c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword min - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
kashak88 commented 3 months ago

Here's some details for above from systems perspective: The airbyte server is located on AWS ec2 instance, the database in on prem. Airbyte accesses the server via NAT'ed port. I've manually connected using mysql client from airbyte instance and everything checks out - it lands on a proper DB server, the grants for the user allow it to SELECT against all tables. When we setup the source and click "test connection" I can see in 'show processlist' that airbyte is successfully connecting from airbyte server. When we setup snowflake as destination, it throws "SELECT command denied to user ''@'10.24.1.50' " which makes absolutely no sense. Why is the user empty ('') and why is the source host '10.24.1.50' when it should be 35.183.X.X (and I can literally see airbyte user connecting from that IP just before we get this error)

We were on 0.59.0 originally and patching to 0.61.0 didn't help

marcosmarxm commented 3 months ago

Hello @kashak88 and @sluo2024 please open an issue reporting the problem to MySQLconnector and provide the version you're using for that connector.