ChangeHandler doesn't quote varchar columns with hex-looking values

Bug #1038276 reported by Brian Fraser on 2012-08-17
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to
Fix Released
Brian Fraser

Bug Description

Reported by Alex Geis (ageis) on
Alex Geis (ageis) wrote on 2012-08-01: #4

Chiming in here as I've run into a problem with both pt-table-sync and pt-archiver. I've written a simple script to first archive a table to a backup table using pt-archiver with delete turned off, and then run pt-table-sync to keep it synced from time to time. Unfortunately, there are thousands of entries that aren't copied correctly on archive and aren't synced corrected due to a specific varchar field not being quoted (from what appears to be due to the field appearing as hex where it's not). Including an example.

The problematic field is: `screen_name`=0xD1
(where screen_name is a twitter username, so obviously not supposed to be hex)

UPDATE `21social_digitalreverie_backup`.`21social_digitalreverie_backup_twitter_users` SET `datetime_unix`='1336517818', `datetime_created_unix`='1189650978', `name_hash`='05bcb7dbe787e88213b68ab1b230133f', `screen_name`=0xD1,

... additional fields...

`followers_count`='1243', `friends_count`='1368', `statuses_count`='12356', `favourites_count`='105', `listed_count`='44', `geo_enabled`='1', `status_sync`='1' WHERE `uid`='8845752' LIMIT 1 /*percona-toolkit src_db:21social_digitalreverie_twitter src_tbl:21social_digitalreverie_twitter_users src_dsn:A=utf8,D=21social_digitalreverie_twitter,h=localhost,p=...,t=21social_digitalreverie_twitter_users,u=ageis dst_db:21social_digitalreverie_backup dst_tbl:21social_digitalreverie_backup_twitter_users dst_dsn:A=utf8,D=21social_digitalreverie_backup,h=ubuntu4.21.grid,p=...,t=21social_digitalreverie_backup_twitter_users,u=ageis lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:19359 user:root host:ubuntu1*/;
Hide comment
Alex Geis (ageis) wrote on 2012-08-01: #5

Temporarily solved the issue I mentioned by commenting out the following line in pt-table-sync, even though I figure it's not a good solution...

return $val if $val =~ m/^0x[0-9a-fA-F]+$/; # hex data (line 1070)

Related branches

Brian Fraser (fraserbn) on 2012-08-17
Changed in percona-toolkit:
assignee: nobody → Brian Fraser (fraserbn)
milestone: none → 2.1.4
status: New → In Progress
tags: added: pt-table-checksum pt-table-sync value-quoting
Changed in percona-toolkit:
importance: Undecided → Medium
Brian Fraser (fraserbn) on 2012-08-24
Changed in percona-toolkit:
status: In Progress → Fix Committed
Daniel Nichter (daniel-nichter) wrote :

This fix broke t/pt-table-sync/issue_22.t. See 2.1 @ r382.

Changed in percona-toolkit:
status: Fix Committed → In Progress
Brian Fraser (fraserbn) on 2012-09-19
Changed in percona-toolkit:
status: In Progress → Fix Committed
Brian Fraser (fraserbn) on 2012-09-26
Changed in percona-toolkit:
status: Fix Committed → Fix Released

Percona now uses JIRA for bug reports so this bug report is migrated to:

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

Duplicates of this bug

Other bug subscribers