Schedule type Cron: Unable to schedule for specific day of the week

[hellmund]

Topic

Cron expression parsing issue

Relevant information

A parsing error occurs, when entering a particular format of a Cron expression:

When trying to configure a connection with the following Cron expression 0 0 15 * * 1, the subtitle below the input field correctly says At 03:00 PM, only on Sunday. However, when clicking "Save changes", the following error occurs:

Invalid cron expression.
Check the [Quartz cron expression docs](http://www.quartz-scheduler.org/documentation/quartz-2.3.0/tutorials/crontrigger.html) to make sure your syntax is correct.

with the following console output:

airbyte-server                    | 2024-06-30 23:55:48 ERROR i.a.s.a.ApiHelper(execute):49 - Unexpected Exception
airbyte-server                    | io.airbyte.api.problems.throwable.generated.CronValidationInvalidExpressionProblem: The cron expression is invalid
airbyte-server                    | 	at io.airbyte.commons.server.handlers.helpers.ConnectionScheduleHelper.populateSyncFromScheduleTypeAndData(ConnectionScheduleHelper.java:103) ~[io.airbyte-airbyte-commons-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.commons.server.handlers.ConnectionsHandler.applyPatchToStandardSync(ConnectionsHandler.java:224) ~[io.airbyte-airbyte-commons-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.commons.server.handlers.ConnectionsHandler.updateConnection(ConnectionsHandler.java:670) ~[io.airbyte-airbyte-commons-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.commons.server.handlers.WebBackendConnectionsHandler.webBackendUpdateConnection(WebBackendConnectionsHandler.java:632) ~[io.airbyte-airbyte-commons-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.server.apis.WebBackendApiController.lambda$webBackendUpdateConnection$5(WebBackendApiController.java:149) ~[io.airbyte-airbyte-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.server.apis.ApiHelper.execute(ApiHelper.java:28) ~[io.airbyte-airbyte-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.server.apis.WebBackendApiController.webBackendUpdateConnection(WebBackendApiController.java:147) ~[io.airbyte-airbyte-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.server.apis.$WebBackendApiController$Definition$Exec.dispatch(Unknown Source) ~[io.airbyte-airbyte-server-0.63.3.jar:?]
airbyte-server                    | 	at io.micronaut.context.AbstractExecutableMethodsDefinition$DispatchedExecutableMethod.invokeUnsafe(AbstractExecutableMethodsDefinition.java:461) ~[micronaut-inject-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.context.DefaultBeanContext$BeanContextUnsafeExecutionHandle.invokeUnsafe(DefaultBeanContext.java:4232) ~[micronaut-inject-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.web.router.AbstractRouteMatch.execute(AbstractRouteMatch.java:271) ~[micronaut-router-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.http.server.RouteExecutor.executeRouteAndConvertBody(RouteExecutor.java:488) ~[micronaut-http-server-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.http.server.RouteExecutor.lambda$callRoute$6(RouteExecutor.java:465) ~[micronaut-http-server-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.core.execution.ExecutionFlow.lambda$async$1(ExecutionFlow.java:87) ~[micronaut-core-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.core.propagation.PropagatedContext.lambda$wrap$3(PropagatedContext.java:211) ~[micronaut-core-4.4.10.jar:4.4.10]
airbyte-server                    | 	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
airbyte-server                    | 	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
airbyte-server                    | 	at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
airbyte-server                    | 2024-06-30 23:55:48 ERROR i.a.c.s.e.p.AbstractThrowableProblemHandler(handle):30 - Throwable Problem Handler caught exception: 
airbyte-server                    | io.airbyte.api.problems.throwable.generated.CronValidationInvalidExpressionProblem: The cron expression is invalid
airbyte-server                    | 	at io.airbyte.commons.server.handlers.helpers.ConnectionScheduleHelper.populateSyncFromScheduleTypeAndData(ConnectionScheduleHelper.java:103) ~[io.airbyte-airbyte-commons-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.commons.server.handlers.ConnectionsHandler.applyPatchToStandardSync(ConnectionsHandler.java:224) ~[io.airbyte-airbyte-commons-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.commons.server.handlers.ConnectionsHandler.updateConnection(ConnectionsHandler.java:670) ~[io.airbyte-airbyte-commons-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.commons.server.handlers.WebBackendConnectionsHandler.webBackendUpdateConnection(WebBackendConnectionsHandler.java:632) ~[io.airbyte-airbyte-commons-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.server.apis.WebBackendApiController.lambda$webBackendUpdateConnection$5(WebBackendApiController.java:149) ~[io.airbyte-airbyte-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.server.apis.ApiHelper.execute(ApiHelper.java:28) ~[io.airbyte-airbyte-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.server.apis.WebBackendApiController.webBackendUpdateConnection(WebBackendApiController.java:147) ~[io.airbyte-airbyte-server-0.63.3.jar:?]
airbyte-server                    | 	at io.airbyte.server.apis.$WebBackendApiController$Definition$Exec.dispatch(Unknown Source) ~[io.airbyte-airbyte-server-0.63.3.jar:?]
airbyte-server                    | 	at io.micronaut.context.AbstractExecutableMethodsDefinition$DispatchedExecutableMethod.invokeUnsafe(AbstractExecutableMethodsDefinition.java:461) ~[micronaut-inject-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.context.DefaultBeanContext$BeanContextUnsafeExecutionHandle.invokeUnsafe(DefaultBeanContext.java:4232) ~[micronaut-inject-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.web.router.AbstractRouteMatch.execute(AbstractRouteMatch.java:271) ~[micronaut-router-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.http.server.RouteExecutor.executeRouteAndConvertBody(RouteExecutor.java:488) ~[micronaut-http-server-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.http.server.RouteExecutor.lambda$callRoute$6(RouteExecutor.java:465) ~[micronaut-http-server-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.core.execution.ExecutionFlow.lambda$async$1(ExecutionFlow.java:87) ~[micronaut-core-4.4.10.jar:4.4.10]
airbyte-server                    | 	at io.micronaut.core.propagation.PropagatedContext.lambda$wrap$3(PropagatedContext.java:211) ~[micronaut-core-4.4.10.jar:4.4.10]
airbyte-server                    | 	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
airbyte-server                    | 	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
airbyte-server                    | 	at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]

The parsing issue also occurs when writing SUN instead of 1 at the end. When scheduling via a cron expression for any day of the week, e.g. 0 0 15 * * ?, then the expression is accepted.

Occurs on airbyte 0.63.3 running on Docker.

[nataliekwong]

Hi there, since we use Quartz syntax, there are some nuances with how cron expressions are written. Here are a few examples of expressions that are valid:

0 0 0 ? * SUN At 12:00 AM, only on Sunday
0 59 23 ? * MON-FRI At 11:59 PM, Monday through Friday
0 0 * ? * MON,TUE Every hour, only on Monday and Tuesday

[hellmund]

Thanks @nataliekwong - that did the trick!

So in my particular example, the correct value which gets accepted would be 0 0 15 ? * 1 instead of 0 0 15 * * 1.

[hongbo-miao]

I met same issue as well. Reported at #40715
Hopefully in the future it can be fixed! ☺️

[NAjustin]

@nataliekwong It's worth noting that while this is covered in the docs, it should probably be noted in the UI (even if that just surfaces that it uses Quartz syntax and links to the docs page).

I think most people who are familiar with cron syntax will expect UNIX-style, and those who aren't won't be able to Google the correct answer very easily without having that context. (Some other settings screens already have the info-icon tooltip, but there's no contextual help or docs on this screen at all.)

[nataliekwong]

Good point! I'll follow up with our Frontend team to add the guidance in the UI.