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

[Lucian Atodiresei]

Database `mydb` is fully replicated to the slaves. We have the following table with different collations for it's columns:
===
USE mydb;
CREATE TABLE `table1` (
  `column1` varchar(255) CHARACTER SET latin1 NOT NULL COMMENT 'column1',
  `column2` varchar(245) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL COMMENT 'column2',
  `column3` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'column3',
  PRIMARY KEY (`column1`,`column2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='None';
===

Command used: pt-table-checksum --replicate=mydb._checksums --databases=mydb --nocheck-replication-filters --run-time 2h

Errors:
===
02-26T01:55:37 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 `mydb`.`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 `mydb`.`table1` /*explain checksum table*/"] at /usr/bin/pt-table-checksum line 11167.

02-26T01:55:37 Error checksumming table mydb.table1: Error executing checksum query: DBD::mysql::st execute failed: Illegal mix of collations for operation 'concat_ws' [for Statement "REPLACE INTO `mydb`.`_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 `mydb`.`table1` /*checksum table*/" with ParamValues: 0='mydb', 1='table1', 2=1, 3=undef, 4=undef, 5=undef] at /usr/bin/pt-table-checksum line 10606.
===

$ pt-table-checksum --version
pt-table-checksum 2.2.13

$ mysql -B -N -e 'show variables like "version"'
version 5.5.41-MariaDB-1~wheezy-log

[Nilnandan Joshi (nilnandan-joshi)]

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:~$

[monty solomon (monty+launchpad)]

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)

[seanlook (1104138797-2)]

Could this bug be resolved?

[Frank Cizmich (frank-cizmich)]

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

[Lucian Atodiresei (lucian-m)]

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
===

[Shahriyar Rzayev (rzayev-sehriyar)]

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