[Bug] insert into ignore data lost

[yuxuan-luo]

Search before asking

• I had searched in the issues and found no similar issues.

Version

master

What's Wrong?

使用 ignore 功能时数据丢失

What You Expected?

doris:
CREATE TABLE stream_load_csv (
id int(11) NULL,
name varchar(10) NULL,
age int(11) NULL DEFAULT "20",
city varchar(10) NOT NULL DEFAULT "beijing",
balance DECIMAL NULL
) ENGINE=OLAP
UNIQUE KEY(id)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

mysql:
CREATE TABLE stream (
id int NOT NULL,
name varchar(255) DEFAULT '',
age int DEFAULT NULL,
city varchar(255) DEFAULT '',
balance int DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

flink sql:
CREATE TABLE doris_sink (
id int ,
name STRING ,
age int ,
city STRING,
balance int
)
WITH (
'connector' = 'doris_test',
'fenodes' = '10.0.3xxx:8030',
'table.identifier' = 'hxxxx.xxxx',
'username' = 'xxx',
'password' = 'xxx',
'sink.enable-delete' = 'true',
'sink.label-prefix' = 'doris_label_v2',
'sink.properties.ignore_mode' = 'true',
'sink.enable-2pc' = 'false'
);

CREATE TABLE cdc_mysql_source (
id int ,
name varchar ,
age int ,
city varchar,
balance int ,
PRIMARY KEY (id) NOT ENFORCED
) WITH (
'connector' = 'mysql-cdc',
'hostname' = '10xxxxx',
'port' = '3306',
'username' = 'xxxx',
'password' = 'xxxxx',
'database-name' = 'xxxx',
'table-name' = 'xxxx'
);

insert into doris_sink select * from cdc_mysql_source;

操作步骤：
一、先在 stream_load_csv 表中插入数据 insert into stream_load_csv values (1,"kevin",18,"shenzhen",400), (2,"bob",20,"beijing",500), (3,"alice",22,"shanghai",600), (4,"jack",24,"hangzhou",700), (5,"tom",26,"guanzhou",800);
二、在mysql表中插入数据并通过flink cdc 同步到 doris

三、用 stream load 导入数据

四、在mysql再插入数据

五、后续查询数据把数据丢失了：

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[JNSimba]

Will there be any problems if directly use curl streamload api to import the incremental data of mysql?

[yuxuan-luo]

Will there be any problems if directly use curl streamload api to import the incremental data of mysql?

yes，
1.insert into stream_load_csv values (1,"kevin",18,"shenzhen",400), (2,"bob",20,"beijing",500), (3,"alice",22,"shanghai",600), (4,"jack",24,"hangzhou",700), (5,"tom",26,"guanzhou",800);
2.curl --location-trusted -u admin -H "label:1a1waa2sdl1oad_local_file_test" -H "column_separator:," -H "ignore_mode:true" -T ./test1.csv http://127.0.0.1:8030/api/hugo/stream_load_csv/_stream_load
3.curl --location-trusted -u admin -H "label:1a1aa2sdl1oad_local_file_test" -H "column_separator:," -H "ignore_mode:true" -T ./test2.csv http://127.0.0.1:8030/api/hugo/stream_load_csv/_stream_load
4.curl --location-trusted -u admin -H "label:11aa2sdl1oad_local_file_test" -H "column_separator:," -H "ignore_mode:true" -T ./test_in.csv http://127.0.0.1:8030/api/hugo/stream_load_csv/_stream_load

test1.csv
1,,,,
10,,280,"shenzhen",1111
2,"bob",,"beijing",5000
20,"leo",300,,2222
30,"sam",320,"shanghai",
3,,,,6000
4,,,"hangzhou",
40,,340,,
5,"tom",,,
50,"cynthia",360,"guanzhou",8000

test2.csv
1,"kevin",180,"shenzhen",4000
10,"alex",280,"shenzhen",1111
2,"bob",200,"beijing",5000
20,"leo",300,"beijing",2222
30,"sam",320,"shanghai",3333
3,"alice",220,"shanghai",6000
4,"jack",240,"hangzhou",7000
40,"Ruth",340,"hangzhou",4444
5,"tom",260,"guanzhou",8000
50,"cynthia",360,"guanzhou",8000

test_in.csv
11,,,,
12,,280,"shenzhen",1111
123,"bob",,"beijing",5000
14,"leo",300,,2222
15,"sam",320,"shanghai",
16,,,,6000
17,,,"hangzhou",
18,,340,,
19,"tom",,,
201,"cynthia",360,"guanzhou",8000

期望结果：
+------+-----------+------+------------+---------+
| id | name | age | city | balance |
+------+-----------+------+------------+---------+
| 1 | kevin | 18 | shenzhen | 400 |
| 2 | bob | 20 | beijing | 500 |
| 3 | alice | 22 | shanghai | 600 |
| 4 | jack | 24 | hangzhou | 700 |
| 5 | tom | 26 | guanzhou | 800 |
| 10 | | 280 | "shenzhen" | 1111 |
| 11 | | NULL | | NULL |
| 12 | | 280 | "shenzhen" | 1111 |
| 14 | "leo" | 300 | | 2222 |
| 15 | "sam" | 320 | "shanghai" | NULL |
| 16 | | NULL | | 6000 |
| 17 | | NULL | "hangzhou" | NULL |
| 18 | | 340 | | NULL |
| 19 | "tom" | NULL | | NULL |
| 20 | "leo" | 300 | | 2222 |
| 30 | "sam" | 320 | "shanghai" | NULL |
| 40 | | 340 | | NULL |
| 50 | "cynthia" | 360 | "guanzhou" | 8000 |
| 123 | "bob" | NULL | "beijing" | 5000 |
| 201 | "cynthia" | 360 | "guanzhou" | 8000 |
+------+-----------+------+------------+---------+

实际结果：
+------+-----------+------+------------+---------+
| id | name | age | city | balance |
+------+-----------+------+------------+---------+
| 11 | | NULL | | NULL |
| 12 | | 280 | "shenzhen" | 1111 |
| 14 | "leo" | 300 | | 2222 |
| 15 | "sam" | 320 | "shanghai" | NULL |
| 16 | | NULL | | 6000 |
| 17 | | NULL | "hangzhou" | NULL |
| 18 | | 340 | | NULL |
| 19 | "tom" | NULL | | NULL |
| 123 | "bob" | NULL | "beijing" | 5000 |
| 201 | "cynthia" | 360 | "guanzhou" | 8000 |
+------+-----------+------+------------+---------+

[bobhan1]

@yuxuan-luo 我本地测的没问题，ignore mode的含义是如果插入行的key在原表中存在，则不插入这一行，test2.csv中插入行的key在原表中均存在，所以test2.csv的内容都不会插入，期望结果应该是

+------+-----------+------+------------+---------+
| id   | name      | age  | city       | balance |
+------+-----------+------+------------+---------+
|    1 |           | NULL |            |    NULL |
|    2 | "bob"     | NULL | "beijing"  |    5000 |
|    3 |           | NULL |            |    6000 |
|    4 |           | NULL | "hangzhou" |    NULL |
|    5 | "tom"     | NULL |            |    NULL |
|   10 |           |  280 | "shenzhen" |    1111 |
|   20 | "leo"     |  300 |            |    2222 |
|   30 | "sam"     |  320 | "shanghai" |    NULL |
|   40 |           |  340 |            |    NULL |
|   50 | "cynthia" |  360 | "guanzhou" |    8000 |
|   11 |           | NULL |            |    NULL |
|   12 |           |  280 | "shenzhen" |    1111 |
|   14 | "leo"     |  300 |            |    2222 |
|   15 | "sam"     |  320 | "shanghai" |    NULL |
|   16 |           | NULL |            |    6000 |
|   17 |           | NULL | "hangzhou" |    NULL |
|   18 |           |  340 |            |    NULL |
|   19 | "tom"     | NULL |            |    NULL |
|  123 | "bob"     | NULL | "beijing"  |    5000 |
|  201 | "cynthia" |  360 | "guanzhou" |    8000 |
+------+-----------+------+------------+---------+

测试过程：
建表

CREATE TABLE t1 (
id int(11) NULL,
name varchar(10) NULL,
age int(11) NULL DEFAULT "20",
city varchar(10) NOT NULL DEFAULT "beijing",
balance DECIMAL NULL
) ENGINE=OLAP
UNIQUE KEY(id)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

第一次导入数据

curl --location-trusted -u root: -H "column_separator:," -H "ignore_mode:true" -T ./test1.csv http://127.0.0.1:8134/api/demo/t1/_stream_load

查询

mysql> select * from t1;
+------+-----------+------+------------+---------+
| id   | name      | age  | city       | balance |
+------+-----------+------+------------+---------+
|    1 |           | NULL |            |    NULL |
|    2 | "bob"     | NULL | "beijing"  |    5000 |
|    3 |           | NULL |            |    6000 |
|    4 |           | NULL | "hangzhou" |    NULL |
|    5 | "tom"     | NULL |            |    NULL |
|   10 |           |  280 | "shenzhen" |    1111 |
|   20 | "leo"     |  300 |            |    2222 |
|   30 | "sam"     |  320 | "shanghai" |    NULL |
|   40 |           |  340 |            |    NULL |
|   50 | "cynthia" |  360 | "guanzhou" |    8000 |
+------+-----------+------+------------+---------+

第二次导入数据

curl --location-trusted -u root: -H "column_separator:," -H "ignore_mode:true" -T ./test2.csv http://127.0.0.1:8134/api/demo/t1/_stream_load

查询

mysql> select * from t1;
+------+-----------+------+------------+---------+
| id   | name      | age  | city       | balance |
+------+-----------+------+------------+---------+
|    1 |           | NULL |            |    NULL |
|    2 | "bob"     | NULL | "beijing"  |    5000 |
|    3 |           | NULL |            |    6000 |
|    4 |           | NULL | "hangzhou" |    NULL |
|    5 | "tom"     | NULL |            |    NULL |
|   10 |           |  280 | "shenzhen" |    1111 |
|   20 | "leo"     |  300 |            |    2222 |
|   30 | "sam"     |  320 | "shanghai" |    NULL |
|   40 |           |  340 |            |    NULL |
|   50 | "cynthia" |  360 | "guanzhou" |    8000 |
+------+-----------+------+------------+---------+

第三次导入数据

curl --location-trusted -u root: -H "column_separator:," -H "ignore_mode:true" -T ./test_in.csv http://127.0.0.1:8134/api/demo/t1/_stream_load

查询

mysql> select * from t1;
+------+-----------+------+------------+---------+
| id   | name      | age  | city       | balance |
+------+-----------+------+------------+---------+
|    1 |           | NULL |            |    NULL |
|    2 | "bob"     | NULL | "beijing"  |    5000 |
|    3 |           | NULL |            |    6000 |
|    4 |           | NULL | "hangzhou" |    NULL |
|    5 | "tom"     | NULL |            |    NULL |
|   10 |           |  280 | "shenzhen" |    1111 |
|   20 | "leo"     |  300 |            |    2222 |
|   30 | "sam"     |  320 | "shanghai" |    NULL |
|   40 |           |  340 |            |    NULL |
|   50 | "cynthia" |  360 | "guanzhou" |    8000 |
|   11 |           | NULL |            |    NULL |
|   12 |           |  280 | "shenzhen" |    1111 |
|   14 | "leo"     |  300 |            |    2222 |
|   15 | "sam"     |  320 | "shanghai" |    NULL |
|   16 |           | NULL |            |    6000 |
|   17 |           | NULL | "hangzhou" |    NULL |
|   18 |           |  340 |            |    NULL |
|   19 | "tom"     | NULL |            |    NULL |
|  123 | "bob"     | NULL | "beijing"  |    5000 |
|  201 | "cynthia" |  360 | "guanzhou" |    8000 |
+------+-----------+------+------------+---------+

[github-actions]

We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!