Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

delete and update events may not be correctly sinked to downstream #8420

Closed
zhaoxinyu opened this issue Mar 2, 2023 · 8 comments · Fixed by #8421 or #8452
Closed

delete and update events may not be correctly sinked to downstream #8420

zhaoxinyu opened this issue Mar 2, 2023 · 8 comments · Fixed by #8421 or #8452
Labels
affects-6.1 This bug affects the 6.1.x(LTS) versions. affects-6.5 This bug affects the 6.5.x(LTS) versions. area/ticdc Issues or PRs related to TiCDC. severity/critical type/bug The issue is confirmed as a bug.

Comments

@zhaoxinyu
Copy link
Contributor

zhaoxinyu commented Mar 2, 2023

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
  1. 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 zhaoxinyu added type/bug The issue is confirmed as a bug. area/ticdc Issues or PRs related to TiCDC. labels Mar 2, 2023
@zhaoxinyu zhaoxinyu changed the title deleting rows may not be correctly synchronized to downstream delete events may not be correctly synchronized to downstream Mar 2, 2023
@zhaoxinyu zhaoxinyu changed the title delete events may not be correctly synchronized to downstream delete events may not be correctly sinked to downstream Mar 2, 2023
@zhaoxinyu
Copy link
Contributor Author

/remove-label severity/moderate

@ti-chi-bot
Copy link
Member

@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
Copy link
Contributor Author

/remove-severity moderate

@zhaoxinyu zhaoxinyu added the affects-6.5 This bug affects the 6.5.x(LTS) versions. label Mar 6, 2023
@zhaoxinyu zhaoxinyu added the affects-6.1 This bug affects the 6.1.x(LTS) versions. label Mar 6, 2023
@zhaoxinyu zhaoxinyu changed the title delete events may not be correctly sinked to downstream delete and update events may not be correctly sinked to downstream Mar 6, 2023
@zhaoxinyu
Copy link
Contributor Author

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

@zhaoxinyu
Copy link
Contributor Author

/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
Copy link
Contributor Author

zhaoxinyu commented Mar 6, 2023

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 = '测试';
  1. 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
Copy link
Contributor

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
Copy link
Contributor

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
affects-6.1 This bug affects the 6.1.x(LTS) versions. affects-6.5 This bug affects the 6.5.x(LTS) versions. area/ticdc Issues or PRs related to TiCDC. severity/critical type/bug The issue is confirmed as a bug.
Projects
None yet
3 participants