Details
-
Bug
-
Resolution: Duplicate
-
Major
-
None
-
1.9.2.Final
-
None
-
False
-
None
-
False
-
Description
What Debezium connector do you use and what version?
SQLServer Connector version 1.9.2 Final.
What is the connector configuration?
cdc:
name: sql-server-connector
connector: sqlserver
config:
converters: timestampConverter
timestampConverter:
type: TimestampConverter
table.include.list: ${TABLE_INCLUDE_LIST}
snapshot.mode: schema_only
signal.data.collection: ${DEBEZIUM_SIGNAL_TABLE}
max.queue.size: 20480
max.batch.size: 10240
snapshot.fetch.size: 10240
incremental.snapshot.chunk.size: 10240
database:
instance: ${DATABASE_INSTANCE}
user: ${DATABASE_USER}
password: ${DATABASE_PASSWORD}
dbname: ${DATABASE_NAME}
hostname: ${DATABASE_HOSTNAME}
port: ${DATABASE_PORT}
server:
name: ${DATABASE_SERVER_NAME}
id: ${DATABASE_SERVER_ID}
include.schema.changes: false
flattening:
enabled: true
deleteHandlingMode: rewrite
addHeaders: db,op
dropTombstones: true
What is the captured database version and mode of depoyment?
Local Server version 15.0.2000.5
What behaviour do you expect?
When NULL is inserted for columns with defaults, the null value should be sent instead of the underlying function.
What behaviour do you see?
The underlying default function is being sent and errors when it is casted to the destination column. Results in missing records between source and target.
Do you see the same behaviour using the latest relesead Debezium version?
Yes
Do you have the connector logs, ideally from start till finish?
Here is an example of one such exception. Can post full log upon request.
2022-06-02 16:18:11.869 ERROR 28524 — [tition-pump-0-8] o.s.integration.handler.LoggingHandler : org.springframework.messaging.MessagingException: nested exception is org.springframework.messaging.MessageHandlingException: error occurred in message handler [com.wellmed.datarap.event.fn.consumer.jdbc.JdbcMessageHandlerFactory$InternalJdbcMessageHandler@4c19a66c]; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [MERGE INTO TanzuTarget AS TARGET
USING (VALUES) AS SOURCE(__deleted)
ON TARGET.M =? AND TARGET.C =? AND TARGET.Name =?
WHEN MATCHED AND 'true'=? THEN
DELETE
WHEN MATCHED THEN
UPDATE SET TARGET.M=?, TARGET.C=?, TARGET.Name=?, TARGET.SoftDelete=?, TARGET.CreatedOn=?, TARGET.CreatedBy=?, TARGET.EditedOn=?, TARGET.EditedBy=?
WHEN NOT MATCHED AND 'true'!=? THEN
INSERT (M, C, Name, SoftDelete, CreatedOn, CreatedBy, EditedOn, EditedBy) VALUES (?, ?, ?, ?, ?, ?, ?, ?);]; SQL state [S0001]; error code [2628]; String or binary data would be truncated in table 'CDCSink.dbo.TanzuTarget', column 'CreatedBy'. Truncated value: 'ight(suser_sname(),len(suser_sname())-charindex(''\'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated in table 'CDCSink.dbo.TanzuTarget', column 'CreatedBy'. Truncated value: 'ight(suser_sname(),len(suser_sname())-charindex(''\'., failedMessage=GenericMessage [payload=byte[206], headers={replyChannel=nullChannel, errorChannel=, messageSent=true, id=99775f24-f200-4590-e3ef-91b715a300f6, io.opentracing.contrib.spring.integration.messaging.OpenTracingChannelInterceptor.SCOPE=io.opentracing.noop.NoopScopeManagerImpl$NoopScopeImpl@6aef32e4, timestamp=1654204691853}], failedMessage=GenericMessage [payload=byte[206], headers={replyChannel=nullChannel, errorChannel=, io.opentracing.contrib.spring.integration.messaging.OpenTracingChannelInterceptor.SCOPE=io.opentracing.noop.NoopScopeManagerImpl$NoopScopeImpl@6aef32e4, correlationId=0656f574-0cfd-472a-9f2e-040f6732032f, messageSent=true, id=8d5eaf84-e40d-cb83-4d1b-3e3b5a83a322, timestamp=1654204691866}]
at com.wellmed.datarap.event.fn.consumer.jdbc.CdcMessageHandler.buildMessagingExceptionMessage(CdcMessageHandler.java:74)
at com.wellmed.datarap.event.fn.consumer.jdbc.CdcMessageHandler.handleError(CdcMessageHandler.java:63)
at com.wellmed.datarap.event.fn.consumer.jdbc.CdcMessageHandler.handleMessage(CdcMessageHandler.java:48)
at jdk.internal.reflect.GeneratedMethodAccessor136.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:169)
at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:119)
at org.springframework.integration.handler.support.MessagingMethodInvokerHelper$HandlerMethod.invoke(MessagingMethodInvokerHelper.java:1102)
at org.springframework.integration.handler.support.MessagingMethodInvokerHelper.invokeHandlerMethod(MessagingMethodInvokerHelper.java:584)
at org.springframework.integration.handler.support.MessagingMethodInvokerHelper.processInternal(MessagingMethodInvokerHelper.java:479)
at org.springframework.integration.handler.support.MessagingMethodInvokerHelper.process(MessagingMethodInvokerHelper.java:357)
at org.springframework.integration.handler.MethodInvokingMessageProcessor.processMessage(MethodInvokingMessageProcessor.java:110)
at org.springframework.integration.handler.ServiceActivatingHandler.handleRequestMessage(ServiceActivatingHandler.java:105)
at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:136)
at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:56)
at org.springframework.integration.dispatcher.AbstractDispatcher.tryOptimizedDispatch(AbstractDispatcher.java:115)
at org.springframework.integration.dispatcher.UnicastingDispatcher.doDispatch(UnicastingDispatcher.java:133)
at org.springframework.integration.dispatcher.UnicastingDispatcher.dispatch(UnicastingDispatcher.java:106)
at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:72)
at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:317)
at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:272)
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:187)
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:166)
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:47)
at org.springframework.messaging.core.AbstractMessageSendingTemplate.send(AbstractMessageSendingTemplate.java:109)
at org.springframework.messaging.core.AbstractMessageSendingTemplate.convertAndSend(AbstractMessageSendingTemplate.java:151)
at org.springframework.messaging.core.AbstractMessageSendingTemplate.convertAndSend(AbstractMessageSendingTemplate.java:143)
at org.springframework.integration.gateway.MessagingGatewaySupport.send(MessagingGatewaySupport.java:429)
at org.springframework.integration.gateway.GatewayProxyFactoryBean.sendOrSendAndReceive(GatewayProxyFactoryBean.java:656)
at org.springframework.integration.gateway.GatewayProxyFactoryBean.invokeGatewayMethod(GatewayProxyFactoryBean.java:588)
at org.springframework.integration.gateway.GatewayProxyFactoryBean.doInvoke(GatewayProxyFactoryBean.java:555)
at org.springframework.integration.gateway.GatewayProxyFactoryBean.invoke(GatewayProxyFactoryBean.java:544)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
at com.sun.proxy.$Proxy109.accept(Unknown Source)
at org.springframework.cloud.function.context.catalog.SimpleFunctionRegistry$FunctionInvocationWrapper.invokeConsumer(SimpleFunctionRegistry.java:976)
at org.springframework.cloud.function.context.catalog.SimpleFunctionRegistry$FunctionInvocationWrapper.doApply(SimpleFunctionRegistry.java:705)
at org.springframework.cloud.function.context.catalog.SimpleFunctionRegistry$FunctionInvocationWrapper.apply(SimpleFunctionRegistry.java:551)
at org.springframework.cloud.stream.function.PartitionAwareFunctionWrapper.apply(PartitionAwareFunctionWrapper.java:84)
at org.springframework.cloud.stream.function.FunctionConfiguration$FunctionWrapper.apply(FunctionConfiguration.java:754)
at org.springframework.cloud.stream.function.FunctionConfiguration$FunctionToDestinationBinder$1.handleMessageInternal(FunctionConfiguration.java:586)
at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:56)
at org.springframework.integration.dispatcher.AbstractDispatcher.tryOptimizedDispatch(AbstractDispatcher.java:115)
at org.springframework.integration.dispatcher.UnicastingDispatcher.doDispatch(UnicastingDispatcher.java:133)
at org.springframework.integration.dispatcher.UnicastingDispatcher.dispatch(UnicastingDispatcher.java:106)
at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:72)
at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:317)
at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:272)
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:187)
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:166)
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:47)
at org.springframework.messaging.core.AbstractMessageSendingTemplate.send(AbstractMessageSendingTemplate.java:109)
at org.springframework.integration.endpoint.MessageProducerSupport.sendMessage(MessageProducerSupport.java:208)
at com.azure.spring.integration.eventhubs.inbound.EventHubsInboundChannelAdapter.access$900(EventHubsInboundChannelAdapter.java:78)
at com.azure.spring.integration.eventhubs.inbound.EventHubsInboundChannelAdapter$IntegrationBatchMessageListener.onMessage(EventHubsInboundChannelAdapter.java:270)
at com.azure.spring.integration.eventhubs.inbound.EventHubsInboundChannelAdapter$IntegrationBatchMessageListener.onMessage(EventHubsInboundChannelAdapter.java:253)
at com.azure.messaging.eventhubs.EventProcessorClientBuilder$1.processEventBatch(EventProcessorClientBuilder.java:763)
at com.azure.messaging.eventhubs.PartitionPumpManager.processEvents(PartitionPumpManager.java:345)
at com.azure.messaging.eventhubs.PartitionPumpManager.lambda$startPartitionPump$2(PartitionPumpManager.java:257)
at reactor.core.publisher.LambdaSubscriber.onNext(LambdaSubscriber.java:160)
at io.opentracing.contrib.reactor.TracedSubscriber.lambda$onNext$2(TracedSubscriber.java:69)
at io.opentracing.contrib.reactor.TracedSubscriber.withActiveSpan(TracedSubscriber.java:95)
at io.opentracing.contrib.reactor.TracedSubscriber.onNext(TracedSubscriber.java:69)
at reactor.core.publisher.FluxPublishOn$PublishOnSubscriber.runAsync(FluxPublishOn.java:440)
at reactor.core.publisher.FluxPublishOn$PublishOnSubscriber.run(FluxPublishOn.java:527)
at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:84)
at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:37)
at io.opentracing.contrib.concurrent.TracedCallable.call(TracedCallable.java:32)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.springframework.messaging.MessageHandlingException: error occurred in message handler [com.wellmed.datarap.event.fn.consumer.jdbc.JdbcMessageHandlerFactory$InternalJdbcMessageHandler@4c19a66c]; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [MERGE INTO TanzuTarget AS TARGET
USING (VALUES) AS SOURCE(__deleted)
ON TARGET.M =? AND TARGET.C =? AND TARGET.Name =?
WHEN MATCHED AND 'true'=? THEN
DELETE
WHEN MATCHED THEN
UPDATE SET TARGET.M=?, TARGET.C=?, TARGET.Name=?, TARGET.SoftDelete=?, TARGET.CreatedOn=?, TARGET.CreatedBy=?, TARGET.EditedOn=?, TARGET.EditedBy=?
WHEN NOT MATCHED AND 'true'!=? THEN
INSERT (M, C, Name, SoftDelete, CreatedOn, CreatedBy, EditedOn, EditedBy) VALUES (?, ?, ?, ?, ?, ?, ?, ?);]; SQL state [S0001]; error code [2628]; String or binary data would be truncated in table 'CDCSink.dbo.TanzuTarget', column 'CreatedBy'. Truncated value: 'ight(suser_sname(),len(suser_sname())-charindex(''\'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated in table 'CDCSink.dbo.TanzuTarget', column 'CreatedBy'. Truncated value: 'ight(suser_sname(),len(suser_sname())-charindex(''\'., failedMessage=GenericMessage [payload=byte[206], headers={replyChannel=nullChannel, errorChannel=, messageSent=true, id=99775f24-f200-4590-e3ef-91b715a300f6, io.opentracing.contrib.spring.integration.messaging.OpenTracingChannelInterceptor.SCOPE=io.opentracing.noop.NoopScopeManagerImpl$NoopScopeImpl@6aef32e4, timestamp=1654204691853}]
at org.springframework.integration.support.utils.IntegrationUtils.wrapInHandlingExceptionIfNecessary(IntegrationUtils.java:191)
at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:65)
at com.wellmed.datarap.event.fn.consumer.jdbc.CdcMessageHandler.handleError(CdcMessageHandler.java:60)
... 72 more
Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [MERGE INTO TanzuTarget AS TARGET
USING (VALUES) AS SOURCE(__deleted)
ON TARGET.M =? AND TARGET.C =? AND TARGET.Name =?
WHEN MATCHED AND 'true'=? THEN
DELETE
WHEN MATCHED THEN
UPDATE SET TARGET.M=?, TARGET.C=?, TARGET.Name=?, TARGET.SoftDelete=?, TARGET.CreatedOn=?, TARGET.CreatedBy=?, TARGET.EditedOn=?, TARGET.EditedBy=?
WHEN NOT MATCHED AND 'true'!=? THEN
INSERT (M, C, Name, SoftDelete, CreatedOn, CreatedBy, EditedOn, EditedBy) VALUES (?, ?, ?, ?, ?, ?, ?, ?);]; SQL state [S0001]; error code [2628]; String or binary data would be truncated in table 'CDCSink.dbo.TanzuTarget', column 'CreatedBy'. Truncated value: 'ight(suser_sname(),len(suser_sname())-charindex(''\'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated in table 'CDCSink.dbo.TanzuTarget', column 'CreatedBy'. Truncated value: 'ight(suser_sname(),len(suser_sname())-charindex(''\'.
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:981)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:328)
at org.springframework.integration.jdbc.JdbcMessageHandler.executeUpdateQuery(JdbcMessageHandler.java:271)
at org.springframework.integration.jdbc.JdbcMessageHandler.handleMessageInternal(JdbcMessageHandler.java:175)
at com.wellmed.datarap.event.fn.consumer.jdbc.JdbcMessageHandlerFactory$InternalJdbcMessageHandler.handleMessageInternal(JdbcMessageHandlerFactory.java:257)
at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:56)
... 73 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated in table 'CDCSink.dbo.TanzuTarget', column 'CreatedBy'. Truncated value: 'ight(suser_sname(),len(suser_sname())-charindex(''\'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:483)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at io.opentracing.contrib.jdbc.TracingPreparedStatement.executeUpdate(TracingPreparedStatement.java:76)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at io.opentracing.contrib.common.WrapperProxy$1.invoke(WrapperProxy.java:73)
at com.sun.proxy.$Proxy700.executeUpdate(Unknown Source)
at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:965)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
... 80 more
2022-06-02 16:18:11.870 ERROR 28524 — [tition-pump-0-8] c.w.d.e.f.c.j.JdbcConsumerConfiguration : Handling exception via error channel 'bean 'errorChannel'', id='8d5eaf84-e40d-cb83-4d1b-3e3b5a83a322', correlationId='0656f574-0cfd-472a-9f2e-040f6732032f', message='GenericMessage [payload=byte[206], headers={replyChannel=nullChannel, errorChannel=, io.opentracing.contrib.spring.integration.messaging.OpenTracingChannelInterceptor.SCOPE=io.opentracing.noop.NoopScopeManagerImpl$NoopScopeImpl@6aef32e4, correlationId=0656f574-0cfd-472a-9f2e-040f6732032f, messageSent=true, id=8d5eaf84-e40d-cb83-4d1b-3e3b5a83a322, timestamp=1654204691866}]'