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

[Brian Fraser]

Reported by Alex Geis (ageis) on https://bugs.launchpad.net/percona-toolkit/+bug/953101
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)

[Daniel Nichter (daniel-nichter)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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