job has stuck and there is no way to manually sync or reset

[spagu]

Application logs:

656 - Waiting PT10M before restarting the workflow for connection f79fb227-ab3a-42r6-b2c3-165599492851, to prevent spamming temporal with restarts.
2023-07-05 20:32:04 ERROR i.a.w.t.s.ConnectionManagerWorkflowImpl(runMandatoryActivityWithOutput):647 - [ACTIVITY-FAILURE] Connection f79fb227-ab3a-42r6-b2c3-165599492851 failed to run an activity.(AttemptNumberFailureInput).  Connection manager workflow will be restarted after a delay of PT10M.
io.temporal.failure.ActivityFailure: scheduledEventId=211, startedEventId=212, activityType='AttemptFailureWithAttemptNumber', activityId='597786c9-dc03-30a8-b81d-0255bc859e04', identity='1@a036e1485fbd', retryState=RETRY_STATE_MAXIMUM_ATTEMPTS_REACHED

and

023-07-05 22:24:20 ERROR i.a.s.a.ApiHelper(execute):40 - Unexpected Exception
io.airbyte.commons.server.errors.ValueConflictKnownException: A sync is already running for: f79fb227-ab3a-42r6-b2c3-165599492851
	at io.airbyte.commons.server.handlers.SchedulerHandler.readJobFromResult(SchedulerHandler.java:695) ~[io.airbyte-airbyte-commons-server-0.50.6.jar:?]
	at io.airbyte.commons.server.handlers.SchedulerHandler.submitManualSyncToWorker(SchedulerHandler.java:665) ~[io.airbyte-airbyte-commons-server-0.50.6.jar:?]
	at io.airbyte.commons.server.handlers.SchedulerHandler.syncConnection(SchedulerHandler.java:516) ~[io.airbyte-airbyte-commons-server-0.50.6.jar:?]
	at io.airbyte.server.apis.ConnectionApiController.lambda$syncConnection$8(ConnectionApiController.java:133) ~[io.airbyte-airbyte-server-0.50.6.jar:?]
	at io.airbyte.server.apis.ApiHelper.execute(ApiHelper.java:26) ~[io.airbyte-airbyte-server-0.50.6.jar:?]
	at io.airbyte.server.apis.ConnectionApiController.syncConnection(ConnectionApiController.java:133) ~[io.airbyte-airbyte-server-0.50.6.jar:?]
	at io.airbyte.server.apis.$ConnectionApiController$Definition$Exec.dispatch(Unknown Source) ~[io.airbyte-airbyte-server-0.50.6.jar:?]
	at io.micronaut.context.AbstractExecutableMethodsDefinition$DispatchedExecutableMethod.invoke(AbstractExecutableMethodsDefinition.java:371) ~[micronaut-inject-3.9.4.jar:3.9.4]
	at io.micronaut.context.DefaultBeanContext$4.invoke(DefaultBeanContext.java:594) ~[micronaut-inject-3.9.4.jar:3.9.4]
	at io.micronaut.web.router.AbstractRouteMatch.execute(AbstractRouteMatch.java:303) ~[micronaut-router-3.9.4.jar:3.9.4]
	at io.micronaut.web.router.RouteMatch.execute(RouteMatch.java:111) ~[micronaut-router-3.9.4.jar:3.9.4]
	at io.micronaut.http.context.ServerRequestContext.with(ServerRequestContext.java:103) ~[micronaut-http-3.9.4.jar:3.9.4]
	at io.micronaut.http.server.RouteExecutor.lambda$executeRoute$14(RouteExecutor.java:659) ~[micronaut-http-server-3.9.4.jar:3.9.4]
	at reactor.core.publisher.FluxDeferContextual.subscribe(FluxDeferContextual.java:49) ~[reactor-core-3.5.0.jar:3.5.0]
	at reactor.core.publisher.InternalFluxOperator.subscribe(InternalFluxOperator.java:62) ~[reactor-core-3.5.0.jar:3.5.0]
	at reactor.core.publisher.FluxSubscribeOn$SubscribeOnSubscriber.run(FluxSubscribeOn.java:194) ~[reactor-core-3.5.0.jar:3.5.0]
	at io.micronaut.reactive.reactor.instrument.ReactorInstrumentation.lambda$init$0(ReactorInstrumentation.java:62) ~[micronaut-runtime-3.9.4.jar:3.9.4]
	at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:84) ~[reactor-core-3.5.0.jar:3.5.0]
	at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:37) ~[reactor-core-3.5.0.jar:3.5.0]
	at io.micronaut.scheduling.instrument.InvocationInstrumenterWrappedCallable.call(InvocationInstrumenterWrappedCallable.java:53) ~[micronaut-context-3.9.4.jar:3.9.4]
	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) ~[?:?]

Unfortunately, I cannot find any information about the running job. Each time I get an error when I try to SYNC now.:

Failed to start sync: A sync is already running for: f79fb227-ab3a-42r6-b2c3-165599492851

Reset your data ends with

Something went wrong

changing replication type end with:

Unknown error (http.500.eVhC2rKw3Lz6JKK76hWBL2)

it is airbyte v0.50.6

I did try to remove manually all connection_id's from Postgres, but it does not help:

DELETE from connection_operation where connection_id='f79fb227-ab3a-42r6-b2c3-165599492851';
DELETE from stream_reset where connection_id='f79fb227-ab3a-42r6-b2c3-165599492851';
DELETE from notification_configuration where connection_id='f79fb227-ab3a-42r6-b2c3-165599492851';
DELETE from schema_management where connection_id='f79fb227-ab3a-42r6-b2c3-165599492851';
DELETE from stream_statuses where connection_id='f79fb227-ab3a-42r6-b2c3-165599492851';
DELETE from retry_states where connection_id='f79fb227-ab3a-42r6-b2c3-165599492851';

Any ideas?

[andnig]

Same issue with 0.50.7.

[masyagin1998]

Same issue with 0.50.1

[andnig]

I'm not sure if this helps anyone. I "solved" (work-arounded) this issue by deleting the temporal database inside the airbyte postgres server.

1. I made sure, that all correctly running jobs were done.
2. I stopped all new jobs from being run.
3. Delete the temporal table
4. Redeploy airbyte containers (but I guess a restart of temporal would be enough).

This made temporal to recreate the temporal database and well... the problem was solved.

I'm aware that this is a super hacky solution, but for me it was good enough.

[masyagin1998]

@andnig which table is a temporal one?

[andnig]

There are two databases in the airbyte postgres server

1. temporal
2. temporal_visibility

I deleted both of them, and the platform worked again.

Whether this is the official way and whether this has any side effects, I don't know. For me it solved the issue and I didn't have it now for 2 weeks.

Maybe if someone wants to fix this issue for good: I'm quite sure I ran into this issue because I had a running job/sync and I helm upgraded the platform while the job was running. While I'm aware that this is far from best practice, I'm not sure, why this resulted in temporal going crazy.

[marcosmarxm]

Hello @andnig after deleting the temporal tables the platform return to a good state without you trigger manual the sync or you need to restart the sync?

[andnig]

I didn't restart the sync specially, but I restarted the whole platform via kubectl after deleting the tables. So not sure about that, sorry.

[apostoltego]

Had the same issue with a few connectors. It appears this was due them being active during an upgrade from 0.44.8 -> 0.50.8 by the looks of it.
Tried pinging the API to cancel the jobs, which was succesful but new runs were still failing to start with the same error. Stopped everything, deleted the DBs like @andnig described and restarted and everything was back to normal.

[cesar-loadsmart]

Hey folks!

Just to let you know that after you reset/delete the temporal database, you should check the state of your CRON based connections.

In our case they also got stuck :/ , I had to manually trigger them or Disable/Enable so that they could return to their previous behavior.

[pcorbel]

Hello, I have a lot of stuck jobs and even with resetting Temporal, i have the following logs
On worker

2023-09-11 08:49:11 WARN i.t.i.s.DeterministicRunnerImpl(close):347 - Promise completed with exception and was never accessed. The ignored exception:
io.temporal.failure.CanceledFailure: Canceled by request
	at java.lang.Thread.getStackTrace(Thread.java:2550) ~[?:?]
	at io.temporal.internal.sync.CompletablePromiseImpl.throwFailure(CompletablePromiseImpl.java:137) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.sync.CompletablePromiseImpl.getImpl(CompletablePromiseImpl.java:96) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.sync.CompletablePromiseImpl.get(CompletablePromiseImpl.java:75) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.sync.DeterministicRunnerImpl.close(DeterministicRunnerImpl.java:344) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.sync.SyncWorkflow.close(SyncWorkflow.java:152) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.replay.ReplayWorkflowExecutor.close(ReplayWorkflowExecutor.java:142) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.replay.ReplayWorkflowRunTaskHandler.close(ReplayWorkflowRunTaskHandler.java:265) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.worker.WorkflowExecutorCache.lambda$new$0(WorkflowExecutorCache.java:67) ~[temporal-sdk-1.17.0.jar:?]
	at com.google.common.cache.LocalCache.processPendingNotifications(LocalCache.java:1814) ~[guava-31.1-jre.jar:?]
	at com.google.common.cache.LocalCache$Segment.runUnlockedCleanup(LocalCache.java:3467) ~[guava-31.1-jre.jar:?]
	at com.google.common.cache.LocalCache$Segment.postWriteCleanup(LocalCache.java:3443) ~[guava-31.1-jre.jar:?]

on Server

2023-09-11 08:48:45 WARN i.a.c.s.h.JobHistoryHandler(listJobsFor):156 - Missing stats for job 9142 attempt 0
2023-09-11 08:48:45 WARN i.a.c.s.h.JobHistoryHandler(listJobsFor):156 - Missing stats for job 9128 attempt 0
2023-09-11 08:48:45 WARN i.a.c.s.h.JobHistoryHandler(listJobsFor):156 - Missing stats for job 9128 attempt 1

Does someone know how to completely (and safely) remove all old jobs (but still keep the connections an d the state) and restart all jobs from scratch once and for all so that everything is fixed and running?