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  Edit
Everyone can see this information.

Other bug subscribers