delete and update events may not be correctly sinked to downstream

[zhaoxinyu]

What did you do?

1. run csv_storage_basic test

CASE="csv_storage_basic" docker-compose -f ./deployments/ticdc/docker-compose/docker-compose-storage-integration.yml up

2. run sync_diff_inspector to check difference between upstream and downstream.

What did you expect to see?

All dml events can be sinked correctly.

What did you see instead?

Delete and update events are not sinked properly.

Versions of the cluster

TiCDC version (execute cdc version):

v6.7.0-master

[zhaoxinyu]

/remove-label severity/moderate

[ti-chi-bot]

@zhaoxinyu: The label(s) severity/moderate cannot be applied. These labels are supported: duplicate, bug-from-internal-test, bug-from-user, 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, affects-6.4, affects-6.5, affects-6.6, 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, may-affects-6.4, may-affects-6.5, may-affects-6.6, needs-cherry-pick-release-4.0, needs-cherry-pick-release-5.0, needs-cherry-pick-release-5.1, needs-cherry-pick-release-5.2, needs-cherry-pick-release-5.3, needs-cherry-pick-release-5.4, needs-cherry-pick-release-6.0, needs-cherry-pick-release-6.1, needs-cherry-pick-release-6.2, needs-cherry-pick-release-6.3, needs-cherry-pick-release-6.4, needs-cherry-pick-release-6.5, needs-cherry-pick-release-6.6, question, release-blocker, wontfix, require-LGT1, require-LGT3.

In response to this:

/remove-label severity/moderate

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.

[zhaoxinyu]

/remove-severity moderate

[zhaoxinyu]

This bug also affects MySQL sink, not only storage consumer.

[zhaoxinyu]

/remove-label may-affects-4.0
/remove-label may-affects-5.0
/remove-label may-affects-5.1
/remove-label may-affects-5.2
/remove-label may-affects-5.3
/remove-label may-affects-5.4

[zhaoxinyu]

For MySQL sink, the reproduce steps are as follows:

1. create a changefeed with MySQL sink.
2. execute the following queries in upstream TiDB:

use test
CREATE TABLE t0 ( id INT not null, name varchar(128) CHARACTER SET gbk not null, country char(32) CHARACTER SET gbk, city varchar(64), description  text CHARACTER SET gbk, image tinyblob, UNIQUE
KEY (id, name) ) ENGINE = Innodb;

INSERT INTO t0
VALUES (1, '测试', "中国", "上海", "你好,世界"
	, 0xC4E3BAC3CAC0BDE7);

INSERT INTO t0
VALUES (2, '部署', "美国", "纽约", "世界,你好"
	, 0xCAC0BDE7C4E3BAC3);

UPDATE t0
SET name = '开发'
WHERE name = '测试';

3. Check the rows in t0 in downstream MySQL:

We expect to see the following output:

mysql> select * from t0;
+----+--------+---------+--------+---------------+--------------------+
| id | name   | country | city   | description   | image              |
+----+--------+---------+--------+---------------+--------------------+
|  1 | 开发   | 中国    | 上海   | 你好,世界     | 0xC4E3BAC3CAC0BDE7 |
|  2 | 部署   | 美国    | 纽约   | 世界,你好     | 0xCAC0BDE7C4E3BAC3 |
+----+--------+---------+--------+---------------+--------------------+
2 rows in set (0.00 sec)

But we actually got:

mysql> select * from t0;
+----+--------+---------+--------+---------------+--------------------+
| id | name   | country | city   | description   | image              |
+----+--------+---------+--------+---------------+--------------------+
|  1 | 开发   | NULL    | NULL   | NULL          | NULL               |
|  2 | 部署   | 美国    | 纽约   | 世界,你好     | 0xCAC0BDE7C4E3BAC3 |
+----+--------+---------+--------+---------------+--------------------+
2 rows in set (0.00 sec)

[asddongmen]

After enabling batch DML feature, when converting update statements, TiCDC will transform the statements into the following format:

from:

CREATE TABLE t0 ( id INT not null, name varchar(128) CHARACTER SET gbk not null, country char(32) CHARACTER SET gbk, city varchar(64), description  text CHARACTER SET gbk, image tinyblob, UNIQUE
KEY (id, name) ) ENGINE = Innodb;

UPDATE t0
SET name = '开发'
WHERE name = '测试';

covert to:

"UPDATE `test`.`t0` SET `id`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `name`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `country`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `city`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `description`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `image`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END WHERE ROW(`id`,`name`) IN (ROW(?,?))\"]"] [values="[[1,\"测试\",1,1,\"测试\",\"开发\",1,\"测试\",\"中国\",1,\"测试\",\"上海\",1,\"测试\",\"你好,世界\",1,\"测试\",\"xOO6w8rAvec=\",1,\"测试\"]]"

The behavior of this statement differs between TiDB and MySQL. It may cause the update statement of this type to not achieve the expected result when executed on MySQL.
Ref: https://docs.pingcap.com/tidb/stable/sql-statement-update#mysql-compatibility
However, all downstream integration tests of TiCDC currently use TiDB, so relevant issues were not previously detected.

[asddongmen]

After enabling batch DML feature, when converting update statements, TiCDC will transform the statements into the following format:

from:

CREATE TABLE t0 ( id INT not null, name varchar(128) CHARACTER SET gbk not null, country char(32) CHARACTER SET gbk, city varchar(64), description  text CHARACTER SET gbk, image tinyblob, UNIQUE
KEY (id, name) ) ENGINE = Innodb;

UPDATE t0
SET name = '开发'
WHERE name = '测试';

covert to:

"UPDATE `test`.`t0` SET `id`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `name`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `country`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `city`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `description`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END, `image`=CASE WHEN ROW(`id`,`name`)=ROW(?,?) THEN ? END WHERE ROW(`id`,`name`) IN (ROW(?,?))\"]"] [values="[[1,\"测试\",1,1,\"测试\",\"开发\",1,\"测试\",\"中国\",1,\"测试\",\"上海\",1,\"测试\",\"你好,世界\",1,\"测试\",\"xOO6w8rAvec=\",1,\"测试\"]]"

The behavior of this statement differs between TiDB and MySQL. It may cause the update statement of this type to not achieve the expected result when executed on MySQL. Ref: https://docs.pingcap.com/tidb/stable/sql-statement-update#mysql-compatibility However, all downstream integration tests of TiCDC currently use TiDB, so relevant issues were not previously detected.

This will be fixed by: #8452