Mysql – Maatkit shows the MySQL replication error in one table, but won’t fix it

maatkitMySQLpercona-toolsreplication

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."

Best Answer

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 !!!

Related Question