Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-3380

CDC events (LSNs) out of order when querying [cdc].[fn_cdc_get_all_changes_#]

XMLWordPrintable

    • False
    • False
    • Undefined
    • Hide

      1. Start the Debezium connector on SQL Server Database.

      2. Wait to finish the snapshot

      3. About few days with Debezium working fine the error occurs

      Show
      1. Start the Debezium connector on SQL Server Database. 2. Wait to finish the snapshot 3. About few days with Debezium working fine the error occurs

      After using Debezium as replicator for few days, the following error occurs:

      Value received: 'org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.Value received: 'org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped. at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:42) at io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource.execute(SqlServerStreamingChangeEventSource.java:283) at io.debezium.pipeline.ChangeEventSourceCoordinator.streamEvents(ChangeEventSourceCoordinator.java:140) at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:113) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) 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)Caused by: java.lang.IllegalStateException: The update before event at 000033df:019c5d33:0005(000033df:018dda41:0012) for table db_name.dbo.some_table was not followed by after event. Please report this as a bug together with a events around given LSN. at io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource.lambda$execute$1(SqlServerStreamingChangeEventSource.java:249) at io.debezium.jdbc.JdbcConnection.prepareQuery(JdbcConnection.java:607) at io.debezium.connector.sqlserver.SqlServerConnection.getChangesForTables(SqlServerConnection.java:247) at io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource.execute(SqlServerStreamingChangeEventSource.java:164)

       

      After debugging the issue, it was discovered that some cdc events were out of order.

      In order to fix this issue we had to modify [cdc].[fn_cdc_get_all_changes_dbo_some_table] and add the following 'order by' at the end of the function:

      order by [__$start_lsn] ASC, [__$command_id] ASC, [__$seqval] ASC, [__$operation] ASC offset 0 ROWS

       Here is the discussion history in this ticket: DBZ-2417

      After going through this issue, we decided to modify the 'order by' and exclude [__$command_id] from 'order by' clause.
      After monitoring for several days everything was working fine so it was decided to include this as a fix. However, several days later, we got the same error while using below ordering:

      order by [__$start_lsn] ASC, [__$seqval] ASC, [__$operation] ASC offset 0 ROWS

       We included [__$command_id] back into the ordering and the issue went away.

       

       Suggestion:

      Is it possible to include an option that would allow the user to override the default query and allow the user the option to query from CDC tables directly?

      e.g.

      select t.*
from [cdc].[dbo_<table_name>_CT] t with (nolock)     
where ( [sys].[fn_cdc_check_parameters]( N'dbo_<table_name>', @from_lsn, @to_lsn, lower(rtrim(ltrim('all update old'))), 0) = 1)
	and (t.__$operation = 1 or t.__$operation = 2 or t.__$operation = 4 or t.__$operation = 3)
	and (t.__$start_lsn <= @to_lsn)
	and (t.__$start_lsn >= @from_lsn)
order by [__$start_lsn] ASC, [__$command_id] ASC, [__$seqval] ASC, [__$operation] ASC

       

            Unassigned Unassigned
            rap70r Rap70r Rap70r (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated: