pt-table-checksum fails on BINARY field in PK

Bug #1381280 reported by Aleksandr Kuzminsky on 2014-10-15
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
High
Frank Cizmich

Bug Description

10-10T00:59:42 Error checksumming table XXX.YYY: Error executing checksum query: Checksum query for table XXX.YYY caused MySQL error 1366:
    Level: Warning
     Code: 1366
  Message: Incorrect string value: '\xAA\xAA\xAA\xAA\xAA...' for column 'lower_boundary' at row 561121
    Query: 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`, `f3`, `f4` + 0)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `XXX`.`YYY` FORCE INDEX(`PRIMARY`) WHERE ((`f1` > ?) OR (`f1` = ? AND `f2` > ?) OR (`f1` = ? AND `f2` = ? AND `f3` >= ?)) AND ((`f1` < ?) OR (`f1` = ? AND `f2` < ?) OR (`f1` = ? AND `f2` = ? AND `f3` <= ?)) /*checksum chunk*/

f2 is BINARY type

Related branches

Changed in percona-toolkit:
assignee: nobody → Frank Cizmich (frank-cizmich)

Could binary charset in percona.checksums be possible workaround?

CREATE TABLE `checksums` (
  `db` binary(64) NOT NULL,
  `tbl` binary(64) NOT NULL,
  `chunk` int(11) NOT NULL,
  `chunk_time` float DEFAULT NULL,
  `chunk_index` varbinary(200) DEFAULT NULL,
  `lower_boundary` blob,
  `upper_boundary` blob,
  `this_crc` binary(40) NOT NULL,
  `this_cnt` int(11) NOT NULL,
  `master_crc` binary(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=InnoDB DEFAULT CHARSET=binary

it doesn't work when binary, master_crc is NULL for every chunk.
latin1 & charset latin1 works fine though

typo. Read "blob lower_boundary and upper_boundary & charset latin1 works fine though"

tags: added: i46854

I wonder if this is really a duplicate of https://bugs.launchpad.net/percona-toolkit/+bug/925781

tags: added: pt-table-checksum
Frank Cizmich (frank-cizmich) wrote :

Hi Aleksandr,

Can you provide SHOW CREATE TABLE output for the source table (alter column names if you wish) and some sample data? (fake data will do, as long as it breaks the tool)
If we can reproduce this we should be able to solve it from within the code.

Thx

Frank Cizmich (frank-cizmich) wrote :

Having trouble reproducing this issue.
As a workaround for this table I'd recommend using --chunk-index-columns=1
This way the tool will use only the first column of the key, ignoring the problematic binary column.

Frank Cizmich (frank-cizmich) wrote :

Aleksandr,

I've ran pt-table-checksum through the test suite using your workaround ( blob datatype for upper and lower boundary ) and the results were ok.
So in principle at least, you should be ok using this fix.

Download full text (4.2 KiB)

This is easy to reproduce (tbin/sql will be uploaded later).

1. Make sure you have default-character-set=utf8 for clients, like this:

[openxs@chief p5.6]$ cat ~/.my.cnf
[client]
default-character-set=utf8

2. Make sure there is no percona database.
3. Load data:

[openxs@chief p5.6]$ bin/mysql --no-defaults -uroot test </tmp/tbin.sql [openxs@chief p5.6]$ bin/mysql --no-defaults -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.6.21-69.0 MySQL Community Server (GPL)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from tbin;
+----------+
| count(*) |
+----------+
| 8192 |
+----------+
1 row in set (0.01 sec)

mysql> exit
Bye

Try to use pt-table-checksum:

[openxs@chief p5.6]$ pt-table-checksum u=root,P=3306,h=127.0.0.1
            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
10-22T09:36:54 0 0 0 1 0 0.142 mysql.columns_priv
10-22T09:36:54 0 0 2 1 0 0.165 mysql.db
10-22T09:36:54 0 0 0 1 0 0.165 mysql.event
10-22T09:36:55 0 0 0 1 0 0.124 mysql.func
10-22T09:36:55 0 0 40 1 0 0.124 mysql.help_category
10-22T09:36:55 0 0 485 1 0 0.123 mysql.help_keyword
10-22T09:36:55 0 0 1090 1 0 0.123 mysql.help_relation
10-22T09:36:55 0 0 533 1 0 0.124 mysql.help_topic
10-22T09:36:55 0 0 18 1 0 0.165 mysql.innodb_index_stats
10-22T09:36:55 0 0 2 1 0 0.124 mysql.innodb_table_stats
10-22T09:36:55 0 0 0 1 0 0.123 mysql.ndb_binlog_index
10-22T09:36:56 0 0 0 1 0 0.133 mysql.plugin
10-22T09:36:56 0 0 0 1 0 0.131 mysql.proc
10-22T09:36:56 0 0 0 1 0 0.132 mysql.procs_priv
10-22T09:36:56 0 0 2 1 0 0.132 mysql.proxies_priv
10-22T09:36:56 0 0 0 1 0 0.141 mysql.servers
10-22T09:36:56 0 0 0 1 0 0.148 mysql.slave_master_info
10-22T09:36:56 0 0 0 1 0 0.149 mysql.slave_relay_log_info
10-22T09:36:57 0 0 0 1 0 0.140 mysql.slave_worker_info
10-22T09:36:57 0 0 0 1 0 0.123 mysql.tables_priv
10-22T09:36:57 0 0 0 1 0 0.125 mysql.time_zone
10-22T09:36:57 0 0 0 1 0 0.366 mysql.time_zone_leap_second
10-22T09:36:57 0 0 0 1 0 0.132 mysql.time_zone_name
10-...

Read more...

Sample table that breaks pt-table-checksum.

Changed in percona-toolkit:
status: New → Confirmed
Changed in percona-toolkit:
importance: Undecided → High
Frank Cizmich (frank-cizmich) wrote :

attached is a version of pt-table-checksum that creates a checksum table using blob datatypes for the boundaries.

Changed in percona-toolkit:
status: Confirmed → In Progress

Now we should make sure pt-table-sync also works with the checksum table using blob data types, and that new structure is documented, and that newer versions of tools work well with chacksum tables from older versions etc

Changed in percona-toolkit:
milestone: none → 2.2.13
Changed in percona-toolkit:
milestone: 2.2.13 → 2.2.14
Frank Cizmich (frank-cizmich) wrote :

Reason for postponed release is that this needs exhaustive testing.

Changed in percona-toolkit:
status: In Progress → Fix Committed
Frank Cizmich (frank-cizmich) wrote :

Added --binary-index flag to optionally create checksum table using BLOB data type.

Changed in percona-toolkit:
status: Fix Committed → Fix Released

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

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

Other bug subscribers