Xaprb

10 Responses to “Maatkit version 1674 released”

1. 1 Bruce Bristol
   Jan 17th, 2008 at 3:27 pm

   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

2. 2 Xaprb
   Jan 17th, 2008 at 3:40 pm

   What does CHECKSUM TABLE say?

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

3. 3 Bruce Bristol
   Jan 17th, 2008 at 3:56 pm

   Checksum comes back with different values.

4. 4 Xaprb
   Jan 17th, 2008 at 4:16 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)…

5. 5 Bruce Bristol
   Jan 17th, 2008 at 4:40 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

6. 6 Xaprb
   Jan 17th, 2008 at 4:47 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.

7. 7 Bruce Bristol
   Jan 17th, 2008 at 4:51 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

8. 8 Xaprb
   Jan 17th, 2008 at 4:54 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.

9. 9 Bruce Bristol
   Jan 17th, 2008 at 5:23 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

10. 10 Bruce Bristol
    Jan 18th, 2008 at 7:27 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

Leave a Reply