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

Bug #1427552 reported by Lucian Atodiresei
22
This bug affects 4 people
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Confirmed
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
Revision history for this message
Nilnandan Joshi (nilnandan-joshi) wrote :

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
Revision history for this message
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)

Revision history for this message
seanlook (1104138797-2) wrote :

Could this bug be resolved?

Revision history for this message
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

Revision history for this message
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
===

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

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

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.