[source-mssql] Incremental sync on 'Timestamp with Timezone' cursor field failing for 'DateTimeParseException'

[kev-datams]

Connector Name

source-mssql

Connector Version

4.1.3

What step the error happened?

During the sync

Relevant information

Hi community,

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 below issue at state reading time 2 days after upgrading from v0.62.4 to v0.63.9:

Looking at state content, we see that cursor value is formatted like 2024-08-01T00:00:33.919417Z (example for today’s value):

According to your Java code, the decoder expects yyyy-MM-dd HH:mm:ss.SSSSSSS XXX pattern, implying the exception observed.

ℹ️ If we manually modify the state value to 2024-08-01 00:00:33.9194170 Z to match the pattern, the sync works as expected, but we can not do it manually daily (as next state is still stored with not expected formatting for state read).

Hypothesis:

• state value looks well formatted ("standard" format, independently from cursor field datatype), but wrongly parsed at reading time for next sync run
• this commit is possibly responsible for the issue

Please could you help us to solve this case ? 🙏

Relevant log output

2024-07-29 09:28:03 source > ERROR main i.a.c.i.b.AirbyteExceptionHandler(uncaughtException):30 Something went wrong in the connector. See the logs for more details. io.airbyte.cdk.integrations.source.relationaldb.state.FailedRecordIteratorException: java.lang.RuntimeException: java.time.format.DateTimeParseException: Text '2024-07-26T23:00:40.299747Z' could not be parsed at index 10
        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

Contribute

• [ ] Yes, I want to contribute

[kev-datams]

hello @evantahler, @xiaohansong, @rodireich FYI 🙏

[isaac47]

Any fix about this error please? I faced the same issue.

[theyueli]

@xiaohansong will continue looking into this.

[kev-datams]

Hi @xiaohansong, do you have an ETA for this fix please ? 🙏

[sakent]

@xiaohansong @theyueli we are experiencing this issue as well, do you have ETA for this fix?

[kev-datams]

Hi team, any news about this impactful issue ?

[xiaohansong]

Sorry about the delay - this somehow falls off my radar. I'll have a fix today.

[kev-datams]

Thanks a lot @xiaohansong! Just upgraded and checked on our side, looks good, issue solved :)