Join Log in
Score:0
von_Olivenbaum avatar Server

Connection to Mysql database failing from different clients

sg flag
von_Olivenbaum

I work with an ETL tool called airbyte to move data from various data sources to our main data warehouse. I am also asking this question in the airbyte slack channel, but would like to ask it here as well. Up until Thursday(06.07.2023) the connection from a mysql database to snowflake via airbyte was working fine. However, it stopped working and I am not sure what the reason for this is. I've added the outputfrom the log file below. Does anyone recognize where the problem may be coming from? Thanks for the support

Edit: I've tried connecting to this database from other ETL tools (Airbyte, Fivetran), and I receive this error:

Unable to validate certificate
com.fivetran.lambda.Retrier$RetrierException: The action failed after 1 attempt(s), the failures were: 1) java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Although the provider mentions that nothing has changed on their end (mysql database), this seems to be related to their side, especially since I receive the same error with different tools and clients.

Edit: I am able to access the mysql database via the ubuntu CLI and the mysql workbench. I'm still not sure as to why this is not working for the third party providers

Log files from airbyte:

2023-07-08 07:04:31 [33mWARN[m 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
2023-07-08 07:04:31 [32mINFO[m i.a.v.j.JsonSchemaValidator(test):130 - JSON schema validation failed. 
errors: $.auth_type: must be a constant value OAuth2.0, $.auth_type: does not have a value in the enumeration [OAuth2.0], $.access_token: is missing but it is required, $.refresh_token: is missing but it is required
2023-07-08 07:04:31 [32mINFO[m i.a.v.j.JsonSchemaValidator(test):130 - JSON schema validation failed. 
errors: $.auth_type: must be a constant value Key Pair Authentication, $.auth_type: does not have a value in the enumeration [Key Pair Authentication], $.private_key: is missing but it is required
2023-07-08 07:04:31 [33mWARN[m 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
2023-07-08 07:04:31 [32mINFO[m i.a.v.j.JsonSchemaValidator(test):130 - JSON schema validation failed. 
errors: $.password: object found, string expected
2023-07-08 07:04:31 [32mINFO[m i.a.w.t.TemporalAttemptExecution(get):136 - Docker volume job log path: /tmp/workspace/63142/0/logs.log
2023-07-08 07:04:31 [32mINFO[m i.a.w.t.TemporalAttemptExecution(get):141 - Executing worker wrapper. Airbyte version: 0.40.23
2023-07-08 07:04:31 [32mINFO[m i.a.a.c.AirbyteApiClient(retryWithJitter):179 - Attempt 0 to save workflow id for cancellation
2023-07-08 07:04:32 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - 
2023-07-08 07:04:32 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - ----- START CHECK -----
2023-07-08 07:04:32 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - 
2023-07-08 07:04:32 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - Checking if airbyte/source-mysql:2.0.11 exists...
2023-07-08 07:04:43 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - airbyte/source-mysql:2.0.11 was found locally.
2023-07-08 07:04:44 [32mINFO[m i.a.w.p.DockerProcessFactory(create):119 - Creating docker container = source-mysql-check-63142-0-izimd with resources io.airbyte.config.ResourceRequirements@525765e7[cpuRequest=,cpuLimit=,memoryRequest=,memoryLimit=]
2023-07-08 07:04:44 [32mINFO[m i.a.w.p.DockerProcessFactory(create):163 - Preparing command: docker run --rm --init -i -w /data/63142/0 --log-driver none --name source-mysql-check-63142-0-izimd --network host -v airbyte_workspace:/data -v /tmp/airbyte_local:/local -e DEPLOYMENT_MODE=OSS -e USE_STREAM_CAPABLE_STATE=true -e WORKER_ENVIRONMENT=DOCKER -e AIRBYTE_ROLE= -e WORKER_CONNECTOR_IMAGE=airbyte/source-mysql:2.0.11 -e WORKER_JOB_ATTEMPT=0 -e AUTO_DETECT_SCHEMA=false -e AIRBYTE_VERSION=0.40.23 -e WORKER_JOB_ID=63142 airbyte/source-mysql:2.0.11 check --config source_config.json
2023-07-08 07:04:44 [32mINFO[m i.a.w.i.VersionedAirbyteStreamFactory(create):97 - Reading messages from protocol version 0.2.0
2023-07-08 07:05:30 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.s.m.MySqlSource(main):407 starting source: class io.airbyte.integrations.source.mysql.MySqlSource
2023-07-08 07:05:30 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationCliParser(parseOptions):126 integration args: {check=null, config=source_config.json}
2023-07-08 07:05:30 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationRunner(runInternal):108 Running integration: io.airbyte.integrations.base.ssh.SshWrappedSource
2023-07-08 07:05:30 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationRunner(runInternal):109 Command: CHECK
2023-07-08 07:05:30 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationRunner(runInternal):110 Integration config: IntegrationConfig{command=CHECK, configPath='source_config.json', catalogPath='null', statePath='null'}
2023-07-08 07:05:31 [33mWARN[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):117 - WARN 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
2023-07-08 07:05:31 [33mWARN[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):117 - WARN 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
2023-07-08 07:05:32 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.s.SshTunnel(getInstance):204 Starting connection with method: NO_TUNNEL
2023-07-08 07:05:33 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO c.z.h.HikariDataSource(<init>):80 HikariPool-1 - Starting...
2023-07-08 07:05:33 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO c.z.h.HikariDataSource(<init>):82 HikariPool-1 - Start completed.
2023-07-08 07:06:33 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO c.z.h.HikariDataSource(close):350 HikariPool-1 - Shutdown initiated...
2023-07-08 07:06:36 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO c.z.h.HikariDataSource(close):352 HikariPool-1 - Shutdown completed.
2023-07-08 07:06:36 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationRunner(runInternal):186 Completed integration: io.airbyte.integrations.base.ssh.SshWrappedSource
2023-07-08 07:06:36 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.s.m.MySqlSource(main):409 completed source: class io.airbyte.integrations.source.mysql.MySqlSource
2023-07-08 07:06:36 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - 
2023-07-08 07:06:36 [32mINFO[m i.a.w.t.TemporalAttemptExecution(get):163 - Stopping cancellation check scheduling...
2023-07-08 07:06:36 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - ----- END CHECK -----
2023-07-08 07:06:36 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - 
2023-07-08 07:06:36 [33mWARN[m i.t.i.w.ActivityWorker$TaskHandlerImpl(logExceptionDuringResultReporting):365 - Failure during reporting of activity result to the server. ActivityId = cc93e74a-db81-37bc-a6b6-3895b739fa1f, ActivityType = RunWithJobOutput, WorkflowId=connection_manager_c68a63fd-ec58-45a3-b8ee-8d787ddf52e7, WorkflowType=ConnectionManagerWorkflow, RunId=61607809-4637-4161-b0f7-ef83a37e2d9d
io.grpc.StatusRuntimeException: NOT_FOUND: invalid activityID or activity already timed out or invoking workflow is completed
    at io.grpc.stub.ClientCalls.toStatusRuntimeException(ClientCalls.java:271) ~[grpc-stub-1.50.2.jar:1.50.2]
    at io.grpc.stub.ClientCalls.getUnchecked(ClientCalls.java:252) ~[grpc-stub-1.50.2.jar:1.50.2]
    at io.grpc.stub.ClientCalls.blockingUnaryCall(ClientCalls.java:165) ~[grpc-stub-1.50.2.jar:1.50.2]
    at io.temporal.api.workflowservice.v1.WorkflowServiceGrpc$WorkflowServiceBlockingStub.respondActivityTaskCompleted(WorkflowServiceGrpc.java:3840) ~[temporal-serviceclient-1.17.0.jar:?]
    at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.lambda$sendReply$0(ActivityWorker.java:303) ~[temporal-sdk-1.17.0.jar:?]
    at io.temporal.internal.retryer.GrpcRetryer.lambda$retry$0(GrpcRetryer.java:52) ~[temporal-serviceclient-1.17.0.jar:?]
    at io.temporal.internal.retryer.GrpcSyncRetryer.retry(GrpcSyncRetryer.java:67) ~[temporal-serviceclient-1.17.0.jar:?]
    at io.temporal.internal.retryer.GrpcRetryer.retryWithResult(GrpcRetryer.java:60) ~[temporal-serviceclient-1.17.0.jar:?]
    at io.temporal.internal.retryer.GrpcRetryer.retry(GrpcRetryer.java:50) ~[temporal-serviceclient-1.17.0.jar:?]
    at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.sendReply(ActivityWorker.java:298) ~[temporal-sdk-1.17.0.jar:?]
    at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handleActivity(ActivityWorker.java:252) ~[temporal-sdk-1.17.0.jar:?]
    at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handle(ActivityWorker.java:206) ~[temporal-sdk-1.17.0.jar:?]
    at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handle(ActivityWorker.java:179) ~[temporal-sdk-1.17.0.jar:?]
    at io.temporal.internal.worker.PollTaskExecutor.lambda$process$0(PollTaskExecutor.java:93) ~[temporal-sdk-1.17.0.jar:?]
    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) ~[?:?]
2023-07-08 07:07:02 [32mINFO[m i.a.w.t.TemporalAttemptExecution(get):136 - Docker volume job log path: /tmp/workspace/63142/0/logs.log
2023-07-08 07:07:02 [32mINFO[m i.a.w.t.TemporalAttemptExecution(get):141 - Executing worker wrapper. Airbyte version: 0.40.23
2023-07-08 07:07:02 [32mINFO[m i.a.a.c.AirbyteApiClient(retryWithJitter):179 - Attempt 0 to save workflow id for cancellation
2023-07-08 07:07:02 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - 
2023-07-08 07:07:02 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - ----- START CHECK -----
2023-07-08 07:07:02 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - 
2023-07-08 07:07:02 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - Checking if airbyte/source-mysql:2.0.11 exists...
2023-07-08 07:07:02 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - airbyte/source-mysql:2.0.11 was found locally.
2023-07-08 07:07:02 [32mINFO[m i.a.w.p.DockerProcessFactory(create):119 - Creating docker container = source-mysql-check-63142-0-otjun with resources io.airbyte.config.ResourceRequirements@525765e7[cpuRequest=,cpuLimit=,memoryRequest=,memoryLimit=]
2023-07-08 07:07:02 [32mINFO[m i.a.w.p.DockerProcessFactory(create):163 - Preparing command: docker run --rm --init -i -w /data/63142/0 --log-driver none --name source-mysql-check-63142-0-otjun --network host -v airbyte_workspace:/data -v /tmp/airbyte_local:/local -e DEPLOYMENT_MODE=OSS -e USE_STREAM_CAPABLE_STATE=true -e WORKER_ENVIRONMENT=DOCKER -e AIRBYTE_ROLE= -e WORKER_CONNECTOR_IMAGE=airbyte/source-mysql:2.0.11 -e WORKER_JOB_ATTEMPT=0 -e AUTO_DETECT_SCHEMA=false -e AIRBYTE_VERSION=0.40.23 -e WORKER_JOB_ID=63142 airbyte/source-mysql:2.0.11 check --config source_config.json
2023-07-08 07:07:02 [32mINFO[m i.a.w.i.VersionedAirbyteStreamFactory(create):97 - Reading messages from protocol version 0.2.0
2023-07-08 07:07:03 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.s.m.MySqlSource(main):407 starting source: class io.airbyte.integrations.source.mysql.MySqlSource
2023-07-08 07:07:03 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationCliParser(parseOptions):126 integration args: {check=null, config=source_config.json}
2023-07-08 07:07:03 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationRunner(runInternal):108 Running integration: io.airbyte.integrations.base.ssh.SshWrappedSource
2023-07-08 07:07:03 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationRunner(runInternal):109 Command: CHECK
2023-07-08 07:07:03 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationRunner(runInternal):110 Integration config: IntegrationConfig{command=CHECK, configPath='source_config.json', catalogPath='null', statePath='null'}
2023-07-08 07:07:03 [33mWARN[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):117 - WARN 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
2023-07-08 07:07:04 [33mWARN[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):117 - WARN 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
2023-07-08 07:07:04 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.s.SshTunnel(getInstance):204 Starting connection with method: NO_TUNNEL
2023-07-08 07:07:04 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO c.z.h.HikariDataSource(<init>):80 HikariPool-1 - Starting...
2023-07-08 07:07:04 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO c.z.h.HikariDataSource(<init>):82 HikariPool-1 - Start completed.
2023-07-08 07:08:04 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO c.z.h.HikariDataSource(close):350 HikariPool-1 - Shutdown initiated...
2023-07-08 07:08:05 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO c.z.h.HikariDataSource(close):352 HikariPool-1 - Shutdown completed.
2023-07-08 07:08:05 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.b.IntegrationRunner(runInternal):186 Completed integration: io.airbyte.integrations.base.ssh.SshWrappedSource
2023-07-08 07:08:05 [32mINFO[m i.a.w.i.DefaultAirbyteStreamFactory(internalLog):120 - INFO i.a.i.s.m.MySqlSource(main):409 completed source: class io.airbyte.integrations.source.mysql.MySqlSource
2023-07-08 07:08:05 [32mINFO[m i.a.w.t.TemporalAttemptExecution(get):163 - Stopping cancellation check scheduling...
2023-07-08 07:08:05 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - 
2023-07-08 07:08:05 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - ----- END CHECK -----
2023-07-08 07:08:05 [32mINFO[m i.a.c.i.LineGobbler(voidCall):114 - 
2023-07-08 07:08:06 [32mINFO[m i.a.n.SlackNotificationClient(notify):149 - Successful notification (200): ok
2023-07-08 07:08:06 [32mINFO[m i.a.v.j.JsonSchemaValidator(test):130 - JSON schema validation failed. 
errors: $.auth_type: must be a constant value OAuth2.0, $.auth_type: does not have a value in the enumeration [OAuth2.0], $.access_token: is missing but it is required, $.refresh_token: is missing but it is required
2023-07-08 07:08:06 [32mINFO[m i.a.v.j.JsonSchemaValidator(test):130 - JSON schema validation failed. 
errors: $.auth_type: must be a constant value Key Pair Authentication, $.auth_type: does not have a value in the enumeration [Key Pair Authentication], $.private_key: is missing but it is required
2023-07-08 07:08:06 [32mINFO[m i.a.v.j.JsonSchemaValidator(test):130 - JSON schema validation failed. 
errors: $.password: object found, string expected
2023-07-08 07:08:06 [32mINFO[m i.a.c.f.EnvVariableFeatureFlags(autoDisablesFailingConnections):20 - Auto Disable Failing Connections: false
107
1 + 6
uz flag
Luuk
After "Unable to validate certificate", you can check how [encrypted connections](https://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html) to your server is configured. For example, when connecting using the CLI, and typing `\s`, do you get "Cipher in use..... " ? (P.S. I have no clue how this related to the connection that airbyte is trying to make)
0
Reply
von_Olivenbaum avatar
sg flag
von_Olivenbaum
Hey, @Luuk. Once I'm connected via the CLI and type ```\s```, the message for SSL is "Cipher in use is TLS_AES_256_GCM_SHA384".
0
Reply
uz flag
Luuk
Ok, it is using SSL. Now you need to find out if airbyte does work with SSL, and if your SSL server **requires** SSL ([require_secure_transport=ON](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_require_secure_transport)), or that you are allowed connecting without SSL (`mysql --ssl-mode=DISABLED`), and what airbyte does do with this.
0
Reply
von_Olivenbaum avatar
sg flag
von_Olivenbaum
@Luuk, airbyte as well as the other tool I mention (fivetran) also use SSL. In the case of airbyte the condition is SSL modes = required for the connection. What I am trying to understand is how and why this can change from Wednesday evening (everything is working fine) to Thursday morning (connection is not working any longer). Also, I'm suspecting this is something on the provider's side since this error message occurs in various tools and also a DB client like DBeaver
0
Reply
uz flag
Luuk
The only option I have is an expired certificate. When did the certificate that is/was used expire? (P.S. The stuff about certificates is on my loooong list, of things to do. )
0
Reply
von_Olivenbaum avatar
sg flag
von_Olivenbaum
@Luuk thanks, I'll ask the provider about that. I've never had to explicitly state a certificate either in third-party tools that are not working or mysql workbench/ubuntu CLI which are successfully connecting
0
Reply
Score:0
von_Olivenbaum avatar Server
sg flag
von_Olivenbaum

While I'm not certain as to why this happened, I am now able to connect to the datawarehouse via 3rd party clients again. The connect timeout was increased today, so that seems to have done the trick. However, I'm still not sure as to why this helped since the connection itself hadn't been updated in the past months

+ 1
uz flag
Luuk
Glad your problem is solved. I was expecting to see the word "timeout" in one of your error messages, and I am, a bit, surprised that that did not show....
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.