cdc sync table error while using NO_BACKSLASH_ESCAPES SQL mode

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

-- downstream
 set global  sql_mode = "NO_BACKSLASH_ESCAPES";

-- upstream
set sql_mode = "NO_BACKSLASH_ESCAPES";
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;

2. What did you expect to see? (Required)

| 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 |

3. What did you see instead (Required)

| 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 |

4. What is your TiDB version? (Required)

tidb

MySQL [regtest5]> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.3.0-alpha
Edition: Community
Git Commit Hash: efc07201e3441ed8dfed08e10ae19fa407dda596
Git Branch: heads/refs/tags/v6.3.0-alpha
UTC Build Time: 2022-09-17 11:08:40
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |

/ # /cdc version
Release Version: v6.3.0-alpha
Git Commit Hash: 6587d4ebb46baeeecf14158a14385f6f7870ef74
Git Branch: heads/refs/tags/v6.3.0-alpha
UTC Build Time: 2022-09-17 11:01:32
Go Version: go version go1.19 linux/amd64
Failpoint Build: false

[aytrack]

The issue is that the \ in the regexp pattern was lost

[windtalker]

@aytrack could you please also test like function to see if this is a regexp related bug or cdc/sql_mode related bug?

[aytrack]

binlog has the same issue. It about the NO_BACKSLASH_ESCAPES sql_mode.

set sql_mode = "NO_BACKSLASH_ESCAPES";
create table t002(id bigint primary key, a varchar(100), b varchar(20) as (a like "\\c"));
set sql_mode = "";
create table t001(id bigint primary key,a varchar(100), b varchar(20) as (a like "\\c"));

downstream

mysql root@127.0.0.1:reg3> show create table t002\G
***************************[ 1. row ]***************************
Table        | t002
Create Table | CREATE TABLE `t002` (
  `id` bigint(20) NOT NULL,
  `a` varchar(100) DEFAULT NULL,
  `b` varchar(20) GENERATED ALWAYS AS (`a` like _utf8mb4'\\c') VIRTUAL,  -- should be `b` varchar(20) GENERATED ALWAYS AS (`a` like _utf8'\\\\c') VIRTUAL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set
Time: 0.002s
mysql root@127.0.0.1:reg3> show create table t001\G
***************************[ 1. row ]***************************
Table        | t001
Create Table | CREATE TABLE `t001` (
  `id` bigint(20) NOT NULL,
  `a` varchar(100) DEFAULT NULL,
  `b` varchar(20) GENERATED ALWAYS AS (`a` like _utf8mb4'\\c') VIRTUAL, 
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set

[windtalker]

I think it's a sql mode related bug, so remove execution label.

[CbcWestwolf]

/assign

[CbcWestwolf]

/unassign

[CbcWestwolf]

/label component/ticdc

[ti-chi-bot]

@CbcWestwolf: The label(s) component/ticdc cannot be applied. These labels are supported: challenge-program, compatibility-breaker, first-time-contributor, contribution, require-LGT3, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-4.0, needs-cherry-pick-5.0, needs-cherry-pick-5.1, needs-cherry-pick-5.2, needs-cherry-pick-5.3, needs-cherry-pick-5.4, needs-cherry-pick-6.0, needs-cherry-pick-6.1, needs-cherry-pick-6.2, needs-cherry-pick-6.3, affects-4.0, affects-5.0, affects-5.1, affects-5.2, affects-5.3, affects-5.4, affects-6.0, affects-6.1, affects-6.2, affects-6.3, may-affects-4.0, may-affects-5.0, may-affects-5.1, may-affects-5.2, may-affects-5.3, may-affects-5.4, may-affects-6.0, may-affects-6.1, may-affects-6.2, may-affects-6.3.

In response to this:

/label component/ticdc

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[CbcWestwolf]

Since this issue has been moved to pingcap/tiflow#7142, I'll close it.

[CbcWestwolf]

/close

[ti-chi-bot]

@CbcWestwolf: Closing this issue.

In response to this:

/close

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.