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

[Daniel Nichter]

[Daniel Nichter (daniel-nichter)]

review: Approve

=== 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 @@
    return $val if $val =~ m/^0x[0-9a-fA-F]+$/  # quote hex data
                   && !$args{is_char};          # unless is_char is true
 
+   return $val if $args{is_float};
+
    $val =~ s/(['\\])/\\$1/g;
    return "'$val'";
 }
@@ -3510,10 +3512,15 @@
    my $types = $self->{tbl_struct}->{type_for};
    return "UPDATE $self->{dst_db_tbl} SET "
       . join(', ', map {
-            my $is_char = ($types->{$_} || '') =~ m/char|text/i;
+            my $is_char  = ($types->{$_} || '') =~ m/char|text/i;
+            my $is_float = ($types->{$_} || '') =~ m/float|double/i;
             $self->{Quoter}->quote($_)
-            . '=' .  $self->{Quoter}->quote_val($row->{$_},
-                                              is_char => $is_char);
+            . '='
+            .  $self->{Quoter}->quote_val(
+                  $row->{$_},
+                  is_char  => $is_char,
+                  is_float => $is_float,
+            );
          } grep { !$in_where{$_} } @cols)
       . " WHERE $where LIMIT 1";
 }
@@ -3552,10 +3559,16 @@
    return "$verb INTO $self->{dst_db_tbl}("
       . join(', ', map { $q->quote($_) } @cols)
       . ') VALUES ('
-      . join(', ', map {
-               my $is_char = ($type_for->{$_} || '') =~ m/char|text/i;
-               $q->quote_val($row->{$_},
-                           is_char => $is_char) } @cols )
+      . join(', ',
+            map {
+               my $is_char  = ($type_for->{$_} || '') =~ m/char|text/i;
+               my $is_float = ($type_for->{$_} || '') =~ m/float|double/i;
+               $q->quote_val(
+                     $row->{$_},
+                     is_char  => $is_char,
+                     is_float => $is_float,
+               )
+            } @cols)
       . ')';
 }
 
@@ -3564,9 +3577,11 @@
    my @clauses = map {
       my $val = $row->{$_};
       my $sep = defined $val ? '=' : ' IS ';
-      my $is_char = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/char|text/i;
+      my $is_char  = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/char|text/i;
+      my $is_float = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/float|double/i;
       $self->{Quoter}->quote($_) . $sep . $self->{Quoter}->quote_val($val,
-                                              is_char => $is_char);
+                                              is_char  => $is_char,
+                                              is_float => $is_float);
    } @$cols;
    return join(' AND ', @clauses);
 }

=== 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 @@
    my $types = $self->{tbl_struct}->{type_for};
    return "UPDATE $self->{dst_db_tbl} SET "
       . join(', ', map {
-            my $is_char = ($types->{$_} || '') =~ m/char|text/i;
+            my $is_char  = ($types->{$_} || '') =~ m/char|text/i;
+            my $is_float = ($types->{$_} || '') =~ m/float|double/i;
             $self->{Quoter}->quote($_)
-            . '=' .  $self->{Quoter}->quote_val($row->{$_},
-                                              is_char => $is_char);
+            . '='
+            .  $self->{Quoter}->quote_val(
+                  $row->{$_},
+                  is_char  => $is_char,
+                  is_float => $is_float,
+            );
          } grep { !$in_where{$_} } @cols)
       . " WHERE $where LIMIT 1";
 }
@@ -399,10 +404,16 @@
    return "$verb INTO $self->{dst_db_tbl}("
       . join(', ', map { $q->quote($_) } @cols)
       . ') VALUES ('
-      . join(', ', map {
-               my $is_char = ($type_for->{$_} || '') =~ m/char|text/i;
-               $q->quote_val($row->{$_},
-                           is_char => $is_char) } @cols )
+      . join(', ',
+            map {
+               my $is_char  = ($type_for->{$_} || '') =~ m/char|text/i;
+               my $is_float = ($type_for->{$_} || '') =~ m/float|double/i;
+               $q->quote_val(
+                     $row->{$_},
+                     is_char  => $is_char,
+                     is_float => $is_float,
+               )
+            } @cols)
       . ')';
 }
 
@@ -420,9 +431,11 @@
    my @clauses = map {
       my $val = $row->{$_};
       my $sep = defined $val ? '=' : ' IS ';
-      my $is_char = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/char|text/i;
+      my $is_char  = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/char|text/i;
+      my $is_float = ($self->{tbl_struct}->{type_for}->{$_} || '') =~ m/float|double/i;
       $self->{Quoter}->quote($_) . $sep . $self->{Quoter}->quote_val($val,
-                                              is_char => $is_char);
+                                              is_char  => $is_char,
+                                              is_float => $is_float);
    } @$cols;
    return join(' AND ', @clauses);
 }

=== 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 @@
    return $val if $val =~ m/^0x[0-9a-fA-F]+$/  # quote hex data
                   && !$args{is_char};          # unless is_char is true
 
+   # https://bugs.launchpad.net/percona-toolkit/+bug/1229861
+   return $val if $args{is_float};
+
    # Quote and return non-numeric vals.
    $val =~ s/(['\\])/\\$1/g;
    return "'$val'";

=== 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 @@
 use warnings FATAL => 'all';
 use English qw(-no_match_vars);
 use Test::More;
+use Data::Dumper;
 
 use PerconaTest;
 use Sandbox;
@@ -27,21 +28,18 @@
 elsif ( !$slave_dbh ) {
    plan skip_all => 'Cannot connect to sandbox slave';
 }
-else {
-   plan tests => 3;
-}
-
-$sb->wipe_clean($master_dbh);
-$sb->wipe_clean($slave_dbh);
+
+my $master_dsn = $sb->dsn_for('master');
+my $slave1_dsn = $sb->dsn_for('slave1');
+
+# #############################################################################
+# Issue 410: mk-table-sync doesn't have --float-precision
+# #############################################################################
+
 $sb->create_dbs($master_dbh, ['test']);
-
-# #############################################################################
-# Issue 410: mk-table-sync doesn't have --float-precision
-# #############################################################################
-
 $master_dbh->do('create table test.fl (id int not null primary key, f float(12,10), d double)');
 $master_dbh->do('insert into test.fl values (1, 1.0000012, 2.0000012)');
-sleep 1;
+$sb->wait_for_slaves();
 $slave_dbh->do('update test.fl set d = 2.0000013 where id = 1');
 
 # The columns really are different at this point so we should
@@ -50,7 +48,7 @@
 $output = remove_traces($output);
 is(
    $output,
-   "REPLACE INTO `test`.`fl`(`id`, `f`, `d`) VALUES ('1', '1.0000011921', '2.0000012');
+   "REPLACE INTO `test`.`fl`(`id`, `f`, `d`) VALUES ('1', 1.0000011921, 2.0000012);
 ",
    'No --float-precision so double col diff at high precision (issue 410)'
 );
@@ -66,9 +64,33 @@
 );
 
 # #############################################################################
+# pt-table-sync quotes floats, prevents syncing
+# https://bugs.launchpad.net/percona-toolkit/+bug/1229861
+# #############################################################################
+
+$sb->load_file('master', "t/pt-table-sync/samples/sync-float.sql");
+$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)");
+
+$output = output(sub {
+      pt_table_sync::main(
+         "$master_dsn,D=sync_float_1229861,t=t",
+         "$slave1_dsn",
+         qw(--no-check-slave --print --execute))
+   },
+   stderr => 1,
+);
+
+my $rows = $slave_dbh->selectall_arrayref("SELECT * FROM sync_float_1229861.t");
+is_deeply(
+   $rows,
+   [],
+   "Sync rows with float values (bug 1229861)"
+) or diag(Dumper($rows), $output);
+
+# #############################################################################
 # Done.
 # #############################################################################
 $sb->wipe_clean($master_dbh);
 $sb->wipe_clean($slave_dbh);
 ok($sb->ok(), "Sandbox servers") or BAIL_OUT(__FILE__ . " broke the sandbox");
-exit;
+done_testing;

=== 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 @@
+DROP DATABASE IF EXISTS sync_float_1229861;
+CREATE DATABASE sync_float_1229861;
+USE sync_float_1229861;
+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;