Error getting deleted records

asked 2018-12-12 05:40:15 -0500

mielios gravatar image

SDC version : StreamSets Data Collector 3.6.0

Ms SQL version : Microsoft SQL Server 2016 (SP2-CU2-GDR) (KB4458621) - 13.0.5201.2 (X64)

Origin : SQL Server Change Tracking

External libraries : mssql-jdbc-7.0.0.jre8.jar and sqljdbc42-6.0.8112.jar

In the logs I see following select query to get the changes:

SELECT * FROM s.table AS P RIGHT OUTER JOIN CHANGETABLE(CHANGES s.table, 0) AS CT ON CT.ID = P.ID WHERE (CT.ID > 'null' AND CT.SYS_CHANGE_VERSION = 75015 ) OR (CT.SYS_CHANGE_VERSION > '75015' )
ORDER BY SYS_CHANGE_VERSION, CT.ID

Followed by this stacktrace:

com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type varchar to numeric. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:5378) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1754) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1018) at com.zaxxer.hikari.proxy.HikariResultSetProxy.next(HikariResultSetProxy.java) at com.streamsets.pipeline.lib.jdbc.multithread.JdbcBaseRunnable.generateBatchAndCommitOffset(JdbcBaseRunnable.java:228) at com.streamsets.pipeline.lib.jdbc.multithread.JdbcBaseRunnable.run(JdbcBaseRunnable.java:144) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:226) at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:33) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:222) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeRunnable.run(SafeScheduledExecutorService.java:188) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:226) at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:33) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:222) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeRunnable.run(SafeScheduledExecutorService.java:188) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)

If I replace > 'null' with > 0 in the select query and run it against the MS SQL server:

SELECT * FROM s.table AS P RIGHT OUTER JOIN CHANGETABLE(CHANGES s.table, 0) AS CT ON CT.ID = P.ID WHERE (CT.ID > 0 AND CT.SYS_CHANGE_VERSION = 75015 ) OR (CT.SYS_CHANGE_VERSION > '75015' )
ORDER BY SYS_CHANGE_VERSION, CT.ID

I see a resultset starting with a deleted record (ID = NULL, SYS_CHANGE_VERSION = 75015 and SYS_CHANGE_OPERATION = D). I suspect the > 'null' part in the select query is generated based on the ID of this deleted record?

Is there something I can change in the configuration ... (more)

edit retag flag offensive close merge delete