Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that's showing some differences on a master and slave according to pt-table-checksum (PTC). I've found some verifying differences beyond that.

Certain tables in the mix have composite primary keys which, while work well enough for PTC do not so much with pt-table-sync (PTS) to try and find the differences. PTS does not seem to honor the newer options of PTC to restrict the depth of composite key searches. End result is the a 5.5Mrow table is left spinning for hours w/ PTS. I'm somewhat convinced there's an infinite loop bug in PTS as the output has just started spitting out the a correction for the same exact record (and nothing else at this point) over and over.

So, really this post is more about alternatives than trying to get this tool to run right.

The most straight forward alternative that comes to mind is flush table with read lock on the master; select * into outfile on master and slave; unlock and diff. However this table is fairly active and I cannot afford it being locked out on the master that long.

I was hoping to do something like start a transaction in repeatable read isolation level and select out from the master there. However I can't find a way to have the slave be stopped specifically at the point in the transaction history as on the master. Things like show master status continue to update after starting a transaction so I can't simply start slave until then.

There's no guarantees about atomicity to "quickly" do "show master status; begin;"

The only other solution of a non blocking method is to write a custom script that might be able to more efficiently do a PTS type thing but with application specific domain knowledge about how the columns of the table in question are generated.

While it might come to that I was hoping to find a more generalized solution for the future when prebuild wheels like PTS turn out not as well rounded as I'd like.

share|improve this question
add comment

2 Answers

There is a reasonable explanation.

There are occasions where VARCHAR storage can sometime produces inconsistent CHECKSUMs.

I have discussed this before your post and after your post

The only thing you can do is one of the following

  • SUGGESTION #1: Reload the offending table using mysqldump
  • SUGGESTION #2: Do a rowcount on Master and Slave (hope they match)

I'd go with SUGGESTION #1

share|improve this answer
add comment

There are actually some well documented bug in syncing while this involve one or more slave.

In some situation, doing heavy alter table, involving foreign keys changing and /or primary key data format, all of master will be synced but NOT the slaves.

Actually there is no "why?" answer.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.