mk-table-checksum and Columns

UPDATE: I submitted a patch to the Maatkit developers, and the column bug mentioned below if fixed in SVN.

At work, we've been struggling with MySQL replication losing synchronization for the last few months. It's usually something like a few timestamps being off here and there, but we've also seen primary keys with artificially high values on the slave, certain replication events just not happening, and other weirdness. One toolset that has been very helpful in figuring out when problems occur is Maatkit. It a suite of tools written in Perl, which allow you to do things like run checksums on the master and slave for a given table, compare, and see if they're out of sync.

Unfortunately, some of the documentation is very sparse, which leads me to this blog entry. Google had nothing useful regarding how to use the columns feature, which allows the checksum to be calculated only on specific columns. I post this here in hopes it will help somebody.

We had been trying to use mk-table-checksum in the following way:

mk-table-checksum 127.0.0.1 -usomeuser -p'password' -tmydb.TableOne --replicate=maint.checksum --columns=UserID,TSAdded

Unforunately, running it this way would give a warning:

Use of uninitialized value in concatenation (.) or string at /usr/bin/mk-table-checksum line 431.

and produce no results. I finally got annoyed enough that I fired up the Perl debugger and stepped through what was going on. As it turns out, the data stored internally in $table->{cols} is all lowercase. If you pass in your column list camel-capped, or uppercase, it simply won't work.

The following invocation would produce useful results:

mk-table-checksum 127.0.0.1 -usomeuser -p'password' -tmydb.TableOne --replicate=maint.checksum --columns=userid,tsadded

Stupidly simple, but not necessarily obvious.