sync-diff-inspector: output binary column in hex form (x'ABCDEF')

[mzhang77]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   In mysql:

create table tb(a int, b binary(16) primary key);
insert into tb(a,b) values(1,0xBF500C00A2034521B819D6EB7065D200);

In tidb:

create table tb(a int, b binary(16) primary key);
insert into tb(a,b) values(2,0xBF500C00A2034521B819D6EB7065D200);

Now run sync_diff_inspector.

2. What did you expect to see?

REPLACE INTO `test`.`tb`(`a`,`b`) VALUES (1,0xBF500C00A2034521B819D6EB7065D200);

3. What did you see instead?

$ cat test\:tb\:0\:0-0\:0.sql 
-- table: test.tb
-- range in sequence: Full
/*
  DIFF COLUMNS ╏ `A`  
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍
  source data  ╏ 1    
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍
  target data  ╏ 2    
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍
*/
REPLACE INTO `test`.`tb`(`a`,`b`) VALUES (1,'?P
                                               ?E!???pe?');

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   NA
5. which tool are you using?
   NA
6. what versionof tool are you using (pump -V or tidb-lightning -V or syncer -V)?

$ sync_diff_inspector -V
App Name: sync_diff_inspector v2.0
Release Version: v6.5.3
Git Commit Hash: 89b804103994cd63273fa9e57e71f270b1a42fcc
Git Branch: heads/refs/tags/v6.5.3
UTC Build Time: 2023-06-05 07:19:58
Go Version: go1.20.3

[D3Hunter]

the fix sql is correct, but not human friendly, to run the sql in mysql-client, must add --binary-mode

[kennytm]

I'm reopening this as an enhancement request. If a column is known to be varbinary or blob, sync-diff-inspector should be able to hex-encode the value.