Merge lp:~percona-toolkit-dev/percona-toolkit/pt-table-checksum-fails-on-BINARY-field-in-PK-1381280 into lp:~percona-toolkit-dev/percona-toolkit/release-2.2.14

[Frank Cizmich]

Problem:

pt-table-checksum fails on tables where a binary column is used as key. This is because the upper and lower boundary column of the checksum table is of type text and fails when storing these keys.

Solution/Workaround:

Good results have been obtained changing checksum datatype to binary (blob)
Since a foolproof testing of this approach in all cases is very difficult, a less risky compromise approach is used: the --binary-key option is provided and documented so as to permit users to create the checksum table with blob datatype.

[Daniel Nichter (daniel-nichter)]

PT tries to use "index" instead of "key" because it's a little more proper (CREATE INDEX, not CREATE KEY; --chunk-index; etc.), so I'd suggest --binary-index as the option name.

MySQL columns types are usually written as uppercase...

[lp:~percona-toolkit-dev/percona-toolkit/pt-table-checksum-fails-on-BINARY-field-in-PK-1381280]

615. By Frank Cizmich on 2015-04-06

pt-t-c changed text of warnings and docs. Minor tweak of test for Ubuntu 14

[Frank Cizmich (frank-cizmich)]

Changed texts and option name as suggested.

Also, unrelated, tweaked progress.t test, which somehow failed on ubuntu 14.04 unless I added a second of delay in one of the tests (both on virtualbox and my own)

[Daniel Nichter (daniel-nichter)]

s/datatype/data type/g :-) Otherwise fine by me.

review: Approve

[lp:~percona-toolkit-dev/percona-toolkit/pt-table-checksum-fails-on-BINARY-field-in-PK-1381280]

616. By Frank Cizmich on 2015-04-07

minor typo

=== modified file 'bin/pt-table-checksum'
--- bin/pt-table-checksum	2015-01-23 10:19:56 +0000
+++ bin/pt-table-checksum	2015-04-07 21:06:09 +0000
@@ -10866,6 +10866,7 @@
          }
       }
    }
+
    
    # USE the correct db (probably the repl db, but maybe --replicate-database).
    use_repl_db(%args);
@@ -10888,6 +10889,7 @@
         . "You need to create the table.\n";
    }
 
+
    # We used to check the table privs here, but:
    # https://bugs.launchpad.net/percona-toolkit/+bug/916168
 
@@ -10974,6 +10976,18 @@
       }
    }
 
+   if ( $o->get('binary-index') ) {
+      PTDEBUG && _d('--binary-index : checking if replicate table has binary type columns');
+      my $create_table = $tp->get_create_table( $dbh, $db, $tbl );
+      if (  $create_table !~ /lower_boundary`?\s+BLOB/si 
+         || $create_table !~ /upper_boundary`?\s+BLOB/si ) 
+      {
+         die "--binary-index was specified but the current checksum table ($db.$tbl) uses" 
+         ." TEXT columns. To use BLOB columns, drop the current checksum table, then recreate" 
+         ." it by specifying --create-replicate-table --binary-index.";
+      }
+   }
+
    return;  # success, repl table is ready to go
 }
 
@@ -11132,6 +11146,10 @@
    my $sql = $o->read_para_after(__FILE__, qr/MAGIC_create_replicate/);
    $sql =~ s/CREATE TABLE checksums/CREATE TABLE IF NOT EXISTS $repl_table/;
    $sql =~ s/;$//;
+   if ( $o->get('binary-index') ) {
+      $sql =~ s/`?lower_boundary`?\s+TEXT/`lower_boundary` BLOB/is;
+      $sql =~ s/`?upper_boundary`?\s+TEXT/`upper_boundary` BLOB/is;
+   }
    PTDEBUG && _d($dbh, $sql);
    eval {
       $dbh->do($sql);
@@ -11808,6 +11826,15 @@
 
 See "Replicas using row-based replication" under L<"LIMITATIONS">.
 
+=item --binary-index
+
+This option modifies the behavior of L<"--create-replicate-table"> such that the
+replicate table's upper and lower boundary columns are created with the BLOB 
+data type.
+This is useful in cases where you have trouble checksuming tables with keys that 
+include a binary data type or that have non-standard character sets.
+See L<"--replicate">.
+
 =item --check-interval
 
 type: time; default: 1; group: Throttle
@@ -12342,22 +12369,24 @@
 structure (MAGIC_create_replicate):
 
   CREATE TABLE checksums (
-     db             char(64)     NOT NULL,
-     tbl            char(64)     NOT NULL,
-     chunk          int          NOT NULL,
-     chunk_time     float            NULL,
-     chunk_index    varchar(200)     NULL,
-     lower_boundary text             NULL,
-     upper_boundary text             NULL,
-     this_crc       char(40)     NOT NULL,
-     this_cnt       int          NOT NULL,
-     master_crc     char(40)         NULL,
-     master_cnt     int              NULL,
-     ts             timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+     db             CHAR(64)     NOT NULL,
+     tbl            CHAR(64)     NOT NULL,
+     chunk          INT          NOT NULL,
+     chunk_time     FLOAT            NULL,
+     chunk_index    VARCHAR(200)     NULL,
+     lower_boundary TEXT             NULL,
+     upper_boundary TEXT             NULL,
+     this_crc       CHAR(40)     NOT NULL,
+     this_cnt       INT          NOT NULL,
+     master_crc     CHAR(40)         NULL,
+     master_cnt     INT              NULL,
+     ts             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (db, tbl, chunk),
      INDEX ts_db_tbl (ts, db, tbl)
   ) ENGINE=InnoDB;
 
+Note: lower_boundary and upper_boundary data type can be BLOB. See L<"--binary-index">.
+
 By default, L<"--[no]create-replicate-table"> is true, so the database and
 the table specified by this option are created automatically if they do not
 exist.

=== modified file 't/pt-table-checksum/progress.t'
--- t/pt-table-checksum/progress.t	2013-03-02 02:02:13 +0000
+++ t/pt-table-checksum/progress.t	2015-04-07 21:06:09 +0000
@@ -68,7 +68,7 @@
 # then starts it again.
 # TEST_WISHLIST PLUGIN_WISHLIST: do this with a plugin to the tool itself,
 # not in this unreliable fashion.
-system("$trunk/util/wait-to-exec '$scripts/wait-for-chunk.sh 12345 sakila city 1' '$scripts/exec-wait-exec.sh 12347 \"stop slave sql_thread\" 2 \"start slave sql_thread\"' 3 >/dev/null &");
+system("$trunk/util/wait-to-exec '$scripts/wait-for-chunk.sh 12345 sakila city 1' '$scripts/exec-wait-exec.sh 12347 \"stop slave sql_thread\" 2 \"start slave sql_thread\"' 4 >/dev/null &");
 
 $output = output(
    sub { pt_table_checksum::main(@args, qw(-d sakila)); },