MySQL Q & amp; A about checksum table

Source: Internet
Author: User

MySQL Q & A talked about whether checksum table can be used to verify data consistency before and after the logical backup. Some interesting problems are found, such as different data insertion sequence, different table engines, and different operating system bits. Whether the insert order of varies. We know that the full table scan can have many orders. Especially when the delete action appears in the table, the logical export operation is performed and then the other table is imported, the full table scan results of the two tables may be different. The logic for calculating the return value of Checksum table is roughly as follows: Cpp Code ha_checksum crc = 0; foreach (row in table) {row_crc = get_crc (row); crc + = row_crc;} return crc; it can be seen that as long as the total number of rows has the same content, it is irrelevant to the order of reading rows. From this logic, we can also draw several conclusions: 1) has nothing to do with the engine used, that is, even if the master and slave nodes do not use the same engine, checksum can also be used for inspection. Although InnoDB has hidden rows, ignore them here. 2) It is irrelevant to whether an index exists. Row_crc is calculated only based on the row data, excluding the index data. That is to say, if the data in the two tables is the same, the table structure (the column content and sequence are the same), the operating system is the same, and the MySQL version is the same, the checksum result can be ensured. In this section, we will discuss whether the order of the "different" fields varies. When row_crc is calculated in a row, each field is calculated in sequence. However, the result of the previous field is used as the input to calculate the next value. Switch (f-> type () {case MYSQL_TYPE_BLOB: case MYSQL_TYPE_VARCHAR: case MYSQL_TYPE_GEOMETRY: case MYSQL_TYPE_BIT: {String tmp; f-> val_str (& tmp ); row_crc = my_checksum (row_crc, (uchar *) tmp. ptr (), tmp. length (); break;} default: row_crc = my_checksum (row_crc, f-> ptr, f-> pack_length (); break;} the field order affects the result. Whether the length of a field varies. Even if the same content is displayed, different checksum values may be obtained. From the above calculation of the crc of each field, if it is a variable-length field (such as varchar), it is not affected because it is used to calculate the actual length. For example, changing the varchar (20) field of a table to varchar (25) does not change the value of checksum. If you change char (20) to char (25) or int to bigint, the checksum is changed. The number of digits in the operating system is different because the returned value is unsigned long, so we are worried about the overflow of 32-bit and 64-bit machines. Fortunately, in the calculation process, ha_myisam is directly defined as uint32, and is converted to unsigned long only when it is returned, so there is no impact. The problem of different character sets has always been vague. It is actually related to the input character set. However, there is a positive conclusion: If the unhex () value of the fields in the table is the same, the obtained checksum is the same.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.