sync-diff-inspector should not use REPLACE in its fix-sql if ignore-columns is used

[kennytm]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

1. Prepare data

drop schema if exists up;
drop schema if exists down;
create schema up;
create schema down;

drop user if exists up;
drop user if exists down;
create user up;
create user down;
grant select on up.* to up;
grant all on down.* to down;

create table up.test (id bigint primary key, d double, a int);
create table down.test like up.test;
insert into up.test values (1, 5, 4);
insert into down.test values (1, 2, 3);

2. Set diff config

export-fix-sql = true

[data-sources.up]
host = "127.0.0.1"
port = 4000
user = "up"
password = ""
route-rules = ["rename"]

[data-sources.down]
host = "127.0.0.1"
port = 4000
user = "down"
password = ""

[routes]
[routes.rename]
schema-pattern = "up"
table-pattern = "test"
target-schema = "down"
target-table = "test"

[task]
output-dir = "./output"
source-instances = ["up"]
target-instance = "down"
target-check-tables = ["down.test"]
target-configs = ["ignore-columns"]

[table-configs]
[table-configs.ignore-columns]
target-tables = ["down.test"]
ignore-columns = ["d"]

3. Run sync-diff. It should say "The data of `down`.`test` is not equal".
4. Execute the fix-sql

mysql -u down -h 127.0.0.1 -P 4000 < ./output/fix-on-down/*.sql

5. Check the fixed result

select * from down.test;

2. What did you expect to see?

The value of d is either unchanged (2) or becomes the upstream value (5).

3. What did you see instead?

The value of d got replaced as the default value (NULL):

mysql> select * from down.test;
+----+------+------+
| id | d    | a    |
+----+------+------+
|  1 | NULL |    4 |
+----+------+------+
1 row in set (0.00 sec)

[kennytm]

Note that the fix-sql generated by sync-diff uses the REPLACE statement, so the current behavior is arguably "expected" (as REPLACE = DELETE + INSERT)

-- table: down.test
-- range in sequence: Full
/*
  DIFF COLUMNS ╏ `A`  
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍
  source data  ╏ 4    
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍
  target data  ╏ 3    
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍
*/
REPLACE INTO `down`.`test`(`id`,`a`) VALUES (1,4);

However, when we use ignore-columns in sync-diff-inspector, it is because diff cannot actually handle those data types (json, bit, blob, etc). So the behavior is not useful for users.

Instead of REPLACE, the generated fix-sql should either use INSERT INTO ON DUPLICATE to keep the original ignored column:

insert into test (id, a) values (1, 4) on duplicate key update id = values(id), a = values(a);

or just generate the full row to replace everything entirely (may be impossible when sync-diff just don't know how to serialize the column):

replace into test (id, d, a) values (1, 5, 4);