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.

We use MySQL 5.1 with a primary/secondary replication setup. I use mk-table-checksum from Maatkit to generate checksums on the master and perform consistency checks on the replica.

mk-table-checksum is used to generate the checksums, and is pretty simple:

% mk-table-checksum localhost \
        --quiet \
        --replicate=test.checksum --empty-replicate-table --create-replicate-table \
        --chunk-size=500000 --sleep-coef=0.5 \
        --ignore-tables=mysql.general_log_backup,mysql.general_log,mysql.slow_log,mysql.help_topic,mysql.tables_priv

These checksums are then replicated to the Replica (slave) server, where we compare the checksums.

Around a month ago we had a server failure. We fixed the server, started up the database and resumed replication. Everything seems to be working fine-- Slave_IO_Running and Slave_SQL_Running are both set to "Yes", everything appears to be getting replicated successfully except for the error mentioned below, etc.

There is one problem, however. When I check the table using the following recommended method on the Replica, I see a consistency problem.

# mysql --execute "SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff, \
>         this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff \
>         FROM test.checksum \
>         WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc);"
+--------+------------------+-------+----------+----------+
| db     | tbl              | chunk | cnt_diff | crc_diff |
+--------+------------------+-------+----------+----------+
| plants | trees_properties |    40 |        0 |        1 |
+--------+------------------+-------+----------+----------+

This problem still occurs even after force another sync on the master using mk-table-sync on the master. I will probably need to replace the corrupt data by hand, but I am unclear what the actual problem is. What is a 'chunk'?

My guess is that only a handful of rows are different, and I can probably replace those rows by hand. But how can I list which rows are missing?

I realize that Maatkit has been deprecated in favor of Percona Tools. I plan to upgrade some day. But for now, "if it ain't broke, don't fix it."

share|improve this question
    
mt-* is old; switch to pt-* –  Rick James Nov 20 '12 at 2:01
add comment

1 Answer 1

I think a chunk is a set number of rows to be scanned and checksummed.

I never did like chunks because if the table order matters, each chunk could be completely different if just one row was written ahead or behind another.

Try not setting a chunk size and see if the entire table is checksummed as a whole as a opposed to each chunk checksum.

You can also run mk-table-sync using the --print option and redirect to a text file to see if there is any SQL created that would have been executed to sync the slave had you used the --execute option. If nothing comes out into the text file from mk-table-sync, you will have to resort to doing physical copying (for MyISAM) or mysqldump the table and loading it on the slave (for InnoDB).

Give it a Try !!!

share|improve this answer
    
On both the master and replica, I ran the mk-table-checksum again without specifying a chunk size. The command shows that the checksum differs for both the primary and secondary. Running a select count(*) from db.table; shows that the table on the master and slave have the same count. This suggests that a number of rows are corrupt. –  Stefan Lasiewski Nov 19 '12 at 21:56
1  
That is not necessarily corruption. Even though the Master and Slave have a table with identical row counts but a different checksum value leans more towards columnar differences. If you run mk-table-sync --sync-to-master --print > diff.sql on the Slave, you will most likely see a lot of REPLACE commands in diff.sql. You would DELETEs and REPLACE otherwise (on mismatched counts) –  RolandoMySQLDBA Nov 19 '12 at 22:18
    
The mk-table-sync --print option shows me a bunch of REPLACE statements. When I run the script, the rows are REPLACED, but the checksum still fails and mk-table-sync --print shows me the same rows... it's like the REPLACE isn't replacing everything... –  Stefan Lasiewski Nov 20 '12 at 0:04
    
1) Did you use --sync=to-master ? 2) Did you specify a chunksize? If you did, please do not use any chunk size. (As a sidenote, I have never used chunksize for the past 4 years for either mk-table-checksum or mk-table-sync. I usually get a cleaner comparison without it). –  RolandoMySQLDBA Nov 20 '12 at 2:42
    
Yes I used --sync-to-master, and I am not currently using a chunksize. It seems that mk-table-sync --sync-to-master is correctly detecting rows which are not in sync, but certain values are not being changed unless the field is quoted. If I do mk-table-sync --sync-to-master --print > diff.sql, and then enclose the last field for each row in quotes, it is successfully changing data on the replica. The field in question has the type varchar(255) and contains values like '0x0000' and '0x8900'. –  Stefan Lasiewski Nov 20 '12 at 19:37
show 1 more 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.