Percona Toolkit moved to https://jira.percona.com/projects/PT

Merge lp:~percona-toolkit-dev/percona-toolkit/fix-sync-float-bug-1229861 into lp:~percona-toolkit-dev/percona-toolkit/release-2.2.5

  1. fix-sync-float-bug-1229861
  2. Merge into release-2.2.5
Proposed by Daniel Nichter
Status: Merged
Approved by: Daniel Nichter
Approved revision: 632
Merged at revision: 631
Proposed branch: lp:~percona-toolkit-dev/percona-toolkit/fix-sync-float-bug-1229861
Merge into: lp:~percona-toolkit-dev/percona-toolkit/release-2.2.5
Diff against target: 242 lines (+97/-32)
5 files modified
bin/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)
To merge this branch: bzr merge lp:~percona-toolkit-dev/percona-toolkit/fix-sync-float-bug-1229861
Reviewer Review Type Date Requested Status
Daniel Nichter Approve
Review via email: mp+190506@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Daniel Nichter (daniel-nichter) :
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'bin/pt-table-sync'
--- bin/pt-table-sync 2013-08-08 19:28:36 +0000
+++ bin/pt-table-sync 2013-10-10 22:59:17 +0000
@@ -1873,6 +1873,8 @@
1873 return $val if $val =~ m/^0x[0-9a-fA-F]+$/ # quote hex data1873 return $val if $val =~ m/^0x[0-9a-fA-F]+$/ # quote hex data
1874 && !$args{is_char}; # unless is_char is true1874 && !$args{is_char}; # unless is_char is true
18751875
1876 return $val if $args{is_float};
1877
1876 $val =~ s/(['\\])/\\$1/g;1878 $val =~ s/(['\\])/\\$1/g;
1877 return "'$val'";1879 return "'$val'";
1878}1880}
@@ -3510,10 +3512,15 @@
3510 my $types = $self->{tbl_struct}->{type_for};3512 my $types = $self->{tbl_struct}->{type_for};
3511 return "UPDATE $self->{dst_db_tbl} SET "3513 return "UPDATE $self->{dst_db_tbl} SET "
3512 . join(', ', map {3514 . join(', ', map {
3513 my $is_char = ($types->{$_} || '') =~ m/char|text/i;3515 my $is_char = ($types->{$_} || '') =~ m/char|text/i;
3516 my $is_float = ($types->{$_} || '') =~ m/float|double/i;
3514 $self->{Quoter}->quote($_)3517 $self->{Quoter}->quote($_)
3515 . '=' . $self->{Quoter}->quote_val($row->{$_},3518 . '='
3516 is_char => $is_char);3519 . $self->{Quoter}->quote_val(
3520 $row->{$_},
3521 is_char => $is_char,
3522 is_float => $is_float,
3523 );
3517 } grep { !$in_where{$_} } @cols)3524 } grep { !$in_where{$_} } @cols)
3518 . " WHERE $where LIMIT 1";3525 . " WHERE $where LIMIT 1";
3519}3526}
@@ -3552,10 +3559,16 @@
3552 return "$verb INTO $self->{dst_db_tbl}("3559 return "$verb INTO $self->{dst_db_tbl}("
3553 . join(', ', map { $q->quote($_) } @cols)3560 . join(', ', map { $q->quote($_) } @cols)
3554 . ') VALUES ('3561 . ') VALUES ('
3555 . join(', ', map {3562 . join(', ',
3556 my $is_char = ($type_for->{$_} || '') =~ m/char|text/i;3563 map {
3557 $q->quote_val($row->{$_},3564 my $is_char = ($type_for->{$_} || '') =~ m/char|text/i;
3558 is_char => $is_char) } @cols )3565 my $is_float = ($type_for->{$_} || '') =~ m/float|double/i;
3566 $q->quote_val(
3567 $row->{$_},
3568 is_char => $is_char,
3569 is_float => $is_float,
3570 )
3571 } @cols)
3559 . ')';3572 . ')';
3560}3573}
35613574
@@ -3564,9 +3577,11 @@
3564 my @clauses = map {3577 my @clauses = map {
3565 my $val = $row->{$_};3578 my $val = $row->{$_};
3566 my $sep = defined $val ? '=' : ' IS ';3579 my $sep = defined $val ? '=' : ' IS ';
3567 my $is_char = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/char|text/i;3580 my $is_char = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/char|text/i;
3581 my $is_float = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/float|double/i;
3568 $self->{Quoter}->quote($_) . $sep . $self->{Quoter}->quote_val($val,3582 $self->{Quoter}->quote($_) . $sep . $self->{Quoter}->quote_val($val,
3569 is_char => $is_char);3583 is_char => $is_char,
3584 is_float => $is_float);
3570 } @$cols;3585 } @$cols;
3571 return join(' AND ', @clauses);3586 return join(' AND ', @clauses);
3572}3587}
35733588
=== modified file 'lib/ChangeHandler.pm'
--- lib/ChangeHandler.pm 2013-01-03 00:19:16 +0000
+++ lib/ChangeHandler.pm 2013-10-10 22:59:17 +0000
@@ -326,10 +326,15 @@
326 my $types = $self->{tbl_struct}->{type_for};326 my $types = $self->{tbl_struct}->{type_for};
327 return "UPDATE $self->{dst_db_tbl} SET "327 return "UPDATE $self->{dst_db_tbl} SET "
328 . join(', ', map {328 . join(', ', map {
329 my $is_char = ($types->{$_} || '') =~ m/char|text/i;329 my $is_char = ($types->{$_} || '') =~ m/char|text/i;
330 my $is_float = ($types->{$_} || '') =~ m/float|double/i;
330 $self->{Quoter}->quote($_)331 $self->{Quoter}->quote($_)
331 . '=' . $self->{Quoter}->quote_val($row->{$_},332 . '='
332 is_char => $is_char);333 . $self->{Quoter}->quote_val(
334 $row->{$_},
335 is_char => $is_char,
336 is_float => $is_float,
337 );
333 } grep { !$in_where{$_} } @cols)338 } grep { !$in_where{$_} } @cols)
334 . " WHERE $where LIMIT 1";339 . " WHERE $where LIMIT 1";
335}340}
@@ -399,10 +404,16 @@
399 return "$verb INTO $self->{dst_db_tbl}("404 return "$verb INTO $self->{dst_db_tbl}("
400 . join(', ', map { $q->quote($_) } @cols)405 . join(', ', map { $q->quote($_) } @cols)
401 . ') VALUES ('406 . ') VALUES ('
402 . join(', ', map {407 . join(', ',
403 my $is_char = ($type_for->{$_} || '') =~ m/char|text/i;408 map {
404 $q->quote_val($row->{$_},409 my $is_char = ($type_for->{$_} || '') =~ m/char|text/i;
405 is_char => $is_char) } @cols )410 my $is_float = ($type_for->{$_} || '') =~ m/float|double/i;
411 $q->quote_val(
412 $row->{$_},
413 is_char => $is_char,
414 is_float => $is_float,
415 )
416 } @cols)
406 . ')';417 . ')';
407}418}
408419
@@ -420,9 +431,11 @@
420 my @clauses = map {431 my @clauses = map {
421 my $val = $row->{$_};432 my $val = $row->{$_};
422 my $sep = defined $val ? '=' : ' IS ';433 my $sep = defined $val ? '=' : ' IS ';
423 my $is_char = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/char|text/i;434 my $is_char = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/char|text/i;
435 my $is_float = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/float|double/i;
424 $self->{Quoter}->quote($_) . $sep . $self->{Quoter}->quote_val($val,436 $self->{Quoter}->quote($_) . $sep . $self->{Quoter}->quote_val($val,
425 is_char => $is_char);437 is_char => $is_char,
438 is_float => $is_float);
426 } @$cols;439 } @$cols;
427 return join(' AND ', @clauses);440 return join(' AND ', @clauses);
428}441}
429442
=== modified file 'lib/Quoter.pm'
--- lib/Quoter.pm 2013-02-19 20:01:58 +0000
+++ lib/Quoter.pm 2013-10-10 22:59:17 +0000
@@ -77,6 +77,9 @@
77 return $val if $val =~ m/^0x[0-9a-fA-F]+$/ # quote hex data77 return $val if $val =~ m/^0x[0-9a-fA-F]+$/ # quote hex data
78 && !$args{is_char}; # unless is_char is true78 && !$args{is_char}; # unless is_char is true
7979
80 # https://bugs.launchpad.net/percona-toolkit/+bug/1229861
81 return $val if $args{is_float};
82
80 # Quote and return non-numeric vals.83 # Quote and return non-numeric vals.
81 $val =~ s/(['\\])/\\$1/g;84 $val =~ s/(['\\])/\\$1/g;
82 return "'$val'";85 return "'$val'";
8386
=== modified file 't/pt-table-sync/float_precision.t'
--- t/pt-table-sync/float_precision.t 2012-07-11 18:21:47 +0000
+++ t/pt-table-sync/float_precision.t 2013-10-10 22:59:17 +0000
@@ -10,6 +10,7 @@
10use warnings FATAL => 'all';10use warnings FATAL => 'all';
11use English qw(-no_match_vars);11use English qw(-no_match_vars);
12use Test::More;12use Test::More;
13use Data::Dumper;
1314
14use PerconaTest;15use PerconaTest;
15use Sandbox;16use Sandbox;
@@ -27,21 +28,18 @@
27elsif ( !$slave_dbh ) {28elsif ( !$slave_dbh ) {
28 plan skip_all => 'Cannot connect to sandbox slave';29 plan skip_all => 'Cannot connect to sandbox slave';
29}30}
30else {31
31 plan tests => 3;32my $master_dsn = $sb->dsn_for('master');
32}33my $slave1_dsn = $sb->dsn_for('slave1');
3334
34$sb->wipe_clean($master_dbh);35# #############################################################################
35$sb->wipe_clean($slave_dbh);36# Issue 410: mk-table-sync doesn't have --float-precision
37# #############################################################################
38
36$sb->create_dbs($master_dbh, ['test']);39$sb->create_dbs($master_dbh, ['test']);
37
38# #############################################################################
39# Issue 410: mk-table-sync doesn't have --float-precision
40# #############################################################################
41
42$master_dbh->do('create table test.fl (id int not null primary key, f float(12,10), d double)');40$master_dbh->do('create table test.fl (id int not null primary key, f float(12,10), d double)');
43$master_dbh->do('insert into test.fl values (1, 1.0000012, 2.0000012)');41$master_dbh->do('insert into test.fl values (1, 1.0000012, 2.0000012)');
44sleep 1;42$sb->wait_for_slaves();
45$slave_dbh->do('update test.fl set d = 2.0000013 where id = 1');43$slave_dbh->do('update test.fl set d = 2.0000013 where id = 1');
4644
47# The columns really are different at this point so we should45# The columns really are different at this point so we should
@@ -50,7 +48,7 @@
50$output = remove_traces($output);48$output = remove_traces($output);
51is(49is(
52 $output,50 $output,
53 "REPLACE INTO `test`.`fl`(`id`, `f`, `d`) VALUES ('1', '1.0000011921', '2.0000012');51 "REPLACE INTO `test`.`fl`(`id`, `f`, `d`) VALUES ('1', 1.0000011921, 2.0000012);
54",52",
55 'No --float-precision so double col diff at high precision (issue 410)'53 'No --float-precision so double col diff at high precision (issue 410)'
56);54);
@@ -66,9 +64,33 @@
66);64);
6765
68# #############################################################################66# #############################################################################
67# pt-table-sync quotes floats, prevents syncing
68# https://bugs.launchpad.net/percona-toolkit/+bug/1229861
69# #############################################################################
70
71$sb->load_file('master', "t/pt-table-sync/samples/sync-float.sql");
72$slave_dbh->do("INSERT INTO sync_float_1229861.t (`c1`, `c2`, `c3`, `snrmin`, `snrmax`, `snravg`) VALUES (1,1,1,29.5,33.5,31.6)");
73
74$output = output(sub {
75 pt_table_sync::main(
76 "$master_dsn,D=sync_float_1229861,t=t",
77 "$slave1_dsn",
78 qw(--no-check-slave --print --execute))
79 },
80 stderr => 1,
81);
82
83my $rows = $slave_dbh->selectall_arrayref("SELECT * FROM sync_float_1229861.t");
84is_deeply(
85 $rows,
86 [],
87 "Sync rows with float values (bug 1229861)"
88) or diag(Dumper($rows), $output);
89
90# #############################################################################
69# Done.91# Done.
70# #############################################################################92# #############################################################################
71$sb->wipe_clean($master_dbh);93$sb->wipe_clean($master_dbh);
72$sb->wipe_clean($slave_dbh);94$sb->wipe_clean($slave_dbh);
73ok($sb->ok(), "Sandbox servers") or BAIL_OUT(__FILE__ . " broke the sandbox");95ok($sb->ok(), "Sandbox servers") or BAIL_OUT(__FILE__ . " broke the sandbox");
74exit;96done_testing;
7597
=== added file 't/pt-table-sync/samples/sync-float.sql'
--- t/pt-table-sync/samples/sync-float.sql 1970-01-01 00:00:00 +0000
+++ t/pt-table-sync/samples/sync-float.sql 2013-10-10 22:59:17 +0000
@@ -0,0 +1,12 @@
1DROP DATABASE IF EXISTS sync_float_1229861;
2CREATE DATABASE sync_float_1229861;
3USE sync_float_1229861;
4CREATE TABLE `t` (
5 `c1` int(10) DEFAULT NULL,
6 `c2` int(10) DEFAULT NULL,
7 `c3` int(10) DEFAULT NULL,
8 `snrmin` float(3,1) DEFAULT NULL,
9 `snrmax` float(3,1) DEFAULT NULL,
10 `snravg` float(3,1) DEFAULT NULL,
11 KEY `c2` (`c2`,`c3`)
12) ENGINE=InnoDB;

Subscribers

People subscribed via source and target branches

to all changes: