Solved

Mysql Checksum Error

Posted on 2011-09-02
12
471 Views
Last Modified: 2012-05-12
Hi,
Im using Mysql replication with 1 master and 1 slave.
To have a check on my data consistency i do a checksum table on both the database and compare them.
The problem is i always get different checksum on only 1 table out of 400.
When i do a sum on each column of that table and compare, it comes similar, also the count is similar.
Is this mysql bug or some other issue.

im using Mysql 5.1.50 SLES 11 SP1 64bit

Please help.
0
Comment
Question by:CCBRONET
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36473412
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
0
 

Author Comment

by:CCBRONET
ID: 36474502
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36474539
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 ...
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:CCBRONET
ID: 36474571
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36474580
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?
0
 

Author Comment

by:CCBRONET
ID: 36474582
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.
0
 

Author Comment

by:CCBRONET
ID: 36474600
yes the table has index for performance.
data type is INT, Decimal and VARCHAR.



Its just weird that it happens only for this table...
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36474645
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?
0
 

Author Comment

by:CCBRONET
ID: 36475099
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.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36475239
Well something is not replicating correctly. You have a DECIMAL and VARCHAR involved. Guess it is possible that a precision is being lost some how or extraneous white space in VARCHAR is occurring on certain transactions...just doesn't sound right.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36475400
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.
0
 

Author Closing Comment

by:CCBRONET
ID: 36565565
d
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 60
Uploading a CSV Data Import via PHP & MySql 3 58
check mysql insert 12 43
MySQL limit and not so limited 13 41
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question