pt-table-checksum reports error on table with different columns collations

Verified with PS 5.6.21.

nilnandan@desktop:~$ pt-table-checksum --version
pt-table-checksum 2.2.14
nilnandan@desktop:~$

nilnandan@desktop:~$ pt-table-checksum --user=root --password=msandbox --socket=/tmp/mysql_sandbox20886.sock --recursion-method dsn=h=localhost,D=percona,t=dsns --no-check-binlog-format --check-interval=10
            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-06T12:44:24 0 0 0 1 0 0.013 mysql.columns_priv
...
05-06T12:44:24 0 0 8 1 0 0.005 mysql.user
05-06T12:44:24 0 1 1 1 0 0.006 percona.dsns
05-06T12:44:43 0 4 16777216 24 0 19.277 test.nil
05-06T12:44:43 0 0 0 1 0 0.012 test.nil-test
05-06T12:44:43 Error executing EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `column1`, `column2`, `column3`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`table1` /*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('#', `column1`, `column2`, `column3`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`table1` /*explain checksum table*/"] at /usr/bin/pt-table-checksum line 11185.

05-06T12:44:43 Error checksumming table test.table1: 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('#', `column1`, `column2`, `column3`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`table1` /*checksum table*/" with ParamValues: 0='test', 1='table1', 2=1, 3=undef, 4=undef, 5=undef] at /usr/bin/pt-table-checksum line 10606.

05-06T12:44:43 2 0 0 1 0 0.005 test.table1
nilnandan@desktop:~$

We experience a similar issue.

execute failed: Illegal mix of collations for operation 'concat_ws' at /usr/bin/pt-table-checksum line 11268.

execute failed: Illegal mix of collations for operation 'concat_ws' at /usr/bin/pt-table-checksum line 10658.

The table has DEFAULT CHARSET=latin1 and the table contains some character columns that use latin1, utf8, and usc2. The PRIMARY KEY column and another column use CHARACTER SET utf8. Another column uses CHARACTER SET ucs2.

pt-table-checksum 2.2.15
mysql Ver 14.14 Distrib 5.6.25-73.1, for Linux (x86_64) using 6.0
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f
Linux 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
CentOS release 6.5 (Final)

Could this bug be resolved?

seanlook

This is vaguely similar to:
https://bugs.launchpad.net/percona-toolkit/+bug/925781

Could you try creating the checksum table with BLOB datatype? Available as of 2.2.14, option --binary-index :
https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#cmdoption-pt-table-checksum--binary-index

I tried pt-table-checksum with option '--binary-index' but the error is still show. Here's the table structure when running with '--binary-index':
===
CREATE TABLE `_checksums` (
  `db` char(64) NOT NULL,
  `tbl` char(64) NOT NULL,
  `chunk` int(11) NOT NULL,
  `chunk_time` float DEFAULT NULL,
  `chunk_index` varchar(200) DEFAULT NULL,
  `lower_boundary` blob,
  `upper_boundary` blob,
  `this_crc` char(40) NOT NULL,
  `this_cnt` int(11) NOT NULL,
  `master_crc` char(40) DEFAULT NULL,
  `master_cnt` int(11) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`db`,`tbl`,`chunk`),
  KEY `ts_db_tbl` (`ts`,`db`,`tbl`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
===

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