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 several tables being reported by pt-table-checksum with CRC_DIFF between my master and slave servers using the following commands:

$ pt-table-checksum h=master,u=user,p=password --empty-replicate-table --databases db --replicate systemadministration.checksums
$ pt-table-checksum h=master,u=user,p=password --databases db --replicate systemadministration.checksums --replicate-check 1

Results in:

Differences on P=3306,h=slave
DB         TBL                         CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
db         table1                          0        0        1 1=1
db         table2                          0        0        1 1=1
db         table3                          0        0        1 1=1
db         table4                          0        0        1 1=1
db         table5                          0        0        1 1=1
db         table6                          0        0        1 1=1
db         table7                          0        0        1 1=1

However, when I run pt-table-sync, the script returns an exit code 0 and says that there are no issues.

$ pt-table-sync --execute --verbose --no-bin-log --tables db.table1 h=master,u=user,p=password h=slave
# Syncing h=slave,p=...,u=user
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 GroupBy   14:10:45 14:12:12 0    db.table1
$ echo $?
0

I've tried the different algorithms for the checksum command and have had no luck.

$ pt-table-checksum h=master,u=user,p=password --empty-replicate-table --algorithm=ACCUM --tables db.table1 --replicate systemadministration.checksums
DATABASE   TABLE                       CHUNK HOST                   ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
db         table1                          0 master                 MyISAM     141836 00141836D0139746   22 NULL NULL NULL
$ pt-table-checksum h=master,u=user,p=password --tables db.table1 --replicate systemadministration.checksums --replicate-check 1
Differences on P=3306,h=slave
DB         TBL                         CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
db         table1                          0        0        1 1=1
$ echo $?
1

Any hints or is there any other tools I can use to verify the data integrity?

share|improve this question
    
There is an improved version of pt-table-sync coming soon which is currently available in the trunk release of Percona toolkit that may work better for you. –  Aaron Brown Dec 6 '11 at 14:25
    
@AaronBrown I looked at the Percona Launchpad and did not find anything newer? Would you be able to point me out to where you're seeing a new version? I looked here: bazaar.launchpad.net/~percona-toolkit-dev/percona-toolkit/2.0/… –  stanleykylee Dec 6 '11 at 22:44
    
Sorry, it's pt-table-checksum that has changed, not pt-table-sync. My mistake. –  Aaron Brown Dec 7 '11 at 2:22
    
@Rolando I tried your scripts out, but getting a "ls: *Repair.txt: No such file or directory" have you seen this error on your script before ? –  user6507 Feb 8 '12 at 3:47
    
Actually, that's error comes up all the time WHEN THERE IS NOTHING TO REPAIR. That's a good thing. –  RolandoMySQLDBA Feb 8 '12 at 17:18
show 1 more comment

2 Answers

The usual cause for this problem is that pt-table-sync is using CRC32 as a hash algorithm, because it's fast and cheap, but it can also give collisions. "codding" and "gnu" have the same crc32, for example. I recommend trying again with MD5 as the function.

share|improve this answer
    
changing the function didn't seem to work. ended up deleting all rows from the tables and then ran pt-table-sync to fill in the tables. checksum and sync returned fine after that. –  stanleykylee Dec 14 '11 at 1:03
add comment

Here is my personal observation:

I have never trusted running mk-table-sync with --execute. I have had past problems with it. I always use --print instead. That way, the SQL for the changes to run on the Slave is printed. I just redirect to a file and look it over.

I have recommended using --print option in an earlier post.

I have also recommended MAATKT in times past

I have an idea

I will post my scripts that I use to automate mk-table-checksum and mk-table-sync

First, create the folder /root/tcs

Next, create the file /root/tcs/TCS.sh

HOSTADDR_MASTER=10.1.1.104
HOSTADDR__SLAVE=10.1.1.105
cd /root/tcs
./TCSExec.sh ${HOSTADDR_MASTER} ${HOSTADDR__SLAVE}

Please note that HOSTADDR_MASTER and HOSTADDR__SLAVE must be different

Next, create /root/tcs/TCSExec.sh

if [ "${1}" == "" ]
then
        exit
fi
if [ "${2}" == "" ]
then
        exit
fi

HOST_MASTER=${1}
HOST__SLAVE=${2}

MYSQL=`which mysql`
TC=/usr/local/sbin/mk-table-checksum
TS=/usr/local/sbin/mk-table-sync
MYSQL_USER=mysqluser
MYSQL_PASS=mysqlpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
TC_HOSTS="${HOST_MASTER} ${HOST__SLAVE}"

TC_OUTPUT=TableChecksumOutput.txt
TC_PARSER=TableChecksumParser.txt
TC_TBLIST=TableChecksumTableList.txt
TC_REPTMP=TableChecksumRepairTemp.txt
TC_RUNLOG=TableChecksumRunLog.txt

rm -f ${TC_OUTPUT}
rm -f ${TC_PARSER}
rm -f ${TC_TBLIST}
rm -f ${TC_REPTMP}
rm -f ${TC_RUNLOG}

DTSTAMP=`date +"%Y-%m-%d %H:%M:%S"`
echo "${DTSTAMP} : Initiated Analysis" >> ${TC_RUNLOG}

#
#       Gat Hostname Through MySQL
#
HOSTNAME_MASTER=`${MYSQL} ${MYSQL_CONN} -h${HOST_MASTER} --skip-column-names -A -e"SHOW VARIABLES LIKE 'hostname'" | awk '{print $2}'`
HOSTNAME__SLAVE=`${MYSQL} ${MYSQL_CONN} -h${HOST__SLAVE} --skip-column-names -A -e"SHOW VARIABLES LIKE 'hostname'" | awk '{print $2}'`

#
#       Gather All Databases
#

DATABASES_TO_CHECKSUM=""
SPC=""
for DB in `${MYSQL} ${MYSQL_CONN} --skip-column-names -A -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')"`
do
        DATABASES_TO_CHECKSUM="${DATABASES_TO_CHECKSUM}${SPC}${DB}"
        SPC=" "
done

#
#       Run mk-table-checksum on every table in each database
#       Report on tables that have different checksums on master and slave
#

for DB in `echo "${DATABASES_TO_CHECKSUM}"`
do
        TC_REPORT=${DB}_Report.txt
        TC_REPAIR=${DB}_Repair.txt
        rm -f ${TC_REPORT}
        rm -f ${TC_REPAIR}
        ${TC} ${MYSQL_CONN} --databases ${DB} ${TC_HOSTS} > ${TC_OUTPUT}
        LINES_TO_OUTPUT=`cat ${TC_OUTPUT} | wc -l`
        (( LINES_TO_OUTPUT-- ))
        tail -${LINES_TO_OUTPUT} ${TC_OUTPUT} | awk '{ print $2,$7 }' | sort -u > ${TC_PARSER}
        cat ${TC_PARSER} | awk '{ print $1 }' | sort -u > ${TC_TBLIST}
        CHECKSUM_FAILURES=0
        for TB in `cat ${TC_TBLIST}`
        do
                UNIQUE_LINES=`grep "^${TB} " ${TC_PARSER} | sort -u | wc -l`
                if [ ${UNIQUE_LINES} -gt 1 ]
                then
                        grep "^${TB} " ${TC_PARSER} >> ${TC_REPORT}
                        (( CHECKSUM_FAILURES++ ))
                fi
        done
        if [ ${CHECKSUM_FAILURES} -gt 1 ]
        then
                cat ${TC_REPORT} | awk '{ print $1 }' | sort -u > ${TC_REPTMP}
                for TB in `cat ${TC_REPTMP}`
                do
                        echo "${DB} ${TB}" >> ${TC_REPAIR}
                done
                echo >> ${TC_REPORT}
                echo "Number of Tables Failing Checksum : ${CHECKSUM_FAILURES}" >> ${TC_REPORT}
        fi
done

DTSTAMP=`date +"%Y-%m-%d %H:%M:%S"`
echo "${DTSTAMP} : Completed Checksum" >> ${TC_RUNLOG}

#
#       Create table analysis report for each table
#

FILE_COUNT=0
FILES_TO_REPORT=""
SPC=""
for FIL in `ls *Repair.txt`
do
        (( FILE_COUNT++ ))
        FILES_TO_REPORT="${FILES_TO_REPORT}${SPC}${FIL}"
        SPC=" "
done
if [ ${FILE_COUNT} -gt 0 ]
then
        for FIL in `echo "${FILES_TO_REPORT}"`
        do
                DB=`echo ${FIL} | sed 's/\_/ /g' | awk '{print $1}'`
                TABLE_REPORT=${DB}_TableAnalysis.txt
                TC_REPORT=${DB}_Report.txt
                rm -f ${TABLE_REPORT}
                LINES_LEFT=`wc -l ${FIL} | awk '{print $1}'`
                while [ ${LINES_LEFT} -gt 0 ]
                do
                        DB=`tail -${LINES_LEFT} ${FIL} | head -1 | awk '{print $1}'`
                        TB=`tail -${LINES_LEFT} ${FIL} | head -1 | awk '{print $2}'`
                        CHKSUM_MASTER=`grep "^${TB} " ${TC_REPORT} | head -1 | awk '{print $2}'`
                        CHKSUM__SLAVE=`grep "^${TB} " ${TC_REPORT} | tail -1 | awk '{print $2}'`
                        ${MYSQL} ${MYSQL_CONN} -h${HOST_MASTER} --skip-column-names -A -e"SELECT CONCAT(RPAD('${HOST_MASTER}',16,' '),RPAD(engine,12,' '),RPAD(table_name,30,' '),LPAD(data_length,15,' '),LPAD(index_length,15,' '),LPAD(data_length+index_length,15,' '),LPAD('${CHKSUM_MASTER}',20,' ')) FROM information_schema.tables WHERE table_schema='${DB}' and table_name='${TB}'" >> ${TABLE_REPORT}
                        ${MYSQL} ${MYSQL_CONN} -h${HOST__SLAVE} --skip-column-names -A -e"SELECT CONCAT(RPAD('${HOST__SLAVE}',16,' '),RPAD(engine,12,' '),RPAD(table_name,30,' '),LPAD(data_length,15,' '),LPAD(index_length,15,' '),LPAD(data_length+index_length,15,' '),LPAD('${CHKSUM__SLAVE}',20,' ')) FROM information_schema.tables WHERE table_schema='${DB}' and table_name='${TB}'" >> ${TABLE_REPORT}
                        echo "----------------------------------------------------------------------------------------------------------------------------" >> ${TABLE_REPORT}
                        (( LINES_LEFT-- ))
                done
        done
fi

DTSTAMP=`date +"%Y-%m-%d %H:%M:%S"`
echo "${DTSTAMP} : Creating SQL Fixes" >> ${TC_RUNLOG}

rm -f *.sql
if [ ${FILE_COUNT} -gt 0 ]
then
        for FIL in `echo "${FILES_TO_REPORT}"`
        do
                DB=`echo ${FIL} | sed 's/\_/ /g' | awk '{print $1}'`
                LINES_LEFT=`wc -l ${FIL} | awk '{print $1}'`
                while [ ${LINES_LEFT} -gt 0 ]
                do
                        DB=`tail -${LINES_LEFT} ${FIL} | head -1 | awk '{print $1}'`
                        TB=`tail -${LINES_LEFT} ${FIL} | head -1 | awk '{print $2}'`
                        SYNC_SQL_1=Sync-From-${HOSTNAME_MASTER}-To-${HOSTNAME__SLAVE}-${DB}-${TB}.sql
                        SYNC_SQL_2=Sync-From-${HOSTNAME__SLAVE}-To-${HOSTNAME_MASTER}-${DB}-${TB}.sql
                        ${TS} --print --sync-to-master h=${HOST__SLAVE},u=lwdba,p=${MYSQL_PASS},D=${DB},t=${TB} > ${SYNC_SQL_1}
                        SQL_FILE_SIZE=`cat ${SYNC_SQL_1} | wc -c`
                        if [ ${SQL_FILE_SIZE} -eq 0 ]
                        then
                                rm -f ${SYNC_SQL_1}
                        else
                                DTSTAMP=`date +"%Y-%m-%d %H:%M:%S"`
                                echo "${DTSTAMP} : SQL Fix ${SYNC_SQL_1}" >> ${TC_RUNLOG}
                        fi
                        (( LINES_LEFT-- ))
                done
        done
fi



DTSTAMP=`date +"%Y-%m-%d %H:%M:%S"`
echo "${DTSTAMP} : Completed Analysis" >> ${TC_RUNLOG}

rm -f ${TC_OUTPUT}
rm -f ${TC_PARSER}
rm -f ${TC_TBLIST}
rm -f ${TC_REPTMP}

Just call /root/tcs/TCS.sh and it will call /root/tcs/TCSExec.sh.

Make sure of the following:

  • TCS.sh has the correct IP Addresses for Master and Slave
  • TCSExec.sh has the correct mysql username and password to connect to master and slave
  • Hardcode the absolute path for mk-table-checksum and mk-table-sync (you can substitute pt-table-checksum and pt-table-sync). My script is based on using maatkit version 4047. You can adjust the parameters appropriately.
  • chmod +x /root/tcs/*.sh

Look it over and see if you can use it.

Give it a Try !!!

share|improve this answer
    
I tried using the Maatkit Tools instead of the Percona Tools and found the same results. I didn't run your script as I know there are only certain tables which is out of sync. The rest of the server / databases look to be fine. The same issue with mk-table-sync saying the table is fine but mk-table-checksum is saying there is one CRC_DIFF. –  stanleykylee Dec 6 '11 at 0:44
1  
@stanleykylee : There may be one or more rows in the slave that the master does not have, and vice versa. That happens once in a while. Sometimes maatkit does get it 100%. If the table is small enough, you should probably mysqldump the table playing lone wolf and load that mysqldump into the slave and call it a day. –  RolandoMySQLDBA Dec 6 '11 at 3:16
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.