Maatkit version 1674 released at Xaprb

Maatkit contains essential command-line utilities for MySQL, such as a table checksum tool and query profiler. It provides missing features such as checking slaves for data consistency, with emphasis on quality and scriptability.

This release contains bug fixes and new features.

Changelog for mk-archiver:

2008-01-05: version 1.0.6

   * Made suffixes for time options optional (bug #1858696).

Changelog for mk-deadlock-logger:

2008-01-05: version 1.0.8

   * Made suffixes for time options optional (bug #1858696).

Changelog for mk-heartbeat:

2008-01-05: version 1.0.6

   * Made suffixes for time options optional (bug #1858696).

Changelog for mk-parallel-dump:

2008-01-05: version 1.0.4

   * Second and later chunks had DROP/CREATE TABLE (bug #1863949).
   * Made suffixes for time options optional (bug #1858696).
   * --locktables didn't disable --flushlock.

Changelog for mk-parallel-restore:

2008-01-05: version 1.0.3

   * Made suffixes for time options optional (bug #1858696).
   * --ignoretables was ignored.

Changelog for mk-slave-delay:

2008-01-05: version 1.0.5

   * Made suffixes for time options optional (bug #1858696).
   * The program was ignoring some connection parameters.
   * Made the program use master when the I/O thread waits for relay log space.

Changelog for mk-slave-restart:

2008-01-05: version 1.0.5

   * Made suffixes for time options optional (bug #1858696).
   * Added logic to discard corrupt relay logs.
   * Added --monitor, --sentinel, and --stop.
   * Added --quiet and changed --verbose to 1 by default.
   * Added the ability to monitor many servers with --recurse.

Changelog for mk-table-checksum:

2008-01-05: version 1.1.24

   * Added support for the FNV_64 UDF, which is distributed with Maatkit.
   * --emptyrepltbl didn't Do The Right Thing by default.
   * --explain didn't disable --emptyrepltbl
   * Made suffixes for time options optional (bug #1858696).
   * The --float-precision option was ignored.
   * (mk-checksum-filter) -i, -d options worked only on multiple files.

Changelog for mk-table-sync:

2008-01-05: version 1.0.3

   * Added the --function command-line option.
   * Added support for the FNV_64 hash function (see mk-table-checksum).
   * Made suffixes for time options optional (bug #1858696).
   * InnoDB tables use --transaction unless it's explicitly specified.

Further Reading:

Written by Xaprb

January 5th, 2008 at 3:10 pm

Posted in Uncategorized

Tagged with backups, MySQL, replication, SQL, synchronization, UDF

10 Responses to 'Maatkit version 1674 released'

Subscribe to comments with RSS

Hi Baron,

I have an interesting problem (?) with mk-table-checksum

Using BIT_XOR it shows the tables are different.

Doing a SQL comparison shows them the same. I know we can expect different results with doubles and/or floats, but this table has neither.

Table layout and comparison shown below:

mysql> desc auas;
——————– ————– —— —– ——— ——-
| Field | Type | Null | Key | Default | Extra |
——————– ————– —— —– ——— ——-
| id | varchar(36) | NO | PRI | NULL | |
| user_id | varchar(36) | YES | MUL | NULL | |
| category_id | varchar(36) | YES | MUL | NULL | |
| category_type | varchar(100) | YES | | NULL | |
| modified_user_id | varchar(36) | YES | | NULL | |
| created_by | varchar(36) | YES | | NULL | |
| date_modified | datetime | YES | | NULL | |
| date_created | datetime | YES | | NULL | |
| allow_read | tinyint(1) | YES | | 0 | |
| allow_edit | tinyint(1) | YES | | 0 | |
| allow_read_by_user | tinyint(1) | YES | | 0 | |
| allow_edit_by_user | tinyint(1) | YES | | 0 | |
| allow_read_by_team | tinyint(1) | YES | | 0 | |
| allow_edit_by_team | tinyint(1) | YES | | 0 | |
| deleted | tinyint(1) | NO | MUL | 0 | |
——————– ————– —— —– ——— ——-
15 rows in set (0.00 sec)

mysql> create table auad select m.id from auam m, auas s
-> where
-> m.id = s.id and
-> (m.user_id s.user_id
-> or m.category_id s.category_id
-> or m.category_type s.category_type
-> or m.modified_user_id s.modified_user_id
-> or m.created_by s.created_by
-> or m.date_modified s.date_modified
-> or m.date_created s.date_created
-> or m.allow_read s.allow_read
-> or m.allow_edit s.allow_edit
-> or m.allow_read_by_user s.allow_read_by_user
-> or m.allow_edit_by_user s.allow_edit_by_user
-> or m.allow_read_by_team s.allow_read_by_team
-> or m.allow_edit_by_team s.allow_edit_by_team
-> or m.deleted s.deleted);
Query OK, 0 rows affected (4.88 sec)
Records: 0 Duplicates: 0 Warnings: 0

Bruce Bristol

17 Jan 08 at 3:27 pm
What does CHECKSUM TABLE say?

Trailing spaces are stripped from comparisons so SQL joins may not agree with the checksums.

Xaprb

17 Jan 08 at 3:40 pm
Checksum comes back with different values.

Bruce Bristol

17 Jan 08 at 3:56 pm
Something’s different then. You could try comparing the tables by doing the same comparison above, but write the WHERE clause like this:

MD5(col) = MD5(col)…

Xaprb

17 Jan 08 at 4:16 pm
Still match…

mysql> create table auad select m.id from auam m, auas s
-> where
-> m.id = s.id and
-> (MD5(m.user_id ) MD5(m.user_id )
-> or MD5(m.category_id ) MD5(m.category_id )
-> or MD5(m.category_type ) MD5(m.category_type )
-> or MD5(m.modified_user_id ) MD5(m.modified_user_id )
-> or MD5(m.created_by ) MD5(m.created_by )
-> or MD5(m.date_modified ) MD5(m.date_modified )
-> or MD5(m.date_created ) MD5(m.date_created )
-> or MD5(m.allow_read ) MD5(m.allow_read )
-> or MD5(m.allow_edit ) MD5(m.allow_edit )
-> or MD5(m.allow_read_by_user) MD5(m.allow_read_by_user)
-> or MD5(m.allow_edit_by_user) MD5(m.allow_edit_by_user)
-> or MD5(m.allow_read_by_team) MD5(m.allow_read_by_team)
-> or MD5(m.allow_edit_by_team) MD5(m.allow_edit_by_team)
-> or MD5(m.deleted ) MD5(m.deleted ));
Query OK, 0 rows affected (26.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

Bruce Bristol

17 Jan 08 at 4:40 pm
Then there are probably rows missing from one of the tables. Check the row count. If you use mk-table-sync with –print, you can see what it thinks is different.

Xaprb

17 Jan 08 at 4:47 pm
Nope. Here are the original table names which I copied to another db to do the SQL comparison:

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sugarcrm acl_users_actions 0 grm-db-slave0 InnoDB 440342 6593e420b3992899b5a6c57493cd99f0 4 0 NULL NULL
sugarcrm acl_users_actions 0 grm-db InnoDB 440342 146557c1fffdb11126c5184a5734b399 4 0 NULL NULL

Bruce Bristol

17 Jan 08 at 4:51 pm
Try running mk-table-sync and see if it finds any rows different, then inspect those rows. I’m curious if that’ll shed any light.

Xaprb

17 Jan 08 at 4:54 pm
I decided to do another sql compare using left join, as I should’ve in the first place and found 300 rows that have different primary keys, so there indeed are differences.

Thanks for your time, Baron!

-Bruce

Bruce Bristol

17 Jan 08 at 5:23 pm
Hi Baron,

Just wanted to let you know I found the cause. They’re using the UUID() function which doesn’t yet work in replication.

Have a good weekend!

-Bruce

Bruce Bristol

18 Jan 08 at 7:27 pm