Table data check fails if export-fix-sql is disabled for tables with complex json

[dveeden]

Bug Report

CREATE TABLE `t1` (
  `id` int NOT NULL,
  `j` json DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO t1 VALUES (1, '{"aaa": 1, "aa": 1, "bb": 1}');

With export-fix-sql = false this would result in this:

$ ./bin/sync_diff_inspector -C sd.toml -L debug
A total of 1 tables need to be compared

Comparing the table structure of `test`.`t1` ... equivalent
Comparing the table data of `test`.`t1` ... failure
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
The data of `test`.`t1` is not equal

The rest of tables are all equal.

A total of 1 tables have been compared, 0 tables finished, 1 tables failed, 0 tables skipped.
The patch file has been generated in 
	'output/fix-on-tidb0/'
You can view the comparison details through './output/sync_diff.log'

With export-fix-sql = true this would result in this:

$ ./bin/sync_diff_inspector -C sd.toml -L debug
A total of 1 tables need to be compared

Comparing the table structure of `test`.`t1` ... equivalent
Comparing the table data of `test`.`t1` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A total of 1 table have been compared and all are equal.
You can view the comparison details through './output/sync_diff.log'

What happens here is this:

• compareChecksumAndGetCount() would return isEqual=false as the checksum doesn't match due to representation differences for the JSON type.
• Then if !isEqual && df.exportFixSQL { would only call compareRows() if exportFixSQL is true.

So when export-fix-sql is set to true it will check row data if the checksum doesn't match, but this isn't done if this setting is set to false.

[dveeden]

Code:

• tidb-tools/sync_diff_inspector/diff.go

  Line 440 in 24aedc3

  } else if !isEqual && df.exportFixSQL {

• tidb-tools/sync_diff_inspector/diff.go

  Line 460 in 24aedc3

  isEqual = isDataEqual

Related: #829

[dveeden]

This is with master @ 24aedc3

[dveeden]

For fixes:

Option 1:

• Always call compareRows() if the checksum doesn't match, check for exportFixSQL only when writing out fix files to disk

Option 2:

• Improve logging and output to make it clear to the user that with export-fix-sql=false it will only compare the checksum and not the data when the checksum fails.

[dveeden]

For JSON support: #674