pt-table-checksum showing checksum difference

MySQL & MariaDB Other MySQL® Questions
1

SUSE 11 Enterprise
MySQL 5.5.8
pt-table-checksum 1.0.1

I have MASTER-SLAVE replication set up.
pt-table-checksum consistently returns different checksums for 2 tables. When I do a mysqldump on the tables and the do a DIFF on the dump files, there are no differences.

I’ve also refreshed the slave 2 times and restarted replication, but the tables still show different checksums for those tables.

Below are the fields from one of the tables.

search_index | CREATE TABLE search_index (
word varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘The search_total.word that is associated with the search item.’,
sid int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘The search_dataset.sid of the searchable item to which the word belongs.’,
type varchar(16) NOT NULL COMMENT ‘The search_dataset.type of the searchable item to which the word belongs.’,
score float DEFAULT NULL COMMENT ‘The numeric score of the word, higher being more important.’,
PRIMARY KEY (word,sid,type),
KEY sid_type (sid,type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Any ideas what might be going on?

Thanks!

2

I changed to “–algorithm=BIT_XOR” and now the checksums match.

Any idea what’s causing this?

Thanks!

3

Sometimes there is a difference in floating-point values at precisions beyond what mysqldump will output. I don’t know why changing to BIT_XOR would change the results. I would suggest using small chunks and isolating a specific chunk, then selecting the rows from that chunk on both servers, using ROUND() if needed to make float/double columns show a lot of digits of precision (20 digits, perhaps).