pt-table-sync quotes float values, can't sync

Bug #1229861 reported by Valerii Kravchuk
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
High
Daniel Nichter

Bug Description

pt-table-sync quotes every literal numeric value in statement generated:

sub quote_val {
   my ( $self, $val, %args ) = @_;

   return 'NULL' unless defined $val; # undef = NULL
   return "''" if $val eq ''; # blank string = ''
   return $val if $val =~ m/^0x[0-9a-fA-F]+$/ # quote hex data
                  && !$args{is_char}; # unless is_char is true

   $val =~ s/(['\\])/\\$1/g;
   return "'$val'";
}

As a result, it may generate statements like this:

DELETE FROM `test`.`t` WHERE `c1`='1' AND `c2`='1' AND `c3`='1' AND `snrmin`='29.5' AND `snrmax`='33.5' AND `snravg`='31.6' LIMIT 1

that affects zero rows:

slave1 [localhost] {msandbox} (test) > select * FROM `test`.`t` WHERE `c1`='1' AND `c2`='1' AND `c3`='1' AND `snrmin`='29.5' AND `snrmax`='33.5' AND `snravg`='31.6' LIMIT 1;
Empty set (0.00 sec)

while without quotes it works and row is properly identified:

slave1 [localhost] {msandbox} (test) > select * FROM `test`.`t` WHERE `c1`='1' AND `c2`='1' AND `c3`='1' AND `snrmin`=29.5 AND `snrmax`=33.5 AND `snravg`=31.6 LIMIT 1;
+------+------+------+--------+--------+--------+
| c1 | c2 | c3 | snrmin | snrmax | snravg |
+------+------+------+--------+--------+--------+
| 1 | 1 | 1 | 29.5 | 33.5 | 31.6 |
+------+------+------+--------+--------+--------+
1 row in set (0.00 sec)

To reproduce, run the following on master:

CREATE TABLE `t` (
`c1` int(10) DEFAULT NULL,
`c2` int(10) DEFAULT NULL,
`c3` int(10) DEFAULT NULL,
`snrmin` float(3,1) DEFAULT NULL,
`snrmax` float(3,1) DEFAULT NULL,
`snravg` float(3,1) DEFAULT NULL,
KEY `c2` (`c2`,`c3`)
) ENGINE=InnoDB;

Then make slave out of sync with INSERT like this:

INSERT INTO `test`.`t`(`c1`, `c2`, `c3`, `snrmin`, `snrmax`, `snravg`) VALUES (1,1,1,29.5,33.5,31.6);

and then try to sync slave with master, like this:

[openxs@chief percona-toolkit-2.2]$ bin/pt-table-sync --print --no-check-slave h=chief,P=18876,D=test,t=t,u=root h=chief,P=18877,D=test,t=t,u=root
DELETE FROM `test`.`t` WHERE `c1`='1' AND `c2`='1' AND `c3`='1' AND `snrmin`='29.5' AND `snrmax`='33.5' AND `snravg`='31.6' LIMIT 1 /*percona-toolkit src_db:test src_tbl:t src_dsn:D=test,P=18876,h=chief,t=t,u=root dst_db:test dst_tbl:t dst_dsn:D=test,P=18877,h=chief,t=t,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:3166 user:openxs host:chief*/;

Related branches

tags: added: i35438
Changed in percona-toolkit:
status: New → Confirmed
summary: - pt-table-sync always quote float values and this may prevent proper
- syncing
+ pt-table-sync quotes floats, prevents syncing
Changed in percona-toolkit:
status: Confirmed → In Progress
importance: Undecided → High
assignee: nobody → Daniel Nichter (daniel-nichter)
milestone: none → 2.2.5
tags: added: percona-35438 pt-table-sync value-quoting
removed: i35438
summary: - pt-table-sync quotes floats, prevents syncing
+ pt-table-sync quotes float values, can't sync
Changed in percona-toolkit:
status: In Progress → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Damon (894615808-b) wrote :

I have a problem as same above , I used pt-table-sync version is 2.2.11 , but i can't sync delete record .

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

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

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.