Mysql Checksum Error

CCBRONET,

The metadata row may be slightly different. Here is a good explanation.
http://www.bluegecko.net/mysql/using-checksums-to-ensure-table-consistency-in-mysql/
Note how both tables have same content 'hello world' but yield different CHECKSUM. If you were to say take CRC32() of each row and sum that, then the two tables are likely to come out the same. The difference is likely the CRC32() of the metadata row.

You may find this useful:
http://www.xaprb.com/blog/2007/01/25/how-to-calculate-table-checksums-in-mysql/ 
and follow:
http://www.xaprb.com/blog/2007/02/26/introducing-mysql-table-checksum/
The Perl solution of original article is identified as being at the source forge project linked in the second. Might be worth reading up on and trying given you are on SuSE and should have Perl installed.

Kevin

The Metadata are same and both tables are innodb engine...

if i truncate the table in slave and reload the data from master the checksum are similar.

Hmm. That is interesting. So sounds like a hiccup in the hashing of CHECKSUM. It is not guaranteed to not have collisions, but I thought it was at least guaranteed to give exact results, i.e., be deterministic. Re-reading the manual after I started to wonder about my previous statement, I was reminded of the point regarding format changes. Is it possible that the servers are on different versions or something in the table changed for some reason ...

Both Master Slave are installed by me...
having same OS SLES 11 SP1 x64
Mysql 5.1.50 64bit
Same Struc and strictly Innodb only.

Slave is readonly. So no chance of any modification in data.

Hmm. And it is only the one table, correct? Is it always the same table after some period of time? If so, does the table have an index? What data type is it?

Also i tried scraping the slave db and reinstalled with the proper master position.. After few days the checksum again differs only for that table.

yes the table has index for performance.
data type is INT, Decimal and VARCHAR.



Its just weird that it happens only for this table...

Might be a bug with combination as CHECKSUM will look for a suitable index from my understanding, but maybe not. Another thought from the same article above which I meant to ask is with the slave being readonly, have you also ensured to LOCK the master table before comparing checksums?

yes Checksum was done when no master activity was der.
Also cross checked if master and slave were on the same position when checksum was taken.

Bug... i cant say that coz if i reload the data for that table from master it shows proper checksum....
Also after resync that table It shows proper checksum for few days.

Only thing that makes me skeptical is the top MySQL experts all seem to have written their own. Makes me feel less warm and fuzzy with the standard CHECKSUM. However, I don't have anything concrete as of yet to explain what is failing you here.

d