TiCDC sql event filter can not handle ddl that using "" as identifier.

[asddongmen]

What did you do?

1. create a changefeed
2. add ANSI_QUOTES to upstream TiDB SQL mode variables.

set sql_mode= "ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, ANSI_QUOTES"

3. create a table in upstream TiDB using a ddl that the its table name and column name are quoted by "".

CREATE TABLE "my_table" (
  "column1" VARCHAR(40) NOT NULL,
  "column2" DECIMAL(1,0) NOT NULL,
  "column3" DECIMAL(5,0) NOT NULL DEFAULT '0',
  "column4" CHAR(9) NOT NULL,
  "column5" VARCHAR(32) NOT NULL,
  "column6" DECIMAL(1,0) NOT NULL,
  "column7" DECIMAL(1,0) DEFAULT NULL,
  "column8" DECIMAL(13,0) DEFAULT NULL,
  "column9" DECIMAL(13,0) DEFAULT NULL,
  "column10" DECIMAL(3,0) DEFAULT NULL,
  "column11" DATETIME(6) DEFAULT NULL,
  "column12" DECIMAL(1,0) NOT NULL,
  "column13" CHAR(8) DEFAULT NULL,
  "column14" DATETIME(6) DEFAULT NULL,
  "column15" CHAR(8) DEFAULT NULL,
  "column16" DECIMAL(13,0) DEFAULT NULL,
  "column17" DECIMAL(13,0) DEFAULT NULL,
  "column18" DECIMAL(13,0) DEFAULT NULL,
  "column19" DECIMAL(13,0) DEFAULT NULL,
  "column20" DECIMAL(13,0) DEFAULT NULL,
  "column21" DATETIME(6) DEFAULT NULL,
  "column22" VARCHAR(30) DEFAULT NULL,
  "column23" DECIMAL(13,0) DEFAULT NULL,
  "column24" CHAR(20) DEFAULT NULL,
  "column25" VARCHAR(30) DEFAULT NULL,
  "column26" DECIMAL(13,0) DEFAULT NULL,
  "column27" CHAR(20) DEFAULT NULL,
  "column28" DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  "column29" DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (column1, column2, column3),
  KEY idx_column4_search_key (column5),
  KEY idx_column11_search_key (column11)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

What did you expect to see?

changefeed works normally.

What did you see instead?

Changefeed error.

> ./cdc cli changefeed list
[
  {
    "id": "test1",
    "namespace": "default",
    "summary": {
      "state": "warning",
      "tso": 444904478024925188,
      "checkpoint": "2023-10-13 13:44:55.786",
      "error": {
        "time": "2023-10-13T13:44:57.87892+08:00",
        "addr": "127.0.0.1:8300",
        "code": "CDC:ErrHandleDDLFailed",
        "message": "[CDC:ErrHandleDDLFailed]handle ddl failed, job: ID:108, Type:create table, State:done, SchemaState:public, SchemaID:2, TableID:107, RowCount:0, ArgLen:0, start time: 2023-10-13 13:44:56.336 +0800 CST, Err:\u003cnil\u003e, ErrCount:0, SnapshotVersion:0, query: CREATE TABLE \"my_table\" (\n  \"column1\" VARCHAR(40) NOT NULL,\n  \"column2\" DECIMAL(1,0) NOT NULL,\n  \"column3\" DECIMAL(5,0) NOT NULL DEFAULT '0',\n  \"column4\" CHAR(9) NOT NULL,\n  \"column5\" VARCHAR(32) NOT NULL,\n  \"column6\" DECIMAL(1,0) NOT NULL,\n  \"column7\" DECIMAL(1,0) DEFAULT NULL,\n  \"column8\" DECIMAL(13,0) DEFAULT NULL,\n  \"column9\" DECIMAL(13,0) DEFAULT NULL,\n  \"column10\" DECIMAL(3,0) DEFAULT NULL,\n  \"column11\" DATETIME(6) DEFAULT NULL,\n  \"column12\" DECIMAL(1,0) NOT NULL,\n  \"column13\" CHAR(8) DEFAULT NULL,\n  \"column14\" DATETIME(6) DEFAULT NULL,\n  \"column15\" CHAR(8) DEFAULT NULL,\n  \"column16\" DECIMAL(13,0) DEFAULT NULL,\n  \"column17\" DECIMAL(13,0) DEFAULT NULL,\n  \"column18\" DECIMAL(13,0) DEFAULT NULL,\n  \"column19\" DECIMAL(13,0) DEFAULT NULL,\n  \"column20\" DECIMAL(13,0) DEFAULT NULL,\n  \"column21\" DATETIME(6) DEFAULT NULL,\n  \"column22\" VARCHAR(30) DEFAULT NULL,\n  \"column23\" DECIMAL(13,0) DEFAULT NULL,\n  \"column24\" CHAR(20) DEFAULT NULL,\n  \"column25\" VARCHAR(30) DEFAULT NULL,\n  \"column26\" DECIMAL(13,0) DEFAULT NULL,\n  \"column27\" CHAR(20) DEFAULT NULL,\n  \"column28\" DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n  \"column29\" DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n  PRIMARY KEY (column1, column2, column3),\n  KEY idx_column4_search_key (column5),\n  KEY idx_column11_search_key (column11)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin, startTs: 444904478169104400. If you want to skip this DDL and continue with replication, you can manually execute this DDL downstream. Afterwards, add `ignore-txn-start-ts=[444904478169104400]` to the changefeed in the filter configuration.: [CDC:ErrConvertDDLToEventTypeFailed]failed to convert ddl 'CREATE TABLE \"my_table\" (\n  \"column1\" VARCHAR(40) NOT NULL,\n  \"column2\" DECIMAL(1,0) NOT NULL,\n  \"column3\" DECIMAL(5,0) NOT NULL DEFAULT '0',\n  \"column4\" CHAR(9) NOT NULL,\n  \"column5\" VARCHAR(32) NOT NULL,\n  \"column6\" DECIMAL(1,0) NOT NULL,\n  \"column7\" DECIMAL(1,0) DEFAULT NULL,\n  \"column8\" DECIMAL(13,0) DEFAULT NULL,\n  \"column9\" DECIMAL(13,0) DEFAULT NULL,\n  \"column10\" DECIMAL(3,0) DEFAULT NULL,\n  \"column11\" DATETIME(6) DEFAULT NULL,\n  \"column12\" DECIMAL(1,0) NOT NULL,\n  \"column13\" CHAR(8) DEFAULT NULL,\n  \"column14\" DATETIME(6) DEFAULT NULL,\n  \"column15\" CHAR(8) DEFAULT NULL,\n  \"column16\" DECIMAL(13,0) DEFAULT NULL,\n  \"column17\" DECIMAL(13,0) DEFAULT NULL,\n  \"column18\" DECIMAL(13,0) DEFAULT NULL,\n  \"column19\" DECIMAL(13,0) DEFAULT NULL,\n  \"column20\" DECIMAL(13,0) DEFAULT NULL,\n  \"column21\" DATETIME(6) DEFAULT NULL,\n  \"column22\" VARCHAR(30) DEFAULT NULL,\n  \"column23\" DECIMAL(13,0) DEFAULT NULL,\n  \"column24\" CHAR(20) DEFAULT NULL,\n  \"column25\" VARCHAR(30) DEFAULT NULL,\n  \"column26\" DECIMAL(13,0) DEFAULT NULL,\n  \"column27\" CHAR(20) DEFAULT NULL,\n  \"column28\" DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n  \"column29\" DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n  PRIMARY KEY (column1, column2, column3),\n  KEY idx_column4_search_key (column5),\n  KEY idx_column11_search_key (column11)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin' to filter event type: line 1 column 23 near \"\"my_table\" (\n  \"column1\" VARCHAR(40) NOT NULL,\n  \"column2\" DECIMAL(1,0) NOT NULL,\n  \"column3\" DECIMAL(5,0) NOT NULL DEFAULT '0',\n  \"column4\" CHAR(9) NOT NULL,\n  \"column5\" VARCHAR(32) NOT NULL,\n  \"column6\" DECIMAL(1,0) NOT NULL,\n  \"column7\" DECIMAL(1,0) DEFAULT NULL,\n  \"column8\" DECIMAL(13,0) DEFAULT NULL,\n  \"column9\" DECIMAL(13,0) DEFAULT NULL,\n  \"column10\" DECIMAL(3,0) DEFAULT NULL,\n  \"column11\" DATETIME(6) DEFAULT NULL,\n  \"column12\" DECIMAL(1,0) NOT NULL,\n  \"column13\" CHAR(8) DEFAULT NULL,\n  \"column14\" DATETIME(6) DEFAULT NULL,\n  \"column15\" CHAR(8) DEFAULT NULL,\n  \"column16\" DECIMAL(13,0) DEFAULT NULL,\n  \"column17\" DECIMAL(13,0) DEFAULT NULL,\n  \"column18\" DECIMAL(13,0) DEFAULT NULL,\n  \"column19\" DECIMAL(13,0) DEFAULT NULL,\n  \"column20\" DECIMAL(13,0) DEFAULT NULL,\n  \"column21\" DATETIME(6) DEFAULT NULL,\n  \"column22\" VARCHAR(30) DEFAULT NULL,\n  \"column23\" DECIMAL(13,0) DEFAULT NULL,\n  \"column24\" CHAR(20) DEFAULT NULL,\n  \"column25\" VARCHAR(30) DEFAULT NULL,\n  \"column26\" DECIMAL(13,0) DEFAULT NULL,\n  \"column27\" CHAR(20) DEFAULT NULL,\n  \"column28\" DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n  \"column29\" DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n  PRIMARY KEY (column1, column2, column3),\n  KEY idx_column4_search_key (column5),\n  KEY idx_column11_search_key (column11)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin\" "
      }
    }
  }
]

Versions of the cluster

Upstream TiDB cluster version (execute SELECT tidb_version(); in a MySQL client):

(paste TiDB cluster version here)

Upstream TiKV version (execute tikv-server --version):

(paste TiKV version here)

TiCDC version (execute cdc version):

v7.1.1

[asddongmen]

The reason for this issue is that the internal parser used by TiCDC uses the default SQL mode, while the upstream TiDB has added the "an" SQL mode. As a result, TiCDC's parser cannot correctly interpret double quotes (""), treating them as identifiers.

[seiya-annie]

/found customer