LP #953101: TableSyncNibble fails to quote varchar columns in primary key
Description
Environment
Smart Checklist
Activity
lpjirasync January 24, 2018 at 6:26 PM
**Comment from Launchpad by: Daniel Nichter on: 13-10-2012 16:57:20
I think this bug, or at least part of it, was fixed as bug 1038276. If there's still an issue here with the latest version of the tools, someone can reply and we'll re-open this issue.
lpjirasync January 24, 2018 at 6:26 PM
**Comment from Launchpad by: Brian Fraser on: 22-08-2012 21:07:28
Alex, just for organization's sake, I opened a new bug for your report at https://bugs.launchpad.net/percona-toolkit/+bug/1038276
Dave, I can't seem able to reproduce your bug, but I do notice that you're using a pretty old version of the toolkit; Does the bug persist in newer versions? If it does, could you send the output of running the tool with PTDEBUG=1?
lpjirasync January 24, 2018 at 6:26 PM
**Comment from Launchpad by: Alex Geis on: 01-08-2012 09:42:50
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)
lpjirasync January 24, 2018 at 6:26 PM
**Comment from Launchpad by: Alex Geis on: 01-08-2012 06:50:03
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/;
lpjirasync January 24, 2018 at 6:26 PM
**Comment from Launchpad by: Dave Juntgen on: 18-04-2012 02:55:40
Sorry - was thinking pt-table-checksum here, the command was:
./pt-table-sync --execute --user=xxxx --password=xxxx --database=wc_gyn --tables=user_patients --sync-to-master slave1
Details
Assignee
UnassignedUnassignedReporter
lpjirasynclpjirasync(Deactivated)Priority
Low
Details
Details
Assignee
Reporter
lpjirasyncPriority
LowSmart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Smart Checklist
**Reported in Launchpad by Dave Juntgen last update 13-10-2012 16:57:36
TableSyncNibble:4800 23720 Next boundary sql: SELECT /nibble boundary 0/ `id`,`id_type`,`pat_id`,`role_id`,`rank`,`entered_date` FROM `wc_gyn`.`user_patients` FORCE INDEX (`PRIMARY`) WHERE (((`pat_id` > 479583) OR (`pat_id` = 479583 AND `id` > 3357) OR (`pat_id` = 479583 AND `id` = 3357 AND `id_type` > user) OR (`pat_id` = 479583 AND `id` = 3357 AND `id_type` = user AND `role_id` >= 2)) AND ((`pat_id` < 548043) OR (`pat_id` = 548043 AND `id` < 6337) OR (`pat_id` = 548043 AND `id` = 6337 AND `id_type` < user) OR (`pat_id` = 548043 AND `id` = 6337 AND `id_type` = user AND `role_id` <= 3))) ORDER BY `pat_id`,`id`,`id_type`,`role_id` LIMIT 999, 1
TableSyncNibble:4812 23720 DBD::mysql::db selectall_arrayref failed: fetch() without execute() [for Statement "EXPLAIN SELECT /nibble boundary 0/ `id`,`id_type`,`pat_id`,`role_id`,`rank`,`entered_date` FROM `wc_gyn`.`user_patients` FORCE INDEX (`PRIMARY`) WHERE (((`pat_id` > 479583) OR (`pat_id` = 479583 AND `id` > 3357) OR (`pat_id` = 479583 AND `id` = 3357 AND `id_type` > user) OR (`pat_id` = 479583 AND `id` = 3357 AND `id_type` = user AND `role_id` >= 2)) AND ((`pat_id` < 548043) OR (`pat_id` = 548043 AND `id` < 6337) OR (`pat_id` = 548043 AND `id` = 6337 AND `id_type` < user) OR (`pat_id` = 548043 AND `id` = 6337 AND `id_type` = user AND `role_id` <= 3))) ORDER BY `pat_id`,`id`,`id_type`,`role_id` LIMIT 999, 1"] at ./pt-table-sync line 4809.
Cannot nibble table `wc_gyn`.`user_patients` because MySQL chose no index instead of the `PRIMARY` index at ./pt-table-sync line 4748. while doing wc_gyn.user_patients on xxx.xxx.xxx.xxx
I believe this might have been report once before but without PTDEBUG=1 turn on.
In this example, the column 'id_type' is a varchar() and the WHERE criteria must be quoted, so:
(`pat_id` = 548043 AND `id` = 6337 AND `id_type` < user) – BAD
should be:
(`pat_id` = 548043 AND `id` = 6337 AND `id_type` < 'user') – MAYBE???
Now that I think of it, the expression `id_type` < 'user' may produce incorrect results when comparing strings.