pt-table-sync --print option binary columns not readable in output

Bug #1185124 reported by Heather Sullivan on 2013-05-28
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit

Bug Description

We have a number of columns in our application that use binary(16). These columns are printing out with garbage characters, even with the --hex-blog variable set. Is there a way to get the hex version of the binary output?


    default: yes

    HEX() BLOB, TEXT and BINARY columns.

    When row data from the source is fetched to create queries to sync the data (i.e. the queries seen with --print and executed by --execute), binary columns are wrapped in HEX() so the binary data does not produce an invalid SQL statement. You can disable this option but you probably shouldn’t.
MySQL version (master & slave): 5.5.17

[tungsten@master101 ~]$ pt-table-sync --version
pt-table-sync 2.2.2

Example problem column output from select:
SELECT `account_id`, HEX(contact_id), HEX(channel_id), `address`, `state`, `create_source`, `update_source`, `created_at`, `updated_at`, `deleted_at` FROM channels WHERE `account_id` = 1100373722264 AND address = '<email address hidden>';
   account_id HEX(contact_id) HEX(channel_id) address state create_source update_source created_at updated_at deleted_at
------------- -------------------------------- -------------------------------- --------------- ------ ------------- ------------- ------------------- ------------------- ------------
1100373722264 463E1F409C9611E2BA7300163E5DE2F1 464439C09C9611E2BA7300163E5DE2F1 <email address hidden> I A A 2013-04-03 19:40:01 2013-04-03 19:40:01 (NULL)

Output from pt-table-sync - contact_id & channel_id are not readable.
[tungsten@master101 ~]$ pt-table-sync --verbose --print h=master101,u=perconaadm,P=3306 h=slave102,u=perconaadm,P=3306 --ask-pass --nocheck-slave --nocheck-master --wait 0 --databases HMS_Contacts01 --hex-blob
Enter password for master101:
Enter password for slave102:
# Syncing P=3306,h=slave102,p=...,u=perconaadm
# 0 0 0 0 Nibble 11:55:27 11:55:28 0 HMS_Contacts01.channel_counts
UPDATE `HMS_Contacts01`.`channels` SET `address`='<email address hidden>', `state`='I', `create_source`='A', `update_source`='A', `created_at`='2013-04-03 19:40:01', `updated_at`='2013-04-03 19:40:01', `deleted_at`=NULL, `description`=NULL, `history`='{"history":[{"state":"I","event":"I","source":"A","time":1268534887}]}', `source_ip`=NULL, `optin_at`='2010-03-14 02:48:07', `optout_at`=NULL, `confirmed`='0' WHERE `account_id`='1100373722264' AND `contact_id`='F>@âºs>]âñ' AND `channel_id`='FD9Àâºs>]âñ' LIMIT 1 /*percona-toolkit src_db:HMS_Contacts01 src_tbl:channels src_dsn:P=3306,h=master101,p=...,u=perconaadm dst_db:HMS_Contacts01 dst_tbl:channels dst_dsn:P=3306,h=slave102,p=...,u=perconaadm lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:16918 user:tungsten host:master101*/;
# 0 0 0 1 Nibble 11:55:28 11:55:30 2 HMS_Contacts01.channels

Daniel Nichter (daniel-nichter) wrote :

--hex-blob should work, since it's not, it's a bug somewhere. There's no quick workaround I can think of; pt-table-sync is rather complex. We'll look into this and fix.

tags: added: hex pt-table-sync value-quoting
Changed in percona-toolkit:
status: New → Triaged
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers