Merge lp:~percona-toolkit-dev/percona-toolkit/pt-table-checksum-does-not-work-with-sql_mode-ONLY_FULL_GROUP_BY-1019479 into lp:~percona-toolkit-dev/percona-toolkit/release-2.2.13

[Frank Cizmich]

Problem:
pt-table-checksum fails when the sql_mode ONLY_FULL_GROUP_BY is set, even though the query that fails is deterministic.

Solution:
disable this mode during the session. (leave all other modes on)

Notes:
- tried to modify the query to comply, but it is dynamically generated on the fly according to different parameters so a solution along these lines was either not elegant or not efficient.

- important issue to fix for upcoming 5.7 version since this sql_mode will be on by default.

[Daniel Nichter (daniel-nichter)]

review: Approve

=== modified file 'bin/pt-table-checksum'
--- bin/pt-table-checksum	2014-11-11 13:28:27 +0000
+++ bin/pt-table-checksum	2015-01-06 18:03:52 +0000
@@ -9131,6 +9131,26 @@
       return if $o->get('explain');
       my $sql;
 
+      # https://bugs.launchpad.net/percona-toolkit/+bug/1019479
+      # sql_mode ONLY_FULL_GROUP_BY often raises error even when query is
+      # safe and deterministic. It's best to turn it off for the session
+      # at this point.
+      $sql = 'SELECT @@SQL_MODE';
+      PTDEBUG && _d($dbh, $sql);
+      my ($sql_mode) = eval { $dbh->selectrow_array($sql) };
+      if ( $EVAL_ERROR ) {
+         die "Error getting the current SQL_MODE: $EVAL_ERROR";
+      }
+      $sql_mode =~ s/ONLY_FULL_GROUP_BY//i;
+      $sql = qq[SET SQL_MODE='$sql_mode'];
+      PTDEBUG && _d($dbh, $sql);
+      eval { $dbh->do($sql) };
+      if ( $EVAL_ERROR ) {
+         die "Error setting SQL_MODE"
+           . ": $EVAL_ERROR";
+      }
+ 
+
       # https://bugs.launchpad.net/percona-toolkit/+bug/919352
       # The tool shouldn't blindly attempt to change binlog_format;
       # instead, it should check if it's already set to STATEMENT.
 
=== modified file 't/pt-table-checksum/basics.t'
--- t/pt-table-checksum/basics.t	2014-08-05 19:51:34 +0000
+++ t/pt-table-checksum/basics.t	2015-01-06 18:03:52 +0000
@@ -56,6 +56,7 @@
    $master_dbh->do("use $repl_db");
 }
 
+
 # ############################################################################
 # Default checksum and results.  The tool does not technically require any
 # options on well-configured systems (which the test env cannot be).  With
@@ -509,6 +510,24 @@
 );
 
 # #############################################################################
+# Bug 1019479: does not work with sql_mode ONLY_FULL_GROUP_BY
+# #############################################################################
+
+# add a couple more modes to test that commas don't affect setting
+$master_dbh->do("SET sql_mode = 'NO_ZERO_DATE,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES'");
+
+# force chunk-size because bug doesn't show up if table done in one chunk 
+$exit_status = pt_table_checksum::main(@args,
+   qw(--quiet --quiet -t sakila.actor --chunk-size=50));
+
+is(
+   $exit_status,
+   0,
+   "sql_mode ONLY_FULL_GROUP_BY is overidden"
+);
+
+
+# #############################################################################
 # Done.
 # #############################################################################
 $sb->wipe_clean($master_dbh);