How to Manually Delete Information in TiCDC Without Syncing to Downstream, While Syncing Business Deletion Information to Downstream

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: ticdc手工删除信息不同步到下游,业务删除信息同步到下游,如何做到

| username: wluckdog

【TiDB Usage Environment】Production Environment / Testing / PoC
【TiDB Version】tidb 6.5
【Reproduction Path】ticdc condition filtering
ignore-delete-value-expr = “name = ‘john’” # Filter out delete DMLs that contain the condition name = ‘john’

I set ignore-delete-value-expr = “1 = 1’” #
In my cdc configuration file, I configured the above filtering condition to distinguish between manual deletions that are not synchronized downstream and actual business deletions that are synchronized downstream.
delete from t_table where no=‘92171802708161882’ and 1 = 1
The above manual deletion statement was synchronized to Kafka.

【Encountered Problem: Problem Phenomenon and Impact】
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

| username: 春风十里 | Original post link

You have an extra quotation mark here?

| username: 小龙虾爱大龙虾 | Original post link

It’s useless. CDC captures the change logs of TiKV. CDC cannot see the original SQL you executed, and its filtering is based on the value of the data.

| username: 小龙虾爱大龙虾 | Original post link

  • ignore-delete-value-expr: Configures an SQL expression that applies to DELETE type DML events with specified values.
  • ignore-insert-value-expr: Configures an SQL expression that applies to INSERT type DML events with specified values.
  • ignore-update-old-value-expr: Configures an SQL expression that applies to UPDATE type DML events with specified old values.
  • ignore-update-new-value-expr: Configures an SQL expression that applies to UPDATE type DML events with specified new values.
| username: Jasper | Original post link

This 1=1 actually has no effect and will be optimized out during the logical optimization phase. The ignore-delete-value-expr must be within the range of the column that is actually to be deleted in order to take effect.

| username: wluckdog | Original post link

I also think that “where 1=1” is useless, it’s just to mark the statement, the specific value is useful. Moreover, after using the expression to filter, batch deletion of unsynchronized data will cause CDC delay.

| username: wangccsy | Original post link

You must be serious.

| username: wluckdog | Original post link

I am seriously testing the functionality I need to implement.

| username: Jellybean | Original post link

CDC captures the change data from TiKV and only recognizes row data changes. It cannot distinguish whether the change was made manually or by business operations.

Therefore, the conclusion is that regardless of the source, as long as a row of data is changed, TiCDC will synchronize these changes to the downstream.

| username: tidb菜鸟一只 | Original post link

The underlying mechanism for CDC to obtain changes is by retrieving key-value pairs from TiKV, not through the binlog of the TiDB server. Therefore, it cannot distinguish differences in your SQL.

| username: Holland | Original post link

Unable to distinguish

| username: dba远航 | Original post link

Indistinguishable