Maatkit version 1674 released
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.
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