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.