pt-table-sync quotes float values, can't sync
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
$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,
and then try to sync slave with master, like this:
[openxs@chief percona-
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:
Related branches
- Daniel Nichter: Approve
-
Diff: 242 lines (+97/-32)5 files modifiedbin/pt-table-sync (+24/-9)
lib/ChangeHandler.pm (+22/-9)
lib/Quoter.pm (+3/-0)
t/pt-table-sync/float_precision.t (+36/-14)
t/pt-table-sync/samples/sync-float.sql (+12/-0)
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 |
I have a problem as same above , I used pt-table-sync version is 2.2.11 , but i can't sync delete record .