TiCDC misses backslash(`\`) when replicating ddl to downstream

[CbcWestwolf]

What did you do?

This is a bug found in tidb issue.

1. Set sql mode in both upstream and downstream tidbs

-- downstream
set sql_mode = "NO_BACKSLASH_ESCAPES";
set global  sql_mode = "NO_BACKSLASH_ESCAPES";

-- upstream
set sql_mode = "NO_BACKSLASH_ESCAPES";
set global  sql_mode = "NO_BACKSLASH_ESCAPES";

2. Create a changefeed to start a replication

-- upstream
create table t20(id bigint primary key, a text, b text as ((regexp_replace(a, "^[1-9]\d{9,29}$", "aaaaa"))), c text as ((regexp_substr(a, "^[1-9]\d{9,29}$"))), d text as ((regexp_like(a, "^[1-9]\d{9,29}$"))), e text as ((regexp_instr(a, "^[1-9]\d{9,29}$"))));

-- downstream
show create table t20;

What did you expect to see?

| t20   | CREATE TABLE `t20` (
  `id` bigint(20) NOT NULL,
  `a` text DEFAULT NULL,
  `b` text GENERATED ALWAYS AS ((regexp_replace(`a`, _utf8'^[1-9]\\d{9,29}$', _utf8'aaaaa'))) VIRTUAL,
  `c` text GENERATED ALWAYS AS ((regexp_substr(`a`, _utf8'^[1-9]\\d{9,29}$'))) VIRTUAL,
  `d` text GENERATED ALWAYS AS ((regexp_like(`a`, _utf8'^[1-9]\\d{9,29}$'))) VIRTUAL,
  `e` text GENERATED ALWAYS AS ((regexp_instr(`a`, _utf8'^[1-9]\\d{9,29}$'))) VIRTUAL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |

What did you see instead?

| t20   | CREATE TABLE `t20` (
  `id` bigint(20) NOT NULL,
  `a` text DEFAULT NULL,
  `b` text GENERATED ALWAYS AS ((regexp_replace(`a`, _utf8mb4'^[1-9]d{9,29}$', _utf8mb4'aaaaa'))) VIRTUAL,
  `c` text GENERATED ALWAYS AS ((regexp_substr(`a`, _utf8mb4'^[1-9]d{9,29}$'))) VIRTUAL,
  `d` text GENERATED ALWAYS AS ((regexp_like(`a`, _utf8mb4'^[1-9]d{9,29}$'))) VIRTUAL,
  `e` text GENERATED ALWAYS AS ((regexp_instr(`a`, _utf8mb4'^[1-9]d{9,29}$'))) VIRTUAL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |

Versions of the cluster

You can reproduce it using tiup playground nightly --db 1 --kv 1 --tiflash 0 --ticdc 1 (upstream) and tiup playground nightly --db 1 --kv 1 --tiflash 0 (downstream)

[fubinzh]

/severity Major

[hongyunyan]

From v7.5, we can set corresponding sql_mode in changefeed config to avoid this problem.

[hongyunyan]

This is related PR: https://github.com/pingcap/tiflow/pull/9941

[CbcWestwolf]

Since there is a solution, I will close this issue now.