pt-table-checksum does not work in tabe has columns with mismatching collaitons

[Sveta Smirnova]

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/percona-toolkit/bin/pt-table-checksum h=127.0.0.1,P=13000,u=root
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(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db1`.`cp1251` /*explain checksum table*/: DBD::mysql::st execute failed: Illegal mix of collations for operation 'concat_ws' [for Statement "EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db1`.`cp1251` /*explain checksum table*/"] at /home/sveta/build/percona-toolkit/bin/pt-table-checksum line 11351.

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`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db1`.`cp1251` /*checksum table*/" with ParamValues: 0='db1', 1='cp1251', 2=1, 3=undef, 4=undef, 5=undef] at /home/sveta/build/percona-toolkit/bin/pt-table-checksum line 10741.

            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(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)) AS crc FROM `test`.`cp1251`;
ERROR 1271 (HY000): Illegal mix of collations for operation 'concat_ws'

but

mysql> SELECT BIT_XOR(CAST(CRC32(CONCAT_WS('#', convert(`f1` using utf8mb4), `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)) AS crc FROM `test`.`cp1251`;
+------------+
| crc |
+------------+
| 1794999662 |
+------------+
1 row in set (0,00 sec)

[Sveta Smirnova (svetasmirnova)]

$ ~/build/percona-toolkit/bin/pt-table-checksum --version
pt-table-checksum 2.2.20

[Jericho Rivera (jericho-rivera)]

Also affects pt-3.0.1

03-21T07:09:05 Error executing EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`cp1251` /*explain checksum table*/: DBD::mysql::st execute failed: Illegal mix of collations for operation 'concat_ws' [for Statement "EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`cp1251` /*explain checksum table*/"] at /usr/bin/pt-table-checksum line 11352.

03-21T07:09:05 Error checksumming table test.cp1251: Error executing checksum query: DBD::mysql::st execute failed: Illegal mix of collations for operation 'concat_ws' [for Statement "REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`cp1251` /*checksum table*/" with ParamValues: 0='test', 1='cp1251', 2=1, 3=undef, 4=undef, 5=undef] at /usr/bin/pt-table-checksum line 10742.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-729