-
Notifications
You must be signed in to change notification settings - Fork 5.8k
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.
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
Feature request: Support CHECKSUM TABLE command #1895
Comments
@sjmudd But we can't use the same algorithm to calculate the checksum value because we store data in a different format than MySQL, so the checksum value for the table imported from MySQL to TiDB will be different. |
I stumbled across
The current workaround to compare data between TiDB and MySQL data sources is to use
I will leave this request open as a request for a MySQL-compatible |
Close this issue since the feature is supported. |
There are two challenges to implement a MySQL-Compatible
/cc @kennytm, @IANTHEREAL |
From MySQL document about
|
I don't see how Lightning or
|
We need to investigate the user scenario in depth, and then make a decision after the research is completed. |
As @kennytm notes, the use-case is not for lightning, but could be used for DM. Because of SQL modes, timezones and character sets logical restores could corrupt data. This is true even if TiDB is ostensibly behaving correctly (i.e. it could be misconfiguration). MySQL users often use it during upgrades (MySQL <-> MySQL), for example logically dumping a 5.7 system and then restoring on 8.0 and comparing the data. I have found problematic behavior changes in MySQL this way. I can't find the bug now, but I think it was in 5.0 it started storing negative infinity values differently. The feature request here is to implement the same algorithm as MySQL so it can be used for a MySQL <-> TiDB upgrade. That is, it could either be used by DM or a manual inspection by the DBA, who is concerned about such logical corruptions. |
This is a different type of checksum (page checksums on the physical format). It is not related to |
In Sync-diff-inspector, we have a custom checksum command based on: SELECT bit_xor(
CAST(crc32(
concat_ws(',',
col1, col2, col3, …, colN,
concat(isnull(col1), isnull(col2), …, isnull(colN))
)
) AS UNSIGNED)
)
FROM t; (for sure this is not a general solution because the |
When importing data into TiDB we want to be sure the data is correct. A convenient command in MySQL is CHECKSUM TABLE and this command is not currently supported by TiDB.
Adding it would make it easy to confirm if the data loaded in from an external source matches or not.
Example I did something like:
$ ssh mysqlhost sudo mysqldump test SomeTable | mysql -h tidbhost -P 4000 -u root -D test
MySQL 5.6:
TiDB:
So providing some sort of CHECKSUM table command would be great.
The text was updated successfully, but these errors were encountered: