Failure in date time conversion during mssql first sync: Long cannot be cast to class java.sql.Timestamp

[rodireich]

Connector Name

source-mssql

Connector Version

4.0.0

What step the error happened?

None

Relevant information

Discovered in workspace

Error while reading for the first time

Relevant log output

2024-03-06 18:05:59 source > ERROR i.d.r.RelationalSnapshotChangeEventSource(doExecute):179 Error during snapshot io.debezium.DebeziumException: java.lang.ClassCastException: class java.lang.Long cannot be cast to class java.sql.Timestamp (java.lang.Long is in module java.base of loader 'bootstrap'; java.sql.Timestamp is in module java.sql of loader 'platform')
        at io.debezium.relational.RelationalSnapshotChangeEventSource.lambda$createSchemaChangeEventsForTables$3(RelationalSnapshotChangeEventSource.java:387) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.pipeline.EventDispatcher.dispatchSchemaChangeEvent(EventDispatcher.java:367) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.relational.RelationalSnapshotChangeEventSource.createSchemaChangeEventsForTables(RelationalSnapshotChangeEventSource.java:382) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:154) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:92) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:253) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:237) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.connector.sqlserver.SqlServerChangeEventSourceCoordinator.executeChangeEventSources(SqlServerChangeEventSourceCoordinator.java:82) ~[debezium-connector-sqlserver-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:137) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572) ~[?:?]
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
        at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
Caused by: java.lang.ClassCastException: class java.lang.Long cannot be cast to class java.sql.Timestamp (java.lang.Long is in module java.base of loader 'bootstrap'; java.sql.Timestamp is in module java.sql of loader 'platform')
        at io.airbyte.integrations.source.mssql.MssqlDebeziumConverter.lambda$registerDatetime$3(MssqlDebeziumConverter.java:133) ~[io.airbyte.airbyte-integrations.connectors-source-mssql.jar:?]
        at io.debezium.relational.CustomConverterRegistry.lambda$getValueConverter$1(CustomConverterRegistry.java:147) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.relational.TableSchemaBuilder.addField(TableSchemaBuilder.java:406) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.relational.TableSchemaBuilder.lambda$create$2(TableSchemaBuilder.java:149) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184) ~[?:?]
        at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179) ~[?:?]
        at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1708) ~[?:?]
        at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) ~[?:?]
        at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) ~[?:?]
        at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151) ~[?:?]
        at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174) ~[?:?]
        at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
        at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596) ~[?:?]
        at io.debezium.relational.TableSchemaBuilder.create(TableSchemaBuilder.java:147) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.relational.RelationalDatabaseSchema.buildAndRegisterSchema(RelationalDatabaseSchema.java:122) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.connector.sqlserver.SqlServerDatabaseSchema.applySchemaChange(SqlServerDatabaseSchema.java:53) ~[debezium-connector-sqlserver-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.pipeline.EventDispatcher$SchemaChangeEventReceiver.schemaChangeEvent(EventDispatcher.java:622) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        at io.debezium.relational.RelationalSnapshotChangeEventSource.lambda$createSchemaChangeEventsForTables$3(RelationalSnapshotChangeEventSource.java:384) ~[debezium-core-2.4.0.Final.jar:2.4.0.Final]
        ... 13 more

Contribute

• Yes, I want to contribute

[rodireich]

Related to a code change in #32573

[Pankaj-chaudhary]

@rodireich I still face this issue when i have a max date constraint defined on my source mssql column.

Column Definition:
[ValidTo] [datetime2](0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

Column Constraint:
ALTER TABLE [dbo].[Record] ADD CONSTRAINT [Record_ValidTo] DEFAULT ('9999.12.31 23:59:59.99') FOR [ValidTo]

It works fine, when the constraint is dropped. Happing on version 4.0.0 also.

Issue: The default date is equivalent to 253402300799990 EPOCH Timestamp and for Airbyte it seems like an impossible date.

Can you confirm if that happens to you also.

[rodireich]

The issue as you mention @Pankaj-chaudhary is related to a default value.
During schema history creation the values are given as epoch nanoseconds and need to be converted as such.