TableSyncNibble fails to quote varchar columns in primary key

[Dave Juntgen]

# 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.

[Daniel Nichter (daniel-nichter)]

Odd that the tool didn't quote "user"--it should. It won't quote values that it thinks are hex, but "user" is certainly not hex. What's your full command line? And could you also provide a SHOW CREATE TABLE for the table so we can make sure the tool is parsing it correctly.

[Dave Juntgen (djuntgen)]

Daniel - I do not have the producing command line verbatim, however it was something like this:

./pt-table-checksum \
--quiet \
--empty-replicate-table \
--replicate master_replica_checksum.wc_gyn \
--pid=wc_gym.pid \
--user=xxx \
--password=xxx \
--databases=wc_gyn masterdb

mysql> SHOW CREATE TABLE `wc_gyn`.`user_patients`\G
*************************** 1. row ***************************
       Table: user_patients
Create Table: CREATE TABLE `user_patients` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `id_type` varchar(10) NOT NULL DEFAULT '',
  `pat_id` int(11) NOT NULL DEFAULT '0',
  `role_id` int(10) unsigned NOT NULL DEFAULT '0',
  `rank` int(10) unsigned NOT NULL DEFAULT '0',
  `entered_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`pat_id`,`id`,`id_type`,`role_id`),
  KEY `id` (`id`),
  KEY `id_type_role` (`id_type`,`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW INDEXES FROM `wc_gyn`.`user_patients`;
+---------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user_patients | 0 | PRIMARY | 1 | pat_id | A | 227962 | NULL | NULL | | BTREE | |
| user_patients | 0 | PRIMARY | 2 | id | A | 227962 | NULL | NULL | | BTREE | |
| user_patients | 0 | PRIMARY | 3 | id_type | A | 227962 | NULL | NULL | | BTREE | |
| user_patients | 0 | PRIMARY | 4 | role_id | A | 227962 | NULL | NULL | | BTREE | |
| user_patients | 1 | id | 1 | id | A | 587 | NULL | NULL | | BTREE | |
| user_patients | 1 | id_type_role | 1 | id_type | A | 10 | NULL | NULL | | BTREE | |
| user_patients | 1 | id_type_role | 2 | role_id | A | 10 | NULL | NULL | | BTREE | |
+---------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.12 sec)

[Dave Juntgen (djuntgen)]

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

[Alex Geis (ageis)]

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*/;

[Alex Geis (ageis)]

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)

[Brian Fraser (fraserbn)]

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?

[Daniel Nichter (daniel-nichter)]

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.

[Shahriyar Rzayev (rzayev-sehriyar)]

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