[source-MSSQL] incremental append failed due to `datetimeoffset` with CDC as update method

[Pankaj-chaudhary]

Connector Name

source-mssql

Connector Version

4.0.0

What step the error happened?

During the sync

Relevant information

I am doing a sync from an SQL Server to postgres DB.

Using incremental/append-deduped with CDC

The first sync itself is putting null values for my column LastModifiedDate which is of type datetimeoffset in MSSQL source.

2024-03-18 04:20:11 ERROR i.d.u.Loggings(logErrorAndTraceRecord):58 - Failed to properly convert data value for 'training.dbo.AssociatedEntities.LastModifiedDate' of type datetimeoffset 2024-03-18 04:20:11 source > java.time.format.DateTimeParseException: Text '2020-04-22 09:16:20.6566667 +00:00' could not be parsed at index 19

Note: It works for the values where the time is 00:00:00.0000000 +00:00.

Fails for: 2020-04-22 09:16:20.6566667 +00:00
But Works for: 2020-04-22 00:00:00.0000000 +00:00

Relevant log output

2024-03-18 04:20:11 ERROR i.d.u.Loggings(logErrorAndTraceRecord):58 - Failed to properly convert data value for 'training.dbo.AssociatedEntities.LastModifiedDate' of type datetimeoffset
2024-03-18 04:20:11 source > java.time.format.DateTimeParseException: Text '2020-04-22 09:16:20.6566667 +00:00' could not be parsed at index 19
2024-03-18 04:20:11 source >         at java.base/java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:2052) ~[?:?]
2024-03-18 04:20:11 source >         at java.base/java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1954) ~[?:?]
2024-03-18 04:20:11 source >         at java.base/java.time.OffsetDateTime.parse(OffsetDateTime.java:404) ~[?:?]
2024-03-18 04:20:11 source >         at io.airbyte.integrations.source.mssql.MssqlDebeziumConverter.lambda$registerDateTimeOffSet$4(MssqlDebeziumConverter.java:148) ~[io.airbyte.airbyte-integrations.connectors-source-mssql-0.50.45.jar:?]
2024-03-18 04:20:11 source >         at io.debezium.relational.CustomConverterRegistry.lambda$getValueConverter$1(CustomConverterRegistry.java:147) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
2024-03-18 04:20:11 source >         at io.debezium.relational.TableSchemaBuilder.lambda$createValueGenerator$5(TableSchemaBuilder.java:281) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
2024-03-18 04:20:11 source >         at io.debezium.relational.TableSchema.valueFromColumnData(TableSchema.java:141) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
2024-03-18 04:20:11 source >         at io.debezium.relational.RelationalChangeRecordEmitter.emitReadRecord(RelationalChangeRecordEmitter.java:87) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
2024-03-18 04:20:11 source >         at io.debezium.relational.RelationalChangeRecordEmitter.emitChangeRecords(RelationalChangeRecordEmitter.java:50) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
2024-03-18 04:20:11 source >         at io.debezium.pipeline.EventDispatcher.dispatchSnapshotEvent(EventDispatcher.java:207) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
2024-03-18 04:20:11 source >         at io.debezium.relational.RelationalSnapshotChangeEventSource.doCreateDataEventsForTable(RelationalSnapshotChangeEventSource.java:574) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
2024-03-18 04:20:11 source >         at io.debezium.relational.RelationalSnapshotChangeEventSource.lambda$createDataEventsForTableCallable$6(RelationalSnapshotChangeEventSource.java:515) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
2024-03-18 04:20:11 source >         at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
2024-03-18 04:20:11 source >         at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
2024-03-18 04:20:11 source >         at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
2024-03-18 04:20:11 source >         at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
2024-03-18 04:20:11 source >         at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
2024-03-18 04:20:11 source >         at java.base/java.lang.Thread.run(Thread.java:833) [?:?]

Contribute

• Yes, I want to contribute

[pankaj-lmg]

@rodireich mate can you please assist on this one too?

I tried to executed the java code MssqlDebeziumConverter.registerDateTimeOffset offline where it is failing, and it worked all right. Not sure if it is docker container local timezone issue or something else. Please advise.

[marcosmarxm]

@pankaj-lmg would be possible to you test using Airbyte Cloud?

[pankaj-lmg]

@marcosmarxm Unfortunately, for now our intention is to deploy on kubernetes as going via cloud will open a whole lot of work to sort out vpc, network security, etc.

Would appreciate your thoughts on what can be done to get around this issue on airbyte running in container.

Like i said, the below code worked perfect on an online compiler:

import java.time.OffsetDateTime;
import java.time.format.DateTimeFormatter;

public class Main {
  public static final DateTimeFormatter OFFSETDATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSSS XXX");
  private static final String DEBEZIUM_DATETIMEOFFSET_FORMAT = "yyyy-MM-dd HH:mm:ss[.][SSSSSSS] XXX";
   public static void main(String[] args) {
      DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(DEBEZIUM_DATETIMEOFFSET_FORMAT);
      String date = "2022-11-14 21:20:06.4466667 +00:00";
      OffsetDateTime date1 = OffsetDateTime.parse(date.toString(), dateTimeFormatter);
      System.out.println(date1);
      System.out.println(toISO8601String(date1));
   }
  public static String toISO8601String(final OffsetDateTime date) {
    return date.format(OFFSETDATETIME_FORMATTER);
  }

}