Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
dveeden opened this issue Nov 26, 2024 · 4 comments · May be fixed by #831
Open

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

dveeden opened this issue Nov 26, 2024 · 4 comments · May be fixed by #831
Labels
severity/critical type/bug

Comments

@dveeden
Copy link
Contributor

dveeden commented Nov 26, 2024

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
Copy link
Contributor Author

dveeden commented Nov 26, 2024

Code:

Related: #829

@dveeden
Copy link
Contributor Author

dveeden commented Nov 26, 2024

This is with master @ 24aedc3

@dveeden
Copy link
Contributor Author

dveeden commented Nov 26, 2024

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
Copy link
Contributor Author

dveeden commented Nov 26, 2024

For JSON support: #674

This was referenced Nov 27, 2024
Open
Draft
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
severity/critical type/bug
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sync_diff_inspector: Compare rows even if export-fix-sql is false dveeden/tidb-tools
1 participant
@dveeden