Issue with MSSQL source connector cursor value format

Slack Knowledge Base Q&A Connector Questions
, , , ,
1

Summary

The user is facing an issue with the MSSQL source connector in Airbyte v0.63.9 where the cursor value format is not matching the expected pattern. The cursor value is in ‘Timestamp with Timezone’ format, causing an exception. Manually modifying the state value temporarily resolves the issue. The user suspects a recent commit may be responsible for the problem.


Question

Hi community :wave:
:red_circle: we currently use Airbyte v0.63.9 and the MSSQL source connector v4.1.3 to retrieve data using Incremental | Append, where cursor field is typed as Timestamp with Timezone
we observed attached issue at state reading time (2 days after upgrading to v0.63.9 - we previously were in v0.62.4)
=> looking at state content, we see that cursor value is formatted like 2024-08-01T00:00:33.919417Z (cf example for today’s value)
=> according to <airbyte/airbyte-integrations/connectors/source-mssql/src/main/java/io/airbyte/integrations/source/mssql/MssqlSourceOperations.java at v0.63.9 · airbytehq/airbyte · GitHub Java code>, the decoder expects "yyyy-MM-dd HH:mm:ss.SSSSSSS XXX" pattern, implying the exception
=> if we manually modify the state value to 2024-08-01 00:00:33.9194170 Z , it works, but we can not do it manually daily…
please could you help us to solve this issue ? :pray:

:bulb: perhaps this commit is responsible for the issue ? https://github.com/airbytehq/airbyte/commit/131ee051ec155652aa5a2135958788abb91d4c51
=> missing to apply same logic at reading time for Timestamp with Timezone fields ?

NB: nothing seems to have changed on v0.63.13 :confused:
<@U06CW2SDKCN> FYI


This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.

Join the conversation on Slack

["mssql-source-connector", "incremental-append", "timestamp-with-timezone", "cursor-value-format", "exception", "state-value", "java-code", "reading-time", "v0.63.9", "commit-issue"]
2

perhaps this commit is responsible for the issue ? https://github.com/airbytehq/airbyte/commit/131ee051ec155652aa5a2135958788abb91d4c51

3

detailed error logs

        at io.airbyte.cdk.integrations.source.relationaldb.state.SourceStateIterator.computeNext(SourceStateIterator.kt:35) ~[airbyte-cdk-db-sources-0.33.2.jar:?]
        at io.airbyte.cdk.integrations.source.relationaldb.state.SourceStateIterator.computeNext(SourceStateIterator.kt:16) ~[airbyte-cdk-db-sources-0.33.2.jar:?]
        at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.0.0-jre.jar:?]
        at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.33.2.jar:?]
        at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.TransformedIterator.hasNext(TransformedIterator.java:46) ~[guava-33.0.0-jre.jar:?]
        at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.33.2.jar:?]
        at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.0.0-jre.jar:?]
        at io.airbyte.commons.util.CompositeIterator.computeNext(CompositeIterator.kt:65) ~[airbyte-cdk-dependencies-0.33.2.jar:?]
        at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.0.0-jre.jar:?]
        at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.33.2.jar:?]
        at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.0.0-jre.jar:?]
        at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.33.2.jar:?]
        at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.0.0-jre.jar:?]
        at java.base/java.util.Iterator.forEachRemaining(Iterator.java:132) ~[?:?]
        at io.airbyte.cdk.integrations.base.IntegrationRunner.produceMessages(IntegrationRunner.kt:261) ~[airbyte-cdk-core-0.33.2.jar:?]
        at io.airbyte.cdk.integrations.base.IntegrationRunner.readSerial(IntegrationRunner.kt:318) ~[airbyte-cdk-core-0.33.2.jar:?]
        at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:184) ~[airbyte-cdk-core-0.33.2.jar:?]
        at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:116) ~[airbyte-cdk-core-0.33.2.jar:?]
        at io.airbyte.integrations.source.mssql.MssqlSource.main(MssqlSource.java:618) ~[io.airbyte.airbyte-integrations.connectors-source-mssql.jar:?]
Caused by: java.lang.RuntimeException: java.time.format.DateTimeParseException: Text '2024-07-26T23:00:40.299747Z' could not be parsed at index 10
        at io.airbyte.integrations.source.mssql.MssqlSourceOperations.setTimestampWithTimezone(MssqlSourceOperations.java:218) ~[io.airbyte.airbyte-integrations.connectors-source-mssql.jar:?]
        at io.airbyte.cdk.db.jdbc.JdbcSourceOperations.setCursorField(JdbcSourceOperations.kt:73) ~[airbyte-cdk-core-0.33.2.jar:?]
        at io.airbyte.cdk.db.jdbc.JdbcSourceOperations.setCursorField(JdbcSourceOperations.kt:18) ~[airbyte-cdk-core-0.33.2.jar:?]
        at io.airbyte.cdk.integrations.source.jdbc.AbstractJdbcSource.getActualCursorRecordCount(AbstractJdbcSource.kt:672) ~[airbyte-cdk-db-sources-0.33.2.jar:?]
        at io.airbyte.cdk.integrations.source.jdbc.AbstractJdbcSource.queryTableIncremental$lambda$22$lambda$21(AbstractJdbcSource.kt:555) ~[airbyte-cdk-db-sources-0.33.2.jar:?]
        at io.airbyte.cdk.db.jdbc.StreamingJdbcDatabase.unsafeQuery(StreamingJdbcDatabase.kt:54) ~[airbyte-cdk-core-0.33.2.jar:?]
        at io.airbyte.cdk.integrations.source.jdbc.AbstractJdbcSource.queryTableIncremental$lambda$22(AbstractJdbcSource.kt:536) ~[airbyte-cdk-db-sources-0.33.2.jar:?]
        at io.airbyte.commons.util.LazyAutoCloseableIterator.computeNext(LazyAutoCloseableIterator.kt:40) ~[airbyte-cdk-dependencies-0.33.2.jar:?]
        at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.TransformedIterator.hasNext(TransformedIterator.java:46) ~[guava-33.0.0-jre.jar:?]
        at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.kt:42) ~[airbyte-cdk-dependencies-0.33.2.jar:?]
        at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:145) ~[guava-33.0.0-jre.jar:?]
        at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:140) ~[guava-33.0.0-jre.jar:?]
        at io.airbyte.cdk.integrations.source.relationaldb.state.SourceStateIterator.computeNext(SourceStateIterator.kt:29) ~[airbyte-cdk-db-sources-0.33.2.jar:?]
        ... 25 more```
4

hi <@U03AEJQM62J>, <@U06JN0M6J15>, seeing your contributions on <https://github.com/airbytehq/airbyte/pull/39419|the PR>, perhaps you could help :pray:
thanks :slightly_smiling_face:

5

Please post this as a github issue and we’ll take a look at it

6

Hi <@U03SDJJNG5R>, please find the Github issue https://github.com/airbytehq/airbyte/issues/42957 :slightly_smiling_face:
thanks for your help