pt-table-checksum does not work in tabe has columns with mismatching collaitons
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Confirmed
|
Medium
|
Carlos Salguero |
Bug Description
If there is a table which has two columns in incompatible collations pt-table-checksum will fail to calculate checksum for this table.
How to repeat.
In MySQL CLI:
create database db1;
use db1;
create table cp1251(f1 varchar(100) character set latin1, f2 varchar(100) character set cp1251) engine=innodb;
set names utf8;
insert into cp1251 values('Sveta', 'Света');
Then run pt-table-checksum:
sveta@Thinkie$ ~/build/
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
03-20T12:24:49 Error executing EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(
03-20T12:24:49 Error checksumming table db1.cp1251: Error executing checksum query: DBD::mysql::st execute failed: Illegal mix of collations for operation 'concat_ws' [for Statement "REPLACE INTO `percona`
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
03-20T12:24:49 2 0 0 1 0 0.003 db1.cp1251
03-20T12:24:49 0 0 2 1 0 0.141 db1.latin1
...
Suggested fix: implement collation-safe comparison method (cast such columns to, say utf8mb4).
For example,
mysql> SELECT BIT_XOR(
ERROR 1271 (HY000): Illegal mix of collations for operation 'concat_ws'
but
mysql> SELECT BIT_XOR(
+------------+
| crc |
+------------+
| 1794999662 |
+------------+
1 row in set (0,00 sec)
Changed in percona-toolkit: | |
importance: | Undecided → Medium |
assignee: | nobody → Carlos Salguero (carlos-salguero) |
tags: | added: pt136 |
$ ~/build/ percona- toolkit/ bin/pt- table-checksum --version
pt-table-checksum 2.2.20