Merge lp:~percona-toolkit-dev/percona-toolkit/detect-key-len-with-range-scan into lp:percona-toolkit/2.1

Proposed by Daniel Nichter
Status: Merged
Merged at revision: 284
Proposed branch: lp:~percona-toolkit-dev/percona-toolkit/detect-key-len-with-range-scan
Merge into: lp:percona-toolkit/2.1
Diff against target: 799 lines (+666/-31)
5 files modified
bin/pt-online-schema-change (+164/-12)
bin/pt-table-checksum (+164/-12)
lib/IndexLength.pm (+175/-0)
t/lib/IndexLength.pm (+135/-0)
t/pt-table-checksum/chunk_index.t (+28/-7)
To merge this branch: bzr merge lp:~percona-toolkit-dev/percona-toolkit/detect-key-len-with-range-scan
Reviewer Review Type Date Requested Status
Daniel Nichter Approve
Review via email: mp+109679@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-online-schema-change'
--- bin/pt-online-schema-change 2012-06-11 12:15:48 +0000
+++ bin/pt-online-schema-change 2012-06-11 16:59:23 +0000
@@ -5128,6 +5128,158 @@
5128# ###########################################################################5128# ###########################################################################
51295129
5130# ###########################################################################5130# ###########################################################################
5131# IndexLength package
5132# This package is a copy without comments from the original. The original
5133# with comments and its test file can be found in the Bazaar repository at,
5134# lib/IndexLength.pm
5135# t/lib/IndexLength.t
5136# See https://launchpad.net/percona-toolkit for more information.
5137# ###########################################################################
5138{
5139
5140package IndexLength;
5141
5142use strict;
5143use warnings FATAL => 'all';
5144use English qw(-no_match_vars);
5145use constant PTDEBUG => $ENV{PTDEBUG} || 0;
5146
5147use Data::Dumper;
5148$Data::Dumper::Indent = 1;
5149$Data::Dumper::Sortkeys = 1;
5150$Data::Dumper::Quotekeys = 0;
5151
5152sub new {
5153 my ( $class, %args ) = @_;
5154 my @required_args = qw(Quoter);
5155 foreach my $arg ( @required_args ) {
5156 die "I need a $arg argument" unless $args{$arg};
5157 }
5158
5159 my $self = {
5160 Quoter => $args{Quoter},
5161 };
5162
5163 return bless $self, $class;
5164}
5165
5166sub index_length {
5167 my ($self, %args) = @_;
5168 my @required_args = qw(Cxn tbl index);
5169 foreach my $arg ( @required_args ) {
5170 die "I need a $arg argument" unless $args{$arg};
5171 }
5172 my ($cxn) = @args{@required_args};
5173
5174 die "The tbl argument does not have a tbl_struct"
5175 unless exists $args{tbl}->{tbl_struct};
5176 die "Index $args{index} does not exist in table $args{tbl}->{name}"
5177 unless $args{tbl}->{tbl_struct}->{keys}->{$args{index}};
5178
5179 my $index_struct = $args{tbl}->{tbl_struct}->{keys}->{$args{index}};
5180 my $index_cols = $index_struct->{cols};
5181 my $n_index_cols = $args{n_index_cols};
5182 if ( !$n_index_cols || $n_index_cols > @$index_cols ) {
5183 $n_index_cols = scalar @$index_cols;
5184 }
5185
5186 my $vals = $self->_get_first_values(
5187 %args,
5188 n_index_cols => $n_index_cols,
5189 );
5190
5191 my $sql = $self->_make_range_query(
5192 %args,
5193 n_index_cols => $n_index_cols,
5194 vals => $vals,
5195 );
5196 my $sth = $cxn->dbh()->prepare($sql);
5197 PTDEBUG && _d($sth->{Statement}, 'params:', @$vals);
5198 $sth->execute(@$vals);
5199 my $row = $sth->fetchrow_hashref();
5200 $sth->finish();
5201 PTDEBUG && _d('Range scan:', Dumper($row));
5202 return $row->{key_len}, $row->{key};
5203}
5204
5205sub _get_first_values {
5206 my ($self, %args) = @_;
5207 my @required_args = qw(Cxn tbl index n_index_cols);
5208 foreach my $arg ( @required_args ) {
5209 die "I need a $arg argument" unless $args{$arg};
5210 }
5211 my ($cxn, $tbl, $index, $n_index_cols) = @args{@required_args};
5212
5213 my $q = $self->{Quoter};
5214
5215 my $index_struct = $tbl->{tbl_struct}->{keys}->{$index};
5216 my $index_cols = $index_struct->{cols};
5217 my $index_columns = join (', ',
5218 map { $q->quote($_) } @{$index_cols}[0..($n_index_cols - 1)]);
5219
5220 my @where;
5221 foreach my $col ( @{$index_cols}[0..($n_index_cols - 1)] ) {
5222 push @where, $q->quote($col) . " IS NOT NULL"
5223 }
5224
5225 my $sql = "SELECT /*!40001 SQL_NO_CACHE */ $index_columns "
5226 . "FROM $tbl->{name} FORCE INDEX (" . $q->quote($index) . ") "
5227 . "WHERE " . join(' AND ', @where)
5228 . " ORDER BY $index_columns "
5229 . "LIMIT 1 /*key_len*/"; # only need 1 row
5230 PTDEBUG && _d($sql);
5231 my $vals = $cxn->dbh()->selectrow_arrayref($sql);
5232 return $vals;
5233}
5234
5235sub _make_range_query {
5236 my ($self, %args) = @_;
5237 my @required_args = qw(tbl index n_index_cols vals);
5238 foreach my $arg ( @required_args ) {
5239 die "I need a $arg argument" unless $args{$arg};
5240 }
5241 my ($tbl, $index, $n_index_cols, $vals) = @args{@required_args};
5242
5243 my $q = $self->{Quoter};
5244
5245 my $index_struct = $tbl->{tbl_struct}->{keys}->{$index};
5246 my $index_cols = $index_struct->{cols};
5247
5248 my @where;
5249 if ( $n_index_cols > 1 ) {
5250 foreach my $n ( 0..($n_index_cols - 2) ) {
5251 my $col = $index_cols->[$n];
5252 my $val = $vals->[$n];
5253 push @where, $q->quote($col) . " = ?";
5254 }
5255 }
5256
5257 my $col = $index_cols->[$n_index_cols - 1];
5258 my $val = $vals->[-1]; # should only be as many vals as cols
5259 push @where, $q->quote($col) . " >= ?";
5260
5261 my $sql = "EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * "
5262 . "FROM $tbl->{name} FORCE INDEX (" . $q->quote($index) . ") "
5263 . "WHERE " . join(' AND ', @where)
5264 . " /*key_len*/";
5265 return $sql;
5266}
5267
5268sub _d {
5269 my ($package, undef, $line) = caller 0;
5270 @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
5271 map { defined $_ ? $_ : 'undef' }
5272 @_;
5273 print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
5274}
5275
52761;
5277}
5278# ###########################################################################
5279# End IndexLength package
5280# ###########################################################################
5281
5282# ###########################################################################
5131# This is a combination of modules and programs in one -- a runnable module.5283# This is a combination of modules and programs in one -- a runnable module.
5132# http://www.perl.com/pub/a/2006/07/13/lightning-articles.html?page=last5284# http://www.perl.com/pub/a/2006/07/13/lightning-articles.html?page=last
5133# Or, look it up in the Camel book on pages 642 and 643 in the 3rd edition.5285# Or, look it up in the Camel book on pages 642 and 643 in the 3rd edition.
@@ -5908,30 +6060,30 @@
5908 }6060 }
5909 else { # chunking the table6061 else { # chunking the table
5910 if ( $o->get('check-plan') ) {6062 if ( $o->get('check-plan') ) {
5911 my $expl = explain_statement(6063 my $idx_len = new IndexLength(Quoter => $q);
5912 sth => $statements->{explain_first_lower_boundary},6064 my ($key_len, $key) = $idx_len->index_length(
5913 tbl => $tbl,6065 Cxn => $args{Cxn},
5914 vals => [],6066 tbl => $tbl,
6067 index => $nibble_iter->nibble_index(),
6068 n_index_cols => $o->get('chunk-index-columns'),
5915 );6069 );
5916 if ( !$expl->{key}6070 if ( !$key || lc($key) ne lc($nibble_iter->nibble_index()) ) {
5917 || lc($expl->{key}) ne lc($nibble_iter->nibble_index()) )
5918 {
5919 die "Cannot determine the key_len of the chunk index "6071 die "Cannot determine the key_len of the chunk index "
5920 . "because MySQL chose "6072 . "because MySQL chose "
5921 . ($expl->{key} ? "the $expl->{key}" : "no") . " index "6073 . ($key ? "the $key" : "no") . " index "
5922 . "instead of the " . $nibble_iter->nibble_index()6074 . "instead of the " . $nibble_iter->nibble_index()
5923 . " index for the first lower boundary statement. "6075 . " index for the first lower boundary statement. "
5924 . "See --[no]check-plan in the documentation for more "6076 . "See --[no]check-plan in the documentation for more "
5925 . "information.";6077 . "information.";
5926 }6078 }
5927 elsif ( !$expl->{key_len} ) {6079 elsif ( !$key_len ) {
5928 die "The key_len of the $expl->{key} index is "6080 die "The key_len of the $key index is "
5929 . (defined $expl->{key_len} ? "zero" : "NULL")6081 . (defined $key_len ? "zero" : "NULL")
5930 . ", but this should not be possible. " 6082 . ", but this should not be possible. "
5931 . "See --[no]check-plan in the documentation for more "6083 . "See --[no]check-plan in the documentation for more "
5932 . "information.";6084 . "information.";
5933 }6085 }
5934 $tbl->{key_len} = $expl->{key_len};6086 $tbl->{key_len} = $key_len;
5935 }6087 }
5936 }6088 }
59376089
59386090
=== modified file 'bin/pt-table-checksum'
--- bin/pt-table-checksum 2012-06-11 12:15:48 +0000
+++ bin/pt-table-checksum 2012-06-11 16:59:23 +0000
@@ -6081,6 +6081,158 @@
6081# ###########################################################################6081# ###########################################################################
60826082
6083# ###########################################################################6083# ###########################################################################
6084# IndexLength package
6085# This package is a copy without comments from the original. The original
6086# with comments and its test file can be found in the Bazaar repository at,
6087# lib/IndexLength.pm
6088# t/lib/IndexLength.t
6089# See https://launchpad.net/percona-toolkit for more information.
6090# ###########################################################################
6091{
6092
6093package IndexLength;
6094
6095use strict;
6096use warnings FATAL => 'all';
6097use English qw(-no_match_vars);
6098use constant PTDEBUG => $ENV{PTDEBUG} || 0;
6099
6100use Data::Dumper;
6101$Data::Dumper::Indent = 1;
6102$Data::Dumper::Sortkeys = 1;
6103$Data::Dumper::Quotekeys = 0;
6104
6105sub new {
6106 my ( $class, %args ) = @_;
6107 my @required_args = qw(Quoter);
6108 foreach my $arg ( @required_args ) {
6109 die "I need a $arg argument" unless $args{$arg};
6110 }
6111
6112 my $self = {
6113 Quoter => $args{Quoter},
6114 };
6115
6116 return bless $self, $class;
6117}
6118
6119sub index_length {
6120 my ($self, %args) = @_;
6121 my @required_args = qw(Cxn tbl index);
6122 foreach my $arg ( @required_args ) {
6123 die "I need a $arg argument" unless $args{$arg};
6124 }
6125 my ($cxn) = @args{@required_args};
6126
6127 die "The tbl argument does not have a tbl_struct"
6128 unless exists $args{tbl}->{tbl_struct};
6129 die "Index $args{index} does not exist in table $args{tbl}->{name}"
6130 unless $args{tbl}->{tbl_struct}->{keys}->{$args{index}};
6131
6132 my $index_struct = $args{tbl}->{tbl_struct}->{keys}->{$args{index}};
6133 my $index_cols = $index_struct->{cols};
6134 my $n_index_cols = $args{n_index_cols};
6135 if ( !$n_index_cols || $n_index_cols > @$index_cols ) {
6136 $n_index_cols = scalar @$index_cols;
6137 }
6138
6139 my $vals = $self->_get_first_values(
6140 %args,
6141 n_index_cols => $n_index_cols,
6142 );
6143
6144 my $sql = $self->_make_range_query(
6145 %args,
6146 n_index_cols => $n_index_cols,
6147 vals => $vals,
6148 );
6149 my $sth = $cxn->dbh()->prepare($sql);
6150 PTDEBUG && _d($sth->{Statement}, 'params:', @$vals);
6151 $sth->execute(@$vals);
6152 my $row = $sth->fetchrow_hashref();
6153 $sth->finish();
6154 PTDEBUG && _d('Range scan:', Dumper($row));
6155 return $row->{key_len}, $row->{key};
6156}
6157
6158sub _get_first_values {
6159 my ($self, %args) = @_;
6160 my @required_args = qw(Cxn tbl index n_index_cols);
6161 foreach my $arg ( @required_args ) {
6162 die "I need a $arg argument" unless $args{$arg};
6163 }
6164 my ($cxn, $tbl, $index, $n_index_cols) = @args{@required_args};
6165
6166 my $q = $self->{Quoter};
6167
6168 my $index_struct = $tbl->{tbl_struct}->{keys}->{$index};
6169 my $index_cols = $index_struct->{cols};
6170 my $index_columns = join (', ',
6171 map { $q->quote($_) } @{$index_cols}[0..($n_index_cols - 1)]);
6172
6173 my @where;
6174 foreach my $col ( @{$index_cols}[0..($n_index_cols - 1)] ) {
6175 push @where, $q->quote($col) . " IS NOT NULL"
6176 }
6177
6178 my $sql = "SELECT /*!40001 SQL_NO_CACHE */ $index_columns "
6179 . "FROM $tbl->{name} FORCE INDEX (" . $q->quote($index) . ") "
6180 . "WHERE " . join(' AND ', @where)
6181 . " ORDER BY $index_columns "
6182 . "LIMIT 1 /*key_len*/"; # only need 1 row
6183 PTDEBUG && _d($sql);
6184 my $vals = $cxn->dbh()->selectrow_arrayref($sql);
6185 return $vals;
6186}
6187
6188sub _make_range_query {
6189 my ($self, %args) = @_;
6190 my @required_args = qw(tbl index n_index_cols vals);
6191 foreach my $arg ( @required_args ) {
6192 die "I need a $arg argument" unless $args{$arg};
6193 }
6194 my ($tbl, $index, $n_index_cols, $vals) = @args{@required_args};
6195
6196 my $q = $self->{Quoter};
6197
6198 my $index_struct = $tbl->{tbl_struct}->{keys}->{$index};
6199 my $index_cols = $index_struct->{cols};
6200
6201 my @where;
6202 if ( $n_index_cols > 1 ) {
6203 foreach my $n ( 0..($n_index_cols - 2) ) {
6204 my $col = $index_cols->[$n];
6205 my $val = $vals->[$n];
6206 push @where, $q->quote($col) . " = ?";
6207 }
6208 }
6209
6210 my $col = $index_cols->[$n_index_cols - 1];
6211 my $val = $vals->[-1]; # should only be as many vals as cols
6212 push @where, $q->quote($col) . " >= ?";
6213
6214 my $sql = "EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * "
6215 . "FROM $tbl->{name} FORCE INDEX (" . $q->quote($index) . ") "
6216 . "WHERE " . join(' AND ', @where)
6217 . " /*key_len*/";
6218 return $sql;
6219}
6220
6221sub _d {
6222 my ($package, undef, $line) = caller 0;
6223 @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
6224 map { defined $_ ? $_ : 'undef' }
6225 @_;
6226 print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
6227}
6228
62291;
6230}
6231# ###########################################################################
6232# End IndexLength package
6233# ###########################################################################
6234
6235# ###########################################################################
6084# This is a combination of modules and programs in one -- a runnable module.6236# This is a combination of modules and programs in one -- a runnable module.
6085# http://www.perl.com/pub/a/2006/07/13/lightning-articles.html?page=last6237# http://www.perl.com/pub/a/2006/07/13/lightning-articles.html?page=last
6086# Or, look it up in the Camel book on pages 642 and 643 in the 3rd edition.6238# Or, look it up in the Camel book on pages 642 and 643 in the 3rd edition.
@@ -6748,30 +6900,30 @@
6748 }6900 }
6749 else { # chunking the table6901 else { # chunking the table
6750 if ( $o->get('check-plan') ) {6902 if ( $o->get('check-plan') ) {
6751 my $expl = explain_statement(6903 my $idx_len = new IndexLength(Quoter => $q);
6752 sth => $statements->{explain_first_lower_boundary},6904 my ($key_len, $key) = $idx_len->index_length(
6753 tbl => $tbl,6905 Cxn => $args{Cxn},
6754 vals => [],6906 tbl => $tbl,
6907 index => $nibble_iter->nibble_index(),
6908 n_index_cols => $o->get('chunk-index-columns'),
6755 );6909 );
6756 if ( !$expl->{key}6910 if ( !$key || lc($key) ne lc($nibble_iter->nibble_index()) ) {
6757 || lc($expl->{key}) ne lc($nibble_iter->nibble_index()) )
6758 {
6759 die "Cannot determine the key_len of the chunk index "6911 die "Cannot determine the key_len of the chunk index "
6760 . "because MySQL chose "6912 . "because MySQL chose "
6761 . ($expl->{key} ? "the $expl->{key}" : "no") . " index "6913 . ($key ? "the $key" : "no") . " index "
6762 . "instead of the " . $nibble_iter->nibble_index()6914 . "instead of the " . $nibble_iter->nibble_index()
6763 . " index for the first lower boundary statement. "6915 . " index for the first lower boundary statement. "
6764 . "See --[no]check-plan in the documentation for more "6916 . "See --[no]check-plan in the documentation for more "
6765 . "information.";6917 . "information.";
6766 }6918 }
6767 elsif ( !$expl->{key_len} ) {6919 elsif ( !$key_len ) {
6768 die "The key_len of the $expl->{key} index is "6920 die "The key_len of the $key index is "
6769 . (defined $expl->{key_len} ? "zero" : "NULL")6921 . (defined $key_len ? "zero" : "NULL")
6770 . ", but this should not be possible. " 6922 . ", but this should not be possible. "
6771 . "See --[no]check-plan in the documentation for more "6923 . "See --[no]check-plan in the documentation for more "
6772 . "information.";6924 . "information.";
6773 }6925 }
6774 $tbl->{key_len} = $expl->{key_len};6926 $tbl->{key_len} = $key_len;
6775 }6927 }
6776 }6928 }
67776929
67786930
=== added file 'lib/IndexLength.pm'
--- lib/IndexLength.pm 1970-01-01 00:00:00 +0000
+++ lib/IndexLength.pm 2012-06-11 16:59:23 +0000
@@ -0,0 +1,175 @@
1# This program is copyright 2012 Percona Inc.
2# Feedback and improvements are welcome.
3#
4# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
5# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
6# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
7#
8# This program is free software; you can redistribute it and/or modify it under
9# the terms of the GNU General Public License as published by the Free Software
10# Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
11# systems, you can issue `man perlgpl' or `man perlartistic' to read these
12# licenses.
13#
14# You should have received a copy of the GNU General Public License along with
15# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
16# Place, Suite 330, Boston, MA 02111-1307 USA.
17# ###########################################################################
18# IndexLength package
19# ###########################################################################
20{
21# Package: IndexLength
22# IndexLength get the key_len of a index.
23
24package IndexLength;
25
26use strict;
27use warnings FATAL => 'all';
28use English qw(-no_match_vars);
29use constant PTDEBUG => $ENV{PTDEBUG} || 0;
30
31use Data::Dumper;
32$Data::Dumper::Indent = 1;
33$Data::Dumper::Sortkeys = 1;
34$Data::Dumper::Quotekeys = 0;
35
36sub new {
37 my ( $class, %args ) = @_;
38 my @required_args = qw(Quoter);
39 foreach my $arg ( @required_args ) {
40 die "I need a $arg argument" unless $args{$arg};
41 }
42
43 my $self = {
44 Quoter => $args{Quoter},
45 };
46
47 return bless $self, $class;
48}
49
50# Returns the length of the index in bytes using only
51# the first N left-most columns of the index.
52sub index_length {
53 my ($self, %args) = @_;
54 my @required_args = qw(Cxn tbl index);
55 foreach my $arg ( @required_args ) {
56 die "I need a $arg argument" unless $args{$arg};
57 }
58 my ($cxn) = @args{@required_args};
59
60 die "The tbl argument does not have a tbl_struct"
61 unless exists $args{tbl}->{tbl_struct};
62 die "Index $args{index} does not exist in table $args{tbl}->{name}"
63 unless $args{tbl}->{tbl_struct}->{keys}->{$args{index}};
64
65 my $index_struct = $args{tbl}->{tbl_struct}->{keys}->{$args{index}};
66 my $index_cols = $index_struct->{cols};
67 my $n_index_cols = $args{n_index_cols};
68 if ( !$n_index_cols || $n_index_cols > @$index_cols ) {
69 $n_index_cols = scalar @$index_cols;
70 }
71
72 # Get the first row with non-NULL values.
73 my $vals = $self->_get_first_values(
74 %args,
75 n_index_cols => $n_index_cols,
76 );
77
78 # Make an EXPLAIN query to scan the range and execute it.
79 my $sql = $self->_make_range_query(
80 %args,
81 n_index_cols => $n_index_cols,
82 vals => $vals,
83 );
84 my $sth = $cxn->dbh()->prepare($sql);
85 PTDEBUG && _d($sth->{Statement}, 'params:', @$vals);
86 $sth->execute(@$vals);
87 my $row = $sth->fetchrow_hashref();
88 $sth->finish();
89 PTDEBUG && _d('Range scan:', Dumper($row));
90 return $row->{key_len}, $row->{key};
91}
92
93sub _get_first_values {
94 my ($self, %args) = @_;
95 my @required_args = qw(Cxn tbl index n_index_cols);
96 foreach my $arg ( @required_args ) {
97 die "I need a $arg argument" unless $args{$arg};
98 }
99 my ($cxn, $tbl, $index, $n_index_cols) = @args{@required_args};
100
101 my $q = $self->{Quoter};
102
103 # Select just the index columns.
104 my $index_struct = $tbl->{tbl_struct}->{keys}->{$index};
105 my $index_cols = $index_struct->{cols};
106 my $index_columns = join (', ',
107 map { $q->quote($_) } @{$index_cols}[0..($n_index_cols - 1)]);
108
109 # Where no index column is null, because we can't > NULL.
110 my @where;
111 foreach my $col ( @{$index_cols}[0..($n_index_cols - 1)] ) {
112 push @where, $q->quote($col) . " IS NOT NULL"
113 }
114
115 my $sql = "SELECT /*!40001 SQL_NO_CACHE */ $index_columns "
116 . "FROM $tbl->{name} FORCE INDEX (" . $q->quote($index) . ") "
117 . "WHERE " . join(' AND ', @where)
118 . " ORDER BY $index_columns "
119 . "LIMIT 1 /*key_len*/"; # only need 1 row
120 PTDEBUG && _d($sql);
121 my $vals = $cxn->dbh()->selectrow_arrayref($sql);
122 return $vals;
123}
124
125sub _make_range_query {
126 my ($self, %args) = @_;
127 my @required_args = qw(tbl index n_index_cols vals);
128 foreach my $arg ( @required_args ) {
129 die "I need a $arg argument" unless $args{$arg};
130 }
131 my ($tbl, $index, $n_index_cols, $vals) = @args{@required_args};
132
133 my $q = $self->{Quoter};
134
135 my $index_struct = $tbl->{tbl_struct}->{keys}->{$index};
136 my $index_cols = $index_struct->{cols};
137
138 # All but the last index col = val.
139 my @where;
140 if ( $n_index_cols > 1 ) {
141 # -1 for zero-index array as usual, then -1 again because
142 # we don't want the last column; that's added below.
143 foreach my $n ( 0..($n_index_cols - 2) ) {
144 my $col = $index_cols->[$n];
145 my $val = $vals->[$n];
146 push @where, $q->quote($col) . " = ?";
147 }
148 }
149
150 # The last index col > val. This causes the range scan using just
151 # the N left-most index columns.
152 my $col = $index_cols->[$n_index_cols - 1];
153 my $val = $vals->[-1]; # should only be as many vals as cols
154 push @where, $q->quote($col) . " >= ?";
155
156 my $sql = "EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * "
157 . "FROM $tbl->{name} FORCE INDEX (" . $q->quote($index) . ") "
158 . "WHERE " . join(' AND ', @where)
159 . " /*key_len*/";
160 return $sql;
161}
162
163sub _d {
164 my ($package, undef, $line) = caller 0;
165 @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
166 map { defined $_ ? $_ : 'undef' }
167 @_;
168 print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
169}
170
1711;
172}
173# ###########################################################################
174# End IndexLength package
175# ###########################################################################
0176
=== added file 't/lib/IndexLength.pm'
--- t/lib/IndexLength.pm 1970-01-01 00:00:00 +0000
+++ t/lib/IndexLength.pm 2012-06-11 16:59:23 +0000
@@ -0,0 +1,135 @@
1#!/usr/bin/perl
2
3BEGIN {
4 die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n"
5 unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH};
6 unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib";
7};
8
9use strict;
10use warnings FATAL => 'all';
11use English qw(-no_match_vars);
12use Test::More;
13
14use PerconaTest;
15use DSNParser;
16use Sandbox;
17
18use Cxn;
19use Quoter;
20use TableParser;
21use OptionParser;
22use IndexLength;
23
24use constant PTDEBUG => $ENV{PTDEBUG} || 0;
25use constant PTDEVDEBUG => $ENV{PTDEBUG} || 0;
26
27use Data::Dumper;
28$Data::Dumper::Indent = 1;
29$Data::Dumper::Sortkeys = 1;
30$Data::Dumper::Quotekeys = 0;
31
32my $dp = new DSNParser(opts=>$dsn_opts);
33my $sb = new Sandbox(basedir => '/tmp', DSNParser => $dp);
34my $dbh = $sb->get_dbh_for('master');
35
36if ( !$dbh ) {
37 plan skip_all => 'Cannot connect to sandbox master';
38}
39else {
40 plan tests => 7;
41}
42
43my $output;
44my $q = new Quoter();
45my $tp = new TableParser(Quoter => $q);
46my $il = new IndexLength(Quoter => $q);
47my $o = new OptionParser(description => 'IndexLength');
48$o->get_specs("$trunk/bin/pt-table-checksum");
49my $cxn = new Cxn(
50 dbh => $dbh,
51 dsn => { h=>'127.1', P=>'12345', n=>'h=127.1,P=12345' },
52 DSNParser => $dp,
53 OptionParser => $o,
54);
55
56sub test_index_len {
57 my (%args) = @_;
58 my @required_args = qw(name tbl index len);
59 foreach my $arg ( @required_args ) {
60 die "I need a $arg argument" unless $args{$arg};
61 }
62
63 my ($len, $key) = $il->index_length(
64 Cxn => $cxn,
65 tbl => $args{tbl},
66 index => $args{index},
67 n_index_cols => $args{n_index_cols},
68 );
69
70 is(
71 $len,
72 $args{len},
73 "$args{name}"
74 );
75}
76
77# #############################################################################
78# bad_plan, PK with 4 cols
79# #############################################################################
80$sb->load_file('master', "t/pt-table-checksum/samples/bad-plan-bug-1010232.sql");
81my $tbl_struct = $tp->parse(
82 $tp->get_create_table($dbh, 'bad_plan', 't'));
83my $tbl = {
84 name => $q->quote('bad_plan', 't'),
85 tbl_struct => $tbl_struct,
86};
87
88for my $n ( 1..4 ) {
89 my $len = $n * 2 + ($n >= 2 ? 1 : 0);
90 test_index_len(
91 name => "bad_plan.t $n cols = $len bytes",
92 tbl => $tbl,
93 index => "PRIMARY",
94 n_index_cols => $n,
95 len => $len,
96 );
97}
98
99# #############################################################################
100# Some sakila tables
101# #############################################################################
102$tbl_struct = $tp->parse(
103 $tp->get_create_table($dbh, 'sakila', 'film_actor'));
104$tbl = {
105 name => $q->quote('sakila', 'film_actor'),
106 tbl_struct => $tbl_struct,
107};
108
109test_index_len(
110 name => "sakila.film_actor 1 col = 2 bytes",
111 tbl => $tbl,
112 index => "PRIMARY",
113 n_index_cols => 1,
114 len => 2,
115);
116
117# #############################################################################
118# Use full index if no n_index_cols
119# #############################################################################
120
121# Use sakila.film_actor stuff from previous tests.
122
123test_index_len(
124 name => "sakila.film_actor all cols = 4 bytes",
125 tbl => $tbl,
126 index => "PRIMARY",
127 len => 4,
128);
129
130# #############################################################################
131# Done.
132# #############################################################################
133$sb->wipe_clean($dbh);
134ok($sb->ok(), "Sandbox servers") or BAIL_OUT(__FILE__ . " broke the sandbox");
135exit;
0136
=== modified file 't/pt-table-checksum/chunk_index.t'
--- t/pt-table-checksum/chunk_index.t 2012-06-11 12:07:18 +0000
+++ t/pt-table-checksum/chunk_index.t 2012-06-11 16:59:23 +0000
@@ -25,7 +25,7 @@
25 plan skip_all => 'Cannot connect to sandbox master';25 plan skip_all => 'Cannot connect to sandbox master';
26}26}
27else {27else {
28 plan tests => 16;28 plan tests => 17;
29}29}
3030
31# The sandbox servers run with lock_wait_timeout=3 and it's not dynamic31# The sandbox servers run with lock_wait_timeout=3 and it's not dynamic
@@ -175,7 +175,7 @@
175 $exit_status,175 $exit_status,
176 0,176 0,
177 "Bad key_len chunks are not errors"177 "Bad key_len chunks are not errors"
178);178) or diag($output);
179179
180cmp_ok(180cmp_ok(
181 PerconaTest::count_checksum_results($output, 'skipped'),181 PerconaTest::count_checksum_results($output, 'skipped'),
@@ -205,19 +205,40 @@
205 sub {205 sub {
206 $exit_status = pt_table_checksum::main(206 $exit_status = pt_table_checksum::main(
207 $master_dsn, '--max-load', '',207 $master_dsn, '--max-load', '',
208 qw(--lock-wait-timeout 3 --chunk-size 5000 -t sakila.rental),208 qw(--lock-wait-timeout 3 --chunk-size 1000 -t sakila.film_actor),
209 qw(--chunk-index rental_date --chunk-index-columns 5),209 qw(--chunk-index PRIMARY --chunk-index-columns 9),
210 qw(--explain --explain));210 );
211 },211 },
212 stderr => 1,212 stderr => 1,
213);213);
214214
215is(215is(
216 $exit_status,216 PerconaTest::count_checksum_results($output, 'rows'),
217 0,217 5462,
218 "--chunk-index-columns > number of index columns"218 "--chunk-index-columns > number of index columns"
219) or diag($output);
220
221$output = output(
222 sub {
223 $exit_status = pt_table_checksum::main(
224 $master_dsn, '--max-load', '',
225 qw(--lock-wait-timeout 3 --chunk-size 1000 -t sakila.film_actor),
226 qw(--chunk-index-columns 1 --chunk-size-limit 3),
227 );
228 },
229 stderr => 1,
219);230);
220231
232# Since we're not using the full index, it's basically a non-unique index,
233# so there are dupes. The table really has 5462 rows, so we must get
234# at least that many, and probably a few more.
235cmp_ok(
236 PerconaTest::count_checksum_results($output, 'rows'),
237 '>=',
238 5462,
239 "Initial key_len reflects --chunk-index-columns"
240) or diag($output);
241
221# #############################################################################242# #############################################################################
222# Done.243# Done.
223# #############################################################################244# #############################################################################

Subscribers

People subscribed via source and target branches