Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-136

pt-table-checksum shows diffs when table has columns with different collation/charset

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Won't Fix
    • Affects Version/s: 3.0.2
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

      Description

      This is a variant of
      https://bugs.launchpad.net/percona-toolkit/+bug/1674266
      the collation-safe comparison method suggested on that bug is a possible fix albeit use utf8 since utf8mb4 is an unknown character set on 5.1.

      mysql> show create table t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(125) COLLATE latin1_general_ci DEFAULT NULL,
        `service` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
        `comment` varchar(255) COLLATE latin1_general_ci DEFAULT '',
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
      1 row in set (0.00 sec)
      
      mysql> show full columns from t1;
      +---------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
      | Field   | Type         | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
      +---------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
      | id      | int(11)      | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
      | name    | varchar(125) | latin1_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
      | service | varchar(255) | utf8_general_ci   | YES  |     | NULL    |                | select,insert,update,references |         |
      | comment | varchar(255) | latin1_general_ci | YES  |     |         |                | select,insert,update,references |         |
      +---------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
      4 rows in set (0.00 sec)
      
      mysql> select * from t1;
      +----+-----------+----------+----------+
      | id | name      | service  | comment  |
      +----+-----------+----------+----------+
      |  1 | illimité | service1 | comment1 |
      +----+-----------+----------+----------+
      1 row in set (0.00 sec)
      
      [root@ps51 ~]# pt-table-checksum --databases test --tables t1 --recursion-method=dsn=h=localhost,D=percona,t=dsns,A=utf8 --no-check-binlog-format --empty-replicate-table h=localhost,u=root,p=passw0rd
                  TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
      05-09T00:24:37      0      1        1       1       0   0.015 test.t1
      
      Checksum diff:
      mysql> SELECT @@hostname, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `name`, `service`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `t1`;
      +------------+-----+----------+
      | @@hostname | cnt | crc      |
      +------------+-----+----------+
      | ps51       |   1 | 8bf51686 |
      +------------+-----+----------+
      1 row in set (0.00 sec)
      
      mysql> SELECT @@hostname, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `name`, `service`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `t1`;
      +------------+-----+----------+
      | @@hostname | cnt | crc      |
      +------------+-----+----------+
      | ps55       |   1 | 1ee58fbc |
      +------------+-----+----------+
      1 row in set (0.00 sec)
      

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                carlos.salguero Carlos Salguero
                Reporter:
                jericho.rivera Jericho Rivera
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: