Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Won't Fix
-
3.0.2
-
None
-
None
-
None
-
Master:
mysql> select @@version; +------------------+ | @@version | +------------------+ | 5.1.73-14.12-log | +------------------+ 1 row in set (0.00 sec)
Slave:
mysql> select @@version; +-----------------+ | @@version | +-----------------+ | 5.5.54-38.7-log | +-----------------+ 1 row in set (0.00 sec)
Master: mysql> select @@version; +------------------+ | @@version | +------------------+ | 5.1.73-14.12-log | +------------------+ 1 row in set (0.00 sec) Slave: mysql> select @@version; +-----------------+ | @@version | +-----------------+ | 5.5.54-38.7-log | +-----------------+ 1 row in set (0.00 sec)
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)
[[email protected] ~]# 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)