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

Bug #1427552 reported by Lucian Atodiresei on 2015-03-03
22
This bug affects 4 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

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

summary: - pt-table-checksum reports error on tables with different columns
+ 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:~$

Changed in percona-toolkit:
status: New → Confirmed
monty solomon (monty+launchpad) wrote :

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) wrote :

Could this bug be resolved?

Frank Cizmich (frank-cizmich) wrote :

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) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers