Merge lp:~percona-toolkit-dev/percona-toolkit/pt-align into lp:percona-toolkit/2.0
- pt-align
- Merge into 2.0
Proposed by
Daniel Nichter
Status: | Merged | ||||
---|---|---|---|---|---|
Approved by: | Daniel Nichter | ||||
Approved revision: | 260 | ||||
Merged at revision: | 119 | ||||
Proposed branch: | lp:~percona-toolkit-dev/percona-toolkit/pt-align | ||||
Merge into: | lp:percona-toolkit/2.0 | ||||
Diff against target: |
27339 lines (+12682/-10335) (has conflicts) 184 files modified
Changelog (+8/-2) bin/pt-align (+223/-0) bin/pt-sift (+2/-19) bin/pt-table-checksum (+5087/-5507) bin/pt-table-sync (+80/-19) lib/CleanupTask.pm (+69/-0) lib/CompareResults.pm (+6/-4) lib/CopyRowsInsertSelect.pm (+17/-18) lib/Cxn.pm (+215/-0) lib/DSNParser.pm (+7/-5) lib/MasterSlave.pm (+85/-0) lib/MySQLDump.pm (+0/-322) lib/MySQLStatusWaiter.pm (+185/-0) lib/NibbleIterator.pm (+545/-269) lib/OobNibbleIterator.pm (+230/-0) lib/OptionParser.pm (+1/-1) lib/PerconaTest.pm (+66/-4) lib/Progress.pm (+13/-2) lib/ReplicaLagWaiter.pm (+173/-0) lib/Retry.pm (+35/-41) lib/RowChecksum.pm (+82/-71) lib/Sandbox.pm (+2/-0) lib/SchemaIterator.pm (+99/-26) lib/TableChecksum.pm (+10/-12) lib/TableChunker.pm (+5/-5) lib/TableParser.pm (+75/-60) lib/TableSyncer.pm (+17/-18) lib/Transformers.pm (+63/-0) lib/WeightedAvgRate.pm (+102/-0) sandbox/load-sakila-db (+0/-3) sandbox/start-sandbox (+3/-0) sandbox/test-env (+10/-2) t/lib/CleanupTask.t (+36/-0) t/lib/CompareResults.t (+0/-3) t/lib/Cxn.t (+259/-0) t/lib/DSNParser.t (+1/-1) t/lib/MasterSlave.t (+113/-4) t/lib/MySQLDump.t (+0/-92) t/lib/MySQLStatusWaiter.t (+221/-0) t/lib/NibbleIterator.t (+498/-157) t/lib/OobNibbleIterator.t (+264/-0) t/lib/OptionParser.t (+2/-2) t/lib/Progress.t (+49/-4) t/lib/QueryReview.t (+3/-5) t/lib/ReplicaLagWaiter.t (+121/-0) t/lib/Retry.t (+83/-118) t/lib/RowChecksum.t (+64/-53) t/lib/RowDiff-custom.t (+1/-3) t/lib/RowDiff.t (+1/-3) t/lib/SQLParser.t (+1/-1) t/lib/Schema.t (+1/-1) t/lib/SchemaIterator.t (+28/-11) t/lib/TableChecksum.t (+1/-3) t/lib/TableChunker.t (+26/-28) t/lib/TableParser.t (+25/-156) t/lib/TableSyncChunk.t (+5/-7) t/lib/TableSyncNibble.t (+7/-9) t/lib/TableSyncer.t (+32/-41) t/lib/Transformers.t (+58/-1) t/lib/WeightedAvgRate.t (+85/-0) t/lib/samples/MasterSlave/dsn_table.sql (+12/-0) t/lib/samples/NibbleIterator/bad_tables.sql (+21/-0) t/lib/samples/SchemaIterator/all-dbs-tbls.txt (+0/-2) t/lib/samples/SchemaIterator/mysql-user-ddl-5.0.txt (+1/-0) t/lib/samples/SchemaIterator/mysql-user-ddl.txt (+1/-0) t/lib/samples/SchemaIterator/resume-from-ignored-sakila-payment.txt (+3/-0) t/lib/samples/SchemaIterator/resume-from-sakila-payment.txt (+4/-0) t/lib/samples/char-chunking/ascii.sql (+146/-0) t/lib/samples/tables/sakila.actor (+8/-0) t/pt-align/pt-align.t (+32/-0) t/pt-align/samples/001-aligned.txt (+42/-0) t/pt-align/samples/001-raw.txt (+42/-0) t/pt-align/samples/002-aligned.txt (+6/-0) t/pt-align/samples/002-raw.txt (+7/-0) t/pt-table-checksum/arg_table.t (+0/-106) t/pt-table-checksum/basics.t (+280/-118) t/pt-table-checksum/char_chunking.t (+19/-22) t/pt-table-checksum/checksum.t (+0/-59) t/pt-table-checksum/chunk_column.t (+0/-104) t/pt-table-checksum/chunk_index.t (+46/-79) t/pt-table-checksum/chunk_size.t (+78/-18) t/pt-table-checksum/create_replicate_table.t (+84/-60) t/pt-table-checksum/error_handling.t (+79/-6) t/pt-table-checksum/filters.t (+102/-53) t/pt-table-checksum/float_precision.t (+55/-15) t/pt-table-checksum/fnv_64.t (+72/-20) t/pt-table-checksum/force_index.t (+0/-67) t/pt-table-checksum/ignore_columns.t (+40/-20) t/pt-table-checksum/issue_1020.t (+0/-53) t/pt-table-checksum/issue_1182.t (+0/-59) t/pt-table-checksum/issue_122.t (+0/-70) t/pt-table-checksum/issue_1319.t (+0/-52) t/pt-table-checksum/issue_21.t (+0/-92) t/pt-table-checksum/issue_35.t (+0/-62) t/pt-table-checksum/issue_388.t (+11/-3) t/pt-table-checksum/issue_47.t (+32/-12) t/pt-table-checksum/issue_51.t (+0/-77) t/pt-table-checksum/issue_602.t (+12/-6) t/pt-table-checksum/issue_69.t (+0/-75) t/pt-table-checksum/issue_947.t (+0/-49) t/pt-table-checksum/issue_982.t (+0/-226) t/pt-table-checksum/offset_modulo.t (+0/-52) t/pt-table-checksum/option_sanity.t (+132/-7) t/pt-table-checksum/oversize_chunks.t (+0/-63) t/pt-table-checksum/probability.t (+0/-60) t/pt-table-checksum/progress.t (+45/-43) t/pt-table-checksum/replication_filters.t (+233/-41) t/pt-table-checksum/resume.t (+662/-69) t/pt-table-checksum/retry_timeouts.t (+0/-112) t/pt-table-checksum/samples/3tbl-resume.sql (+27/-0) t/pt-table-checksum/samples/arg-table.sql (+0/-7) t/pt-table-checksum/samples/basic_replicate_output (+0/-2) t/pt-table-checksum/samples/before.sql (+0/-80) t/pt-table-checksum/samples/char-chunk-ascii-explain.txt (+23/-9) t/pt-table-checksum/samples/char-chunk-ascii-oversize.txt (+0/-9) t/pt-table-checksum/samples/char-chunk-ascii.txt (+2/-9) t/pt-table-checksum/samples/char-chunking.sql (+0/-110) t/pt-table-checksum/samples/checksum_results/3tbl-resume (+24/-0) t/pt-table-checksum/samples/checksum_results/3tbl-resume-bar (+11/-0) t/pt-table-checksum/samples/checksum_results/sakila-done-1k-chunks (+65/-0) t/pt-table-checksum/samples/checksum_results/sakila-done-singles (+16/-0) t/pt-table-checksum/samples/checksum_tbl.sql (+0/-14) t/pt-table-checksum/samples/checksum_tbl_truncated.sql (+16/-14) t/pt-table-checksum/samples/chunkidx001.txt (+19/-0) t/pt-table-checksum/samples/chunkidx002.txt (+19/-0) t/pt-table-checksum/samples/chunkidx003.txt (+19/-0) t/pt-table-checksum/samples/chunkidx004.txt (+16/-0) t/pt-table-checksum/samples/chunkidx005.txt (+16/-0) t/pt-table-checksum/samples/default-results-5.1.txt (+38/-0) t/pt-table-checksum/samples/float_precision.sql (+7/-0) t/pt-table-checksum/samples/fnv64-sakila-city.txt (+16/-0) t/pt-table-checksum/samples/issue_122.sql (+0/-15) t/pt-table-checksum/samples/issue_21.sql (+5/-3) t/pt-table-checksum/samples/issue_467.txt (+0/-5) t/pt-table-checksum/samples/issue_922.sql (+0/-10) t/pt-table-checksum/samples/no-recheck.txt (+10/-0) t/pt-table-checksum/samples/oversize-chunks-allowed.txt (+0/-6) t/pt-table-checksum/samples/oversize-chunks.sql (+24/-3) t/pt-table-checksum/samples/oversize-chunks.txt (+18/-6) t/pt-table-checksum/samples/replicate.sql (+0/-22) t/pt-table-checksum/samples/resume-chunked-complete.txt (+0/-9) t/pt-table-checksum/samples/resume-chunked-partial.txt (+0/-5) t/pt-table-checksum/samples/resume-complete.txt (+0/-3) t/pt-table-checksum/samples/resume-partial.txt (+0/-2) t/pt-table-checksum/samples/resume.sql (+0/-5) t/pt-table-checksum/samples/resume2-chunked-complete.txt (+0/-17) t/pt-table-checksum/samples/resume2-chunked-partial.txt (+0/-6) t/pt-table-checksum/samples/resume2.sql (+0/-7) t/pt-table-checksum/samples/sample_1 (+0/-6) t/pt-table-checksum/samples/sample_2 (+0/-5) t/pt-table-checksum/samples/sample_schema_opt (+0/-35) t/pt-table-checksum/samples/static-chunk-size-results-5.1.txt (+38/-0) t/pt-table-checksum/samples/unchunkable-table-small.txt (+0/-2) t/pt-table-checksum/samples/unchunkable-table.txt (+0/-1) t/pt-table-checksum/samples/where01.out (+0/-3) t/pt-table-checksum/samples/where01.sql (+0/-26) t/pt-table-checksum/samples/where02.out (+0/-4) t/pt-table-checksum/samples/where02.sql (+0/-26) t/pt-table-checksum/schema.t (+0/-172) t/pt-table-checksum/scripts/exec-wait-exec.sh (+10/-0) t/pt-table-checksum/scripts/wait-for-chunk.sh (+8/-0) t/pt-table-checksum/since.t (+0/-79) t/pt-table-checksum/standard_options.t (+106/-13) t/pt-table-checksum/throttle.t (+39/-226) t/pt-table-checksum/unchunkable_tables.t (+0/-57) t/pt-table-checksum/zero_chunk.t (+0/-56) t/pt-table-sync/bidirectional.t (+17/-18) t/pt-table-sync/diff_where.t (+63/-0) t/pt-table-sync/filters.t (+6/-8) t/pt-table-sync/issue_408.t (+5/-5) t/pt-table-sync/issue_560.t (+6/-11) t/pt-table-sync/issue_627.t (+1/-2) t/pt-table-sync/issue_79.t (+2/-3) t/pt-table-sync/issue_996.t (+5/-8) t/pt-table-sync/samples/bidirectional/queries001.txt (+7/-0) t/pt-table-sync/samples/diff001.sql (+12/-0) t/pt-table-sync/samples/issue_560.sql (+0/-13) t/pt-table-sync/samples/issue_560_output_2.txt (+12/-12) t/pt-table-sync/samples/simple-tbl-ddl.sql (+10/-0) t/pt-table-sync/samples/simple-tbls.sql (+62/-0) t/pt-table-sync/sync_to_differnt_db.t (+5/-6) t/pt-table-sync/triggers.t (+4/-4) util/build-packages (+26/-11) util/wait-to-exec (+40/-0) Text conflict in Changelog |
||||
To merge this branch: | bzr merge lp:~percona-toolkit-dev/percona-toolkit/pt-align | ||||
Related bugs: |
|
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Brian Fraser (community) | Approve | ||
Daniel Nichter | Approve | ||
Review via email:
|
Commit message
Description of the change
To post a comment you must log in.
- 260. By Daniel Nichter
-
Docu pt-align with an example.
Revision history for this message
![](/+icing/build/overlay/assets/skins/sam/images/close.gif)
Daniel Nichter (daniel-nichter) : | # |
review:
Approve
Revision history for this message
![](/+icing/build/overlay/assets/skins/sam/images/close.gif)
Brian Fraser (fraserbn) : | # |
review:
Approve
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === modified file 'Changelog' |
2 | --- Changelog 2011-10-11 14:31:42 +0000 |
3 | +++ Changelog 2011-12-28 18:22:25 +0000 |
4 | @@ -1,7 +1,13 @@ |
5 | Changelog for Percona Toolkit |
6 | |
7 | - * Fixed bug 821717: pt-tcp-model --type=requests crashes |
8 | - |
9 | +<<<<<<< TREE |
10 | + * Fixed bug 821717: pt-tcp-model --type=requests crashes |
11 | + |
12 | +======= |
13 | + * Completely redesigned pt-table-checksum. |
14 | + * Fixed bug 821717: pt-tcp-model --type=requests crashes |
15 | + |
16 | +>>>>>>> MERGE-SOURCE |
17 | v1.0.1 released 2011-09-01 |
18 | |
19 | * Fixed bug 819421: MasterSlave::is_replication_thread() doesn't match all |
20 | |
21 | === added file 'bin/pt-align' |
22 | --- bin/pt-align 1970-01-01 00:00:00 +0000 |
23 | +++ bin/pt-align 2011-12-28 18:22:25 +0000 |
24 | @@ -0,0 +1,223 @@ |
25 | +#!/usr/bin/env perl |
26 | + |
27 | +# This program is part of Percona Toolkit: http://www.percona.com/software/ |
28 | +# See "COPYRIGHT, LICENSE, AND WARRANTY" at the end of this file for legal |
29 | +# notices and disclaimers. |
30 | + |
31 | +# ########################################################################### |
32 | +# This is a combination of modules and programs in one -- a runnable module. |
33 | +# http://www.perl.com/pub/a/2006/07/13/lightning-articles.html?page=last |
34 | +# Or, look it up in the Camel book on pages 642 and 643 in the 3rd edition. |
35 | +# |
36 | +# Check at the end of this package for the call to main() which actually runs |
37 | +# the program. |
38 | +# ########################################################################### |
39 | +package pt_align; |
40 | + |
41 | +use strict; |
42 | +use warnings FATAL => 'all'; |
43 | + |
44 | +use List::Util qw( max ); |
45 | + |
46 | +sub main { |
47 | + local *ARGV; # In the extremely rare case that this is run as a module, |
48 | + # not resetting ARGV (the filehandle) could cause problems. |
49 | + |
50 | + @ARGV = @_; # set global ARGV for this package |
51 | + |
52 | + # Read all lines |
53 | + my @lines; |
54 | + my %word_count; |
55 | + while ( <> ) { |
56 | + my $line = $_; |
57 | + my @words = $line =~ m/(\S+)/g; |
58 | + push @lines, \@words; |
59 | + $word_count{ scalar @words }++; |
60 | + } |
61 | + |
62 | + # Find max number of words per line |
63 | + my @wc = reverse sort { $word_count{$a}<=>$word_count{$b} } keys %word_count; |
64 | + my $m_words = $wc[0]; |
65 | + |
66 | + # Filter out non-conformists |
67 | + @lines = grep { scalar @$_ == $m_words } @lines; |
68 | + die "I need at least 2 lines" unless @lines > 1; |
69 | + |
70 | + # Find the widths and alignments of each column |
71 | + my @fmt; |
72 | + foreach my $i ( 0 .. $m_words-1 ) { |
73 | + my $m_len = max(map { length($_->[$i]) } @lines); |
74 | + my $code = $lines[1]->[$i] =~ m/[^0-9.-]/ |
75 | + ? "%-${m_len}s" |
76 | + : "%${m_len}s"; |
77 | + push @fmt, $code; |
78 | + } |
79 | + my $fmt = join(' ', @fmt) . "\n"; |
80 | + |
81 | + # Print! |
82 | + foreach my $l ( @lines ) { |
83 | + printf $fmt, @$l; |
84 | + } |
85 | +} |
86 | + |
87 | +# ############################################################################ |
88 | +# Run the program. |
89 | +# ############################################################################ |
90 | +if ( !caller ) { exit main(@ARGV); } |
91 | + |
92 | +1; # Because this is a module as well as a script. |
93 | + |
94 | +# ############################################################################ |
95 | +# Documentation |
96 | +# ############################################################################ |
97 | +=pod |
98 | + |
99 | +=head1 NAME |
100 | + |
101 | +pt-align - Align output from other tools to columns. |
102 | + |
103 | +=head1 SYNOPSIS |
104 | + |
105 | +Usage: pt-align [FILES] |
106 | + |
107 | +pt-align aligns output from other tools to columns. If no FILES are specified, |
108 | +STDIN is read. |
109 | + |
110 | +If a tool prints the following output, |
111 | + |
112 | + DATABASE TABLE ROWS |
113 | + foo bar 100 |
114 | + long_db_name table 1 |
115 | + another long_name 500 |
116 | + |
117 | +then pt-align reprints the output as, |
118 | + |
119 | + DATABASE TABLE ROWS |
120 | + foo bar 100 |
121 | + long_db_name table 1 |
122 | + another long_name 500 |
123 | + |
124 | +=head1 RISKS |
125 | + |
126 | +The following section is included to inform users about the potential risks, |
127 | +whether known or unknown, of using this tool. The two main categories of risks |
128 | +are those created by the nature of the tool (e.g. read-only tools vs. read-write |
129 | +tools) and those created by bugs. |
130 | + |
131 | +pt-align is a read-only tool. It should be very low-risk. |
132 | + |
133 | +At the time of this release, we know of no bugs that could cause serious harm |
134 | +to users. |
135 | + |
136 | +The authoritative source for updated information is always the online issue |
137 | +tracking system. Issues that affect this tool will be marked as such. You can |
138 | +see a list of such issues at the following URL: |
139 | +L<http://www.percona.com/bugs/pt-align>. |
140 | + |
141 | +See also L<"BUGS"> for more information on filing bugs and getting help. |
142 | + |
143 | +=head1 DESCRIPTION |
144 | + |
145 | +pt-align reads lines and splits them into words. It counts how many |
146 | +words each line has, and if there is one number that predominates, it assumes |
147 | +this is the number of words in each line. Then it discards all lines that |
148 | +don't have that many words, and looks at the 2nd line that does. It assumes |
149 | +this is the first non-header line. Based on whether each word looks numeric |
150 | +or not, it decides on column alignment. Finally, it goes through and decides |
151 | +how wide each column should be, and then prints them out. |
152 | + |
153 | +This is useful for things like aligning the output of vmstat or iostat so it |
154 | +is easier to read. |
155 | + |
156 | +=head1 OPTIONS |
157 | + |
158 | +This tool does not have any command-line options. |
159 | + |
160 | +=head1 ENVIRONMENT |
161 | + |
162 | +This tool does not use any environment variables. |
163 | + |
164 | +=head1 SYSTEM REQUIREMENTS |
165 | + |
166 | +You need Perl, and some core packages that ought to be installed in any |
167 | +reasonably new version of Perl. |
168 | + |
169 | +=head1 BUGS |
170 | + |
171 | +For a list of known bugs, see L<http://www.percona.com/bugs/pt-align>. |
172 | + |
173 | +Please report bugs at L<https://bugs.launchpad.net/percona-toolkit>. |
174 | +Include the following information in your bug report: |
175 | + |
176 | +=over |
177 | + |
178 | +=item * Complete command-line used to run the tool |
179 | + |
180 | +=item * Tool L<"--version"> |
181 | + |
182 | +=item * MySQL version of all servers involved |
183 | + |
184 | +=item * Output from the tool including STDERR |
185 | + |
186 | +=item * Input files (log/dump/config files, etc.) |
187 | + |
188 | +=back |
189 | + |
190 | +If possible, include debugging output by running the tool with C<PTDEBUG>; |
191 | +see L<"ENVIRONMENT">. |
192 | + |
193 | +=head1 DOWNLOADING |
194 | + |
195 | +Visit L<http://www.percona.com/software/percona-toolkit/> to download the |
196 | +latest release of Percona Toolkit. Or, get the latest release from the |
197 | +command line: |
198 | + |
199 | + wget percona.com/get/percona-toolkit.tar.gz |
200 | + |
201 | + wget percona.com/get/percona-toolkit.rpm |
202 | + |
203 | + wget percona.com/get/percona-toolkit.deb |
204 | + |
205 | +You can also get individual tools from the latest release: |
206 | + |
207 | + wget percona.com/get/TOOL |
208 | + |
209 | +Replace C<TOOL> with the name of any tool. |
210 | + |
211 | +=head1 AUTHORS |
212 | + |
213 | +Baron Schwartz, Brian Fraser, and Daniel Nichter |
214 | + |
215 | +=head1 ABOUT PERCONA TOOLKIT |
216 | + |
217 | +This tool is part of Percona Toolkit, a collection of advanced command-line |
218 | +tools developed by Percona for MySQL support and consulting. Percona Toolkit |
219 | +was forked from two projects in June, 2011: Maatkit and Aspersa. Those |
220 | +projects were created by Baron Schwartz and developed primarily by him and |
221 | +Daniel Nichter, both of whom are employed by Percona. Visit |
222 | +L<http://www.percona.com/software/> for more software developed by Percona. |
223 | + |
224 | +=head1 COPYRIGHT, LICENSE, AND WARRANTY |
225 | + |
226 | +This program is copyright 2010-2011 Baron Schwartz, 2011 Percona Inc. |
227 | +Feedback and improvements are welcome. |
228 | + |
229 | +THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED |
230 | +WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF |
231 | +MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. |
232 | + |
233 | +This program is free software; you can redistribute it and/or modify it under |
234 | +the terms of the GNU General Public License as published by the Free Software |
235 | +Foundation, version 2; OR the Perl Artistic License. On UNIX and similar |
236 | +systems, you can issue `man perlgpl' or `man perlartistic' to read these |
237 | +licenses. |
238 | + |
239 | +You should have received a copy of the GNU General Public License along with |
240 | +this program; if not, write to the Free Software Foundation, Inc., 59 Temple |
241 | +Place, Suite 330, Boston, MA 02111-1307 USA. |
242 | + |
243 | +=head1 VERSION |
244 | + |
245 | +pt-align 2.0.0 |
246 | + |
247 | +=cut |
248 | |
249 | === modified file 'bin/pt-sift' |
250 | --- bin/pt-sift 2011-11-04 16:44:10 +0000 |
251 | +++ bin/pt-sift 2011-12-28 18:22:25 +0000 |
252 | @@ -51,25 +51,8 @@ |
253 | fi |
254 | |
255 | # If the programs we need don't exist, try to get them. |
256 | - # Aspersa tools: |
257 | - for prog in align; do |
258 | - if which $prog >/dev/null 2>&1 ; then |
259 | - eval "PR_$prog"="$(which $prog)" |
260 | - elif [ -f $prog -a -x $prog ]; then |
261 | - eval "PR_$prog"="./$prog" |
262 | - elif [ -f "${BASEDIR}/$prog" -a -x "${BASEDIR}/$prog" ]; then |
263 | - eval "PR_$prog"="${BASEDIR}/$prog" |
264 | - elif which "curl" >/dev/null 2>&1; then |
265 | - echo "Fetching $prog" >&2 |
266 | - curl http://aspersa.googlecode.com/svn/trunk/$prog > "$prog" && chmod +x "$prog" |
267 | - eval "PR_$prog"="./$prog" |
268 | - else |
269 | - echo "Cannot find or fetch required program: $prog" >&2 |
270 | - exit 1 |
271 | - fi |
272 | - done |
273 | # Percona Toolkit tools: |
274 | - for prog in pt-diskstats pt-pmp pt-mext; do |
275 | + for prog in pt-diskstats pt-pmp pt-mext pt-align; do |
276 | # A var can't be named "PR_pt-pmp" so we chop of "pt-" to get |
277 | # the program's basename, resulting in "PR_pmp". |
278 | prog_base=${prog#"pt-"} |
279 | @@ -590,7 +573,7 @@ |
280 | =head1 SYSTEM REQUIREMENTS |
281 | |
282 | This tool requires Bash v3 and the following programs: pt-diskstats, pt-pmp, |
283 | -pt-mext, and align (from Aspersa). If these programs are not in your PATH, |
284 | +pt-mext, and pt-align. If these programs are not in your PATH, |
285 | they will be fetched from the Internet if curl is available. |
286 | |
287 | =head1 BUGS |
288 | |
289 | === modified file 'bin/pt-table-checksum' |
290 | --- bin/pt-table-checksum 2011-09-01 16:00:38 +0000 |
291 | +++ bin/pt-table-checksum 2011-12-28 18:22:25 +0000 |
292 | @@ -9,15 +9,15 @@ |
293 | use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
294 | |
295 | # ########################################################################### |
296 | -# TableParser package |
297 | +# DSNParser package |
298 | # This package is a copy without comments from the original. The original |
299 | # with comments and its test file can be found in the Bazaar repository at, |
300 | -# lib/TableParser.pm |
301 | -# t/lib/TableParser.t |
302 | +# lib/DSNParser.pm |
303 | +# t/lib/DSNParser.t |
304 | # See https://launchpad.net/percona-toolkit for more information. |
305 | # ########################################################################### |
306 | { |
307 | -package TableParser; |
308 | +package DSNParser; |
309 | |
310 | use strict; |
311 | use warnings FATAL => 'all'; |
312 | @@ -25,757 +25,331 @@ |
313 | use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
314 | |
315 | use Data::Dumper; |
316 | -$Data::Dumper::Indent = 1; |
317 | -$Data::Dumper::Sortkeys = 1; |
318 | +$Data::Dumper::Indent = 0; |
319 | $Data::Dumper::Quotekeys = 0; |
320 | |
321 | +eval { |
322 | + require DBI; |
323 | +}; |
324 | +my $have_dbi = $EVAL_ERROR ? 0 : 1; |
325 | + |
326 | sub new { |
327 | my ( $class, %args ) = @_; |
328 | - my @required_args = qw(Quoter); |
329 | - foreach my $arg ( @required_args ) { |
330 | + foreach my $arg ( qw(opts) ) { |
331 | die "I need a $arg argument" unless $args{$arg}; |
332 | } |
333 | - my $self = { %args }; |
334 | + my $self = { |
335 | + opts => {} # h, P, u, etc. Should come from DSN OPTIONS section in POD. |
336 | + }; |
337 | + foreach my $opt ( @{$args{opts}} ) { |
338 | + if ( !$opt->{key} || !$opt->{desc} ) { |
339 | + die "Invalid DSN option: ", Dumper($opt); |
340 | + } |
341 | + MKDEBUG && _d('DSN option:', |
342 | + join(', ', |
343 | + map { "$_=" . (defined $opt->{$_} ? ($opt->{$_} || '') : 'undef') } |
344 | + keys %$opt |
345 | + ) |
346 | + ); |
347 | + $self->{opts}->{$opt->{key}} = { |
348 | + dsn => $opt->{dsn}, |
349 | + desc => $opt->{desc}, |
350 | + copy => $opt->{copy} || 0, |
351 | + }; |
352 | + } |
353 | return bless $self, $class; |
354 | } |
355 | |
356 | +sub prop { |
357 | + my ( $self, $prop, $value ) = @_; |
358 | + if ( @_ > 2 ) { |
359 | + MKDEBUG && _d('Setting', $prop, 'property'); |
360 | + $self->{$prop} = $value; |
361 | + } |
362 | + return $self->{$prop}; |
363 | +} |
364 | + |
365 | sub parse { |
366 | - my ( $self, $ddl, $opts ) = @_; |
367 | - return unless $ddl; |
368 | - if ( ref $ddl eq 'ARRAY' ) { |
369 | - if ( lc $ddl->[0] eq 'table' ) { |
370 | - $ddl = $ddl->[1]; |
371 | - } |
372 | - else { |
373 | - return { |
374 | - engine => 'VIEW', |
375 | - }; |
376 | - } |
377 | - } |
378 | - |
379 | - if ( $ddl !~ m/CREATE (?:TEMPORARY )?TABLE `/ ) { |
380 | - die "Cannot parse table definition; is ANSI quoting " |
381 | - . "enabled or SQL_QUOTE_SHOW_CREATE disabled?"; |
382 | - } |
383 | - |
384 | - my ($name) = $ddl =~ m/CREATE (?:TEMPORARY )?TABLE\s+(`.+?`)/; |
385 | - (undef, $name) = $self->{Quoter}->split_unquote($name) if $name; |
386 | - |
387 | - $ddl =~ s/(`[^`]+`)/\L$1/g; |
388 | - |
389 | - my $engine = $self->get_engine($ddl); |
390 | - |
391 | - my @defs = $ddl =~ m/^(\s+`.*?),?$/gm; |
392 | - my @cols = map { $_ =~ m/`([^`]+)`/ } @defs; |
393 | - MKDEBUG && _d('Table cols:', join(', ', map { "`$_`" } @cols)); |
394 | - |
395 | - my %def_for; |
396 | - @def_for{@cols} = @defs; |
397 | - |
398 | - my (@nums, @null); |
399 | - my (%type_for, %is_nullable, %is_numeric, %is_autoinc); |
400 | - foreach my $col ( @cols ) { |
401 | - my $def = $def_for{$col}; |
402 | - my ( $type ) = $def =~ m/`[^`]+`\s([a-z]+)/; |
403 | - die "Can't determine column type for $def" unless $type; |
404 | - $type_for{$col} = $type; |
405 | - if ( $type =~ m/(?:(?:tiny|big|medium|small)?int|float|double|decimal|year)/ ) { |
406 | - push @nums, $col; |
407 | - $is_numeric{$col} = 1; |
408 | - } |
409 | - if ( $def !~ m/NOT NULL/ ) { |
410 | - push @null, $col; |
411 | - $is_nullable{$col} = 1; |
412 | - } |
413 | - $is_autoinc{$col} = $def =~ m/AUTO_INCREMENT/i ? 1 : 0; |
414 | - } |
415 | - |
416 | - my ($keys, $clustered_key) = $self->get_keys($ddl, $opts, \%is_nullable); |
417 | - |
418 | - my ($charset) = $ddl =~ m/DEFAULT CHARSET=(\w+)/; |
419 | - |
420 | - return { |
421 | - name => $name, |
422 | - cols => \@cols, |
423 | - col_posn => { map { $cols[$_] => $_ } 0..$#cols }, |
424 | - is_col => { map { $_ => 1 } @cols }, |
425 | - null_cols => \@null, |
426 | - is_nullable => \%is_nullable, |
427 | - is_autoinc => \%is_autoinc, |
428 | - clustered_key => $clustered_key, |
429 | - keys => $keys, |
430 | - defs => \%def_for, |
431 | - numeric_cols => \@nums, |
432 | - is_numeric => \%is_numeric, |
433 | - engine => $engine, |
434 | - type_for => \%type_for, |
435 | - charset => $charset, |
436 | - }; |
437 | -} |
438 | - |
439 | -sub sort_indexes { |
440 | - my ( $self, $tbl ) = @_; |
441 | - |
442 | - my @indexes |
443 | - = sort { |
444 | - (($a ne 'PRIMARY') <=> ($b ne 'PRIMARY')) |
445 | - || ( !$tbl->{keys}->{$a}->{is_unique} <=> !$tbl->{keys}->{$b}->{is_unique} ) |
446 | - || ( $tbl->{keys}->{$a}->{is_nullable} <=> $tbl->{keys}->{$b}->{is_nullable} ) |
447 | - || ( scalar(@{$tbl->{keys}->{$a}->{cols}}) <=> scalar(@{$tbl->{keys}->{$b}->{cols}}) ) |
448 | - } |
449 | - grep { |
450 | - $tbl->{keys}->{$_}->{type} eq 'BTREE' |
451 | - } |
452 | - sort keys %{$tbl->{keys}}; |
453 | - |
454 | - MKDEBUG && _d('Indexes sorted best-first:', join(', ', @indexes)); |
455 | - return @indexes; |
456 | -} |
457 | - |
458 | -sub find_best_index { |
459 | - my ( $self, $tbl, $index ) = @_; |
460 | - my $best; |
461 | - if ( $index ) { |
462 | - ($best) = grep { uc $_ eq uc $index } keys %{$tbl->{keys}}; |
463 | - } |
464 | - if ( !$best ) { |
465 | - if ( $index ) { |
466 | - die "Index '$index' does not exist in table"; |
467 | - } |
468 | - else { |
469 | - ($best) = $self->sort_indexes($tbl); |
470 | - } |
471 | - } |
472 | - MKDEBUG && _d('Best index found is', $best); |
473 | - return $best; |
474 | -} |
475 | - |
476 | -sub find_possible_keys { |
477 | - my ( $self, $dbh, $database, $table, $quoter, $where ) = @_; |
478 | - return () unless $where; |
479 | - my $sql = 'EXPLAIN SELECT * FROM ' . $quoter->quote($database, $table) |
480 | - . ' WHERE ' . $where; |
481 | - MKDEBUG && _d($sql); |
482 | - my $expl = $dbh->selectrow_hashref($sql); |
483 | - $expl = { map { lc($_) => $expl->{$_} } keys %$expl }; |
484 | - if ( $expl->{possible_keys} ) { |
485 | - MKDEBUG && _d('possible_keys =', $expl->{possible_keys}); |
486 | - my @candidates = split(',', $expl->{possible_keys}); |
487 | - my %possible = map { $_ => 1 } @candidates; |
488 | - if ( $expl->{key} ) { |
489 | - MKDEBUG && _d('MySQL chose', $expl->{key}); |
490 | - unshift @candidates, grep { $possible{$_} } split(',', $expl->{key}); |
491 | - MKDEBUG && _d('Before deduping:', join(', ', @candidates)); |
492 | - my %seen; |
493 | - @candidates = grep { !$seen{$_}++ } @candidates; |
494 | - } |
495 | - MKDEBUG && _d('Final list:', join(', ', @candidates)); |
496 | - return @candidates; |
497 | - } |
498 | - else { |
499 | - MKDEBUG && _d('No keys in possible_keys'); |
500 | - return (); |
501 | - } |
502 | -} |
503 | - |
504 | -sub check_table { |
505 | - my ( $self, %args ) = @_; |
506 | - my @required_args = qw(dbh db tbl); |
507 | - foreach my $arg ( @required_args ) { |
508 | - die "I need a $arg argument" unless $args{$arg}; |
509 | - } |
510 | - my ($dbh, $db, $tbl) = @args{@required_args}; |
511 | - my $q = $self->{Quoter}; |
512 | - my $db_tbl = $q->quote($db, $tbl); |
513 | - MKDEBUG && _d('Checking', $db_tbl); |
514 | - |
515 | - my $sql = "SHOW TABLES FROM " . $q->quote($db) |
516 | - . ' LIKE ' . $q->literal_like($tbl); |
517 | - MKDEBUG && _d($sql); |
518 | - my $row; |
519 | - eval { |
520 | - $row = $dbh->selectrow_arrayref($sql); |
521 | - }; |
522 | - if ( $EVAL_ERROR ) { |
523 | - MKDEBUG && _d($EVAL_ERROR); |
524 | - return 0; |
525 | - } |
526 | - if ( !$row->[0] || $row->[0] ne $tbl ) { |
527 | - MKDEBUG && _d('Table does not exist'); |
528 | - return 0; |
529 | - } |
530 | - |
531 | - MKDEBUG && _d('Table exists; no privs to check'); |
532 | - return 1 unless $args{all_privs}; |
533 | - |
534 | - $sql = "SHOW FULL COLUMNS FROM $db_tbl"; |
535 | - MKDEBUG && _d($sql); |
536 | - eval { |
537 | - $row = $dbh->selectrow_hashref($sql); |
538 | - }; |
539 | - if ( $EVAL_ERROR ) { |
540 | - MKDEBUG && _d($EVAL_ERROR); |
541 | - return 0; |
542 | - } |
543 | - if ( !scalar keys %$row ) { |
544 | - MKDEBUG && _d('Table has no columns:', Dumper($row)); |
545 | - return 0; |
546 | - } |
547 | - my $privs = $row->{privileges} || $row->{Privileges}; |
548 | - |
549 | - $sql = "DELETE FROM $db_tbl LIMIT 0"; |
550 | - MKDEBUG && _d($sql); |
551 | - eval { |
552 | - $dbh->do($sql); |
553 | - }; |
554 | - my $can_delete = $EVAL_ERROR ? 0 : 1; |
555 | - |
556 | - MKDEBUG && _d('User privs on', $db_tbl, ':', $privs, |
557 | - ($can_delete ? 'delete' : '')); |
558 | - |
559 | - if ( !($privs =~ m/select/ && $privs =~ m/insert/ && $privs =~ m/update/ |
560 | - && $can_delete) ) { |
561 | - MKDEBUG && _d('User does not have all privs'); |
562 | - return 0; |
563 | - } |
564 | - |
565 | - MKDEBUG && _d('User has all privs'); |
566 | - return 1; |
567 | -} |
568 | - |
569 | -sub get_engine { |
570 | - my ( $self, $ddl, $opts ) = @_; |
571 | - my ( $engine ) = $ddl =~ m/\).*?(?:ENGINE|TYPE)=(\w+)/; |
572 | - MKDEBUG && _d('Storage engine:', $engine); |
573 | - return $engine || undef; |
574 | -} |
575 | - |
576 | -sub get_keys { |
577 | - my ( $self, $ddl, $opts, $is_nullable ) = @_; |
578 | - my $engine = $self->get_engine($ddl); |
579 | - my $keys = {}; |
580 | - my $clustered_key = undef; |
581 | - |
582 | - KEY: |
583 | - foreach my $key ( $ddl =~ m/^ ((?:[A-Z]+ )?KEY .*)$/gm ) { |
584 | - |
585 | - next KEY if $key =~ m/FOREIGN/; |
586 | - |
587 | - my $key_ddl = $key; |
588 | - MKDEBUG && _d('Parsed key:', $key_ddl); |
589 | - |
590 | - if ( $engine !~ m/MEMORY|HEAP/ ) { |
591 | - $key =~ s/USING HASH/USING BTREE/; |
592 | - } |
593 | - |
594 | - my ( $type, $cols ) = $key =~ m/(?:USING (\w+))? \((.+)\)/; |
595 | - my ( $special ) = $key =~ m/(FULLTEXT|SPATIAL)/; |
596 | - $type = $type || $special || 'BTREE'; |
597 | - if ( $opts->{mysql_version} && $opts->{mysql_version} lt '004001000' |
598 | - && $engine =~ m/HEAP|MEMORY/i ) |
599 | + my ( $self, $dsn, $prev, $defaults ) = @_; |
600 | + if ( !$dsn ) { |
601 | + MKDEBUG && _d('No DSN to parse'); |
602 | + return; |
603 | + } |
604 | + MKDEBUG && _d('Parsing', $dsn); |
605 | + $prev ||= {}; |
606 | + $defaults ||= {}; |
607 | + my %given_props; |
608 | + my %final_props; |
609 | + my $opts = $self->{opts}; |
610 | + |
611 | + foreach my $dsn_part ( split(/,/, $dsn) ) { |
612 | + if ( my ($prop_key, $prop_val) = $dsn_part =~ m/^(.)=(.*)$/ ) { |
613 | + $given_props{$prop_key} = $prop_val; |
614 | + } |
615 | + else { |
616 | + MKDEBUG && _d('Interpreting', $dsn_part, 'as h=', $dsn_part); |
617 | + $given_props{h} = $dsn_part; |
618 | + } |
619 | + } |
620 | + |
621 | + foreach my $key ( keys %$opts ) { |
622 | + MKDEBUG && _d('Finding value for', $key); |
623 | + $final_props{$key} = $given_props{$key}; |
624 | + if ( !defined $final_props{$key} |
625 | + && defined $prev->{$key} && $opts->{$key}->{copy} ) |
626 | { |
627 | - $type = 'HASH'; # MySQL pre-4.1 supports only HASH indexes on HEAP |
628 | - } |
629 | - |
630 | - my ($name) = $key =~ m/(PRIMARY|`[^`]*`)/; |
631 | - my $unique = $key =~ m/PRIMARY|UNIQUE/ ? 1 : 0; |
632 | - my @cols; |
633 | - my @col_prefixes; |
634 | - foreach my $col_def ( $cols =~ m/`[^`]+`(?:\(\d+\))?/g ) { |
635 | - my ($name, $prefix) = $col_def =~ m/`([^`]+)`(?:\((\d+)\))?/; |
636 | - push @cols, $name; |
637 | - push @col_prefixes, $prefix; |
638 | - } |
639 | - $name =~ s/`//g; |
640 | - |
641 | - MKDEBUG && _d( $name, 'key cols:', join(', ', map { "`$_`" } @cols)); |
642 | - |
643 | - $keys->{$name} = { |
644 | - name => $name, |
645 | - type => $type, |
646 | - colnames => $cols, |
647 | - cols => \@cols, |
648 | - col_prefixes => \@col_prefixes, |
649 | - is_unique => $unique, |
650 | - is_nullable => scalar(grep { $is_nullable->{$_} } @cols), |
651 | - is_col => { map { $_ => 1 } @cols }, |
652 | - ddl => $key_ddl, |
653 | - }; |
654 | - |
655 | - if ( $engine =~ m/InnoDB/i && !$clustered_key ) { |
656 | - my $this_key = $keys->{$name}; |
657 | - if ( $this_key->{name} eq 'PRIMARY' ) { |
658 | - $clustered_key = 'PRIMARY'; |
659 | - } |
660 | - elsif ( $this_key->{is_unique} && !$this_key->{is_nullable} ) { |
661 | - $clustered_key = $this_key->{name}; |
662 | - } |
663 | - MKDEBUG && $clustered_key && _d('This key is the clustered key'); |
664 | - } |
665 | - } |
666 | - |
667 | - return $keys, $clustered_key; |
668 | -} |
669 | - |
670 | -sub get_fks { |
671 | - my ( $self, $ddl, $opts ) = @_; |
672 | - my $q = $self->{Quoter}; |
673 | - my $fks = {}; |
674 | - |
675 | - foreach my $fk ( |
676 | - $ddl =~ m/CONSTRAINT .* FOREIGN KEY .* REFERENCES [^\)]*\)/mg ) |
677 | - { |
678 | - my ( $name ) = $fk =~ m/CONSTRAINT `(.*?)`/; |
679 | - my ( $cols ) = $fk =~ m/FOREIGN KEY \(([^\)]+)\)/; |
680 | - my ( $parent, $parent_cols ) = $fk =~ m/REFERENCES (\S+) \(([^\)]+)\)/; |
681 | - |
682 | - my ($db, $tbl) = $q->split_unquote($parent, $opts->{database}); |
683 | - my %parent_tbl = (tbl => $tbl); |
684 | - $parent_tbl{db} = $db if $db; |
685 | - |
686 | - if ( $parent !~ m/\./ && $opts->{database} ) { |
687 | - $parent = $q->quote($opts->{database}) . ".$parent"; |
688 | - } |
689 | - |
690 | - $fks->{$name} = { |
691 | - name => $name, |
692 | - colnames => $cols, |
693 | - cols => [ map { s/[ `]+//g; $_; } split(',', $cols) ], |
694 | - parent_tbl => \%parent_tbl, |
695 | - parent_tblname => $parent, |
696 | - parent_cols => [ map { s/[ `]+//g; $_; } split(',', $parent_cols) ], |
697 | - parent_colnames=> $parent_cols, |
698 | - ddl => $fk, |
699 | - }; |
700 | - } |
701 | - |
702 | - return $fks; |
703 | -} |
704 | - |
705 | -sub remove_auto_increment { |
706 | - my ( $self, $ddl ) = @_; |
707 | - $ddl =~ s/(^\).*?) AUTO_INCREMENT=\d+\b/$1/m; |
708 | - return $ddl; |
709 | -} |
710 | - |
711 | -sub remove_secondary_indexes { |
712 | - my ( $self, $ddl ) = @_; |
713 | - my $sec_indexes_ddl; |
714 | - my $tbl_struct = $self->parse($ddl); |
715 | - |
716 | - if ( ($tbl_struct->{engine} || '') =~ m/InnoDB/i ) { |
717 | - my $clustered_key = $tbl_struct->{clustered_key}; |
718 | - $clustered_key ||= ''; |
719 | - |
720 | - my @sec_indexes = map { |
721 | - my $key_def = $_->{ddl}; |
722 | - $key_def =~ s/([\(\)])/\\$1/g; |
723 | - $ddl =~ s/\s+$key_def//i; |
724 | - |
725 | - my $key_ddl = "ADD $_->{ddl}"; |
726 | - $key_ddl .= ',' unless $key_ddl =~ m/,$/; |
727 | - $key_ddl; |
728 | - } |
729 | - grep { $_->{name} ne $clustered_key } |
730 | - values %{$tbl_struct->{keys}}; |
731 | - MKDEBUG && _d('Secondary indexes:', Dumper(\@sec_indexes)); |
732 | - |
733 | - if ( @sec_indexes ) { |
734 | - $sec_indexes_ddl = join(' ', @sec_indexes); |
735 | - $sec_indexes_ddl =~ s/,$//; |
736 | - } |
737 | - |
738 | - $ddl =~ s/,(\n\) )/$1/s; |
739 | + $final_props{$key} = $prev->{$key}; |
740 | + MKDEBUG && _d('Copying value for', $key, 'from previous DSN'); |
741 | + } |
742 | + if ( !defined $final_props{$key} ) { |
743 | + $final_props{$key} = $defaults->{$key}; |
744 | + MKDEBUG && _d('Copying value for', $key, 'from defaults'); |
745 | + } |
746 | + } |
747 | + |
748 | + foreach my $key ( keys %given_props ) { |
749 | + die "Unknown DSN option '$key' in '$dsn'. For more details, " |
750 | + . "please use the --help option, or try 'perldoc $PROGRAM_NAME' " |
751 | + . "for complete documentation." |
752 | + unless exists $opts->{$key}; |
753 | + } |
754 | + if ( (my $required = $self->prop('required')) ) { |
755 | + foreach my $key ( keys %$required ) { |
756 | + die "Missing required DSN option '$key' in '$dsn'. For more details, " |
757 | + . "please use the --help option, or try 'perldoc $PROGRAM_NAME' " |
758 | + . "for complete documentation." |
759 | + unless $final_props{$key}; |
760 | + } |
761 | + } |
762 | + |
763 | + return \%final_props; |
764 | +} |
765 | + |
766 | +sub parse_options { |
767 | + my ( $self, $o ) = @_; |
768 | + die 'I need an OptionParser object' unless ref $o eq 'OptionParser'; |
769 | + my $dsn_string |
770 | + = join(',', |
771 | + map { "$_=".$o->get($_); } |
772 | + grep { $o->has($_) && $o->get($_) } |
773 | + keys %{$self->{opts}} |
774 | + ); |
775 | + MKDEBUG && _d('DSN string made from options:', $dsn_string); |
776 | + return $self->parse($dsn_string); |
777 | +} |
778 | + |
779 | +sub as_string { |
780 | + my ( $self, $dsn, $props ) = @_; |
781 | + return $dsn unless ref $dsn; |
782 | + my @keys = $props ? @$props : sort keys %$dsn; |
783 | + return join(',', |
784 | + map { "$_=" . ($_ eq 'p' ? '...' : $dsn->{$_}) } |
785 | + grep { |
786 | + exists $self->{opts}->{$_} |
787 | + && exists $dsn->{$_} |
788 | + && defined $dsn->{$_} |
789 | + } @keys); |
790 | +} |
791 | + |
792 | +sub usage { |
793 | + my ( $self ) = @_; |
794 | + my $usage |
795 | + = "DSN syntax is key=value[,key=value...] Allowable DSN keys:\n\n" |
796 | + . " KEY COPY MEANING\n" |
797 | + . " === ==== =============================================\n"; |
798 | + my %opts = %{$self->{opts}}; |
799 | + foreach my $key ( sort keys %opts ) { |
800 | + $usage .= " $key " |
801 | + . ($opts{$key}->{copy} ? 'yes ' : 'no ') |
802 | + . ($opts{$key}->{desc} || '[No description]') |
803 | + . "\n"; |
804 | + } |
805 | + $usage .= "\n If the DSN is a bareword, the word is treated as the 'h' key.\n"; |
806 | + return $usage; |
807 | +} |
808 | + |
809 | +sub get_cxn_params { |
810 | + my ( $self, $info ) = @_; |
811 | + my $dsn; |
812 | + my %opts = %{$self->{opts}}; |
813 | + my $driver = $self->prop('dbidriver') || ''; |
814 | + if ( $driver eq 'Pg' ) { |
815 | + $dsn = 'DBI:Pg:dbname=' . ( $info->{D} || '' ) . ';' |
816 | + . join(';', map { "$opts{$_}->{dsn}=$info->{$_}" } |
817 | + grep { defined $info->{$_} } |
818 | + qw(h P)); |
819 | } |
820 | else { |
821 | - MKDEBUG && _d('Not removing secondary indexes from', |
822 | - $tbl_struct->{engine}, 'table'); |
823 | - } |
824 | - |
825 | - return $ddl, $sec_indexes_ddl, $tbl_struct; |
826 | -} |
827 | - |
828 | -sub _d { |
829 | - my ($package, undef, $line) = caller 0; |
830 | - @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; } |
831 | - map { defined $_ ? $_ : 'undef' } |
832 | - @_; |
833 | - print STDERR "# $package:$line $PID ", join(' ', @_), "\n"; |
834 | -} |
835 | - |
836 | -1; |
837 | -} |
838 | -# ########################################################################### |
839 | -# End TableParser package |
840 | -# ########################################################################### |
841 | - |
842 | -# ########################################################################### |
843 | -# TableChecksum package |
844 | -# This package is a copy without comments from the original. The original |
845 | -# with comments and its test file can be found in the Bazaar repository at, |
846 | -# lib/TableChecksum.pm |
847 | -# t/lib/TableChecksum.t |
848 | -# See https://launchpad.net/percona-toolkit for more information. |
849 | -# ########################################################################### |
850 | -{ |
851 | -package TableChecksum; |
852 | - |
853 | -use strict; |
854 | -use warnings FATAL => 'all'; |
855 | -use English qw(-no_match_vars); |
856 | -use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
857 | - |
858 | -use List::Util qw(max); |
859 | - |
860 | -our %ALGOS = ( |
861 | - CHECKSUM => { pref => 0, hash => 0 }, |
862 | - BIT_XOR => { pref => 2, hash => 1 }, |
863 | - ACCUM => { pref => 3, hash => 1 }, |
864 | -); |
865 | - |
866 | -sub new { |
867 | - my ( $class, %args ) = @_; |
868 | - foreach my $arg ( qw(Quoter VersionParser) ) { |
869 | - die "I need a $arg argument" unless defined $args{$arg}; |
870 | - } |
871 | - my $self = { %args }; |
872 | - return bless $self, $class; |
873 | -} |
874 | - |
875 | -sub crc32 { |
876 | - my ( $self, $string ) = @_; |
877 | - my $poly = 0xEDB88320; |
878 | - my $crc = 0xFFFFFFFF; |
879 | - foreach my $char ( split(//, $string) ) { |
880 | - my $comp = ($crc ^ ord($char)) & 0xFF; |
881 | - for ( 1 .. 8 ) { |
882 | - $comp = $comp & 1 ? $poly ^ ($comp >> 1) : $comp >> 1; |
883 | - } |
884 | - $crc = (($crc >> 8) & 0x00FFFFFF) ^ $comp; |
885 | - } |
886 | - return $crc ^ 0xFFFFFFFF; |
887 | -} |
888 | - |
889 | -sub get_crc_wid { |
890 | - my ( $self, $dbh, $func ) = @_; |
891 | - my $crc_wid = 16; |
892 | - if ( uc $func ne 'FNV_64' && uc $func ne 'FNV1A_64' ) { |
893 | - eval { |
894 | - my ($val) = $dbh->selectrow_array("SELECT $func('a')"); |
895 | - $crc_wid = max(16, length($val)); |
896 | - }; |
897 | - } |
898 | - return $crc_wid; |
899 | -} |
900 | - |
901 | -sub get_crc_type { |
902 | - my ( $self, $dbh, $func ) = @_; |
903 | - my $type = ''; |
904 | - my $length = 0; |
905 | - my $sql = "SELECT $func('a')"; |
906 | - my $sth = $dbh->prepare($sql); |
907 | - eval { |
908 | - $sth->execute(); |
909 | - $type = $sth->{mysql_type_name}->[0]; |
910 | - $length = $sth->{mysql_length}->[0]; |
911 | - MKDEBUG && _d($sql, $type, $length); |
912 | - if ( $type eq 'bigint' && $length < 20 ) { |
913 | - $type = 'int'; |
914 | - } |
915 | + $dsn = 'DBI:mysql:' . ( $info->{D} || '' ) . ';' |
916 | + . join(';', map { "$opts{$_}->{dsn}=$info->{$_}" } |
917 | + grep { defined $info->{$_} } |
918 | + qw(F h P S A)) |
919 | + . ';mysql_read_default_group=client'; |
920 | + } |
921 | + MKDEBUG && _d($dsn); |
922 | + return ($dsn, $info->{u}, $info->{p}); |
923 | +} |
924 | + |
925 | +sub fill_in_dsn { |
926 | + my ( $self, $dbh, $dsn ) = @_; |
927 | + my $vars = $dbh->selectall_hashref('SHOW VARIABLES', 'Variable_name'); |
928 | + my ($user, $db) = $dbh->selectrow_array('SELECT USER(), DATABASE()'); |
929 | + $user =~ s/@.*//; |
930 | + $dsn->{h} ||= $vars->{hostname}->{Value}; |
931 | + $dsn->{S} ||= $vars->{'socket'}->{Value}; |
932 | + $dsn->{P} ||= $vars->{port}->{Value}; |
933 | + $dsn->{u} ||= $user; |
934 | + $dsn->{D} ||= $db; |
935 | +} |
936 | + |
937 | +sub get_dbh { |
938 | + my ( $self, $cxn_string, $user, $pass, $opts ) = @_; |
939 | + $opts ||= {}; |
940 | + my $defaults = { |
941 | + AutoCommit => 0, |
942 | + RaiseError => 1, |
943 | + PrintError => 0, |
944 | + ShowErrorStatement => 1, |
945 | + mysql_enable_utf8 => ($cxn_string =~ m/charset=utf8/i ? 1 : 0), |
946 | }; |
947 | - $sth->finish; |
948 | - MKDEBUG && _d('crc_type:', $type, 'length:', $length); |
949 | - return ($type, $length); |
950 | -} |
951 | - |
952 | -sub best_algorithm { |
953 | - my ( $self, %args ) = @_; |
954 | - my ( $alg, $dbh ) = @args{ qw(algorithm dbh) }; |
955 | - my $vp = $self->{VersionParser}; |
956 | - my @choices = sort { $ALGOS{$a}->{pref} <=> $ALGOS{$b}->{pref} } keys %ALGOS; |
957 | - die "Invalid checksum algorithm $alg" |
958 | - if $alg && !$ALGOS{$alg}; |
959 | - |
960 | - if ( |
961 | - $args{where} || $args{chunk} # CHECKSUM does whole table |
962 | - || $args{replicate} # CHECKSUM can't do INSERT.. SELECT |
963 | - || !$vp->version_ge($dbh, '4.1.1')) # CHECKSUM doesn't exist |
964 | - { |
965 | - MKDEBUG && _d('Cannot use CHECKSUM algorithm'); |
966 | - @choices = grep { $_ ne 'CHECKSUM' } @choices; |
967 | - } |
968 | - |
969 | - if ( !$vp->version_ge($dbh, '4.1.1') ) { |
970 | - MKDEBUG && _d('Cannot use BIT_XOR algorithm because MySQL < 4.1.1'); |
971 | - @choices = grep { $_ ne 'BIT_XOR' } @choices; |
972 | - } |
973 | - |
974 | - if ( $alg && grep { $_ eq $alg } @choices ) { |
975 | - MKDEBUG && _d('User requested', $alg, 'algorithm'); |
976 | - return $alg; |
977 | - } |
978 | - |
979 | - if ( $args{count} && grep { $_ ne 'CHECKSUM' } @choices ) { |
980 | - MKDEBUG && _d('Not using CHECKSUM algorithm because COUNT desired'); |
981 | - @choices = grep { $_ ne 'CHECKSUM' } @choices; |
982 | - } |
983 | - |
984 | - MKDEBUG && _d('Algorithms, in order:', @choices); |
985 | - return $choices[0]; |
986 | -} |
987 | - |
988 | -sub is_hash_algorithm { |
989 | - my ( $self, $algorithm ) = @_; |
990 | - return $ALGOS{$algorithm} && $ALGOS{$algorithm}->{hash}; |
991 | -} |
992 | - |
993 | -sub choose_hash_func { |
994 | - my ( $self, %args ) = @_; |
995 | - my @funcs = qw(CRC32 FNV1A_64 FNV_64 MD5 SHA1); |
996 | - if ( $args{function} ) { |
997 | - unshift @funcs, $args{function}; |
998 | - } |
999 | - my ($result, $error); |
1000 | - do { |
1001 | - my $func; |
1002 | + @{$defaults}{ keys %$opts } = values %$opts; |
1003 | + |
1004 | + if ( $opts->{mysql_use_result} ) { |
1005 | + $defaults->{mysql_use_result} = 1; |
1006 | + } |
1007 | + |
1008 | + if ( !$have_dbi ) { |
1009 | + die "Cannot connect to MySQL because the Perl DBI module is not " |
1010 | + . "installed or not found. Run 'perl -MDBI' to see the directories " |
1011 | + . "that Perl searches for DBI. If DBI is not installed, try:\n" |
1012 | + . " Debian/Ubuntu apt-get install libdbi-perl\n" |
1013 | + . " RHEL/CentOS yum install perl-DBI\n" |
1014 | + . " OpenSolaris pgk install pkg:/SUNWpmdbi\n"; |
1015 | + |
1016 | + } |
1017 | + |
1018 | + my $dbh; |
1019 | + my $tries = 2; |
1020 | + while ( !$dbh && $tries-- ) { |
1021 | + MKDEBUG && _d($cxn_string, ' ', $user, ' ', $pass, |
1022 | + join(', ', map { "$_=>$defaults->{$_}" } keys %$defaults )); |
1023 | + |
1024 | eval { |
1025 | - $func = shift(@funcs); |
1026 | - my $sql = "SELECT $func('test-string')"; |
1027 | - MKDEBUG && _d($sql); |
1028 | - $args{dbh}->do($sql); |
1029 | - $result = $func; |
1030 | + $dbh = DBI->connect($cxn_string, $user, $pass, $defaults); |
1031 | + |
1032 | + if ( $cxn_string =~ m/mysql/i ) { |
1033 | + my $sql; |
1034 | + |
1035 | + $sql = 'SELECT @@SQL_MODE'; |
1036 | + MKDEBUG && _d($dbh, $sql); |
1037 | + my ($sql_mode) = $dbh->selectrow_array($sql); |
1038 | + |
1039 | + $sql = 'SET @@SQL_QUOTE_SHOW_CREATE = 1' |
1040 | + . '/*!40101, @@SQL_MODE=\'NO_AUTO_VALUE_ON_ZERO' |
1041 | + . ($sql_mode ? ",$sql_mode" : '') |
1042 | + . '\'*/'; |
1043 | + MKDEBUG && _d($dbh, $sql); |
1044 | + $dbh->do($sql); |
1045 | + |
1046 | + if ( my ($charset) = $cxn_string =~ m/charset=(\w+)/ ) { |
1047 | + $sql = "/*!40101 SET NAMES $charset*/"; |
1048 | + MKDEBUG && _d($dbh, ':', $sql); |
1049 | + $dbh->do($sql); |
1050 | + MKDEBUG && _d('Enabling charset for STDOUT'); |
1051 | + if ( $charset eq 'utf8' ) { |
1052 | + binmode(STDOUT, ':utf8') |
1053 | + or die "Can't binmode(STDOUT, ':utf8'): $OS_ERROR"; |
1054 | + } |
1055 | + else { |
1056 | + binmode(STDOUT) or die "Can't binmode(STDOUT): $OS_ERROR"; |
1057 | + } |
1058 | + } |
1059 | + |
1060 | + if ( $self->prop('set-vars') ) { |
1061 | + $sql = "SET " . $self->prop('set-vars'); |
1062 | + MKDEBUG && _d($dbh, ':', $sql); |
1063 | + $dbh->do($sql); |
1064 | + } |
1065 | + } |
1066 | }; |
1067 | - if ( $EVAL_ERROR && $EVAL_ERROR =~ m/failed: (.*?) at \S+ line/ ) { |
1068 | - $error .= qq{$func cannot be used because "$1"\n}; |
1069 | - MKDEBUG && _d($func, 'cannot be used because', $1); |
1070 | - } |
1071 | - } while ( @funcs && !$result ); |
1072 | - |
1073 | - die $error unless $result; |
1074 | - MKDEBUG && _d('Chosen hash func:', $result); |
1075 | - return $result; |
1076 | -} |
1077 | - |
1078 | -sub optimize_xor { |
1079 | - my ( $self, %args ) = @_; |
1080 | - my ($dbh, $func) = @args{qw(dbh function)}; |
1081 | - |
1082 | - die "$func never needs the BIT_XOR optimization" |
1083 | - if $func =~ m/^(?:FNV1A_64|FNV_64|CRC32)$/i; |
1084 | - |
1085 | - my $opt_slice = 0; |
1086 | - my $unsliced = uc $dbh->selectall_arrayref("SELECT $func('a')")->[0]->[0]; |
1087 | - my $sliced = ''; |
1088 | - my $start = 1; |
1089 | - my $crc_wid = length($unsliced) < 16 ? 16 : length($unsliced); |
1090 | - |
1091 | - do { # Try different positions till sliced result equals non-sliced. |
1092 | - MKDEBUG && _d('Trying slice', $opt_slice); |
1093 | - $dbh->do('SET @crc := "", @cnt := 0'); |
1094 | - my $slices = $self->make_xor_slices( |
1095 | - query => "\@crc := $func('a')", |
1096 | - crc_wid => $crc_wid, |
1097 | - opt_slice => $opt_slice, |
1098 | - ); |
1099 | - |
1100 | - my $sql = "SELECT CONCAT($slices) AS TEST FROM (SELECT NULL) AS x"; |
1101 | - $sliced = ($dbh->selectrow_array($sql))[0]; |
1102 | - if ( $sliced ne $unsliced ) { |
1103 | - MKDEBUG && _d('Slice', $opt_slice, 'does not work'); |
1104 | - $start += 16; |
1105 | - ++$opt_slice; |
1106 | - } |
1107 | - } while ( $start < $crc_wid && $sliced ne $unsliced ); |
1108 | - |
1109 | - if ( $sliced eq $unsliced ) { |
1110 | - MKDEBUG && _d('Slice', $opt_slice, 'works'); |
1111 | - return $opt_slice; |
1112 | - } |
1113 | - else { |
1114 | - MKDEBUG && _d('No slice works'); |
1115 | - return undef; |
1116 | - } |
1117 | -} |
1118 | - |
1119 | -sub make_xor_slices { |
1120 | - my ( $self, %args ) = @_; |
1121 | - foreach my $arg ( qw(query crc_wid) ) { |
1122 | - die "I need a $arg argument" unless defined $args{$arg}; |
1123 | - } |
1124 | - my ( $query, $crc_wid, $opt_slice ) = @args{qw(query crc_wid opt_slice)}; |
1125 | - |
1126 | - my @slices; |
1127 | - for ( my $start = 1; $start <= $crc_wid; $start += 16 ) { |
1128 | - my $len = $crc_wid - $start + 1; |
1129 | - if ( $len > 16 ) { |
1130 | - $len = 16; |
1131 | - } |
1132 | - push @slices, |
1133 | - "LPAD(CONV(BIT_XOR(" |
1134 | - . "CAST(CONV(SUBSTRING(\@crc, $start, $len), 16, 10) AS UNSIGNED))" |
1135 | - . ", 10, 16), $len, '0')"; |
1136 | - } |
1137 | - |
1138 | - if ( defined $opt_slice && $opt_slice < @slices ) { |
1139 | - $slices[$opt_slice] =~ s/\@crc/\@crc := $query/; |
1140 | - } |
1141 | - else { |
1142 | - map { s/\@crc/$query/ } @slices; |
1143 | - } |
1144 | - |
1145 | - return join(', ', @slices); |
1146 | -} |
1147 | - |
1148 | -sub make_row_checksum { |
1149 | - my ( $self, %args ) = @_; |
1150 | - my ( $tbl_struct, $func ) = @args{ qw(tbl_struct function) }; |
1151 | - my $q = $self->{Quoter}; |
1152 | - |
1153 | - my $sep = $args{sep} || '#'; |
1154 | - $sep =~ s/'//g; |
1155 | - $sep ||= '#'; |
1156 | - |
1157 | - my $ignorecols = $args{ignorecols} || {}; |
1158 | - |
1159 | - my %cols = map { lc($_) => 1 } |
1160 | - grep { !exists $ignorecols->{$_} } |
1161 | - ($args{cols} ? @{$args{cols}} : @{$tbl_struct->{cols}}); |
1162 | - my %seen; |
1163 | - my @cols = |
1164 | - map { |
1165 | - my $type = $tbl_struct->{type_for}->{$_}; |
1166 | - my $result = $q->quote($_); |
1167 | - if ( $type eq 'timestamp' ) { |
1168 | - $result .= ' + 0'; |
1169 | - } |
1170 | - elsif ( $args{float_precision} && $type =~ m/float|double/ ) { |
1171 | - $result = "ROUND($result, $args{float_precision})"; |
1172 | - } |
1173 | - elsif ( $args{trim} && $type =~ m/varchar/ ) { |
1174 | - $result = "TRIM($result)"; |
1175 | - } |
1176 | - $result; |
1177 | - } |
1178 | - grep { |
1179 | - $cols{$_} && !$seen{$_}++ |
1180 | - } |
1181 | - @{$tbl_struct->{cols}}; |
1182 | - |
1183 | - my $query; |
1184 | - if ( !$args{no_cols} ) { |
1185 | - $query = join(', ', |
1186 | - map { |
1187 | - my $col = $_; |
1188 | - if ( $col =~ m/\+ 0/ ) { |
1189 | - my ($real_col) = /^(\S+)/; |
1190 | - $col .= " AS $real_col"; |
1191 | - } |
1192 | - elsif ( $col =~ m/TRIM/ ) { |
1193 | - my ($real_col) = m/TRIM\(([^\)]+)\)/; |
1194 | - $col .= " AS $real_col"; |
1195 | - } |
1196 | - $col; |
1197 | - } @cols) |
1198 | - . ', '; |
1199 | - } |
1200 | - |
1201 | - if ( uc $func ne 'FNV_64' && uc $func ne 'FNV1A_64' ) { |
1202 | - my @nulls = grep { $cols{$_} } @{$tbl_struct->{null_cols}}; |
1203 | - if ( @nulls ) { |
1204 | - my $bitmap = "CONCAT(" |
1205 | - . join(', ', map { 'ISNULL(' . $q->quote($_) . ')' } @nulls) |
1206 | - . ")"; |
1207 | - push @cols, $bitmap; |
1208 | - } |
1209 | - |
1210 | - $query .= @cols > 1 |
1211 | - ? "$func(CONCAT_WS('$sep', " . join(', ', @cols) . '))' |
1212 | - : "$func($cols[0])"; |
1213 | - } |
1214 | - else { |
1215 | - my $fnv_func = uc $func; |
1216 | - $query .= "$fnv_func(" . join(', ', @cols) . ')'; |
1217 | - } |
1218 | - |
1219 | - return $query; |
1220 | -} |
1221 | - |
1222 | -sub make_checksum_query { |
1223 | - my ( $self, %args ) = @_; |
1224 | - my @required_args = qw(db tbl tbl_struct algorithm crc_wid crc_type); |
1225 | - foreach my $arg( @required_args ) { |
1226 | - die "I need a $arg argument" unless $args{$arg}; |
1227 | - } |
1228 | - my ( $db, $tbl, $tbl_struct, $algorithm, |
1229 | - $crc_wid, $crc_type) = @args{@required_args}; |
1230 | - my $func = $args{function}; |
1231 | - my $q = $self->{Quoter}; |
1232 | - my $result; |
1233 | - |
1234 | - die "Invalid or missing checksum algorithm" |
1235 | - unless $algorithm && $ALGOS{$algorithm}; |
1236 | - |
1237 | - if ( $algorithm eq 'CHECKSUM' ) { |
1238 | - return "CHECKSUM TABLE " . $q->quote($db, $tbl); |
1239 | - } |
1240 | - |
1241 | - my $expr = $self->make_row_checksum(%args, no_cols=>1); |
1242 | - |
1243 | - if ( $algorithm eq 'BIT_XOR' ) { |
1244 | - if ( $crc_type =~ m/int$/ ) { |
1245 | - $result = "COALESCE(LOWER(CONV(BIT_XOR(CAST($expr AS UNSIGNED)), 10, 16)), 0) AS crc "; |
1246 | - } |
1247 | - else { |
1248 | - my $slices = $self->make_xor_slices( query => $expr, %args ); |
1249 | - $result = "COALESCE(LOWER(CONCAT($slices)), 0) AS crc "; |
1250 | - } |
1251 | - } |
1252 | - else { |
1253 | - if ( $crc_type =~ m/int$/ ) { |
1254 | - $result = "COALESCE(RIGHT(MAX(" |
1255 | - . "\@crc := CONCAT(LPAD(\@cnt := \@cnt + 1, 16, '0'), " |
1256 | - . "CONV(CAST($func(CONCAT(\@crc, $expr)) AS UNSIGNED), 10, 16))" |
1257 | - . "), $crc_wid), 0) AS crc "; |
1258 | - } |
1259 | - else { |
1260 | - $result = "COALESCE(RIGHT(MAX(" |
1261 | - . "\@crc := CONCAT(LPAD(\@cnt := \@cnt + 1, 16, '0'), " |
1262 | - . "$func(CONCAT(\@crc, $expr)))" |
1263 | - . "), $crc_wid), 0) AS crc "; |
1264 | - } |
1265 | - } |
1266 | - if ( $args{replicate} ) { |
1267 | - $result = "REPLACE /*PROGRESS_COMMENT*/ INTO $args{replicate} " |
1268 | - . "(db, tbl, chunk, boundaries, this_cnt, this_crc) " |
1269 | - . "SELECT ?, ?, /*CHUNK_NUM*/ ?, COUNT(*) AS cnt, $result"; |
1270 | - } |
1271 | - else { |
1272 | - $result = "SELECT " |
1273 | - . ($args{buffer} ? 'SQL_BUFFER_RESULT ' : '') |
1274 | - . "/*PROGRESS_COMMENT*//*CHUNK_NUM*/ COUNT(*) AS cnt, $result"; |
1275 | - } |
1276 | - return $result . "FROM /*DB_TBL*//*INDEX_HINT*//*WHERE*/"; |
1277 | -} |
1278 | - |
1279 | -sub find_replication_differences { |
1280 | - my ( $self, $dbh, $table ) = @_; |
1281 | - |
1282 | - (my $sql = <<" EOF") =~ s/\s+/ /gm; |
1283 | - SELECT db, tbl, chunk, boundaries, |
1284 | - COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, |
1285 | - COALESCE( |
1286 | - this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), |
1287 | - 0 |
1288 | - ) AS crc_diff, |
1289 | - this_cnt, master_cnt, this_crc, master_crc |
1290 | - FROM $table |
1291 | - WHERE master_cnt <> this_cnt OR master_crc <> this_crc |
1292 | - OR ISNULL(master_crc) <> ISNULL(this_crc) |
1293 | - EOF |
1294 | - |
1295 | - MKDEBUG && _d($sql); |
1296 | - my $diffs = $dbh->selectall_arrayref($sql, { Slice => {} }); |
1297 | - return @$diffs; |
1298 | + if ( !$dbh && $EVAL_ERROR ) { |
1299 | + MKDEBUG && _d($EVAL_ERROR); |
1300 | + if ( $EVAL_ERROR =~ m/not a compiled character set|character set utf8/ ) { |
1301 | + MKDEBUG && _d('Going to try again without utf8 support'); |
1302 | + delete $defaults->{mysql_enable_utf8}; |
1303 | + } |
1304 | + elsif ( $EVAL_ERROR =~ m/locate DBD\/mysql/i ) { |
1305 | + die "Cannot connect to MySQL because the Perl DBD::mysql module is " |
1306 | + . "not installed or not found. Run 'perl -MDBD::mysql' to see " |
1307 | + . "the directories that Perl searches for DBD::mysql. If " |
1308 | + . "DBD::mysql is not installed, try:\n" |
1309 | + . " Debian/Ubuntu apt-get install libdbd-mysql-perl\n" |
1310 | + . " RHEL/CentOS yum install perl-DBD-MySQL\n" |
1311 | + . " OpenSolaris pgk install pkg:/SUNWapu13dbd-mysql\n"; |
1312 | + } |
1313 | + if ( !$tries ) { |
1314 | + die $EVAL_ERROR; |
1315 | + } |
1316 | + } |
1317 | + } |
1318 | + |
1319 | + MKDEBUG && _d('DBH info: ', |
1320 | + $dbh, |
1321 | + Dumper($dbh->selectrow_hashref( |
1322 | + 'SELECT DATABASE(), CONNECTION_ID(), VERSION()/*!50038 , @@hostname*/')), |
1323 | + 'Connection info:', $dbh->{mysql_hostinfo}, |
1324 | + 'Character set info:', Dumper($dbh->selectall_arrayref( |
1325 | + 'SHOW VARIABLES LIKE "character_set%"', { Slice => {}})), |
1326 | + '$DBD::mysql::VERSION:', $DBD::mysql::VERSION, |
1327 | + '$DBI::VERSION:', $DBI::VERSION, |
1328 | + ); |
1329 | + |
1330 | + return $dbh; |
1331 | +} |
1332 | + |
1333 | +sub get_hostname { |
1334 | + my ( $self, $dbh ) = @_; |
1335 | + if ( my ($host) = ($dbh->{mysql_hostinfo} || '') =~ m/^(\w+) via/ ) { |
1336 | + return $host; |
1337 | + } |
1338 | + my ( $hostname, $one ) = $dbh->selectrow_array( |
1339 | + 'SELECT /*!50038 @@hostname, */ 1'); |
1340 | + return $hostname; |
1341 | +} |
1342 | + |
1343 | +sub disconnect { |
1344 | + my ( $self, $dbh ) = @_; |
1345 | + MKDEBUG && $self->print_active_handles($dbh); |
1346 | + $dbh->disconnect; |
1347 | +} |
1348 | + |
1349 | +sub print_active_handles { |
1350 | + my ( $self, $thing, $level ) = @_; |
1351 | + $level ||= 0; |
1352 | + printf("# Active %sh: %s %s %s\n", ($thing->{Type} || 'undef'), "\t" x $level, |
1353 | + $thing, (($thing->{Type} || '') eq 'st' ? $thing->{Statement} || '' : '')) |
1354 | + or die "Cannot print: $OS_ERROR"; |
1355 | + foreach my $handle ( grep {defined} @{ $thing->{ChildHandles} } ) { |
1356 | + $self->print_active_handles( $handle, $level + 1 ); |
1357 | + } |
1358 | +} |
1359 | + |
1360 | +sub copy { |
1361 | + my ( $self, $dsn_1, $dsn_2, %args ) = @_; |
1362 | + die 'I need a dsn_1 argument' unless $dsn_1; |
1363 | + die 'I need a dsn_2 argument' unless $dsn_2; |
1364 | + my %new_dsn = map { |
1365 | + my $key = $_; |
1366 | + my $val; |
1367 | + if ( $args{overwrite} ) { |
1368 | + $val = defined $dsn_1->{$key} ? $dsn_1->{$key} : $dsn_2->{$key}; |
1369 | + } |
1370 | + else { |
1371 | + $val = defined $dsn_2->{$key} ? $dsn_2->{$key} : $dsn_1->{$key}; |
1372 | + } |
1373 | + $key => $val; |
1374 | + } keys %{$self->{opts}}; |
1375 | + return \%new_dsn; |
1376 | } |
1377 | |
1378 | sub _d { |
1379 | @@ -789,7 +363,7 @@ |
1380 | 1; |
1381 | } |
1382 | # ########################################################################### |
1383 | -# End TableChecksum package |
1384 | +# End DSNParser package |
1385 | # ########################################################################### |
1386 | |
1387 | # ########################################################################### |
1388 | @@ -1743,7 +1317,7 @@ |
1389 | $opt->{value} = ($pre || '') . $num; |
1390 | } |
1391 | else { |
1392 | - $self->save_error("Invalid size for --$opt->{long}"); |
1393 | + $self->save_error("Invalid size for --$opt->{long}: $val"); |
1394 | } |
1395 | return; |
1396 | } |
1397 | @@ -1818,345 +1392,129 @@ |
1398 | # ########################################################################### |
1399 | |
1400 | # ########################################################################### |
1401 | -# DSNParser package |
1402 | +# Cxn package |
1403 | # This package is a copy without comments from the original. The original |
1404 | # with comments and its test file can be found in the Bazaar repository at, |
1405 | -# lib/DSNParser.pm |
1406 | -# t/lib/DSNParser.t |
1407 | +# lib/Cxn.pm |
1408 | +# t/lib/Cxn.t |
1409 | # See https://launchpad.net/percona-toolkit for more information. |
1410 | # ########################################################################### |
1411 | { |
1412 | -package DSNParser; |
1413 | +package Cxn; |
1414 | |
1415 | use strict; |
1416 | use warnings FATAL => 'all'; |
1417 | use English qw(-no_match_vars); |
1418 | use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
1419 | |
1420 | -use Data::Dumper; |
1421 | -$Data::Dumper::Indent = 0; |
1422 | -$Data::Dumper::Quotekeys = 0; |
1423 | - |
1424 | -eval { |
1425 | - require DBI; |
1426 | -}; |
1427 | -my $have_dbi = $EVAL_ERROR ? 0 : 1; |
1428 | +use constant PERCONA_TOOLKIT_TEST_USE_DSN_NAMES => $ENV{PERCONA_TOOLKIT_TEST_USE_DSN_NAMES} || 0; |
1429 | |
1430 | sub new { |
1431 | my ( $class, %args ) = @_; |
1432 | - foreach my $arg ( qw(opts) ) { |
1433 | + my @required_args = qw(DSNParser OptionParser); |
1434 | + foreach my $arg ( @required_args ) { |
1435 | die "I need a $arg argument" unless $args{$arg}; |
1436 | - } |
1437 | + }; |
1438 | + my ($dp, $o) = @args{@required_args}; |
1439 | + |
1440 | + my $dsn_defaults = $dp->parse_options($o); |
1441 | + my $prev_dsn = $args{prev_dsn}; |
1442 | + my $dsn = $args{dsn}; |
1443 | + if ( !$dsn ) { |
1444 | + $args{dsn_string} ||= 'h=' . ($dsn_defaults->{h} || 'localhost'); |
1445 | + |
1446 | + $dsn = $dp->parse( |
1447 | + $args{dsn_string}, $prev_dsn, $dsn_defaults); |
1448 | + } |
1449 | + elsif ( $prev_dsn ) { |
1450 | + $dsn = $dp->copy($prev_dsn, $dsn); |
1451 | + } |
1452 | + |
1453 | my $self = { |
1454 | - opts => {} # h, P, u, etc. Should come from DSN OPTIONS section in POD. |
1455 | + dsn => $dsn, |
1456 | + dbh => $args{dbh}, |
1457 | + dsn_name => $dp->as_string($dsn, [qw(h P S)]), |
1458 | + hostname => '', |
1459 | + set => $args{set}, |
1460 | + dbh_set => 0, |
1461 | + OptionParser => $o, |
1462 | + DSNParser => $dp, |
1463 | }; |
1464 | - foreach my $opt ( @{$args{opts}} ) { |
1465 | - if ( !$opt->{key} || !$opt->{desc} ) { |
1466 | - die "Invalid DSN option: ", Dumper($opt); |
1467 | - } |
1468 | - MKDEBUG && _d('DSN option:', |
1469 | - join(', ', |
1470 | - map { "$_=" . (defined $opt->{$_} ? ($opt->{$_} || '') : 'undef') } |
1471 | - keys %$opt |
1472 | - ) |
1473 | - ); |
1474 | - $self->{opts}->{$opt->{key}} = { |
1475 | - dsn => $opt->{dsn}, |
1476 | - desc => $opt->{desc}, |
1477 | - copy => $opt->{copy} || 0, |
1478 | - }; |
1479 | - } |
1480 | + |
1481 | return bless $self, $class; |
1482 | } |
1483 | |
1484 | -sub prop { |
1485 | - my ( $self, $prop, $value ) = @_; |
1486 | - if ( @_ > 2 ) { |
1487 | - MKDEBUG && _d('Setting', $prop, 'property'); |
1488 | - $self->{$prop} = $value; |
1489 | - } |
1490 | - return $self->{$prop}; |
1491 | -} |
1492 | - |
1493 | -sub parse { |
1494 | - my ( $self, $dsn, $prev, $defaults ) = @_; |
1495 | - if ( !$dsn ) { |
1496 | - MKDEBUG && _d('No DSN to parse'); |
1497 | - return; |
1498 | - } |
1499 | - MKDEBUG && _d('Parsing', $dsn); |
1500 | - $prev ||= {}; |
1501 | - $defaults ||= {}; |
1502 | - my %given_props; |
1503 | - my %final_props; |
1504 | - my $opts = $self->{opts}; |
1505 | - |
1506 | - foreach my $dsn_part ( split(/,/, $dsn) ) { |
1507 | - if ( my ($prop_key, $prop_val) = $dsn_part =~ m/^(.)=(.*)$/ ) { |
1508 | - $given_props{$prop_key} = $prop_val; |
1509 | - } |
1510 | - else { |
1511 | - MKDEBUG && _d('Interpreting', $dsn_part, 'as h=', $dsn_part); |
1512 | - $given_props{h} = $dsn_part; |
1513 | - } |
1514 | - } |
1515 | - |
1516 | - foreach my $key ( keys %$opts ) { |
1517 | - MKDEBUG && _d('Finding value for', $key); |
1518 | - $final_props{$key} = $given_props{$key}; |
1519 | - if ( !defined $final_props{$key} |
1520 | - && defined $prev->{$key} && $opts->{$key}->{copy} ) |
1521 | - { |
1522 | - $final_props{$key} = $prev->{$key}; |
1523 | - MKDEBUG && _d('Copying value for', $key, 'from previous DSN'); |
1524 | - } |
1525 | - if ( !defined $final_props{$key} ) { |
1526 | - $final_props{$key} = $defaults->{$key}; |
1527 | - MKDEBUG && _d('Copying value for', $key, 'from defaults'); |
1528 | - } |
1529 | - } |
1530 | - |
1531 | - foreach my $key ( keys %given_props ) { |
1532 | - die "Unknown DSN option '$key' in '$dsn'. For more details, " |
1533 | - . "please use the --help option, or try 'perldoc $PROGRAM_NAME' " |
1534 | - . "for complete documentation." |
1535 | - unless exists $opts->{$key}; |
1536 | - } |
1537 | - if ( (my $required = $self->prop('required')) ) { |
1538 | - foreach my $key ( keys %$required ) { |
1539 | - die "Missing required DSN option '$key' in '$dsn'. For more details, " |
1540 | - . "please use the --help option, or try 'perldoc $PROGRAM_NAME' " |
1541 | - . "for complete documentation." |
1542 | - unless $final_props{$key}; |
1543 | - } |
1544 | - } |
1545 | - |
1546 | - return \%final_props; |
1547 | -} |
1548 | - |
1549 | -sub parse_options { |
1550 | - my ( $self, $o ) = @_; |
1551 | - die 'I need an OptionParser object' unless ref $o eq 'OptionParser'; |
1552 | - my $dsn_string |
1553 | - = join(',', |
1554 | - map { "$_=".$o->get($_); } |
1555 | - grep { $o->has($_) && $o->get($_) } |
1556 | - keys %{$self->{opts}} |
1557 | - ); |
1558 | - MKDEBUG && _d('DSN string made from options:', $dsn_string); |
1559 | - return $self->parse($dsn_string); |
1560 | -} |
1561 | - |
1562 | -sub as_string { |
1563 | - my ( $self, $dsn, $props ) = @_; |
1564 | - return $dsn unless ref $dsn; |
1565 | - my %allowed = $props ? map { $_=>1 } @$props : (); |
1566 | - return join(',', |
1567 | - map { "$_=" . ($_ eq 'p' ? '...' : $dsn->{$_}) } |
1568 | - grep { defined $dsn->{$_} && $self->{opts}->{$_} } |
1569 | - grep { !$props || $allowed{$_} } |
1570 | - sort keys %$dsn ); |
1571 | -} |
1572 | - |
1573 | -sub usage { |
1574 | +sub connect { |
1575 | my ( $self ) = @_; |
1576 | - my $usage |
1577 | - = "DSN syntax is key=value[,key=value...] Allowable DSN keys:\n\n" |
1578 | - . " KEY COPY MEANING\n" |
1579 | - . " === ==== =============================================\n"; |
1580 | - my %opts = %{$self->{opts}}; |
1581 | - foreach my $key ( sort keys %opts ) { |
1582 | - $usage .= " $key " |
1583 | - . ($opts{$key}->{copy} ? 'yes ' : 'no ') |
1584 | - . ($opts{$key}->{desc} || '[No description]') |
1585 | - . "\n"; |
1586 | - } |
1587 | - $usage .= "\n If the DSN is a bareword, the word is treated as the 'h' key.\n"; |
1588 | - return $usage; |
1589 | -} |
1590 | - |
1591 | -sub get_cxn_params { |
1592 | - my ( $self, $info ) = @_; |
1593 | - my $dsn; |
1594 | - my %opts = %{$self->{opts}}; |
1595 | - my $driver = $self->prop('dbidriver') || ''; |
1596 | - if ( $driver eq 'Pg' ) { |
1597 | - $dsn = 'DBI:Pg:dbname=' . ( $info->{D} || '' ) . ';' |
1598 | - . join(';', map { "$opts{$_}->{dsn}=$info->{$_}" } |
1599 | - grep { defined $info->{$_} } |
1600 | - qw(h P)); |
1601 | - } |
1602 | - else { |
1603 | - $dsn = 'DBI:mysql:' . ( $info->{D} || '' ) . ';' |
1604 | - . join(';', map { "$opts{$_}->{dsn}=$info->{$_}" } |
1605 | - grep { defined $info->{$_} } |
1606 | - qw(F h P S A)) |
1607 | - . ';mysql_read_default_group=client'; |
1608 | - } |
1609 | - MKDEBUG && _d($dsn); |
1610 | - return ($dsn, $info->{u}, $info->{p}); |
1611 | -} |
1612 | - |
1613 | -sub fill_in_dsn { |
1614 | - my ( $self, $dbh, $dsn ) = @_; |
1615 | - my $vars = $dbh->selectall_hashref('SHOW VARIABLES', 'Variable_name'); |
1616 | - my ($user, $db) = $dbh->selectrow_array('SELECT USER(), DATABASE()'); |
1617 | - $user =~ s/@.*//; |
1618 | - $dsn->{h} ||= $vars->{hostname}->{Value}; |
1619 | - $dsn->{S} ||= $vars->{'socket'}->{Value}; |
1620 | - $dsn->{P} ||= $vars->{port}->{Value}; |
1621 | - $dsn->{u} ||= $user; |
1622 | - $dsn->{D} ||= $db; |
1623 | -} |
1624 | - |
1625 | -sub get_dbh { |
1626 | - my ( $self, $cxn_string, $user, $pass, $opts ) = @_; |
1627 | - $opts ||= {}; |
1628 | - my $defaults = { |
1629 | - AutoCommit => 0, |
1630 | - RaiseError => 1, |
1631 | - PrintError => 0, |
1632 | - ShowErrorStatement => 1, |
1633 | - mysql_enable_utf8 => ($cxn_string =~ m/charset=utf8/i ? 1 : 0), |
1634 | - }; |
1635 | - @{$defaults}{ keys %$opts } = values %$opts; |
1636 | - |
1637 | - if ( $opts->{mysql_use_result} ) { |
1638 | - $defaults->{mysql_use_result} = 1; |
1639 | - } |
1640 | - |
1641 | - if ( !$have_dbi ) { |
1642 | - die "Cannot connect to MySQL because the Perl DBI module is not " |
1643 | - . "installed or not found. Run 'perl -MDBI' to see the directories " |
1644 | - . "that Perl searches for DBI. If DBI is not installed, try:\n" |
1645 | - . " Debian/Ubuntu apt-get install libdbi-perl\n" |
1646 | - . " RHEL/CentOS yum install perl-DBI\n" |
1647 | - . " OpenSolaris pgk install pkg:/SUNWpmdbi\n"; |
1648 | - |
1649 | - } |
1650 | - |
1651 | - my $dbh; |
1652 | - my $tries = 2; |
1653 | - while ( !$dbh && $tries-- ) { |
1654 | - MKDEBUG && _d($cxn_string, ' ', $user, ' ', $pass, |
1655 | - join(', ', map { "$_=>$defaults->{$_}" } keys %$defaults )); |
1656 | - |
1657 | - eval { |
1658 | - $dbh = DBI->connect($cxn_string, $user, $pass, $defaults); |
1659 | - |
1660 | - if ( $cxn_string =~ m/mysql/i ) { |
1661 | - my $sql; |
1662 | - |
1663 | - $sql = 'SELECT @@SQL_MODE'; |
1664 | - MKDEBUG && _d($dbh, $sql); |
1665 | - my ($sql_mode) = $dbh->selectrow_array($sql); |
1666 | - |
1667 | - $sql = 'SET @@SQL_QUOTE_SHOW_CREATE = 1' |
1668 | - . '/*!40101, @@SQL_MODE=\'NO_AUTO_VALUE_ON_ZERO' |
1669 | - . ($sql_mode ? ",$sql_mode" : '') |
1670 | - . '\'*/'; |
1671 | - MKDEBUG && _d($dbh, $sql); |
1672 | - $dbh->do($sql); |
1673 | - |
1674 | - if ( my ($charset) = $cxn_string =~ m/charset=(\w+)/ ) { |
1675 | - $sql = "/*!40101 SET NAMES $charset*/"; |
1676 | - MKDEBUG && _d($dbh, ':', $sql); |
1677 | - $dbh->do($sql); |
1678 | - MKDEBUG && _d('Enabling charset for STDOUT'); |
1679 | - if ( $charset eq 'utf8' ) { |
1680 | - binmode(STDOUT, ':utf8') |
1681 | - or die "Can't binmode(STDOUT, ':utf8'): $OS_ERROR"; |
1682 | - } |
1683 | - else { |
1684 | - binmode(STDOUT) or die "Can't binmode(STDOUT): $OS_ERROR"; |
1685 | - } |
1686 | - } |
1687 | - |
1688 | - if ( $self->prop('set-vars') ) { |
1689 | - $sql = "SET " . $self->prop('set-vars'); |
1690 | - MKDEBUG && _d($dbh, ':', $sql); |
1691 | - $dbh->do($sql); |
1692 | - } |
1693 | - } |
1694 | - }; |
1695 | - if ( !$dbh && $EVAL_ERROR ) { |
1696 | - MKDEBUG && _d($EVAL_ERROR); |
1697 | - if ( $EVAL_ERROR =~ m/not a compiled character set|character set utf8/ ) { |
1698 | - MKDEBUG && _d('Going to try again without utf8 support'); |
1699 | - delete $defaults->{mysql_enable_utf8}; |
1700 | - } |
1701 | - elsif ( $EVAL_ERROR =~ m/locate DBD\/mysql/i ) { |
1702 | - die "Cannot connect to MySQL because the Perl DBD::mysql module is " |
1703 | - . "not installed or not found. Run 'perl -MDBD::mysql' to see " |
1704 | - . "the directories that Perl searches for DBD::mysql. If " |
1705 | - . "DBD::mysql is not installed, try:\n" |
1706 | - . " Debian/Ubuntu apt-get install libdbd-mysql-perl\n" |
1707 | - . " RHEL/CentOS yum install perl-DBD-MySQL\n" |
1708 | - . " OpenSolaris pgk install pkg:/SUNWapu13dbd-mysql\n"; |
1709 | - } |
1710 | - if ( !$tries ) { |
1711 | - die $EVAL_ERROR; |
1712 | - } |
1713 | + my $dsn = $self->{dsn}; |
1714 | + my $dp = $self->{DSNParser}; |
1715 | + my $o = $self->{OptionParser}; |
1716 | + |
1717 | + my $dbh = $self->{dbh}; |
1718 | + if ( !$dbh || !$dbh->ping() ) { |
1719 | + if ( $o->get('ask-pass') && !$self->{asked_for_pass} ) { |
1720 | + $dsn->{p} = OptionParser::prompt_noecho("Enter MySQL password: "); |
1721 | + $self->{asked_for_pass} = 1; |
1722 | } |
1723 | - } |
1724 | - |
1725 | - MKDEBUG && _d('DBH info: ', |
1726 | - $dbh, |
1727 | - Dumper($dbh->selectrow_hashref( |
1728 | - 'SELECT DATABASE(), CONNECTION_ID(), VERSION()/*!50038 , @@hostname*/')), |
1729 | - 'Connection info:', $dbh->{mysql_hostinfo}, |
1730 | - 'Character set info:', Dumper($dbh->selectall_arrayref( |
1731 | - 'SHOW VARIABLES LIKE "character_set%"', { Slice => {}})), |
1732 | - '$DBD::mysql::VERSION:', $DBD::mysql::VERSION, |
1733 | - '$DBI::VERSION:', $DBI::VERSION, |
1734 | - ); |
1735 | - |
1736 | + $dbh = $dp->get_dbh($dp->get_cxn_params($dsn), { AutoCommit => 1 }); |
1737 | + } |
1738 | + MKDEBUG && _d($dbh, 'Connected dbh to', $self->{name}); |
1739 | + |
1740 | + return $self->set_dbh($dbh); |
1741 | +} |
1742 | + |
1743 | +sub set_dbh { |
1744 | + my ($self, $dbh) = @_; |
1745 | + |
1746 | + if ( $self->{dbh} && $self->{dbh} == $dbh && $self->{dbh_set} ) { |
1747 | + MKDEBUG && _d($dbh, 'Already set dbh'); |
1748 | + return $dbh; |
1749 | + } |
1750 | + |
1751 | + MKDEBUG && _d($dbh, 'Setting dbh'); |
1752 | + |
1753 | + $dbh->{FetchHashKeyName} = 'NAME_lc'; |
1754 | + |
1755 | + my $sql = 'SELECT @@hostname, @@server_id'; |
1756 | + MKDEBUG && _d($dbh, $sql); |
1757 | + my ($hostname, $server_id) = $dbh->selectrow_array($sql); |
1758 | + MKDEBUG && _d($dbh, 'hostname:', $hostname, $server_id); |
1759 | + if ( $hostname ) { |
1760 | + $self->{hostname} = $hostname; |
1761 | + } |
1762 | + |
1763 | + if ( my $set = $self->{set}) { |
1764 | + $set->($dbh); |
1765 | + } |
1766 | + |
1767 | + $self->{dbh} = $dbh; |
1768 | + $self->{dbh_set} = 1; |
1769 | return $dbh; |
1770 | } |
1771 | |
1772 | -sub get_hostname { |
1773 | - my ( $self, $dbh ) = @_; |
1774 | - if ( my ($host) = ($dbh->{mysql_hostinfo} || '') =~ m/^(\w+) via/ ) { |
1775 | - return $host; |
1776 | - } |
1777 | - my ( $hostname, $one ) = $dbh->selectrow_array( |
1778 | - 'SELECT /*!50038 @@hostname, */ 1'); |
1779 | - return $hostname; |
1780 | -} |
1781 | - |
1782 | -sub disconnect { |
1783 | - my ( $self, $dbh ) = @_; |
1784 | - MKDEBUG && $self->print_active_handles($dbh); |
1785 | - $dbh->disconnect; |
1786 | -} |
1787 | - |
1788 | -sub print_active_handles { |
1789 | - my ( $self, $thing, $level ) = @_; |
1790 | - $level ||= 0; |
1791 | - printf("# Active %sh: %s %s %s\n", ($thing->{Type} || 'undef'), "\t" x $level, |
1792 | - $thing, (($thing->{Type} || '') eq 'st' ? $thing->{Statement} || '' : '')) |
1793 | - or die "Cannot print: $OS_ERROR"; |
1794 | - foreach my $handle ( grep {defined} @{ $thing->{ChildHandles} } ) { |
1795 | - $self->print_active_handles( $handle, $level + 1 ); |
1796 | - } |
1797 | -} |
1798 | - |
1799 | -sub copy { |
1800 | - my ( $self, $dsn_1, $dsn_2, %args ) = @_; |
1801 | - die 'I need a dsn_1 argument' unless $dsn_1; |
1802 | - die 'I need a dsn_2 argument' unless $dsn_2; |
1803 | - my %new_dsn = map { |
1804 | - my $key = $_; |
1805 | - my $val; |
1806 | - if ( $args{overwrite} ) { |
1807 | - $val = defined $dsn_1->{$key} ? $dsn_1->{$key} : $dsn_2->{$key}; |
1808 | - } |
1809 | - else { |
1810 | - $val = defined $dsn_2->{$key} ? $dsn_2->{$key} : $dsn_1->{$key}; |
1811 | - } |
1812 | - $key => $val; |
1813 | - } keys %{$self->{opts}}; |
1814 | - return \%new_dsn; |
1815 | +sub dbh { |
1816 | + my ($self) = @_; |
1817 | + return $self->{dbh}; |
1818 | +} |
1819 | + |
1820 | +sub dsn { |
1821 | + my ($self) = @_; |
1822 | + return $self->{dsn}; |
1823 | +} |
1824 | + |
1825 | +sub name { |
1826 | + my ($self) = @_; |
1827 | + return $self->{dsn_name} if PERCONA_TOOLKIT_TEST_USE_DSN_NAMES; |
1828 | + return $self->{hostname} || $self->{dsn_name} || 'unknown host'; |
1829 | +} |
1830 | + |
1831 | +sub DESTROY { |
1832 | + my ($self) = @_; |
1833 | + if ( $self->{dbh} ) { |
1834 | + MKDEBUG && _d('Disconnecting dbh', $self->{dbh}, $self->{name}); |
1835 | + $self->{dbh}->disconnect(); |
1836 | + } |
1837 | + return; |
1838 | } |
1839 | |
1840 | sub _d { |
1841 | @@ -2170,7 +1528,84 @@ |
1842 | 1; |
1843 | } |
1844 | # ########################################################################### |
1845 | -# End DSNParser package |
1846 | +# End Cxn package |
1847 | +# ########################################################################### |
1848 | + |
1849 | +# ########################################################################### |
1850 | +# Quoter package |
1851 | +# This package is a copy without comments from the original. The original |
1852 | +# with comments and its test file can be found in the Bazaar repository at, |
1853 | +# lib/Quoter.pm |
1854 | +# t/lib/Quoter.t |
1855 | +# See https://launchpad.net/percona-toolkit for more information. |
1856 | +# ########################################################################### |
1857 | +{ |
1858 | +package Quoter; |
1859 | + |
1860 | +use strict; |
1861 | +use warnings FATAL => 'all'; |
1862 | +use English qw(-no_match_vars); |
1863 | +use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
1864 | + |
1865 | +sub new { |
1866 | + my ( $class, %args ) = @_; |
1867 | + return bless {}, $class; |
1868 | +} |
1869 | + |
1870 | +sub quote { |
1871 | + my ( $self, @vals ) = @_; |
1872 | + foreach my $val ( @vals ) { |
1873 | + $val =~ s/`/``/g; |
1874 | + } |
1875 | + return join('.', map { '`' . $_ . '`' } @vals); |
1876 | +} |
1877 | + |
1878 | +sub quote_val { |
1879 | + my ( $self, $val ) = @_; |
1880 | + |
1881 | + return 'NULL' unless defined $val; # undef = NULL |
1882 | + return "''" if $val eq ''; # blank string = '' |
1883 | + return $val if $val =~ m/^0x[0-9a-fA-F]+$/; # hex data |
1884 | + |
1885 | + $val =~ s/(['\\])/\\$1/g; |
1886 | + return "'$val'"; |
1887 | +} |
1888 | + |
1889 | +sub split_unquote { |
1890 | + my ( $self, $db_tbl, $default_db ) = @_; |
1891 | + $db_tbl =~ s/`//g; |
1892 | + my ( $db, $tbl ) = split(/[.]/, $db_tbl); |
1893 | + if ( !$tbl ) { |
1894 | + $tbl = $db; |
1895 | + $db = $default_db; |
1896 | + } |
1897 | + return ($db, $tbl); |
1898 | +} |
1899 | + |
1900 | +sub literal_like { |
1901 | + my ( $self, $like ) = @_; |
1902 | + return unless $like; |
1903 | + $like =~ s/([%_])/\\$1/g; |
1904 | + return "'$like'"; |
1905 | +} |
1906 | + |
1907 | +sub join_quote { |
1908 | + my ( $self, $default_db, $db_tbl ) = @_; |
1909 | + return unless $db_tbl; |
1910 | + my ($db, $tbl) = split(/[.]/, $db_tbl); |
1911 | + if ( !$tbl ) { |
1912 | + $tbl = $db; |
1913 | + $db = $default_db; |
1914 | + } |
1915 | + $db = "`$db`" if $db && $db !~ m/^`/; |
1916 | + $tbl = "`$tbl`" if $tbl && $tbl !~ m/^`/; |
1917 | + return $db ? "$db.$tbl" : $tbl; |
1918 | +} |
1919 | + |
1920 | +1; |
1921 | +} |
1922 | +# ########################################################################### |
1923 | +# End Quoter package |
1924 | # ########################################################################### |
1925 | |
1926 | # ########################################################################### |
1927 | @@ -2257,294 +1692,417 @@ |
1928 | # ########################################################################### |
1929 | |
1930 | # ########################################################################### |
1931 | -# MySQLDump package |
1932 | +# TableParser package |
1933 | # This package is a copy without comments from the original. The original |
1934 | # with comments and its test file can be found in the Bazaar repository at, |
1935 | -# lib/MySQLDump.pm |
1936 | -# t/lib/MySQLDump.t |
1937 | +# lib/TableParser.pm |
1938 | +# t/lib/TableParser.t |
1939 | # See https://launchpad.net/percona-toolkit for more information. |
1940 | # ########################################################################### |
1941 | { |
1942 | -package MySQLDump; |
1943 | +package TableParser; |
1944 | |
1945 | use strict; |
1946 | use warnings FATAL => 'all'; |
1947 | use English qw(-no_match_vars); |
1948 | use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
1949 | |
1950 | -( our $before = <<'EOF') =~ s/^ //gm; |
1951 | - /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
1952 | - /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
1953 | - /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
1954 | - /*!40101 SET NAMES utf8 */; |
1955 | - /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
1956 | - /*!40103 SET TIME_ZONE='+00:00' */; |
1957 | - /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
1958 | - /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
1959 | - /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
1960 | - /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
1961 | -EOF |
1962 | - |
1963 | -( our $after = <<'EOF') =~ s/^ //gm; |
1964 | - /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
1965 | - /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
1966 | - /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
1967 | - /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
1968 | - /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
1969 | - /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
1970 | - /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
1971 | - /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
1972 | -EOF |
1973 | +use Data::Dumper; |
1974 | +$Data::Dumper::Indent = 1; |
1975 | +$Data::Dumper::Sortkeys = 1; |
1976 | +$Data::Dumper::Quotekeys = 0; |
1977 | |
1978 | sub new { |
1979 | my ( $class, %args ) = @_; |
1980 | - my $self = { |
1981 | - cache => 0, # Afaik no script uses this cache any longer because |
1982 | - }; |
1983 | + my @required_args = qw(Quoter); |
1984 | + foreach my $arg ( @required_args ) { |
1985 | + die "I need a $arg argument" unless $args{$arg}; |
1986 | + } |
1987 | + my $self = { %args }; |
1988 | return bless $self, $class; |
1989 | } |
1990 | |
1991 | -sub dump { |
1992 | - my ( $self, $dbh, $quoter, $db, $tbl, $what ) = @_; |
1993 | - |
1994 | - if ( $what eq 'table' ) { |
1995 | - my $ddl = $self->get_create_table($dbh, $quoter, $db, $tbl); |
1996 | - return unless $ddl; |
1997 | - if ( $ddl->[0] eq 'table' ) { |
1998 | - return $before |
1999 | - . 'DROP TABLE IF EXISTS ' . $quoter->quote($tbl) . ";\n" |
2000 | - . $ddl->[1] . ";\n"; |
2001 | - } |
2002 | - else { |
2003 | - return 'DROP TABLE IF EXISTS ' . $quoter->quote($tbl) . ";\n" |
2004 | - . '/*!50001 DROP VIEW IF EXISTS ' |
2005 | - . $quoter->quote($tbl) . "*/;\n/*!50001 " |
2006 | - . $self->get_tmp_table($dbh, $quoter, $db, $tbl) . "*/;\n"; |
2007 | - } |
2008 | - } |
2009 | - elsif ( $what eq 'triggers' ) { |
2010 | - my $trgs = $self->get_triggers($dbh, $quoter, $db, $tbl); |
2011 | - if ( $trgs && @$trgs ) { |
2012 | - my $result = $before . "\nDELIMITER ;;\n"; |
2013 | - foreach my $trg ( @$trgs ) { |
2014 | - if ( $trg->{sql_mode} ) { |
2015 | - $result .= qq{/*!50003 SET SESSION SQL_MODE='$trg->{sql_mode}' */;;\n}; |
2016 | - } |
2017 | - $result .= "/*!50003 CREATE */ "; |
2018 | - if ( $trg->{definer} ) { |
2019 | - my ( $user, $host ) |
2020 | - = map { s/'/''/g; "'$_'"; } |
2021 | - split('@', $trg->{definer}, 2); |
2022 | - $result .= "/*!50017 DEFINER=$user\@$host */ "; |
2023 | - } |
2024 | - $result .= sprintf("/*!50003 TRIGGER %s %s %s ON %s\nFOR EACH ROW %s */;;\n\n", |
2025 | - $quoter->quote($trg->{trigger}), |
2026 | - @{$trg}{qw(timing event)}, |
2027 | - $quoter->quote($trg->{table}), |
2028 | - $trg->{statement}); |
2029 | - } |
2030 | - $result .= "DELIMITER ;\n\n/*!50003 SET SESSION SQL_MODE=\@OLD_SQL_MODE */;\n\n"; |
2031 | - return $result; |
2032 | - } |
2033 | - else { |
2034 | - return undef; |
2035 | - } |
2036 | - } |
2037 | - elsif ( $what eq 'view' ) { |
2038 | - my $ddl = $self->get_create_table($dbh, $quoter, $db, $tbl); |
2039 | - return '/*!50001 DROP TABLE IF EXISTS ' . $quoter->quote($tbl) . "*/;\n" |
2040 | - . '/*!50001 DROP VIEW IF EXISTS ' . $quoter->quote($tbl) . "*/;\n" |
2041 | - . '/*!50001 ' . $ddl->[1] . "*/;\n"; |
2042 | - } |
2043 | - else { |
2044 | - die "You didn't say what to dump."; |
2045 | - } |
2046 | -} |
2047 | - |
2048 | -sub _use_db { |
2049 | - my ( $self, $dbh, $quoter, $new ) = @_; |
2050 | - if ( !$new ) { |
2051 | - MKDEBUG && _d('No new DB to use'); |
2052 | - return; |
2053 | - } |
2054 | - my $sql = 'USE ' . $quoter->quote($new); |
2055 | - MKDEBUG && _d($dbh, $sql); |
2056 | - $dbh->do($sql); |
2057 | - return; |
2058 | -} |
2059 | - |
2060 | sub get_create_table { |
2061 | - my ( $self, $dbh, $quoter, $db, $tbl ) = @_; |
2062 | - if ( !$self->{cache} || !$self->{tables}->{$db}->{$tbl} ) { |
2063 | - my $sql = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, ' |
2064 | - . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), } |
2065 | - . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, ' |
2066 | - . '@@SQL_QUOTE_SHOW_CREATE := 1 */'; |
2067 | - MKDEBUG && _d($sql); |
2068 | - eval { $dbh->do($sql); }; |
2069 | - MKDEBUG && $EVAL_ERROR && _d($EVAL_ERROR); |
2070 | - $self->_use_db($dbh, $quoter, $db); |
2071 | - $sql = "SHOW CREATE TABLE " . $quoter->quote($db, $tbl); |
2072 | - MKDEBUG && _d($sql); |
2073 | - my $href; |
2074 | - eval { $href = $dbh->selectrow_hashref($sql); }; |
2075 | - if ( $EVAL_ERROR ) { |
2076 | - warn "Failed to $sql. The table may be damaged.\nError: $EVAL_ERROR"; |
2077 | - return; |
2078 | - } |
2079 | - |
2080 | - $sql = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, ' |
2081 | - . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */'; |
2082 | - MKDEBUG && _d($sql); |
2083 | - $dbh->do($sql); |
2084 | - my ($key) = grep { m/create table/i } keys %$href; |
2085 | - if ( $key ) { |
2086 | - MKDEBUG && _d('This table is a base table'); |
2087 | - $self->{tables}->{$db}->{$tbl} = [ 'table', $href->{$key} ]; |
2088 | + my ( $self, $dbh, $db, $tbl ) = @_; |
2089 | + die "I need a dbh parameter" unless $dbh; |
2090 | + die "I need a db parameter" unless $db; |
2091 | + die "I need a tbl parameter" unless $tbl; |
2092 | + my $q = $self->{Quoter}; |
2093 | + |
2094 | + my $sql = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, ' |
2095 | + . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), } |
2096 | + . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, ' |
2097 | + . '@@SQL_QUOTE_SHOW_CREATE := 1 */'; |
2098 | + MKDEBUG && _d($sql); |
2099 | + eval { $dbh->do($sql); }; |
2100 | + MKDEBUG && $EVAL_ERROR && _d($EVAL_ERROR); |
2101 | + |
2102 | + $sql = 'USE ' . $q->quote($db); |
2103 | + MKDEBUG && _d($dbh, $sql); |
2104 | + $dbh->do($sql); |
2105 | + |
2106 | + $sql = "SHOW CREATE TABLE " . $q->quote($db, $tbl); |
2107 | + MKDEBUG && _d($sql); |
2108 | + my $href; |
2109 | + eval { $href = $dbh->selectrow_hashref($sql); }; |
2110 | + if ( $EVAL_ERROR ) { |
2111 | + MKDEBUG && _d($EVAL_ERROR); |
2112 | + return; |
2113 | + } |
2114 | + |
2115 | + $sql = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, ' |
2116 | + . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */'; |
2117 | + MKDEBUG && _d($sql); |
2118 | + $dbh->do($sql); |
2119 | + |
2120 | + my ($key) = grep { m/create table/i } keys %$href; |
2121 | + if ( $key ) { |
2122 | + MKDEBUG && _d('This table is a base table'); |
2123 | + $href->{$key} =~ s/\b[ ]{2,}/ /g; |
2124 | + $href->{$key} .= "\n"; |
2125 | + } |
2126 | + else { |
2127 | + MKDEBUG && _d('This table is a view'); |
2128 | + ($key) = grep { m/create view/i } keys %$href; |
2129 | + } |
2130 | + |
2131 | + return $href->{$key}; |
2132 | +} |
2133 | + |
2134 | +sub parse { |
2135 | + my ( $self, $ddl, $opts ) = @_; |
2136 | + return unless $ddl; |
2137 | + |
2138 | + if ( $ddl !~ m/CREATE (?:TEMPORARY )?TABLE `/ ) { |
2139 | + die "Cannot parse table definition; is ANSI quoting " |
2140 | + . "enabled or SQL_QUOTE_SHOW_CREATE disabled?"; |
2141 | + } |
2142 | + |
2143 | + my ($name) = $ddl =~ m/CREATE (?:TEMPORARY )?TABLE\s+(`.+?`)/; |
2144 | + (undef, $name) = $self->{Quoter}->split_unquote($name) if $name; |
2145 | + |
2146 | + $ddl =~ s/(`[^`]+`)/\L$1/g; |
2147 | + |
2148 | + my $engine = $self->get_engine($ddl); |
2149 | + |
2150 | + my @defs = $ddl =~ m/^(\s+`.*?),?$/gm; |
2151 | + my @cols = map { $_ =~ m/`([^`]+)`/ } @defs; |
2152 | + MKDEBUG && _d('Table cols:', join(', ', map { "`$_`" } @cols)); |
2153 | + |
2154 | + my %def_for; |
2155 | + @def_for{@cols} = @defs; |
2156 | + |
2157 | + my (@nums, @null); |
2158 | + my (%type_for, %is_nullable, %is_numeric, %is_autoinc); |
2159 | + foreach my $col ( @cols ) { |
2160 | + my $def = $def_for{$col}; |
2161 | + my ( $type ) = $def =~ m/`[^`]+`\s([a-z]+)/; |
2162 | + die "Can't determine column type for $def" unless $type; |
2163 | + $type_for{$col} = $type; |
2164 | + if ( $type =~ m/(?:(?:tiny|big|medium|small)?int|float|double|decimal|year)/ ) { |
2165 | + push @nums, $col; |
2166 | + $is_numeric{$col} = 1; |
2167 | + } |
2168 | + if ( $def !~ m/NOT NULL/ ) { |
2169 | + push @null, $col; |
2170 | + $is_nullable{$col} = 1; |
2171 | + } |
2172 | + $is_autoinc{$col} = $def =~ m/AUTO_INCREMENT/i ? 1 : 0; |
2173 | + } |
2174 | + |
2175 | + my ($keys, $clustered_key) = $self->get_keys($ddl, $opts, \%is_nullable); |
2176 | + |
2177 | + my ($charset) = $ddl =~ m/DEFAULT CHARSET=(\w+)/; |
2178 | + |
2179 | + return { |
2180 | + name => $name, |
2181 | + cols => \@cols, |
2182 | + col_posn => { map { $cols[$_] => $_ } 0..$#cols }, |
2183 | + is_col => { map { $_ => 1 } @cols }, |
2184 | + null_cols => \@null, |
2185 | + is_nullable => \%is_nullable, |
2186 | + is_autoinc => \%is_autoinc, |
2187 | + clustered_key => $clustered_key, |
2188 | + keys => $keys, |
2189 | + defs => \%def_for, |
2190 | + numeric_cols => \@nums, |
2191 | + is_numeric => \%is_numeric, |
2192 | + engine => $engine, |
2193 | + type_for => \%type_for, |
2194 | + charset => $charset, |
2195 | + }; |
2196 | +} |
2197 | + |
2198 | +sub sort_indexes { |
2199 | + my ( $self, $tbl ) = @_; |
2200 | + |
2201 | + my @indexes |
2202 | + = sort { |
2203 | + (($a ne 'PRIMARY') <=> ($b ne 'PRIMARY')) |
2204 | + || ( !$tbl->{keys}->{$a}->{is_unique} <=> !$tbl->{keys}->{$b}->{is_unique} ) |
2205 | + || ( $tbl->{keys}->{$a}->{is_nullable} <=> $tbl->{keys}->{$b}->{is_nullable} ) |
2206 | + || ( scalar(@{$tbl->{keys}->{$a}->{cols}}) <=> scalar(@{$tbl->{keys}->{$b}->{cols}}) ) |
2207 | + } |
2208 | + grep { |
2209 | + $tbl->{keys}->{$_}->{type} eq 'BTREE' |
2210 | + } |
2211 | + sort keys %{$tbl->{keys}}; |
2212 | + |
2213 | + MKDEBUG && _d('Indexes sorted best-first:', join(', ', @indexes)); |
2214 | + return @indexes; |
2215 | +} |
2216 | + |
2217 | +sub find_best_index { |
2218 | + my ( $self, $tbl, $index ) = @_; |
2219 | + my $best; |
2220 | + if ( $index ) { |
2221 | + ($best) = grep { uc $_ eq uc $index } keys %{$tbl->{keys}}; |
2222 | + } |
2223 | + if ( !$best ) { |
2224 | + if ( $index ) { |
2225 | + die "Index '$index' does not exist in table"; |
2226 | } |
2227 | else { |
2228 | - MKDEBUG && _d('This table is a view'); |
2229 | - ($key) = grep { m/create view/i } keys %$href; |
2230 | - $self->{tables}->{$db}->{$tbl} = [ 'view', $href->{$key} ]; |
2231 | - } |
2232 | - } |
2233 | - return $self->{tables}->{$db}->{$tbl}; |
2234 | -} |
2235 | - |
2236 | -sub get_columns { |
2237 | - my ( $self, $dbh, $quoter, $db, $tbl ) = @_; |
2238 | - MKDEBUG && _d('Get columns for', $db, $tbl); |
2239 | - if ( !$self->{cache} || !$self->{columns}->{$db}->{$tbl} ) { |
2240 | - $self->_use_db($dbh, $quoter, $db); |
2241 | - my $sql = "SHOW COLUMNS FROM " . $quoter->quote($db, $tbl); |
2242 | - MKDEBUG && _d($sql); |
2243 | - my $cols = $dbh->selectall_arrayref($sql, { Slice => {} }); |
2244 | - |
2245 | - $self->{columns}->{$db}->{$tbl} = [ |
2246 | - map { |
2247 | - my %row; |
2248 | - @row{ map { lc $_ } keys %$_ } = values %$_; |
2249 | - \%row; |
2250 | - } @$cols |
2251 | - ]; |
2252 | - } |
2253 | - return $self->{columns}->{$db}->{$tbl}; |
2254 | -} |
2255 | - |
2256 | -sub get_tmp_table { |
2257 | - my ( $self, $dbh, $quoter, $db, $tbl ) = @_; |
2258 | - my $result = 'CREATE TABLE ' . $quoter->quote($tbl) . " (\n"; |
2259 | - $result .= join(",\n", |
2260 | - map { ' ' . $quoter->quote($_->{field}) . ' ' . $_->{type} } |
2261 | - @{$self->get_columns($dbh, $quoter, $db, $tbl)}); |
2262 | - $result .= "\n)"; |
2263 | - MKDEBUG && _d($result); |
2264 | - return $result; |
2265 | -} |
2266 | - |
2267 | -sub get_triggers { |
2268 | - my ( $self, $dbh, $quoter, $db, $tbl ) = @_; |
2269 | - if ( !$self->{cache} || !$self->{triggers}->{$db} ) { |
2270 | - $self->{triggers}->{$db} = {}; |
2271 | - my $sql = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, ' |
2272 | - . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), } |
2273 | - . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, ' |
2274 | - . '@@SQL_QUOTE_SHOW_CREATE := 1 */'; |
2275 | - MKDEBUG && _d($sql); |
2276 | - eval { $dbh->do($sql); }; |
2277 | - MKDEBUG && $EVAL_ERROR && _d($EVAL_ERROR); |
2278 | - $sql = "SHOW TRIGGERS FROM " . $quoter->quote($db); |
2279 | - MKDEBUG && _d($sql); |
2280 | - my $sth = $dbh->prepare($sql); |
2281 | - $sth->execute(); |
2282 | - if ( $sth->rows ) { |
2283 | - my $trgs = $sth->fetchall_arrayref({}); |
2284 | - foreach my $trg (@$trgs) { |
2285 | - my %trg; |
2286 | - @trg{ map { lc $_ } keys %$trg } = values %$trg; |
2287 | - push @{ $self->{triggers}->{$db}->{ $trg{table} } }, \%trg; |
2288 | - } |
2289 | - } |
2290 | - $sql = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, ' |
2291 | - . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */'; |
2292 | - MKDEBUG && _d($sql); |
2293 | + ($best) = $self->sort_indexes($tbl); |
2294 | + } |
2295 | + } |
2296 | + MKDEBUG && _d('Best index found is', $best); |
2297 | + return $best; |
2298 | +} |
2299 | + |
2300 | +sub find_possible_keys { |
2301 | + my ( $self, $dbh, $database, $table, $quoter, $where ) = @_; |
2302 | + return () unless $where; |
2303 | + my $sql = 'EXPLAIN SELECT * FROM ' . $quoter->quote($database, $table) |
2304 | + . ' WHERE ' . $where; |
2305 | + MKDEBUG && _d($sql); |
2306 | + my $expl = $dbh->selectrow_hashref($sql); |
2307 | + $expl = { map { lc($_) => $expl->{$_} } keys %$expl }; |
2308 | + if ( $expl->{possible_keys} ) { |
2309 | + MKDEBUG && _d('possible_keys =', $expl->{possible_keys}); |
2310 | + my @candidates = split(',', $expl->{possible_keys}); |
2311 | + my %possible = map { $_ => 1 } @candidates; |
2312 | + if ( $expl->{key} ) { |
2313 | + MKDEBUG && _d('MySQL chose', $expl->{key}); |
2314 | + unshift @candidates, grep { $possible{$_} } split(',', $expl->{key}); |
2315 | + MKDEBUG && _d('Before deduping:', join(', ', @candidates)); |
2316 | + my %seen; |
2317 | + @candidates = grep { !$seen{$_}++ } @candidates; |
2318 | + } |
2319 | + MKDEBUG && _d('Final list:', join(', ', @candidates)); |
2320 | + return @candidates; |
2321 | + } |
2322 | + else { |
2323 | + MKDEBUG && _d('No keys in possible_keys'); |
2324 | + return (); |
2325 | + } |
2326 | +} |
2327 | + |
2328 | +sub check_table { |
2329 | + my ( $self, %args ) = @_; |
2330 | + my @required_args = qw(dbh db tbl); |
2331 | + foreach my $arg ( @required_args ) { |
2332 | + die "I need a $arg argument" unless $args{$arg}; |
2333 | + } |
2334 | + my ($dbh, $db, $tbl) = @args{@required_args}; |
2335 | + my $q = $self->{Quoter}; |
2336 | + my $db_tbl = $q->quote($db, $tbl); |
2337 | + MKDEBUG && _d('Checking', $db_tbl); |
2338 | + |
2339 | + my $sql = "SHOW TABLES FROM " . $q->quote($db) |
2340 | + . ' LIKE ' . $q->literal_like($tbl); |
2341 | + MKDEBUG && _d($sql); |
2342 | + my $row; |
2343 | + eval { |
2344 | + $row = $dbh->selectrow_arrayref($sql); |
2345 | + }; |
2346 | + if ( $EVAL_ERROR ) { |
2347 | + MKDEBUG && _d($EVAL_ERROR); |
2348 | + return 0; |
2349 | + } |
2350 | + if ( !$row->[0] || $row->[0] ne $tbl ) { |
2351 | + MKDEBUG && _d('Table does not exist'); |
2352 | + return 0; |
2353 | + } |
2354 | + |
2355 | + MKDEBUG && _d('Table exists; no privs to check'); |
2356 | + return 1 unless $args{all_privs}; |
2357 | + |
2358 | + $sql = "SHOW FULL COLUMNS FROM $db_tbl"; |
2359 | + MKDEBUG && _d($sql); |
2360 | + eval { |
2361 | + $row = $dbh->selectrow_hashref($sql); |
2362 | + }; |
2363 | + if ( $EVAL_ERROR ) { |
2364 | + MKDEBUG && _d($EVAL_ERROR); |
2365 | + return 0; |
2366 | + } |
2367 | + if ( !scalar keys %$row ) { |
2368 | + MKDEBUG && _d('Table has no columns:', Dumper($row)); |
2369 | + return 0; |
2370 | + } |
2371 | + my $privs = $row->{privileges} || $row->{Privileges}; |
2372 | + |
2373 | + $sql = "DELETE FROM $db_tbl LIMIT 0"; |
2374 | + MKDEBUG && _d($sql); |
2375 | + eval { |
2376 | $dbh->do($sql); |
2377 | - } |
2378 | - if ( $tbl ) { |
2379 | - return $self->{triggers}->{$db}->{$tbl}; |
2380 | - } |
2381 | - return values %{$self->{triggers}->{$db}}; |
2382 | -} |
2383 | - |
2384 | -sub get_databases { |
2385 | - my ( $self, $dbh, $quoter, $like ) = @_; |
2386 | - if ( !$self->{cache} || !$self->{databases} || $like ) { |
2387 | - my $sql = 'SHOW DATABASES'; |
2388 | - my @params; |
2389 | - if ( $like ) { |
2390 | - $sql .= ' LIKE ?'; |
2391 | - push @params, $like; |
2392 | - } |
2393 | - my $sth = $dbh->prepare($sql); |
2394 | - MKDEBUG && _d($sql, @params); |
2395 | - $sth->execute( @params ); |
2396 | - my @dbs = map { $_->[0] } @{$sth->fetchall_arrayref()}; |
2397 | - $self->{databases} = \@dbs unless $like; |
2398 | - return @dbs; |
2399 | - } |
2400 | - return @{$self->{databases}}; |
2401 | + }; |
2402 | + my $can_delete = $EVAL_ERROR ? 0 : 1; |
2403 | + |
2404 | + MKDEBUG && _d('User privs on', $db_tbl, ':', $privs, |
2405 | + ($can_delete ? 'delete' : '')); |
2406 | + |
2407 | + if ( !($privs =~ m/select/ && $privs =~ m/insert/ && $privs =~ m/update/ |
2408 | + && $can_delete) ) { |
2409 | + MKDEBUG && _d('User does not have all privs'); |
2410 | + return 0; |
2411 | + } |
2412 | + |
2413 | + MKDEBUG && _d('User has all privs'); |
2414 | + return 1; |
2415 | +} |
2416 | + |
2417 | +sub get_engine { |
2418 | + my ( $self, $ddl, $opts ) = @_; |
2419 | + my ( $engine ) = $ddl =~ m/\).*?(?:ENGINE|TYPE)=(\w+)/; |
2420 | + MKDEBUG && _d('Storage engine:', $engine); |
2421 | + return $engine || undef; |
2422 | +} |
2423 | + |
2424 | +sub get_keys { |
2425 | + my ( $self, $ddl, $opts, $is_nullable ) = @_; |
2426 | + my $engine = $self->get_engine($ddl); |
2427 | + my $keys = {}; |
2428 | + my $clustered_key = undef; |
2429 | + |
2430 | + KEY: |
2431 | + foreach my $key ( $ddl =~ m/^ ((?:[A-Z]+ )?KEY .*)$/gm ) { |
2432 | + |
2433 | + next KEY if $key =~ m/FOREIGN/; |
2434 | + |
2435 | + my $key_ddl = $key; |
2436 | + MKDEBUG && _d('Parsed key:', $key_ddl); |
2437 | + |
2438 | + if ( $engine !~ m/MEMORY|HEAP/ ) { |
2439 | + $key =~ s/USING HASH/USING BTREE/; |
2440 | + } |
2441 | + |
2442 | + my ( $type, $cols ) = $key =~ m/(?:USING (\w+))? \((.+)\)/; |
2443 | + my ( $special ) = $key =~ m/(FULLTEXT|SPATIAL)/; |
2444 | + $type = $type || $special || 'BTREE'; |
2445 | + if ( $opts->{mysql_version} && $opts->{mysql_version} lt '004001000' |
2446 | + && $engine =~ m/HEAP|MEMORY/i ) |
2447 | + { |
2448 | + $type = 'HASH'; # MySQL pre-4.1 supports only HASH indexes on HEAP |
2449 | + } |
2450 | + |
2451 | + my ($name) = $key =~ m/(PRIMARY|`[^`]*`)/; |
2452 | + my $unique = $key =~ m/PRIMARY|UNIQUE/ ? 1 : 0; |
2453 | + my @cols; |
2454 | + my @col_prefixes; |
2455 | + foreach my $col_def ( $cols =~ m/`[^`]+`(?:\(\d+\))?/g ) { |
2456 | + my ($name, $prefix) = $col_def =~ m/`([^`]+)`(?:\((\d+)\))?/; |
2457 | + push @cols, $name; |
2458 | + push @col_prefixes, $prefix; |
2459 | + } |
2460 | + $name =~ s/`//g; |
2461 | + |
2462 | + MKDEBUG && _d( $name, 'key cols:', join(', ', map { "`$_`" } @cols)); |
2463 | + |
2464 | + $keys->{$name} = { |
2465 | + name => $name, |
2466 | + type => $type, |
2467 | + colnames => $cols, |
2468 | + cols => \@cols, |
2469 | + col_prefixes => \@col_prefixes, |
2470 | + is_unique => $unique, |
2471 | + is_nullable => scalar(grep { $is_nullable->{$_} } @cols), |
2472 | + is_col => { map { $_ => 1 } @cols }, |
2473 | + ddl => $key_ddl, |
2474 | + }; |
2475 | + |
2476 | + if ( $engine =~ m/InnoDB/i && !$clustered_key ) { |
2477 | + my $this_key = $keys->{$name}; |
2478 | + if ( $this_key->{name} eq 'PRIMARY' ) { |
2479 | + $clustered_key = 'PRIMARY'; |
2480 | + } |
2481 | + elsif ( $this_key->{is_unique} && !$this_key->{is_nullable} ) { |
2482 | + $clustered_key = $this_key->{name}; |
2483 | + } |
2484 | + MKDEBUG && $clustered_key && _d('This key is the clustered key'); |
2485 | + } |
2486 | + } |
2487 | + |
2488 | + return $keys, $clustered_key; |
2489 | +} |
2490 | + |
2491 | +sub get_fks { |
2492 | + my ( $self, $ddl, $opts ) = @_; |
2493 | + my $q = $self->{Quoter}; |
2494 | + my $fks = {}; |
2495 | + |
2496 | + foreach my $fk ( |
2497 | + $ddl =~ m/CONSTRAINT .* FOREIGN KEY .* REFERENCES [^\)]*\)/mg ) |
2498 | + { |
2499 | + my ( $name ) = $fk =~ m/CONSTRAINT `(.*?)`/; |
2500 | + my ( $cols ) = $fk =~ m/FOREIGN KEY \(([^\)]+)\)/; |
2501 | + my ( $parent, $parent_cols ) = $fk =~ m/REFERENCES (\S+) \(([^\)]+)\)/; |
2502 | + |
2503 | + my ($db, $tbl) = $q->split_unquote($parent, $opts->{database}); |
2504 | + my %parent_tbl = (tbl => $tbl); |
2505 | + $parent_tbl{db} = $db if $db; |
2506 | + |
2507 | + if ( $parent !~ m/\./ && $opts->{database} ) { |
2508 | + $parent = $q->quote($opts->{database}) . ".$parent"; |
2509 | + } |
2510 | + |
2511 | + $fks->{$name} = { |
2512 | + name => $name, |
2513 | + colnames => $cols, |
2514 | + cols => [ map { s/[ `]+//g; $_; } split(',', $cols) ], |
2515 | + parent_tbl => \%parent_tbl, |
2516 | + parent_tblname => $parent, |
2517 | + parent_cols => [ map { s/[ `]+//g; $_; } split(',', $parent_cols) ], |
2518 | + parent_colnames=> $parent_cols, |
2519 | + ddl => $fk, |
2520 | + }; |
2521 | + } |
2522 | + |
2523 | + return $fks; |
2524 | +} |
2525 | + |
2526 | +sub remove_auto_increment { |
2527 | + my ( $self, $ddl ) = @_; |
2528 | + $ddl =~ s/(^\).*?) AUTO_INCREMENT=\d+\b/$1/m; |
2529 | + return $ddl; |
2530 | } |
2531 | |
2532 | sub get_table_status { |
2533 | - my ( $self, $dbh, $quoter, $db, $like ) = @_; |
2534 | - if ( !$self->{cache} || !$self->{table_status}->{$db} || $like ) { |
2535 | - my $sql = "SHOW TABLE STATUS FROM " . $quoter->quote($db); |
2536 | - my @params; |
2537 | - if ( $like ) { |
2538 | - $sql .= ' LIKE ?'; |
2539 | - push @params, $like; |
2540 | - } |
2541 | - MKDEBUG && _d($sql, @params); |
2542 | - my $sth = $dbh->prepare($sql); |
2543 | - $sth->execute(@params); |
2544 | - my @tables = @{$sth->fetchall_arrayref({})}; |
2545 | - @tables = map { |
2546 | - my %tbl; # Make a copy with lowercased keys |
2547 | - @tbl{ map { lc $_ } keys %$_ } = values %$_; |
2548 | - $tbl{engine} ||= $tbl{type} || $tbl{comment}; |
2549 | - delete $tbl{type}; |
2550 | - \%tbl; |
2551 | - } @tables; |
2552 | - $self->{table_status}->{$db} = \@tables unless $like; |
2553 | - return @tables; |
2554 | - } |
2555 | - return @{$self->{table_status}->{$db}}; |
2556 | -} |
2557 | - |
2558 | -sub get_table_list { |
2559 | - my ( $self, $dbh, $quoter, $db, $like ) = @_; |
2560 | - if ( !$self->{cache} || !$self->{table_list}->{$db} || $like ) { |
2561 | - my $sql = "SHOW /*!50002 FULL*/ TABLES FROM " . $quoter->quote($db); |
2562 | - my @params; |
2563 | - if ( $like ) { |
2564 | - $sql .= ' LIKE ?'; |
2565 | - push @params, $like; |
2566 | - } |
2567 | - MKDEBUG && _d($sql, @params); |
2568 | - my $sth = $dbh->prepare($sql); |
2569 | - $sth->execute(@params); |
2570 | - my @tables = @{$sth->fetchall_arrayref()}; |
2571 | - @tables = map { |
2572 | - my %tbl = ( |
2573 | - name => $_->[0], |
2574 | - engine => ($_->[1] || '') eq 'VIEW' ? 'VIEW' : '', |
2575 | - ); |
2576 | - \%tbl; |
2577 | - } @tables; |
2578 | - $self->{table_list}->{$db} = \@tables unless $like; |
2579 | - return @tables; |
2580 | - } |
2581 | - return @{$self->{table_list}->{$db}}; |
2582 | + my ( $self, $dbh, $db, $like ) = @_; |
2583 | + my $q = $self->{Quoter}; |
2584 | + my $sql = "SHOW TABLE STATUS FROM " . $q->quote($db); |
2585 | + my @params; |
2586 | + if ( $like ) { |
2587 | + $sql .= ' LIKE ?'; |
2588 | + push @params, $like; |
2589 | + } |
2590 | + MKDEBUG && _d($sql, @params); |
2591 | + my $sth = $dbh->prepare($sql); |
2592 | + eval { $sth->execute(@params); }; |
2593 | + if ($EVAL_ERROR) { |
2594 | + MKDEBUG && _d($EVAL_ERROR); |
2595 | + return; |
2596 | + } |
2597 | + my @tables = @{$sth->fetchall_arrayref({})}; |
2598 | + @tables = map { |
2599 | + my %tbl; # Make a copy with lowercased keys |
2600 | + @tbl{ map { lc $_ } keys %$_ } = values %$_; |
2601 | + $tbl{engine} ||= $tbl{type} || $tbl{comment}; |
2602 | + delete $tbl{type}; |
2603 | + \%tbl; |
2604 | + } @tables; |
2605 | + return @tables; |
2606 | } |
2607 | |
2608 | sub _d { |
2609 | @@ -2558,923 +2116,250 @@ |
2610 | 1; |
2611 | } |
2612 | # ########################################################################### |
2613 | -# End MySQLDump package |
2614 | +# End TableParser package |
2615 | # ########################################################################### |
2616 | |
2617 | # ########################################################################### |
2618 | -# TableChunker package |
2619 | +# TableNibbler package |
2620 | # This package is a copy without comments from the original. The original |
2621 | # with comments and its test file can be found in the Bazaar repository at, |
2622 | -# lib/TableChunker.pm |
2623 | -# t/lib/TableChunker.t |
2624 | +# lib/TableNibbler.pm |
2625 | +# t/lib/TableNibbler.t |
2626 | # See https://launchpad.net/percona-toolkit for more information. |
2627 | # ########################################################################### |
2628 | { |
2629 | -package TableChunker; |
2630 | +package TableNibbler; |
2631 | |
2632 | use strict; |
2633 | use warnings FATAL => 'all'; |
2634 | use English qw(-no_match_vars); |
2635 | use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
2636 | |
2637 | -use POSIX qw(floor ceil); |
2638 | -use List::Util qw(min max); |
2639 | -use Data::Dumper; |
2640 | -$Data::Dumper::Indent = 1; |
2641 | -$Data::Dumper::Sortkeys = 1; |
2642 | -$Data::Dumper::Quotekeys = 0; |
2643 | - |
2644 | sub new { |
2645 | my ( $class, %args ) = @_; |
2646 | - foreach my $arg ( qw(Quoter MySQLDump) ) { |
2647 | + my @required_args = qw(TableParser Quoter); |
2648 | + foreach my $arg ( @required_args ) { |
2649 | die "I need a $arg argument" unless $args{$arg}; |
2650 | } |
2651 | - |
2652 | - my %int_types = map { $_ => 1 } qw(bigint date datetime int mediumint smallint time timestamp tinyint year); |
2653 | - my %real_types = map { $_ => 1 } qw(decimal double float); |
2654 | - |
2655 | - my $self = { |
2656 | - %args, |
2657 | - int_types => \%int_types, |
2658 | - real_types => \%real_types, |
2659 | - EPOCH => '1970-01-01', |
2660 | - }; |
2661 | - |
2662 | + my $self = { %args }; |
2663 | return bless $self, $class; |
2664 | } |
2665 | |
2666 | -sub find_chunk_columns { |
2667 | - my ( $self, %args ) = @_; |
2668 | - foreach my $arg ( qw(tbl_struct) ) { |
2669 | - die "I need a $arg argument" unless $args{$arg}; |
2670 | - } |
2671 | - my $tbl_struct = $args{tbl_struct}; |
2672 | - |
2673 | - my @possible_indexes; |
2674 | - foreach my $index ( values %{ $tbl_struct->{keys} } ) { |
2675 | - |
2676 | - next unless $index->{type} eq 'BTREE'; |
2677 | - |
2678 | - next if grep { defined } @{$index->{col_prefixes}}; |
2679 | - |
2680 | - if ( $args{exact} ) { |
2681 | - next unless $index->{is_unique} && @{$index->{cols}} == 1; |
2682 | - } |
2683 | - |
2684 | - push @possible_indexes, $index; |
2685 | - } |
2686 | - MKDEBUG && _d('Possible chunk indexes in order:', |
2687 | - join(', ', map { $_->{name} } @possible_indexes)); |
2688 | - |
2689 | - my $can_chunk_exact = 0; |
2690 | - my @candidate_cols; |
2691 | - foreach my $index ( @possible_indexes ) { |
2692 | - my $col = $index->{cols}->[0]; |
2693 | - |
2694 | - my $col_type = $tbl_struct->{type_for}->{$col}; |
2695 | - next unless $self->{int_types}->{$col_type} |
2696 | - || $self->{real_types}->{$col_type} |
2697 | - || $col_type =~ m/char/; |
2698 | - |
2699 | - push @candidate_cols, { column => $col, index => $index->{name} }; |
2700 | - } |
2701 | - |
2702 | - $can_chunk_exact = 1 if $args{exact} && scalar @candidate_cols; |
2703 | - |
2704 | - if ( MKDEBUG ) { |
2705 | - my $chunk_type = $args{exact} ? 'Exact' : 'Inexact'; |
2706 | - _d($chunk_type, 'chunkable:', |
2707 | - join(', ', map { "$_->{column} on $_->{index}" } @candidate_cols)); |
2708 | - } |
2709 | - |
2710 | - my @result; |
2711 | - MKDEBUG && _d('Ordering columns by order in tbl, PK first'); |
2712 | - if ( $tbl_struct->{keys}->{PRIMARY} ) { |
2713 | - my $pk_first_col = $tbl_struct->{keys}->{PRIMARY}->{cols}->[0]; |
2714 | - @result = grep { $_->{column} eq $pk_first_col } @candidate_cols; |
2715 | - @candidate_cols = grep { $_->{column} ne $pk_first_col } @candidate_cols; |
2716 | - } |
2717 | - my $i = 0; |
2718 | - my %col_pos = map { $_ => $i++ } @{$tbl_struct->{cols}}; |
2719 | - push @result, sort { $col_pos{$a->{column}} <=> $col_pos{$b->{column}} } |
2720 | - @candidate_cols; |
2721 | - |
2722 | - if ( MKDEBUG ) { |
2723 | - _d('Chunkable columns:', |
2724 | - join(', ', map { "$_->{column} on $_->{index}" } @result)); |
2725 | - _d('Can chunk exactly:', $can_chunk_exact); |
2726 | - } |
2727 | - |
2728 | - return ($can_chunk_exact, @result); |
2729 | -} |
2730 | - |
2731 | -sub calculate_chunks { |
2732 | - my ( $self, %args ) = @_; |
2733 | - my @required_args = qw(dbh db tbl tbl_struct chunk_col rows_in_range chunk_size); |
2734 | +sub generate_asc_stmt { |
2735 | + my ( $self, %args ) = @_; |
2736 | + my @required_args = qw(tbl_struct index); |
2737 | foreach my $arg ( @required_args ) { |
2738 | die "I need a $arg argument" unless defined $args{$arg}; |
2739 | } |
2740 | - MKDEBUG && _d('Calculate chunks for', |
2741 | - join(", ", map {"$_=".(defined $args{$_} ? $args{$_} : "undef")} |
2742 | - qw(db tbl chunk_col min max rows_in_range chunk_size zero_chunk exact) |
2743 | - )); |
2744 | - |
2745 | - if ( !$args{rows_in_range} ) { |
2746 | - MKDEBUG && _d("Empty table"); |
2747 | - return '1=1'; |
2748 | - } |
2749 | - |
2750 | - if ( $args{rows_in_range} < $args{chunk_size} ) { |
2751 | - MKDEBUG && _d("Chunk size larger than rows in range"); |
2752 | - return '1=1'; |
2753 | - } |
2754 | - |
2755 | - my $q = $self->{Quoter}; |
2756 | - my $dbh = $args{dbh}; |
2757 | - my $chunk_col = $args{chunk_col}; |
2758 | - my $tbl_struct = $args{tbl_struct}; |
2759 | - my $col_type = $tbl_struct->{type_for}->{$chunk_col}; |
2760 | - MKDEBUG && _d('chunk col type:', $col_type); |
2761 | - |
2762 | - my %chunker; |
2763 | - if ( $tbl_struct->{is_numeric}->{$chunk_col} || $col_type =~ /date|time/ ) { |
2764 | - %chunker = $self->_chunk_numeric(%args); |
2765 | - } |
2766 | - elsif ( $col_type =~ m/char/ ) { |
2767 | - %chunker = $self->_chunk_char(%args); |
2768 | - } |
2769 | - else { |
2770 | - die "Cannot chunk $col_type columns"; |
2771 | - } |
2772 | - MKDEBUG && _d("Chunker:", Dumper(\%chunker)); |
2773 | - my ($col, $start_point, $end_point, $interval, $range_func) |
2774 | - = @chunker{qw(col start_point end_point interval range_func)}; |
2775 | - |
2776 | - my @chunks; |
2777 | - if ( $start_point < $end_point ) { |
2778 | - |
2779 | - push @chunks, "$col = 0" if $chunker{have_zero_chunk}; |
2780 | - |
2781 | - my ($beg, $end); |
2782 | - my $iter = 0; |
2783 | - for ( my $i = $start_point; $i < $end_point; $i += $interval ) { |
2784 | - ($beg, $end) = $self->$range_func($dbh, $i, $interval, $end_point); |
2785 | - |
2786 | - if ( $iter++ == 0 ) { |
2787 | - push @chunks, |
2788 | - ($chunker{have_zero_chunk} ? "$col > 0 AND " : "") |
2789 | - ."$col < " . $q->quote_val($end); |
2790 | + my ($tbl_struct, $index) = @args{@required_args}; |
2791 | + my @cols = $args{cols} ? @{$args{cols}} : @{$tbl_struct->{cols}}; |
2792 | + my $q = $self->{Quoter}; |
2793 | + |
2794 | + die "Index '$index' does not exist in table" |
2795 | + unless exists $tbl_struct->{keys}->{$index}; |
2796 | + MKDEBUG && _d('Will ascend index', $index); |
2797 | + |
2798 | + my @asc_cols = @{$tbl_struct->{keys}->{$index}->{cols}}; |
2799 | + if ( $args{asc_first} ) { |
2800 | + @asc_cols = $asc_cols[0]; |
2801 | + MKDEBUG && _d('Ascending only first column'); |
2802 | + } |
2803 | + MKDEBUG && _d('Will ascend columns', join(', ', @asc_cols)); |
2804 | + |
2805 | + my @asc_slice; |
2806 | + my %col_posn = do { my $i = 0; map { $_ => $i++ } @cols }; |
2807 | + foreach my $col ( @asc_cols ) { |
2808 | + if ( !exists $col_posn{$col} ) { |
2809 | + push @cols, $col; |
2810 | + $col_posn{$col} = $#cols; |
2811 | + } |
2812 | + push @asc_slice, $col_posn{$col}; |
2813 | + } |
2814 | + MKDEBUG && _d('Will ascend, in ordinal position:', join(', ', @asc_slice)); |
2815 | + |
2816 | + my $asc_stmt = { |
2817 | + cols => \@cols, |
2818 | + index => $index, |
2819 | + where => '', |
2820 | + slice => [], |
2821 | + scols => [], |
2822 | + }; |
2823 | + |
2824 | + if ( @asc_slice ) { |
2825 | + my $cmp_where; |
2826 | + foreach my $cmp ( qw(< <= >= >) ) { |
2827 | + $cmp_where = $self->generate_cmp_where( |
2828 | + type => $cmp, |
2829 | + slice => \@asc_slice, |
2830 | + cols => \@cols, |
2831 | + quoter => $q, |
2832 | + is_nullable => $tbl_struct->{is_nullable}, |
2833 | + ); |
2834 | + $asc_stmt->{boundaries}->{$cmp} = $cmp_where->{where}; |
2835 | + } |
2836 | + my $cmp = $args{asc_only} ? '>' : '>='; |
2837 | + $asc_stmt->{where} = $asc_stmt->{boundaries}->{$cmp}; |
2838 | + $asc_stmt->{slice} = $cmp_where->{slice}; |
2839 | + $asc_stmt->{scols} = $cmp_where->{scols}; |
2840 | + } |
2841 | + |
2842 | + return $asc_stmt; |
2843 | +} |
2844 | + |
2845 | +sub generate_cmp_where { |
2846 | + my ( $self, %args ) = @_; |
2847 | + foreach my $arg ( qw(type slice cols is_nullable) ) { |
2848 | + die "I need a $arg arg" unless defined $args{$arg}; |
2849 | + } |
2850 | + my @slice = @{$args{slice}}; |
2851 | + my @cols = @{$args{cols}}; |
2852 | + my $is_nullable = $args{is_nullable}; |
2853 | + my $type = $args{type}; |
2854 | + my $q = $self->{Quoter}; |
2855 | + |
2856 | + (my $cmp = $type) =~ s/=//; |
2857 | + |
2858 | + my @r_slice; # Resulting slice columns, by ordinal |
2859 | + my @r_scols; # Ditto, by name |
2860 | + |
2861 | + my @clauses; |
2862 | + foreach my $i ( 0 .. $#slice ) { |
2863 | + my @clause; |
2864 | + |
2865 | + foreach my $j ( 0 .. $i - 1 ) { |
2866 | + my $ord = $slice[$j]; |
2867 | + my $col = $cols[$ord]; |
2868 | + my $quo = $q->quote($col); |
2869 | + if ( $is_nullable->{$col} ) { |
2870 | + push @clause, "((? IS NULL AND $quo IS NULL) OR ($quo = ?))"; |
2871 | + push @r_slice, $ord, $ord; |
2872 | + push @r_scols, $col, $col; |
2873 | } |
2874 | else { |
2875 | - push @chunks, "$col >= " . $q->quote_val($beg) . " AND $col < " . $q->quote_val($end); |
2876 | - } |
2877 | - } |
2878 | - |
2879 | - my $chunk_range = lc $args{chunk_range} || 'open'; |
2880 | - my $nullable = $args{tbl_struct}->{is_nullable}->{$args{chunk_col}}; |
2881 | - pop @chunks; |
2882 | - if ( @chunks ) { |
2883 | - push @chunks, "$col >= " . $q->quote_val($beg) |
2884 | - . ($chunk_range eq 'openclosed' |
2885 | - ? " AND $col <= " . $q->quote_val($args{max}) : ""); |
2886 | - } |
2887 | - else { |
2888 | - push @chunks, $nullable ? "$col IS NOT NULL" : '1=1'; |
2889 | - } |
2890 | - if ( $nullable ) { |
2891 | - push @chunks, "$col IS NULL"; |
2892 | - } |
2893 | - } |
2894 | - else { |
2895 | - MKDEBUG && _d('No chunks; using single chunk 1=1'); |
2896 | - push @chunks, '1=1'; |
2897 | - } |
2898 | - |
2899 | - return @chunks; |
2900 | -} |
2901 | - |
2902 | -sub _chunk_numeric { |
2903 | - my ( $self, %args ) = @_; |
2904 | - my @required_args = qw(dbh db tbl tbl_struct chunk_col rows_in_range chunk_size); |
2905 | - foreach my $arg ( @required_args ) { |
2906 | - die "I need a $arg argument" unless defined $args{$arg}; |
2907 | - } |
2908 | - my $q = $self->{Quoter}; |
2909 | - my $db_tbl = $q->quote($args{db}, $args{tbl}); |
2910 | - my $col_type = $args{tbl_struct}->{type_for}->{$args{chunk_col}}; |
2911 | - |
2912 | - my $range_func; |
2913 | - if ( $col_type =~ m/(?:int|year|float|double|decimal)$/ ) { |
2914 | - $range_func = 'range_num'; |
2915 | - } |
2916 | - elsif ( $col_type =~ m/^(?:timestamp|date|time)$/ ) { |
2917 | - $range_func = "range_$col_type"; |
2918 | - } |
2919 | - elsif ( $col_type eq 'datetime' ) { |
2920 | - $range_func = 'range_datetime'; |
2921 | - } |
2922 | - |
2923 | - my ($start_point, $end_point); |
2924 | - eval { |
2925 | - $start_point = $self->value_to_number( |
2926 | - value => $args{min}, |
2927 | - column_type => $col_type, |
2928 | - dbh => $args{dbh}, |
2929 | - ); |
2930 | - $end_point = $self->value_to_number( |
2931 | - value => $args{max}, |
2932 | - column_type => $col_type, |
2933 | - dbh => $args{dbh}, |
2934 | - ); |
2935 | - }; |
2936 | - if ( $EVAL_ERROR ) { |
2937 | - if ( $EVAL_ERROR =~ m/don't know how to chunk/ ) { |
2938 | - die $EVAL_ERROR; |
2939 | - } |
2940 | - else { |
2941 | - die "Error calculating chunk start and end points for table " |
2942 | - . "`$args{tbl_struct}->{name}` on column `$args{chunk_col}` " |
2943 | - . "with min/max values " |
2944 | - . join('/', |
2945 | - map { defined $args{$_} ? $args{$_} : 'undef' } qw(min max)) |
2946 | - . ":\n\n" |
2947 | - . $EVAL_ERROR |
2948 | - . "\nVerify that the min and max values are valid for the column. " |
2949 | - . "If they are valid, this error could be caused by a bug in the " |
2950 | - . "tool."; |
2951 | - } |
2952 | - } |
2953 | - |
2954 | - if ( !defined $start_point ) { |
2955 | - MKDEBUG && _d('Start point is undefined'); |
2956 | - $start_point = 0; |
2957 | - } |
2958 | - if ( !defined $end_point || $end_point < $start_point ) { |
2959 | - MKDEBUG && _d('End point is undefined or before start point'); |
2960 | - $end_point = 0; |
2961 | - } |
2962 | - MKDEBUG && _d("Actual chunk range:", $start_point, "to", $end_point); |
2963 | - |
2964 | - my $have_zero_chunk = 0; |
2965 | - if ( $args{zero_chunk} ) { |
2966 | - if ( $start_point != $end_point && $start_point >= 0 ) { |
2967 | - MKDEBUG && _d('Zero chunking'); |
2968 | - my $nonzero_val = $self->get_nonzero_value( |
2969 | - %args, |
2970 | - db_tbl => $db_tbl, |
2971 | - col => $args{chunk_col}, |
2972 | - col_type => $col_type, |
2973 | - val => $args{min} |
2974 | - ); |
2975 | - $start_point = $self->value_to_number( |
2976 | - value => $nonzero_val, |
2977 | - column_type => $col_type, |
2978 | - dbh => $args{dbh}, |
2979 | - ); |
2980 | - $have_zero_chunk = 1; |
2981 | - } |
2982 | - else { |
2983 | - MKDEBUG && _d("Cannot zero chunk"); |
2984 | - } |
2985 | - } |
2986 | - MKDEBUG && _d("Using chunk range:", $start_point, "to", $end_point); |
2987 | - |
2988 | - my $interval = $args{chunk_size} |
2989 | - * ($end_point - $start_point) |
2990 | - / $args{rows_in_range}; |
2991 | - if ( $self->{int_types}->{$col_type} ) { |
2992 | - $interval = ceil($interval); |
2993 | - } |
2994 | - $interval ||= $args{chunk_size}; |
2995 | - if ( $args{exact} ) { |
2996 | - $interval = $args{chunk_size}; |
2997 | - } |
2998 | - MKDEBUG && _d('Chunk interval:', $interval, 'units'); |
2999 | - |
3000 | - return ( |
3001 | - col => $q->quote($args{chunk_col}), |
3002 | - start_point => $start_point, |
3003 | - end_point => $end_point, |
3004 | - interval => $interval, |
3005 | - range_func => $range_func, |
3006 | - have_zero_chunk => $have_zero_chunk, |
3007 | - ); |
3008 | -} |
3009 | - |
3010 | -sub _chunk_char { |
3011 | - my ( $self, %args ) = @_; |
3012 | - my @required_args = qw(dbh db tbl tbl_struct chunk_col min max rows_in_range chunk_size); |
3013 | - foreach my $arg ( @required_args ) { |
3014 | - die "I need a $arg argument" unless defined $args{$arg}; |
3015 | - } |
3016 | - my $q = $self->{Quoter}; |
3017 | - my $db_tbl = $q->quote($args{db}, $args{tbl}); |
3018 | - my $dbh = $args{dbh}; |
3019 | - my $chunk_col = $args{chunk_col}; |
3020 | - my $row; |
3021 | - my $sql; |
3022 | - |
3023 | - my ($min_col, $max_col) = @{args}{qw(min max)}; |
3024 | - $sql = "SELECT ORD(?) AS min_col_ord, ORD(?) AS max_col_ord"; |
3025 | - MKDEBUG && _d($dbh, $sql); |
3026 | - my $ord_sth = $dbh->prepare($sql); # avoid quoting issues |
3027 | - $ord_sth->execute($min_col, $max_col); |
3028 | - $row = $ord_sth->fetchrow_arrayref(); |
3029 | - my ($min_col_ord, $max_col_ord) = ($row->[0], $row->[1]); |
3030 | - MKDEBUG && _d("Min/max col char code:", $min_col_ord, $max_col_ord); |
3031 | - |
3032 | - my $base; |
3033 | - my @chars; |
3034 | - MKDEBUG && _d("Table charset:", $args{tbl_struct}->{charset}); |
3035 | - if ( ($args{tbl_struct}->{charset} || "") eq "latin1" ) { |
3036 | - my @sorted_latin1_chars = ( |
3037 | - 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, |
3038 | - 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, |
3039 | - 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, |
3040 | - 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, |
3041 | - 88, 89, 90, 91, 92, 93, 94, 95, 96, 123, 124, 125, 126, 161, |
3042 | - 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, |
3043 | - 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, |
3044 | - 190, 191, 215, 216, 222, 223, 247, 255); |
3045 | - |
3046 | - my ($first_char, $last_char); |
3047 | - for my $i ( 0..$#sorted_latin1_chars ) { |
3048 | - $first_char = $i and last if $sorted_latin1_chars[$i] >= $min_col_ord; |
3049 | - } |
3050 | - for my $i ( $first_char..$#sorted_latin1_chars ) { |
3051 | - $last_char = $i and last if $sorted_latin1_chars[$i] >= $max_col_ord; |
3052 | - }; |
3053 | - |
3054 | - @chars = map { chr $_; } @sorted_latin1_chars[$first_char..$last_char]; |
3055 | - $base = scalar @chars; |
3056 | - } |
3057 | - else { |
3058 | - |
3059 | - my $tmp_tbl = '__maatkit_char_chunking_map'; |
3060 | - my $tmp_db_tbl = $q->quote($args{db}, $tmp_tbl); |
3061 | - $sql = "DROP TABLE IF EXISTS $tmp_db_tbl"; |
3062 | - MKDEBUG && _d($dbh, $sql); |
3063 | - $dbh->do($sql); |
3064 | - my $col_def = $args{tbl_struct}->{defs}->{$chunk_col}; |
3065 | - $sql = "CREATE TEMPORARY TABLE $tmp_db_tbl ($col_def) " |
3066 | - . "ENGINE=MEMORY"; |
3067 | - MKDEBUG && _d($dbh, $sql); |
3068 | - $dbh->do($sql); |
3069 | - |
3070 | - $sql = "INSERT INTO $tmp_db_tbl VALUE (CHAR(?))"; |
3071 | - MKDEBUG && _d($dbh, $sql); |
3072 | - my $ins_char_sth = $dbh->prepare($sql); # avoid quoting issues |
3073 | - for my $char_code ( $min_col_ord..$max_col_ord ) { |
3074 | - $ins_char_sth->execute($char_code); |
3075 | - } |
3076 | - |
3077 | - $sql = "SELECT `$chunk_col` FROM $tmp_db_tbl " |
3078 | - . "WHERE `$chunk_col` BETWEEN ? AND ? " |
3079 | - . "ORDER BY `$chunk_col`"; |
3080 | - MKDEBUG && _d($dbh, $sql); |
3081 | - my $sel_char_sth = $dbh->prepare($sql); |
3082 | - $sel_char_sth->execute($min_col, $max_col); |
3083 | - |
3084 | - @chars = map { $_->[0] } @{ $sel_char_sth->fetchall_arrayref() }; |
3085 | - $base = scalar @chars; |
3086 | - |
3087 | - $sql = "DROP TABLE $tmp_db_tbl"; |
3088 | - MKDEBUG && _d($dbh, $sql); |
3089 | - $dbh->do($sql); |
3090 | - } |
3091 | - MKDEBUG && _d("Base", $base, "chars:", @chars); |
3092 | - |
3093 | - |
3094 | - $sql = "SELECT MAX(LENGTH($chunk_col)) FROM $db_tbl " |
3095 | - . ($args{where} ? "WHERE $args{where} " : "") |
3096 | - . "ORDER BY `$chunk_col`"; |
3097 | - MKDEBUG && _d($dbh, $sql); |
3098 | - $row = $dbh->selectrow_arrayref($sql); |
3099 | - my $max_col_len = $row->[0]; |
3100 | - MKDEBUG && _d("Max column value:", $max_col, $max_col_len); |
3101 | - my $n_values; |
3102 | - for my $n_chars ( 1..$max_col_len ) { |
3103 | - $n_values = $base**$n_chars; |
3104 | - if ( $n_values >= $args{chunk_size} ) { |
3105 | - MKDEBUG && _d($n_chars, "chars in base", $base, "expresses", |
3106 | - $n_values, "values"); |
3107 | - last; |
3108 | - } |
3109 | - } |
3110 | - |
3111 | - my $n_chunks = $args{rows_in_range} / $args{chunk_size}; |
3112 | - my $interval = floor($n_values / $n_chunks) || 1; |
3113 | - |
3114 | - my $range_func = sub { |
3115 | - my ( $self, $dbh, $start, $interval, $max ) = @_; |
3116 | - my $start_char = $self->base_count( |
3117 | - count_to => $start, |
3118 | - base => $base, |
3119 | - symbols => \@chars, |
3120 | - ); |
3121 | - my $end_char = $self->base_count( |
3122 | - count_to => min($max, $start + $interval), |
3123 | - base => $base, |
3124 | - symbols => \@chars, |
3125 | - ); |
3126 | - return $start_char, $end_char; |
3127 | - }; |
3128 | - |
3129 | - return ( |
3130 | - col => $q->quote($chunk_col), |
3131 | - start_point => 0, |
3132 | - end_point => $n_values, |
3133 | - interval => $interval, |
3134 | - range_func => $range_func, |
3135 | - ); |
3136 | -} |
3137 | - |
3138 | -sub get_first_chunkable_column { |
3139 | - my ( $self, %args ) = @_; |
3140 | - foreach my $arg ( qw(tbl_struct) ) { |
3141 | - die "I need a $arg argument" unless $args{$arg}; |
3142 | - } |
3143 | - |
3144 | - my ($exact, @cols) = $self->find_chunk_columns(%args); |
3145 | - my $col = $cols[0]->{column}; |
3146 | - my $idx = $cols[0]->{index}; |
3147 | - |
3148 | - my $wanted_col = $args{chunk_column}; |
3149 | - my $wanted_idx = $args{chunk_index}; |
3150 | - MKDEBUG && _d("Preferred chunk col/idx:", $wanted_col, $wanted_idx); |
3151 | - |
3152 | - if ( $wanted_col && $wanted_idx ) { |
3153 | - foreach my $chunkable_col ( @cols ) { |
3154 | - if ( $wanted_col eq $chunkable_col->{column} |
3155 | - && $wanted_idx eq $chunkable_col->{index} ) { |
3156 | - $col = $wanted_col; |
3157 | - $idx = $wanted_idx; |
3158 | - last; |
3159 | - } |
3160 | - } |
3161 | - } |
3162 | - elsif ( $wanted_col ) { |
3163 | - foreach my $chunkable_col ( @cols ) { |
3164 | - if ( $wanted_col eq $chunkable_col->{column} ) { |
3165 | - $col = $wanted_col; |
3166 | - $idx = $chunkable_col->{index}; |
3167 | - last; |
3168 | - } |
3169 | - } |
3170 | - } |
3171 | - elsif ( $wanted_idx ) { |
3172 | - foreach my $chunkable_col ( @cols ) { |
3173 | - if ( $wanted_idx eq $chunkable_col->{index} ) { |
3174 | - $col = $chunkable_col->{column}; |
3175 | - $idx = $wanted_idx; |
3176 | - last; |
3177 | - } |
3178 | - } |
3179 | - } |
3180 | - |
3181 | - MKDEBUG && _d('First chunkable col/index:', $col, $idx); |
3182 | - return $col, $idx; |
3183 | -} |
3184 | - |
3185 | -sub size_to_rows { |
3186 | - my ( $self, %args ) = @_; |
3187 | - my @required_args = qw(dbh db tbl chunk_size); |
3188 | - foreach my $arg ( @required_args ) { |
3189 | - die "I need a $arg argument" unless $args{$arg}; |
3190 | - } |
3191 | - my ($dbh, $db, $tbl, $chunk_size) = @args{@required_args}; |
3192 | - my $q = $self->{Quoter}; |
3193 | - my $du = $self->{MySQLDump}; |
3194 | - |
3195 | - my ($n_rows, $avg_row_length); |
3196 | - |
3197 | - my ( $num, $suffix ) = $chunk_size =~ m/^(\d+)([MGk])?$/; |
3198 | - if ( $suffix ) { # Convert to bytes. |
3199 | - $chunk_size = $suffix eq 'k' ? $num * 1_024 |
3200 | - : $suffix eq 'M' ? $num * 1_024 * 1_024 |
3201 | - : $num * 1_024 * 1_024 * 1_024; |
3202 | - } |
3203 | - elsif ( $num ) { |
3204 | - $n_rows = $num; |
3205 | - } |
3206 | - else { |
3207 | - die "Invalid chunk size $chunk_size; must be an integer " |
3208 | - . "with optional suffix kMG"; |
3209 | - } |
3210 | - |
3211 | - if ( $suffix || $args{avg_row_length} ) { |
3212 | - my ($status) = $du->get_table_status($dbh, $q, $db, $tbl); |
3213 | - $avg_row_length = $status->{avg_row_length}; |
3214 | - if ( !defined $n_rows ) { |
3215 | - $n_rows = $avg_row_length ? ceil($chunk_size / $avg_row_length) : undef; |
3216 | - } |
3217 | - } |
3218 | - |
3219 | - return $n_rows, $avg_row_length; |
3220 | -} |
3221 | - |
3222 | -sub get_range_statistics { |
3223 | - my ( $self, %args ) = @_; |
3224 | - my @required_args = qw(dbh db tbl chunk_col tbl_struct); |
3225 | - foreach my $arg ( @required_args ) { |
3226 | - die "I need a $arg argument" unless $args{$arg}; |
3227 | - } |
3228 | - my ($dbh, $db, $tbl, $col) = @args{@required_args}; |
3229 | - my $where = $args{where}; |
3230 | - my $q = $self->{Quoter}; |
3231 | - |
3232 | - my $col_type = $args{tbl_struct}->{type_for}->{$col}; |
3233 | - my $col_is_numeric = $args{tbl_struct}->{is_numeric}->{$col}; |
3234 | - |
3235 | - my $db_tbl = $q->quote($db, $tbl); |
3236 | - $col = $q->quote($col); |
3237 | - |
3238 | - my ($min, $max); |
3239 | - eval { |
3240 | - my $sql = "SELECT MIN($col), MAX($col) FROM $db_tbl" |
3241 | - . ($args{index_hint} ? " $args{index_hint}" : "") |
3242 | - . ($where ? " WHERE ($where)" : ''); |
3243 | - MKDEBUG && _d($dbh, $sql); |
3244 | - ($min, $max) = $dbh->selectrow_array($sql); |
3245 | - MKDEBUG && _d("Actual end points:", $min, $max); |
3246 | - |
3247 | - ($min, $max) = $self->get_valid_end_points( |
3248 | - %args, |
3249 | - dbh => $dbh, |
3250 | - db_tbl => $db_tbl, |
3251 | - col => $col, |
3252 | - col_type => $col_type, |
3253 | - min => $min, |
3254 | - max => $max, |
3255 | - ); |
3256 | - MKDEBUG && _d("Valid end points:", $min, $max); |
3257 | - }; |
3258 | - if ( $EVAL_ERROR ) { |
3259 | - die "Error getting min and max values for table $db_tbl " |
3260 | - . "on column $col: $EVAL_ERROR"; |
3261 | - } |
3262 | - |
3263 | - my $sql = "EXPLAIN SELECT * FROM $db_tbl" |
3264 | - . ($args{index_hint} ? " $args{index_hint}" : "") |
3265 | - . ($where ? " WHERE $where" : ''); |
3266 | - MKDEBUG && _d($sql); |
3267 | - my $expl = $dbh->selectrow_hashref($sql); |
3268 | - |
3269 | - return ( |
3270 | - min => $min, |
3271 | - max => $max, |
3272 | - rows_in_range => $expl->{rows}, |
3273 | - ); |
3274 | -} |
3275 | - |
3276 | -sub inject_chunks { |
3277 | - my ( $self, %args ) = @_; |
3278 | - foreach my $arg ( qw(database table chunks chunk_num query) ) { |
3279 | - die "I need a $arg argument" unless defined $args{$arg}; |
3280 | - } |
3281 | - MKDEBUG && _d('Injecting chunk', $args{chunk_num}); |
3282 | - my $query = $args{query}; |
3283 | - my $comment = sprintf("/*%s.%s:%d/%d*/", |
3284 | - $args{database}, $args{table}, |
3285 | - $args{chunk_num} + 1, scalar @{$args{chunks}}); |
3286 | - $query =~ s!/\*PROGRESS_COMMENT\*/!$comment!; |
3287 | - my $where = "WHERE (" . $args{chunks}->[$args{chunk_num}] . ')'; |
3288 | - if ( $args{where} && grep { $_ } @{$args{where}} ) { |
3289 | - $where .= " AND (" |
3290 | - . join(" AND ", map { "($_)" } grep { $_ } @{$args{where}} ) |
3291 | - . ")"; |
3292 | - } |
3293 | - my $db_tbl = $self->{Quoter}->quote(@args{qw(database table)}); |
3294 | - my $index_hint = $args{index_hint} || ''; |
3295 | - |
3296 | - MKDEBUG && _d('Parameters:', |
3297 | - Dumper({WHERE => $where, DB_TBL => $db_tbl, INDEX_HINT => $index_hint})); |
3298 | - $query =~ s!/\*WHERE\*/! $where!; |
3299 | - $query =~ s!/\*DB_TBL\*/!$db_tbl!; |
3300 | - $query =~ s!/\*INDEX_HINT\*/! $index_hint!; |
3301 | - $query =~ s!/\*CHUNK_NUM\*/! $args{chunk_num} AS chunk_num,!; |
3302 | - |
3303 | - return $query; |
3304 | -} |
3305 | - |
3306 | - |
3307 | -sub value_to_number { |
3308 | - my ( $self, %args ) = @_; |
3309 | - my @required_args = qw(column_type dbh); |
3310 | - foreach my $arg ( @required_args ) { |
3311 | - die "I need a $arg argument" unless defined $args{$arg}; |
3312 | - } |
3313 | - my $val = $args{value}; |
3314 | - my ($col_type, $dbh) = @args{@required_args}; |
3315 | - MKDEBUG && _d('Converting MySQL', $col_type, $val); |
3316 | - |
3317 | - return unless defined $val; # value is NULL |
3318 | - |
3319 | - my %mysql_conv_func_for = ( |
3320 | - timestamp => 'UNIX_TIMESTAMP', |
3321 | - date => 'TO_DAYS', |
3322 | - time => 'TIME_TO_SEC', |
3323 | - datetime => 'TO_DAYS', |
3324 | - ); |
3325 | - |
3326 | - my $num; |
3327 | - if ( $col_type =~ m/(?:int|year|float|double|decimal)$/ ) { |
3328 | - $num = $val; |
3329 | - } |
3330 | - elsif ( $col_type =~ m/^(?:timestamp|date|time)$/ ) { |
3331 | - my $func = $mysql_conv_func_for{$col_type}; |
3332 | - my $sql = "SELECT $func(?)"; |
3333 | - MKDEBUG && _d($dbh, $sql, $val); |
3334 | - my $sth = $dbh->prepare($sql); |
3335 | - $sth->execute($val); |
3336 | - ($num) = $sth->fetchrow_array(); |
3337 | - } |
3338 | - elsif ( $col_type eq 'datetime' ) { |
3339 | - $num = $self->timestampdiff($dbh, $val); |
3340 | - } |
3341 | - else { |
3342 | - die "I don't know how to chunk $col_type\n"; |
3343 | - } |
3344 | - MKDEBUG && _d('Converts to', $num); |
3345 | - return $num; |
3346 | -} |
3347 | - |
3348 | -sub range_num { |
3349 | - my ( $self, $dbh, $start, $interval, $max ) = @_; |
3350 | - my $end = min($max, $start + $interval); |
3351 | - |
3352 | - |
3353 | - $start = sprintf('%.17f', $start) if $start =~ /e/; |
3354 | - $end = sprintf('%.17f', $end) if $end =~ /e/; |
3355 | - |
3356 | - $start =~ s/\.(\d{5}).*$/.$1/; |
3357 | - $end =~ s/\.(\d{5}).*$/.$1/; |
3358 | - |
3359 | - if ( $end > $start ) { |
3360 | - return ( $start, $end ); |
3361 | - } |
3362 | - else { |
3363 | - die "Chunk size is too small: $end !> $start\n"; |
3364 | - } |
3365 | -} |
3366 | - |
3367 | -sub range_time { |
3368 | - my ( $self, $dbh, $start, $interval, $max ) = @_; |
3369 | - my $sql = "SELECT SEC_TO_TIME($start), SEC_TO_TIME(LEAST($max, $start + $interval))"; |
3370 | - MKDEBUG && _d($sql); |
3371 | - return $dbh->selectrow_array($sql); |
3372 | -} |
3373 | - |
3374 | -sub range_date { |
3375 | - my ( $self, $dbh, $start, $interval, $max ) = @_; |
3376 | - my $sql = "SELECT FROM_DAYS($start), FROM_DAYS(LEAST($max, $start + $interval))"; |
3377 | - MKDEBUG && _d($sql); |
3378 | - return $dbh->selectrow_array($sql); |
3379 | -} |
3380 | - |
3381 | -sub range_datetime { |
3382 | - my ( $self, $dbh, $start, $interval, $max ) = @_; |
3383 | - my $sql = "SELECT DATE_ADD('$self->{EPOCH}', INTERVAL $start SECOND), " |
3384 | - . "DATE_ADD('$self->{EPOCH}', INTERVAL LEAST($max, $start + $interval) SECOND)"; |
3385 | - MKDEBUG && _d($sql); |
3386 | - return $dbh->selectrow_array($sql); |
3387 | -} |
3388 | - |
3389 | -sub range_timestamp { |
3390 | - my ( $self, $dbh, $start, $interval, $max ) = @_; |
3391 | - my $sql = "SELECT FROM_UNIXTIME($start), FROM_UNIXTIME(LEAST($max, $start + $interval))"; |
3392 | - MKDEBUG && _d($sql); |
3393 | - return $dbh->selectrow_array($sql); |
3394 | -} |
3395 | - |
3396 | -sub timestampdiff { |
3397 | - my ( $self, $dbh, $time ) = @_; |
3398 | - my $sql = "SELECT (COALESCE(TO_DAYS('$time'), 0) * 86400 + TIME_TO_SEC('$time')) " |
3399 | - . "- TO_DAYS('$self->{EPOCH} 00:00:00') * 86400"; |
3400 | - MKDEBUG && _d($sql); |
3401 | - my ( $diff ) = $dbh->selectrow_array($sql); |
3402 | - $sql = "SELECT DATE_ADD('$self->{EPOCH}', INTERVAL $diff SECOND)"; |
3403 | - MKDEBUG && _d($sql); |
3404 | - my ( $check ) = $dbh->selectrow_array($sql); |
3405 | - die <<" EOF" |
3406 | - Incorrect datetime math: given $time, calculated $diff but checked to $check. |
3407 | - This could be due to a version of MySQL that overflows on large interval |
3408 | - values to DATE_ADD(), or the given datetime is not a valid date. If not, |
3409 | - please report this as a bug. |
3410 | - EOF |
3411 | - unless $check eq $time; |
3412 | - return $diff; |
3413 | -} |
3414 | - |
3415 | - |
3416 | - |
3417 | - |
3418 | -sub get_valid_end_points { |
3419 | - my ( $self, %args ) = @_; |
3420 | - my @required_args = qw(dbh db_tbl col col_type); |
3421 | - foreach my $arg ( @required_args ) { |
3422 | - die "I need a $arg argument" unless $args{$arg}; |
3423 | - } |
3424 | - my ($dbh, $db_tbl, $col, $col_type) = @args{@required_args}; |
3425 | - my ($real_min, $real_max) = @args{qw(min max)}; |
3426 | - |
3427 | - my $err_fmt = "Error finding a valid %s value for table $db_tbl on " |
3428 | - . "column $col. The real %s value %s is invalid and " |
3429 | - . "no other valid values were found. Verify that the table " |
3430 | - . "has at least one valid value for this column" |
3431 | - . ($args{where} ? " where $args{where}." : "."); |
3432 | - |
3433 | - my $valid_min = $real_min; |
3434 | - if ( defined $valid_min ) { |
3435 | - MKDEBUG && _d("Validating min end point:", $real_min); |
3436 | - $valid_min = $self->_get_valid_end_point( |
3437 | - %args, |
3438 | - val => $real_min, |
3439 | - endpoint => 'min', |
3440 | - ); |
3441 | - die sprintf($err_fmt, 'minimum', 'minimum', |
3442 | - (defined $real_min ? $real_min : "NULL")) |
3443 | - unless defined $valid_min; |
3444 | - } |
3445 | - |
3446 | - my $valid_max = $real_max; |
3447 | - if ( defined $valid_max ) { |
3448 | - MKDEBUG && _d("Validating max end point:", $real_min); |
3449 | - $valid_max = $self->_get_valid_end_point( |
3450 | - %args, |
3451 | - val => $real_max, |
3452 | - endpoint => 'max', |
3453 | - ); |
3454 | - die sprintf($err_fmt, 'maximum', 'maximum', |
3455 | - (defined $real_max ? $real_max : "NULL")) |
3456 | - unless defined $valid_max; |
3457 | - } |
3458 | - |
3459 | - return $valid_min, $valid_max; |
3460 | -} |
3461 | - |
3462 | -sub _get_valid_end_point { |
3463 | - my ( $self, %args ) = @_; |
3464 | - my @required_args = qw(dbh db_tbl col col_type); |
3465 | - foreach my $arg ( @required_args ) { |
3466 | - die "I need a $arg argument" unless $args{$arg}; |
3467 | - } |
3468 | - my ($dbh, $db_tbl, $col, $col_type) = @args{@required_args}; |
3469 | - my $val = $args{val}; |
3470 | - |
3471 | - return $val unless defined $val; |
3472 | - |
3473 | - my $validate = $col_type =~ m/time|date/ ? \&_validate_temporal_value |
3474 | - : undef; |
3475 | - |
3476 | - if ( !$validate ) { |
3477 | - MKDEBUG && _d("No validator for", $col_type, "values"); |
3478 | - return $val; |
3479 | - } |
3480 | - |
3481 | - return $val if defined $validate->($dbh, $val); |
3482 | - |
3483 | - MKDEBUG && _d("Value is invalid, getting first valid value"); |
3484 | - $val = $self->get_first_valid_value( |
3485 | - %args, |
3486 | - val => $val, |
3487 | - validate => $validate, |
3488 | - ); |
3489 | - |
3490 | - return $val; |
3491 | -} |
3492 | - |
3493 | -sub get_first_valid_value { |
3494 | - my ( $self, %args ) = @_; |
3495 | - my @required_args = qw(dbh db_tbl col validate endpoint); |
3496 | - foreach my $arg ( @required_args ) { |
3497 | - die "I need a $arg argument" unless $args{$arg}; |
3498 | - } |
3499 | - my ($dbh, $db_tbl, $col, $validate, $endpoint) = @args{@required_args}; |
3500 | - my $tries = defined $args{tries} ? $args{tries} : 5; |
3501 | - my $val = $args{val}; |
3502 | - |
3503 | - return unless defined $val; |
3504 | - |
3505 | - my $cmp = $endpoint =~ m/min/i ? '>' |
3506 | - : $endpoint =~ m/max/i ? '<' |
3507 | - : die "Invalid endpoint arg: $endpoint"; |
3508 | - my $sql = "SELECT $col FROM $db_tbl " |
3509 | - . ($args{index_hint} ? "$args{index_hint} " : "") |
3510 | - . "WHERE $col $cmp ? AND $col IS NOT NULL " |
3511 | - . ($args{where} ? "AND ($args{where}) " : "") |
3512 | - . "ORDER BY $col LIMIT 1"; |
3513 | - MKDEBUG && _d($dbh, $sql); |
3514 | - my $sth = $dbh->prepare($sql); |
3515 | - |
3516 | - my $last_val = $val; |
3517 | - while ( $tries-- ) { |
3518 | - $sth->execute($last_val); |
3519 | - my ($next_val) = $sth->fetchrow_array(); |
3520 | - MKDEBUG && _d('Next value:', $next_val, '; tries left:', $tries); |
3521 | - if ( !defined $next_val ) { |
3522 | - MKDEBUG && _d('No more rows in table'); |
3523 | - last; |
3524 | - } |
3525 | - if ( defined $validate->($dbh, $next_val) ) { |
3526 | - MKDEBUG && _d('First valid value:', $next_val); |
3527 | - $sth->finish(); |
3528 | - return $next_val; |
3529 | - } |
3530 | - $last_val = $next_val; |
3531 | - } |
3532 | - $sth->finish(); |
3533 | - $val = undef; # no valid value found |
3534 | - |
3535 | - return $val; |
3536 | -} |
3537 | - |
3538 | -sub _validate_temporal_value { |
3539 | - my ( $dbh, $val ) = @_; |
3540 | - my $sql = "SELECT IF(TIME_FORMAT(?,'%H:%i:%s')=?, TIME_TO_SEC(?), TO_DAYS(?))"; |
3541 | - my $res; |
3542 | - eval { |
3543 | - MKDEBUG && _d($dbh, $sql, $val); |
3544 | - my $sth = $dbh->prepare($sql); |
3545 | - $sth->execute($val, $val, $val, $val); |
3546 | - ($res) = $sth->fetchrow_array(); |
3547 | - $sth->finish(); |
3548 | - }; |
3549 | - if ( $EVAL_ERROR ) { |
3550 | - MKDEBUG && _d($EVAL_ERROR); |
3551 | - } |
3552 | - return $res; |
3553 | -} |
3554 | - |
3555 | -sub get_nonzero_value { |
3556 | - my ( $self, %args ) = @_; |
3557 | - my @required_args = qw(dbh db_tbl col col_type); |
3558 | - foreach my $arg ( @required_args ) { |
3559 | - die "I need a $arg argument" unless $args{$arg}; |
3560 | - } |
3561 | - my ($dbh, $db_tbl, $col, $col_type) = @args{@required_args}; |
3562 | - my $tries = defined $args{tries} ? $args{tries} : 5; |
3563 | - my $val = $args{val}; |
3564 | - |
3565 | - my $is_nonzero = $col_type =~ m/time|date/ ? \&_validate_temporal_value |
3566 | - : sub { return $_[1]; }; |
3567 | - |
3568 | - if ( !$is_nonzero->($dbh, $val) ) { # quasi-double-negative, sorry |
3569 | - MKDEBUG && _d('Discarding zero value:', $val); |
3570 | - my $sql = "SELECT $col FROM $db_tbl " |
3571 | - . ($args{index_hint} ? "$args{index_hint} " : "") |
3572 | - . "WHERE $col > ? AND $col IS NOT NULL " |
3573 | - . ($args{where} ? "AND ($args{where}) " : '') |
3574 | - . "ORDER BY $col LIMIT 1"; |
3575 | - MKDEBUG && _d($sql); |
3576 | - my $sth = $dbh->prepare($sql); |
3577 | - |
3578 | - my $last_val = $val; |
3579 | - while ( $tries-- ) { |
3580 | - $sth->execute($last_val); |
3581 | - my ($next_val) = $sth->fetchrow_array(); |
3582 | - if ( $is_nonzero->($dbh, $next_val) ) { |
3583 | - MKDEBUG && _d('First non-zero value:', $next_val); |
3584 | - $sth->finish(); |
3585 | - return $next_val; |
3586 | - } |
3587 | - $last_val = $next_val; |
3588 | - } |
3589 | - $sth->finish(); |
3590 | - $val = undef; # no non-zero value found |
3591 | - } |
3592 | - |
3593 | - return $val; |
3594 | -} |
3595 | - |
3596 | -sub base_count { |
3597 | - my ( $self, %args ) = @_; |
3598 | - my @required_args = qw(count_to base symbols); |
3599 | - foreach my $arg ( @required_args ) { |
3600 | - die "I need a $arg argument" unless defined $args{$arg}; |
3601 | - } |
3602 | - my ($n, $base, $symbols) = @args{@required_args}; |
3603 | - |
3604 | - return $symbols->[0] if $n == 0; |
3605 | - |
3606 | - my $highest_power = floor(log($n)/log($base)); |
3607 | - if ( $highest_power == 0 ){ |
3608 | - return $symbols->[$n]; |
3609 | - } |
3610 | - |
3611 | - my @base_powers; |
3612 | - for my $power ( 0..$highest_power ) { |
3613 | - push @base_powers, ($base**$power) || 1; |
3614 | - } |
3615 | - |
3616 | - my @base_multiples; |
3617 | - foreach my $base_power ( reverse @base_powers ) { |
3618 | - my $multiples = floor($n / $base_power); |
3619 | - push @base_multiples, $multiples; |
3620 | - $n -= $multiples * $base_power; |
3621 | - } |
3622 | - |
3623 | - return join('', map { $symbols->[$_] } @base_multiples); |
3624 | + push @clause, "$quo = ?"; |
3625 | + push @r_slice, $ord; |
3626 | + push @r_scols, $col; |
3627 | + } |
3628 | + } |
3629 | + |
3630 | + my $ord = $slice[$i]; |
3631 | + my $col = $cols[$ord]; |
3632 | + my $quo = $q->quote($col); |
3633 | + my $end = $i == $#slice; # Last clause of the whole group. |
3634 | + if ( $is_nullable->{$col} ) { |
3635 | + if ( $type =~ m/=/ && $end ) { |
3636 | + push @clause, "(? IS NULL OR $quo $type ?)"; |
3637 | + } |
3638 | + elsif ( $type =~ m/>/ ) { |
3639 | + push @clause, "((? IS NULL AND $quo IS NOT NULL) OR ($quo $cmp ?))"; |
3640 | + } |
3641 | + else { # If $type =~ m/</ ) { |
3642 | + push @clause, "((? IS NOT NULL AND $quo IS NULL) OR ($quo $cmp ?))"; |
3643 | + } |
3644 | + push @r_slice, $ord, $ord; |
3645 | + push @r_scols, $col, $col; |
3646 | + } |
3647 | + else { |
3648 | + push @r_slice, $ord; |
3649 | + push @r_scols, $col; |
3650 | + push @clause, ($type =~ m/=/ && $end ? "$quo $type ?" : "$quo $cmp ?"); |
3651 | + } |
3652 | + |
3653 | + push @clauses, '(' . join(' AND ', @clause) . ')'; |
3654 | + } |
3655 | + my $result = '(' . join(' OR ', @clauses) . ')'; |
3656 | + my $where = { |
3657 | + slice => \@r_slice, |
3658 | + scols => \@r_scols, |
3659 | + where => $result, |
3660 | + }; |
3661 | + return $where; |
3662 | +} |
3663 | + |
3664 | +sub generate_del_stmt { |
3665 | + my ( $self, %args ) = @_; |
3666 | + |
3667 | + my $tbl = $args{tbl_struct}; |
3668 | + my @cols = $args{cols} ? @{$args{cols}} : (); |
3669 | + my $tp = $self->{TableParser}; |
3670 | + my $q = $self->{Quoter}; |
3671 | + |
3672 | + my @del_cols; |
3673 | + my @del_slice; |
3674 | + |
3675 | + my $index = $tp->find_best_index($tbl, $args{index}); |
3676 | + die "Cannot find an ascendable index in table" unless $index; |
3677 | + |
3678 | + if ( $index ) { |
3679 | + @del_cols = @{$tbl->{keys}->{$index}->{cols}}; |
3680 | + } |
3681 | + else { |
3682 | + @del_cols = @{$tbl->{cols}}; |
3683 | + } |
3684 | + MKDEBUG && _d('Columns needed for DELETE:', join(', ', @del_cols)); |
3685 | + |
3686 | + my %col_posn = do { my $i = 0; map { $_ => $i++ } @cols }; |
3687 | + foreach my $col ( @del_cols ) { |
3688 | + if ( !exists $col_posn{$col} ) { |
3689 | + push @cols, $col; |
3690 | + $col_posn{$col} = $#cols; |
3691 | + } |
3692 | + push @del_slice, $col_posn{$col}; |
3693 | + } |
3694 | + MKDEBUG && _d('Ordinals needed for DELETE:', join(', ', @del_slice)); |
3695 | + |
3696 | + my $del_stmt = { |
3697 | + cols => \@cols, |
3698 | + index => $index, |
3699 | + where => '', |
3700 | + slice => [], |
3701 | + scols => [], |
3702 | + }; |
3703 | + |
3704 | + my @clauses; |
3705 | + foreach my $i ( 0 .. $#del_slice ) { |
3706 | + my $ord = $del_slice[$i]; |
3707 | + my $col = $cols[$ord]; |
3708 | + my $quo = $q->quote($col); |
3709 | + if ( $tbl->{is_nullable}->{$col} ) { |
3710 | + push @clauses, "((? IS NULL AND $quo IS NULL) OR ($quo = ?))"; |
3711 | + push @{$del_stmt->{slice}}, $ord, $ord; |
3712 | + push @{$del_stmt->{scols}}, $col, $col; |
3713 | + } |
3714 | + else { |
3715 | + push @clauses, "$quo = ?"; |
3716 | + push @{$del_stmt->{slice}}, $ord; |
3717 | + push @{$del_stmt->{scols}}, $col; |
3718 | + } |
3719 | + } |
3720 | + |
3721 | + $del_stmt->{where} = '(' . join(' AND ', @clauses) . ')'; |
3722 | + |
3723 | + return $del_stmt; |
3724 | +} |
3725 | + |
3726 | +sub generate_ins_stmt { |
3727 | + my ( $self, %args ) = @_; |
3728 | + foreach my $arg ( qw(ins_tbl sel_cols) ) { |
3729 | + die "I need a $arg argument" unless $args{$arg}; |
3730 | + } |
3731 | + my $ins_tbl = $args{ins_tbl}; |
3732 | + my @sel_cols = @{$args{sel_cols}}; |
3733 | + |
3734 | + die "You didn't specify any SELECT columns" unless @sel_cols; |
3735 | + |
3736 | + my @ins_cols; |
3737 | + my @ins_slice; |
3738 | + for my $i ( 0..$#sel_cols ) { |
3739 | + next unless $ins_tbl->{is_col}->{$sel_cols[$i]}; |
3740 | + push @ins_cols, $sel_cols[$i]; |
3741 | + push @ins_slice, $i; |
3742 | + } |
3743 | + |
3744 | + return { |
3745 | + cols => \@ins_cols, |
3746 | + slice => \@ins_slice, |
3747 | + }; |
3748 | } |
3749 | |
3750 | sub _d { |
3751 | @@ -3488,84 +2373,7 @@ |
3752 | 1; |
3753 | } |
3754 | # ########################################################################### |
3755 | -# End TableChunker package |
3756 | -# ########################################################################### |
3757 | - |
3758 | -# ########################################################################### |
3759 | -# Quoter package |
3760 | -# This package is a copy without comments from the original. The original |
3761 | -# with comments and its test file can be found in the Bazaar repository at, |
3762 | -# lib/Quoter.pm |
3763 | -# t/lib/Quoter.t |
3764 | -# See https://launchpad.net/percona-toolkit for more information. |
3765 | -# ########################################################################### |
3766 | -{ |
3767 | -package Quoter; |
3768 | - |
3769 | -use strict; |
3770 | -use warnings FATAL => 'all'; |
3771 | -use English qw(-no_match_vars); |
3772 | -use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
3773 | - |
3774 | -sub new { |
3775 | - my ( $class, %args ) = @_; |
3776 | - return bless {}, $class; |
3777 | -} |
3778 | - |
3779 | -sub quote { |
3780 | - my ( $self, @vals ) = @_; |
3781 | - foreach my $val ( @vals ) { |
3782 | - $val =~ s/`/``/g; |
3783 | - } |
3784 | - return join('.', map { '`' . $_ . '`' } @vals); |
3785 | -} |
3786 | - |
3787 | -sub quote_val { |
3788 | - my ( $self, $val ) = @_; |
3789 | - |
3790 | - return 'NULL' unless defined $val; # undef = NULL |
3791 | - return "''" if $val eq ''; # blank string = '' |
3792 | - return $val if $val =~ m/^0x[0-9a-fA-F]+$/; # hex data |
3793 | - |
3794 | - $val =~ s/(['\\])/\\$1/g; |
3795 | - return "'$val'"; |
3796 | -} |
3797 | - |
3798 | -sub split_unquote { |
3799 | - my ( $self, $db_tbl, $default_db ) = @_; |
3800 | - $db_tbl =~ s/`//g; |
3801 | - my ( $db, $tbl ) = split(/[.]/, $db_tbl); |
3802 | - if ( !$tbl ) { |
3803 | - $tbl = $db; |
3804 | - $db = $default_db; |
3805 | - } |
3806 | - return ($db, $tbl); |
3807 | -} |
3808 | - |
3809 | -sub literal_like { |
3810 | - my ( $self, $like ) = @_; |
3811 | - return unless $like; |
3812 | - $like =~ s/([%_])/\\$1/g; |
3813 | - return "'$like'"; |
3814 | -} |
3815 | - |
3816 | -sub join_quote { |
3817 | - my ( $self, $default_db, $db_tbl ) = @_; |
3818 | - return unless $db_tbl; |
3819 | - my ($db, $tbl) = split(/[.]/, $db_tbl); |
3820 | - if ( !$tbl ) { |
3821 | - $tbl = $db; |
3822 | - $db = $default_db; |
3823 | - } |
3824 | - $db = "`$db`" if $db && $db !~ m/^`/; |
3825 | - $tbl = "`$tbl`" if $tbl && $tbl !~ m/^`/; |
3826 | - return $db ? "$db.$tbl" : $tbl; |
3827 | -} |
3828 | - |
3829 | -1; |
3830 | -} |
3831 | -# ########################################################################### |
3832 | -# End Quoter package |
3833 | +# End TableNibbler package |
3834 | # ########################################################################### |
3835 | |
3836 | # ########################################################################### |
3837 | @@ -3593,6 +2401,54 @@ |
3838 | return bless $self, $class; |
3839 | } |
3840 | |
3841 | +sub get_slaves { |
3842 | + my ($self, %args) = @_; |
3843 | + my @required_args = qw(make_cxn OptionParser DSNParser Quoter); |
3844 | + foreach my $arg ( @required_args ) { |
3845 | + die "I need a $arg argument" unless $args{$arg}; |
3846 | + } |
3847 | + my ($make_cxn, $o, $dp) = @args{@required_args}; |
3848 | + |
3849 | + my $slaves = []; |
3850 | + my $method = $o->get('recursion-method'); |
3851 | + MKDEBUG && _d('Slave recursion method:', $method); |
3852 | + if ( !$method || $method =~ m/proocesslist|hosts/i ) { |
3853 | + my @required_args = qw(dbh dsn); |
3854 | + foreach my $arg ( @required_args ) { |
3855 | + die "I need a $arg argument" unless $args{$arg}; |
3856 | + } |
3857 | + my ($dbh, $dsn) = @args{@required_args}; |
3858 | + $self->recurse_to_slaves( |
3859 | + { dbh => $dbh, |
3860 | + dsn => $dsn, |
3861 | + dsn_parser => $dp, |
3862 | + recurse => $o->get('recurse'), |
3863 | + method => $o->get('recursion-method'), |
3864 | + callback => sub { |
3865 | + my ( $dsn, $dbh, $level, $parent ) = @_; |
3866 | + return unless $level; |
3867 | + MKDEBUG && _d('Found slave:', $dp->as_string($dsn)); |
3868 | + push @$slaves, $make_cxn->(dsn => $dsn, dbh => $dbh); |
3869 | + return; |
3870 | + }, |
3871 | + } |
3872 | + ); |
3873 | + } |
3874 | + elsif ( $method =~ m/^dsn=/i ) { |
3875 | + my ($dsn_table_dsn) = $method =~ m/^dsn=(.+)/i; |
3876 | + $slaves = $self->get_cxn_from_dsn_table( |
3877 | + %args, |
3878 | + dsn_table_dsn => $dsn_table_dsn, |
3879 | + ); |
3880 | + } |
3881 | + else { |
3882 | + die "Invalid --recusion-method: $method. Valid values are: " |
3883 | + . "dsn=DSN, hosts, or processlist.\n"; |
3884 | + } |
3885 | + |
3886 | + return $slaves; |
3887 | +} |
3888 | + |
3889 | sub recurse_to_slaves { |
3890 | my ( $self, $args, $level ) = @_; |
3891 | $level ||= 0; |
3892 | @@ -4169,6 +3025,43 @@ |
3893 | return; |
3894 | } |
3895 | |
3896 | +sub get_cxn_from_dsn_table { |
3897 | + my ($self, %args) = @_; |
3898 | + my @required_args = qw(dsn_table_dsn make_cxn DSNParser Quoter); |
3899 | + foreach my $arg ( @required_args ) { |
3900 | + die "I need a $arg argument" unless $args{$arg}; |
3901 | + } |
3902 | + my ($dsn_table_dsn, $make_cxn, $dp, $q) = @args{@required_args}; |
3903 | + MKDEBUG && _d('DSN table DSN:', $dsn_table_dsn); |
3904 | + |
3905 | + my $dsn = $dp->parse($dsn_table_dsn); |
3906 | + my $dsn_table; |
3907 | + if ( $dsn->{D} && $dsn->{t} ) { |
3908 | + $dsn_table = $q->quote($dsn->{D}, $dsn->{t}); |
3909 | + } |
3910 | + elsif ( $dsn->{t} && $dsn->{t} =~ m/\./ ) { |
3911 | + $dsn_table = $q->quote($q->split_unquote($dsn->{t})); |
3912 | + } |
3913 | + else { |
3914 | + die "DSN table DSN does not specify a database (D) " |
3915 | + . "or a database-qualified table (t)"; |
3916 | + } |
3917 | + |
3918 | + my $dsn_tbl_cxn = $make_cxn->(dsn => $dsn); |
3919 | + my $dbh = $dsn_tbl_cxn->connect(); |
3920 | + my $sql = "SELECT dsn FROM $dsn_table ORDER BY id"; |
3921 | + MKDEBUG && _d($sql); |
3922 | + my $dsn_strings = $dbh->selectcol_arrayref($sql); |
3923 | + my @cxn; |
3924 | + if ( $dsn_strings ) { |
3925 | + foreach my $dsn_string ( @$dsn_strings ) { |
3926 | + MKDEBUG && _d('DSN from DSN table:', $dsn_string); |
3927 | + push @cxn, $make_cxn->(dsn_string => $dsn_string); |
3928 | + } |
3929 | + } |
3930 | + return \@cxn; |
3931 | +} |
3932 | + |
3933 | sub _d { |
3934 | my ($package, undef, $line) = caller 0; |
3935 | @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; } |
3936 | @@ -4184,6 +3077,1206 @@ |
3937 | # ########################################################################### |
3938 | |
3939 | # ########################################################################### |
3940 | +# RowChecksum package |
3941 | +# This package is a copy without comments from the original. The original |
3942 | +# with comments and its test file can be found in the Bazaar repository at, |
3943 | +# lib/RowChecksum.pm |
3944 | +# t/lib/RowChecksum.t |
3945 | +# See https://launchpad.net/percona-toolkit for more information. |
3946 | +# ########################################################################### |
3947 | +{ |
3948 | +package RowChecksum; |
3949 | + |
3950 | +use strict; |
3951 | +use warnings FATAL => 'all'; |
3952 | +use English qw(-no_match_vars); |
3953 | +use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
3954 | + |
3955 | +use List::Util qw(max); |
3956 | +use Data::Dumper; |
3957 | +$Data::Dumper::Indent = 1; |
3958 | +$Data::Dumper::Sortkeys = 1; |
3959 | +$Data::Dumper::Quotekeys = 0; |
3960 | + |
3961 | +sub new { |
3962 | + my ( $class, %args ) = @_; |
3963 | + foreach my $arg ( qw(OptionParser Quoter) ) { |
3964 | + die "I need a $arg argument" unless defined $args{$arg}; |
3965 | + } |
3966 | + my $self = { %args }; |
3967 | + return bless $self, $class; |
3968 | +} |
3969 | + |
3970 | +sub make_row_checksum { |
3971 | + my ( $self, %args ) = @_; |
3972 | + my @required_args = qw(tbl); |
3973 | + foreach my $arg( @required_args ) { |
3974 | + die "I need a $arg argument" unless $args{$arg}; |
3975 | + } |
3976 | + my ($tbl) = @args{@required_args}; |
3977 | + |
3978 | + my $o = $self->{OptionParser}; |
3979 | + my $q = $self->{Quoter}; |
3980 | + my $tbl_struct = $tbl->{tbl_struct}; |
3981 | + my $func = $args{func} || uc($o->get('function')); |
3982 | + my $cols = $self->get_checksum_columns(%args); |
3983 | + |
3984 | + my $query; |
3985 | + if ( !$args{no_cols} ) { |
3986 | + $query = join(', ', |
3987 | + map { |
3988 | + my $col = $_; |
3989 | + if ( $col =~ m/\+ 0/ ) { |
3990 | + my ($real_col) = /^(\S+)/; |
3991 | + $col .= " AS $real_col"; |
3992 | + } |
3993 | + elsif ( $col =~ m/TRIM/ ) { |
3994 | + my ($real_col) = m/TRIM\(([^\)]+)\)/; |
3995 | + $col .= " AS $real_col"; |
3996 | + } |
3997 | + $col; |
3998 | + } @{$cols->{select}}) |
3999 | + . ', '; |
4000 | + } |
4001 | + |
4002 | + if ( uc $func ne 'FNV_64' && uc $func ne 'FNV1A_64' ) { |
4003 | + my $sep = $o->get('separator') || '#'; |
4004 | + $sep =~ s/'//g; |
4005 | + $sep ||= '#'; |
4006 | + |
4007 | + my @nulls = grep { $cols->{allowed}->{$_} } @{$tbl_struct->{null_cols}}; |
4008 | + if ( @nulls ) { |
4009 | + my $bitmap = "CONCAT(" |
4010 | + . join(', ', map { 'ISNULL(' . $q->quote($_) . ')' } @nulls) |
4011 | + . ")"; |
4012 | + push @{$cols->{select}}, $bitmap; |
4013 | + } |
4014 | + |
4015 | + $query .= @{$cols->{select}} > 1 |
4016 | + ? "$func(CONCAT_WS('$sep', " . join(', ', @{$cols->{select}}) . '))' |
4017 | + : "$func($cols->{select}->[0])"; |
4018 | + } |
4019 | + else { |
4020 | + my $fnv_func = uc $func; |
4021 | + $query .= "$fnv_func(" . join(', ', @{$cols->{select}}) . ')'; |
4022 | + } |
4023 | + |
4024 | + MKDEBUG && _d('Row checksum:', $query); |
4025 | + return $query; |
4026 | +} |
4027 | + |
4028 | +sub make_chunk_checksum { |
4029 | + my ( $self, %args ) = @_; |
4030 | + my @required_args = qw(tbl); |
4031 | + foreach my $arg( @required_args ) { |
4032 | + die "I need a $arg argument" unless $args{$arg}; |
4033 | + } |
4034 | + if ( !$args{dbh} && !($args{func} && $args{crc_width} && $args{crc_type}) ) { |
4035 | + die "I need a dbh argument" |
4036 | + } |
4037 | + my ($tbl) = @args{@required_args}; |
4038 | + my $o = $self->{OptionParser}; |
4039 | + my $q = $self->{Quoter}; |
4040 | + |
4041 | + my %crc_args = $self->get_crc_args(%args); |
4042 | + MKDEBUG && _d("Checksum strat:", Dumper(\%crc_args)); |
4043 | + |
4044 | + my $row_checksum = $self->make_row_checksum( |
4045 | + %args, |
4046 | + %crc_args, |
4047 | + no_cols => 1 |
4048 | + ); |
4049 | + my $crc; |
4050 | + if ( $crc_args{crc_type} =~ m/int$/ ) { |
4051 | + $crc = "COALESCE(LOWER(CONV(BIT_XOR(CAST($row_checksum AS UNSIGNED)), " |
4052 | + . "10, 16)), 0)"; |
4053 | + } |
4054 | + else { |
4055 | + my $slices = $self->_make_xor_slices( |
4056 | + row_checksum => $row_checksum, |
4057 | + %crc_args, |
4058 | + ); |
4059 | + $crc = "COALESCE(LOWER(CONCAT($slices)), 0)"; |
4060 | + } |
4061 | + |
4062 | + my $select = "COUNT(*) AS cnt, $crc AS crc"; |
4063 | + MKDEBUG && _d('Chunk checksum:', $select); |
4064 | + return $select; |
4065 | +} |
4066 | + |
4067 | +sub get_checksum_columns { |
4068 | + my ($self, %args) = @_; |
4069 | + my @required_args = qw(tbl); |
4070 | + foreach my $arg( @required_args ) { |
4071 | + die "I need a $arg argument" unless $args{$arg}; |
4072 | + } |
4073 | + my ($tbl) = @args{@required_args}; |
4074 | + my $o = $self->{OptionParser}; |
4075 | + my $q = $self->{Quoter}; |
4076 | + |
4077 | + my $trim = $o->get('trim'); |
4078 | + my $float_precision = $o->get('float-precision'); |
4079 | + |
4080 | + my $tbl_struct = $tbl->{tbl_struct}; |
4081 | + my $ignore_col = $o->get('ignore-columns') || {}; |
4082 | + my $all_cols = $o->get('columns') || $tbl_struct->{cols}; |
4083 | + my %cols = map { lc($_) => 1 } grep { !$ignore_col->{$_} } @$all_cols; |
4084 | + my %seen; |
4085 | + my @cols = |
4086 | + map { |
4087 | + my $type = $tbl_struct->{type_for}->{$_}; |
4088 | + my $result = $q->quote($_); |
4089 | + if ( $type eq 'timestamp' ) { |
4090 | + $result .= ' + 0'; |
4091 | + } |
4092 | + elsif ( $float_precision && $type =~ m/float|double/ ) { |
4093 | + $result = "ROUND($result, $float_precision)"; |
4094 | + } |
4095 | + elsif ( $trim && $type =~ m/varchar/ ) { |
4096 | + $result = "TRIM($result)"; |
4097 | + } |
4098 | + $result; |
4099 | + } |
4100 | + grep { |
4101 | + $cols{$_} && !$seen{$_}++ |
4102 | + } |
4103 | + @{$tbl_struct->{cols}}; |
4104 | + |
4105 | + return { |
4106 | + select => \@cols, |
4107 | + allowed => \%cols, |
4108 | + }; |
4109 | +} |
4110 | + |
4111 | +sub get_crc_args { |
4112 | + my ($self, %args) = @_; |
4113 | + my $func = $args{func} || $self->_get_hash_func(%args); |
4114 | + my $crc_width = $args{crc_width}|| $self->_get_crc_width(%args, func=>$func); |
4115 | + my $crc_type = $args{crc_type} || $self->_get_crc_type(%args, func=>$func); |
4116 | + my $opt_slice; |
4117 | + if ( $args{dbh} && $crc_type !~ m/int$/ ) { |
4118 | + $opt_slice = $self->_optimize_xor(%args, func=>$func); |
4119 | + } |
4120 | + |
4121 | + return ( |
4122 | + func => $func, |
4123 | + crc_width => $crc_width, |
4124 | + crc_type => $crc_type, |
4125 | + opt_slice => $opt_slice, |
4126 | + ); |
4127 | +} |
4128 | + |
4129 | +sub _get_hash_func { |
4130 | + my ( $self, %args ) = @_; |
4131 | + my @required_args = qw(dbh); |
4132 | + foreach my $arg( @required_args ) { |
4133 | + die "I need a $arg argument" unless $args{$arg}; |
4134 | + } |
4135 | + my ($dbh) = @args{@required_args}; |
4136 | + my $o = $self->{OptionParser}; |
4137 | + my @funcs = qw(CRC32 FNV1A_64 FNV_64 MD5 SHA1); |
4138 | + |
4139 | + if ( my $func = $o->get('function') ) { |
4140 | + unshift @funcs, $func; |
4141 | + } |
4142 | + |
4143 | + my ($result, $error); |
4144 | + foreach my $func ( @funcs ) { |
4145 | + eval { |
4146 | + my $sql = "SELECT $func('test-string')"; |
4147 | + MKDEBUG && _d($sql); |
4148 | + $args{dbh}->do($sql); |
4149 | + }; |
4150 | + if ( $EVAL_ERROR && $EVAL_ERROR =~ m/failed: (.*?) at \S+ line/ ) { |
4151 | + $error .= qq{$func cannot be used because "$1"\n}; |
4152 | + MKDEBUG && _d($func, 'cannot be used because', $1); |
4153 | + } |
4154 | + MKDEBUG && _d('Chosen hash func:', $result); |
4155 | + return $func; |
4156 | + } |
4157 | + die $error || 'No hash functions (CRC32, MD5, etc.) are available'; |
4158 | +} |
4159 | + |
4160 | +sub _get_crc_width { |
4161 | + my ( $self, %args ) = @_; |
4162 | + my @required_args = qw(dbh func); |
4163 | + foreach my $arg( @required_args ) { |
4164 | + die "I need a $arg argument" unless $args{$arg}; |
4165 | + } |
4166 | + my ($dbh, $func) = @args{@required_args}; |
4167 | + |
4168 | + my $crc_width = 16; |
4169 | + if ( uc $func ne 'FNV_64' && uc $func ne 'FNV1A_64' ) { |
4170 | + eval { |
4171 | + my ($val) = $dbh->selectrow_array("SELECT $func('a')"); |
4172 | + $crc_width = max(16, length($val)); |
4173 | + }; |
4174 | + } |
4175 | + return $crc_width; |
4176 | +} |
4177 | + |
4178 | +sub _get_crc_type { |
4179 | + my ( $self, %args ) = @_; |
4180 | + my @required_args = qw(dbh func); |
4181 | + foreach my $arg( @required_args ) { |
4182 | + die "I need a $arg argument" unless $args{$arg}; |
4183 | + } |
4184 | + my ($dbh, $func) = @args{@required_args}; |
4185 | + |
4186 | + my $type = ''; |
4187 | + my $length = 0; |
4188 | + my $sql = "SELECT $func('a')"; |
4189 | + my $sth = $dbh->prepare($sql); |
4190 | + eval { |
4191 | + $sth->execute(); |
4192 | + $type = $sth->{mysql_type_name}->[0]; |
4193 | + $length = $sth->{mysql_length}->[0]; |
4194 | + MKDEBUG && _d($sql, $type, $length); |
4195 | + if ( $type eq 'bigint' && $length < 20 ) { |
4196 | + $type = 'int'; |
4197 | + } |
4198 | + }; |
4199 | + $sth->finish; |
4200 | + MKDEBUG && _d('crc_type:', $type, 'length:', $length); |
4201 | + return $type; |
4202 | +} |
4203 | + |
4204 | +sub _optimize_xor { |
4205 | + my ( $self, %args ) = @_; |
4206 | + my @required_args = qw(dbh func); |
4207 | + foreach my $arg( @required_args ) { |
4208 | + die "I need a $arg argument" unless $args{$arg}; |
4209 | + } |
4210 | + my ($dbh, $func) = @args{@required_args}; |
4211 | + |
4212 | + die "$func never needs BIT_XOR optimization" |
4213 | + if $func =~ m/^(?:FNV1A_64|FNV_64|CRC32)$/i; |
4214 | + |
4215 | + my $opt_slice = 0; |
4216 | + my $unsliced = uc $dbh->selectall_arrayref("SELECT $func('a')")->[0]->[0]; |
4217 | + my $sliced = ''; |
4218 | + my $start = 1; |
4219 | + my $crc_width = length($unsliced) < 16 ? 16 : length($unsliced); |
4220 | + |
4221 | + do { # Try different positions till sliced result equals non-sliced. |
4222 | + MKDEBUG && _d('Trying slice', $opt_slice); |
4223 | + $dbh->do('SET @crc := "", @cnt := 0'); |
4224 | + my $slices = $self->_make_xor_slices( |
4225 | + row_checksum => "\@crc := $func('a')", |
4226 | + crc_width => $crc_width, |
4227 | + opt_slice => $opt_slice, |
4228 | + ); |
4229 | + |
4230 | + my $sql = "SELECT CONCAT($slices) AS TEST FROM (SELECT NULL) AS x"; |
4231 | + $sliced = ($dbh->selectrow_array($sql))[0]; |
4232 | + if ( $sliced ne $unsliced ) { |
4233 | + MKDEBUG && _d('Slice', $opt_slice, 'does not work'); |
4234 | + $start += 16; |
4235 | + ++$opt_slice; |
4236 | + } |
4237 | + } while ( $start < $crc_width && $sliced ne $unsliced ); |
4238 | + |
4239 | + if ( $sliced eq $unsliced ) { |
4240 | + MKDEBUG && _d('Slice', $opt_slice, 'works'); |
4241 | + return $opt_slice; |
4242 | + } |
4243 | + else { |
4244 | + MKDEBUG && _d('No slice works'); |
4245 | + return undef; |
4246 | + } |
4247 | +} |
4248 | + |
4249 | +sub _make_xor_slices { |
4250 | + my ( $self, %args ) = @_; |
4251 | + my @required_args = qw(row_checksum crc_width); |
4252 | + foreach my $arg( @required_args ) { |
4253 | + die "I need a $arg argument" unless $args{$arg}; |
4254 | + } |
4255 | + my ($row_checksum, $crc_width) = @args{@required_args}; |
4256 | + my ($opt_slice) = $args{opt_slice}; |
4257 | + |
4258 | + my @slices; |
4259 | + for ( my $start = 1; $start <= $crc_width; $start += 16 ) { |
4260 | + my $len = $crc_width - $start + 1; |
4261 | + if ( $len > 16 ) { |
4262 | + $len = 16; |
4263 | + } |
4264 | + push @slices, |
4265 | + "LPAD(CONV(BIT_XOR(" |
4266 | + . "CAST(CONV(SUBSTRING(\@crc, $start, $len), 16, 10) AS UNSIGNED))" |
4267 | + . ", 10, 16), $len, '0')"; |
4268 | + } |
4269 | + |
4270 | + if ( defined $opt_slice && $opt_slice < @slices ) { |
4271 | + $slices[$opt_slice] =~ s/\@crc/\@crc := $row_checksum/; |
4272 | + } |
4273 | + else { |
4274 | + map { s/\@crc/$row_checksum/ } @slices; |
4275 | + } |
4276 | + |
4277 | + return join(', ', @slices); |
4278 | +} |
4279 | + |
4280 | +sub find_replication_differences { |
4281 | + my ($self, %args) = @_; |
4282 | + my @required_args = qw(dbh repl_table); |
4283 | + foreach my $arg( @required_args ) { |
4284 | + die "I need a $arg argument" unless $args{$arg}; |
4285 | + } |
4286 | + my ($dbh, $repl_table) = @args{@required_args}; |
4287 | + |
4288 | + my $sql |
4289 | + = "SELECT CONCAT(db, '.', tbl) AS `table`, " |
4290 | + . "chunk, chunk_index, lower_boundary, upper_boundary, " |
4291 | + . "COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, " |
4292 | + . "COALESCE(" |
4293 | + . "this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0" |
4294 | + . ") AS crc_diff, this_cnt, master_cnt, this_crc, master_crc " |
4295 | + . "FROM $repl_table " |
4296 | + . "WHERE (master_cnt <> this_cnt OR master_crc <> this_crc " |
4297 | + . "OR ISNULL(master_crc) <> ISNULL(this_crc))" |
4298 | + . ($args{where} ? " AND ($args{where})" : ""); |
4299 | + MKDEBUG && _d($sql); |
4300 | + my $diffs = $dbh->selectall_arrayref($sql, { Slice => {} }); |
4301 | + return $diffs; |
4302 | +} |
4303 | + |
4304 | +sub _d { |
4305 | + my ($package, undef, $line) = caller 0; |
4306 | + @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; } |
4307 | + map { defined $_ ? $_ : 'undef' } |
4308 | + @_; |
4309 | + print STDERR "# $package:$line $PID ", join(' ', @_), "\n"; |
4310 | +} |
4311 | + |
4312 | +1; |
4313 | +} |
4314 | +# ########################################################################### |
4315 | +# End RowChecksum package |
4316 | +# ########################################################################### |
4317 | + |
4318 | +# ########################################################################### |
4319 | +# NibbleIterator package |
4320 | +# This package is a copy without comments from the original. The original |
4321 | +# with comments and its test file can be found in the Bazaar repository at, |
4322 | +# lib/NibbleIterator.pm |
4323 | +# t/lib/NibbleIterator.t |
4324 | +# See https://launchpad.net/percona-toolkit for more information. |
4325 | +# ########################################################################### |
4326 | +{ |
4327 | +package NibbleIterator; |
4328 | + |
4329 | +use strict; |
4330 | +use warnings FATAL => 'all'; |
4331 | +use English qw(-no_match_vars); |
4332 | +use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
4333 | + |
4334 | +use Data::Dumper; |
4335 | +$Data::Dumper::Indent = 1; |
4336 | +$Data::Dumper::Sortkeys = 1; |
4337 | +$Data::Dumper::Quotekeys = 0; |
4338 | + |
4339 | +sub new { |
4340 | + my ( $class, %args ) = @_; |
4341 | + my @required_args = qw(Cxn tbl chunk_size OptionParser Quoter TableNibbler TableParser); |
4342 | + foreach my $arg ( @required_args ) { |
4343 | + die "I need a $arg argument" unless $args{$arg}; |
4344 | + } |
4345 | + my ($cxn, $tbl, $chunk_size, $o, $q) = @args{@required_args}; |
4346 | + |
4347 | + my $where = $o->get('where'); |
4348 | + my ($row_est, $mysql_index) = get_row_estimate(%args, where => $where); |
4349 | + my $one_nibble = !defined $args{one_nibble} || $args{one_nibble} |
4350 | + ? $row_est <= $chunk_size * $o->get('chunk-size-limit') |
4351 | + : 0; |
4352 | + MKDEBUG && _d('One nibble:', $one_nibble ? 'yes' : 'no'); |
4353 | + |
4354 | + if ( $args{resume} |
4355 | + && !defined $args{resume}->{lower_boundary} |
4356 | + && !defined $args{resume}->{upper_boundary} ) { |
4357 | + MKDEBUG && _d('Resuming from one nibble table'); |
4358 | + $one_nibble = 1; |
4359 | + } |
4360 | + |
4361 | + my $index = _find_best_index(%args, mysql_index => $mysql_index); |
4362 | + if ( !$index && !$one_nibble ) { |
4363 | + die "There is no good index and the table is oversized."; |
4364 | + } |
4365 | + |
4366 | + my $tbl_struct = $tbl->{tbl_struct}; |
4367 | + my $ignore_col = $o->get('ignore-columns') || {}; |
4368 | + my $all_cols = $o->get('columns') || $tbl_struct->{cols}; |
4369 | + my @cols = grep { !$ignore_col->{$_} } @$all_cols; |
4370 | + my $self; |
4371 | + if ( $one_nibble ) { |
4372 | + my $nibble_sql |
4373 | + = ($args{dml} ? "$args{dml} " : "SELECT ") |
4374 | + . ($args{select} ? $args{select} |
4375 | + : join(', ', map { $q->quote($_) } @cols)) |
4376 | + . " FROM " . $q->quote(@{$tbl}{qw(db tbl)}) |
4377 | + . ($where ? " AND ($where)" : '') |
4378 | + . " /*checksum table*/"; |
4379 | + MKDEBUG && _d('One nibble statement:', $nibble_sql); |
4380 | + |
4381 | + my $explain_nibble_sql |
4382 | + = "EXPLAIN SELECT " |
4383 | + . ($args{select} ? $args{select} |
4384 | + : join(', ', map { $q->quote($_) } @cols)) |
4385 | + . " FROM " . $q->quote(@{$tbl}{qw(db tbl)}) |
4386 | + . ($where ? " AND ($where)" : '') |
4387 | + . " /*explain checksum table*/"; |
4388 | + MKDEBUG && _d('Explain one nibble statement:', $explain_nibble_sql); |
4389 | + |
4390 | + $self = { |
4391 | + %args, |
4392 | + one_nibble => 1, |
4393 | + limit => 0, |
4394 | + nibble_sql => $nibble_sql, |
4395 | + explain_nibble_sql => $explain_nibble_sql, |
4396 | + }; |
4397 | + } |
4398 | + else { |
4399 | + my $index_cols = $tbl->{tbl_struct}->{keys}->{$index}->{cols}; |
4400 | + |
4401 | + my $asc = $args{TableNibbler}->generate_asc_stmt( |
4402 | + %args, |
4403 | + tbl_struct => $tbl->{tbl_struct}, |
4404 | + index => $index, |
4405 | + cols => \@cols, |
4406 | + asc_only => 1, |
4407 | + ); |
4408 | + MKDEBUG && _d('Ascend params:', Dumper($asc)); |
4409 | + |
4410 | + my $from = $q->quote(@{$tbl}{qw(db tbl)}) . " FORCE INDEX(`$index`)"; |
4411 | + my $order_by = join(', ', map {$q->quote($_)} @{$index_cols}); |
4412 | + |
4413 | + my $first_lb_sql |
4414 | + = "SELECT /*!40001 SQL_NO_CACHE */ " |
4415 | + . join(', ', map { $q->quote($_) } @{$asc->{scols}}) |
4416 | + . " FROM $from" |
4417 | + . ($where ? " WHERE $where" : '') |
4418 | + . " ORDER BY $order_by" |
4419 | + . " LIMIT 1" |
4420 | + . " /*first lower boundary*/"; |
4421 | + MKDEBUG && _d('First lower boundary statement:', $first_lb_sql); |
4422 | + |
4423 | + my $resume_lb_sql; |
4424 | + if ( $args{resume} ) { |
4425 | + $resume_lb_sql |
4426 | + = "SELECT /*!40001 SQL_NO_CACHE */ " |
4427 | + . join(', ', map { $q->quote($_) } @{$asc->{scols}}) |
4428 | + . " FROM $from" |
4429 | + . " WHERE " . $asc->{boundaries}->{'>'} |
4430 | + . ($where ? " AND ($where)" : '') |
4431 | + . " ORDER BY $order_by" |
4432 | + . " LIMIT 1" |
4433 | + . " /*resume lower boundary*/"; |
4434 | + MKDEBUG && _d('Resume lower boundary statement:', $resume_lb_sql); |
4435 | + } |
4436 | + |
4437 | + my $last_ub_sql |
4438 | + = "SELECT /*!40001 SQL_NO_CACHE */ " |
4439 | + . join(', ', map { $q->quote($_) } @{$asc->{scols}}) |
4440 | + . " FROM $from" |
4441 | + . ($where ? " WHERE $where" : '') |
4442 | + . " ORDER BY " |
4443 | + . join(' DESC, ', map {$q->quote($_)} @{$index_cols}) . ' DESC' |
4444 | + . " LIMIT 1" |
4445 | + . " /*last upper boundary*/"; |
4446 | + MKDEBUG && _d('Last upper boundary statement:', $last_ub_sql); |
4447 | + |
4448 | + my $ub_sql |
4449 | + = "SELECT /*!40001 SQL_NO_CACHE */ " |
4450 | + . join(', ', map { $q->quote($_) } @{$asc->{scols}}) |
4451 | + . " FROM $from" |
4452 | + . " WHERE " . $asc->{boundaries}->{'>='} |
4453 | + . ($where ? " AND ($where)" : '') |
4454 | + . " ORDER BY $order_by" |
4455 | + . " LIMIT ?, 2" |
4456 | + . " /*next chunk boundary*/"; |
4457 | + MKDEBUG && _d('Upper boundary statement:', $ub_sql); |
4458 | + |
4459 | + my $nibble_sql |
4460 | + = ($args{dml} ? "$args{dml} " : "SELECT ") |
4461 | + . ($args{select} ? $args{select} |
4462 | + : join(', ', map { $q->quote($_) } @{$asc->{cols}})) |
4463 | + . " FROM $from" |
4464 | + . " WHERE " . $asc->{boundaries}->{'>='} # lower boundary |
4465 | + . " AND " . $asc->{boundaries}->{'<='} # upper boundary |
4466 | + . ($where ? " AND ($where)" : '') |
4467 | + . ($args{order_by} ? " ORDER BY $order_by" : "") |
4468 | + . " /*checksum chunk*/"; |
4469 | + MKDEBUG && _d('Nibble statement:', $nibble_sql); |
4470 | + |
4471 | + my $explain_nibble_sql |
4472 | + = "EXPLAIN SELECT " |
4473 | + . ($args{select} ? $args{select} |
4474 | + : join(', ', map { $q->quote($_) } @{$asc->{cols}})) |
4475 | + . " FROM $from" |
4476 | + . " WHERE " . $asc->{boundaries}->{'>='} # lower boundary |
4477 | + . " AND " . $asc->{boundaries}->{'<='} # upper boundary |
4478 | + . ($where ? " AND ($where)" : '') |
4479 | + . ($args{order_by} ? " ORDER BY $order_by" : "") |
4480 | + . " /*explain checksum chunk*/"; |
4481 | + MKDEBUG && _d('Explain nibble statement:', $explain_nibble_sql); |
4482 | + |
4483 | + my $limit = $chunk_size - 1; |
4484 | + MKDEBUG && _d('Initial chunk size (LIMIT):', $limit); |
4485 | + |
4486 | + $self = { |
4487 | + %args, |
4488 | + index => $index, |
4489 | + limit => $limit, |
4490 | + first_lb_sql => $first_lb_sql, |
4491 | + last_ub_sql => $last_ub_sql, |
4492 | + ub_sql => $ub_sql, |
4493 | + nibble_sql => $nibble_sql, |
4494 | + explain_ub_sql => "EXPLAIN $ub_sql", |
4495 | + explain_nibble_sql => $explain_nibble_sql, |
4496 | + resume_lb_sql => $resume_lb_sql, |
4497 | + sql => { |
4498 | + columns => $asc->{scols}, |
4499 | + from => $from, |
4500 | + where => $where, |
4501 | + boundaries => $asc->{boundaries}, |
4502 | + order_by => $order_by, |
4503 | + }, |
4504 | + }; |
4505 | + } |
4506 | + |
4507 | + $self->{row_est} = $row_est; |
4508 | + $self->{nibbleno} = 0; |
4509 | + $self->{have_rows} = 0; |
4510 | + $self->{rowno} = 0; |
4511 | + $self->{oktonibble} = 1; |
4512 | + |
4513 | + return bless $self, $class; |
4514 | +} |
4515 | + |
4516 | +sub next { |
4517 | + my ($self) = @_; |
4518 | + |
4519 | + if ( !$self->{oktonibble} ) { |
4520 | + MKDEBUG && _d('Not ok to nibble'); |
4521 | + return; |
4522 | + } |
4523 | + |
4524 | + my %callback_args = ( |
4525 | + Cxn => $self->{Cxn}, |
4526 | + tbl => $self->{tbl}, |
4527 | + NibbleIterator => $self, |
4528 | + ); |
4529 | + |
4530 | + if ($self->{nibbleno} == 0) { |
4531 | + $self->_prepare_sths(); |
4532 | + $self->_get_bounds(); |
4533 | + if ( my $callback = $self->{callbacks}->{init} ) { |
4534 | + $self->{oktonibble} = $callback->(%callback_args); |
4535 | + MKDEBUG && _d('init callback returned', $self->{oktonibble}); |
4536 | + if ( !$self->{oktonibble} ) { |
4537 | + $self->{no_more_boundaries} = 1; |
4538 | + return; |
4539 | + } |
4540 | + } |
4541 | + } |
4542 | + |
4543 | + NIBBLE: |
4544 | + while ( $self->{have_rows} || $self->_next_boundaries() ) { |
4545 | + if ( !$self->{have_rows} ) { |
4546 | + $self->{nibbleno}++; |
4547 | + MKDEBUG && _d($self->{nibble_sth}->{Statement}, 'params:', |
4548 | + join(', ', (@{$self->{lower}}, @{$self->{upper}}))); |
4549 | + if ( my $callback = $self->{callbacks}->{exec_nibble} ) { |
4550 | + $self->{have_rows} = $callback->(%callback_args); |
4551 | + } |
4552 | + else { |
4553 | + $self->{nibble_sth}->execute(@{$self->{lower}}, @{$self->{upper}}); |
4554 | + $self->{have_rows} = $self->{nibble_sth}->rows(); |
4555 | + } |
4556 | + MKDEBUG && _d($self->{have_rows}, 'rows in nibble', $self->{nibbleno}); |
4557 | + } |
4558 | + |
4559 | + if ( $self->{have_rows} ) { |
4560 | + my $row = $self->{nibble_sth}->fetchrow_arrayref(); |
4561 | + if ( $row ) { |
4562 | + $self->{rowno}++; |
4563 | + MKDEBUG && _d('Row', $self->{rowno}, 'in nibble',$self->{nibbleno}); |
4564 | + return [ @$row ]; |
4565 | + } |
4566 | + } |
4567 | + |
4568 | + MKDEBUG && _d('No rows in nibble or nibble skipped'); |
4569 | + if ( my $callback = $self->{callbacks}->{after_nibble} ) { |
4570 | + $callback->(%callback_args); |
4571 | + } |
4572 | + $self->{rowno} = 0; |
4573 | + $self->{have_rows} = 0; |
4574 | + } |
4575 | + |
4576 | + MKDEBUG && _d('Done nibbling'); |
4577 | + if ( my $callback = $self->{callbacks}->{done} ) { |
4578 | + $callback->(%callback_args); |
4579 | + } |
4580 | + |
4581 | + return; |
4582 | +} |
4583 | + |
4584 | +sub nibble_number { |
4585 | + my ($self) = @_; |
4586 | + return $self->{nibbleno}; |
4587 | +} |
4588 | + |
4589 | +sub set_nibble_number { |
4590 | + my ($self, $n) = @_; |
4591 | + die "I need a number" unless $n; |
4592 | + $self->{nibbleno} = $n; |
4593 | + MKDEBUG && _d('Set new nibble number:', $n); |
4594 | + return; |
4595 | +} |
4596 | + |
4597 | +sub nibble_index { |
4598 | + my ($self) = @_; |
4599 | + return $self->{index}; |
4600 | +} |
4601 | + |
4602 | +sub statements { |
4603 | + my ($self) = @_; |
4604 | + return { |
4605 | + nibble => $self->{nibble_sth}, |
4606 | + explain_nibble => $self->{explain_nibble_sth}, |
4607 | + upper_boundary => $self->{ub_sth}, |
4608 | + explain_upper_boundary => $self->{explain_ub_sth}, |
4609 | + } |
4610 | +} |
4611 | + |
4612 | +sub boundaries { |
4613 | + my ($self) = @_; |
4614 | + return { |
4615 | + first_lower => $self->{first_lower}, |
4616 | + lower => $self->{lower}, |
4617 | + upper => $self->{upper}, |
4618 | + next_lower => $self->{next_lower}, |
4619 | + last_upper => $self->{last_upper}, |
4620 | + }; |
4621 | +} |
4622 | + |
4623 | +sub set_boundary { |
4624 | + my ($self, $boundary, $values) = @_; |
4625 | + die "I need a boundary parameter" |
4626 | + unless $boundary; |
4627 | + die "Invalid boundary: $boundary" |
4628 | + unless $boundary =~ m/^(?:lower|upper|next_lower|last_upper)$/; |
4629 | + die "I need a values arrayref parameter" |
4630 | + unless $values && ref $values eq 'ARRAY'; |
4631 | + $self->{$boundary} = $values; |
4632 | + MKDEBUG && _d('Set new', $boundary, 'boundary:', Dumper($values)); |
4633 | + return; |
4634 | +} |
4635 | + |
4636 | +sub one_nibble { |
4637 | + my ($self) = @_; |
4638 | + return $self->{one_nibble}; |
4639 | +} |
4640 | + |
4641 | +sub chunk_size { |
4642 | + my ($self) = @_; |
4643 | + return $self->{limit} + 1; |
4644 | +} |
4645 | + |
4646 | +sub set_chunk_size { |
4647 | + my ($self, $limit) = @_; |
4648 | + return if $self->{one_nibble}; |
4649 | + die "Chunk size must be > 0" unless $limit; |
4650 | + $self->{limit} = $limit - 1; |
4651 | + MKDEBUG && _d('Set new chunk size (LIMIT):', $limit); |
4652 | + return; |
4653 | +} |
4654 | + |
4655 | +sub sql { |
4656 | + my ($self) = @_; |
4657 | + return $self->{sql}; |
4658 | +} |
4659 | + |
4660 | +sub more_boundaries { |
4661 | + my ($self) = @_; |
4662 | + return !$self->{no_more_boundaries}; |
4663 | +} |
4664 | + |
4665 | +sub row_estimate { |
4666 | + my ($self) = @_; |
4667 | + return $self->{row_est}; |
4668 | +} |
4669 | + |
4670 | +sub _find_best_index { |
4671 | + my (%args) = @_; |
4672 | + my @required_args = qw(Cxn tbl TableParser); |
4673 | + my ($cxn, $tbl, $tp) = @args{@required_args}; |
4674 | + my $tbl_struct = $tbl->{tbl_struct}; |
4675 | + my $indexes = $tbl_struct->{keys}; |
4676 | + |
4677 | + my $want_index = $args{chunk_index}; |
4678 | + if ( $want_index ) { |
4679 | + MKDEBUG && _d('User wants to use index', $want_index); |
4680 | + if ( !exists $indexes->{$want_index} ) { |
4681 | + MKDEBUG && _d('Cannot use user index because it does not exist'); |
4682 | + $want_index = undef; |
4683 | + } |
4684 | + } |
4685 | + |
4686 | + if ( !$want_index && $args{mysql_index} ) { |
4687 | + MKDEBUG && _d('MySQL wants to use index', $args{mysql_index}); |
4688 | + $want_index = $args{mysql_index}; |
4689 | + } |
4690 | + |
4691 | + my $best_index; |
4692 | + my @possible_indexes; |
4693 | + if ( $want_index ) { |
4694 | + if ( $indexes->{$want_index}->{is_unique} ) { |
4695 | + MKDEBUG && _d('Will use wanted index'); |
4696 | + $best_index = $want_index; |
4697 | + } |
4698 | + else { |
4699 | + MKDEBUG && _d('Wanted index is a possible index'); |
4700 | + push @possible_indexes, $want_index; |
4701 | + } |
4702 | + } |
4703 | + else { |
4704 | + MKDEBUG && _d('Auto-selecting best index'); |
4705 | + foreach my $index ( $tp->sort_indexes($tbl_struct) ) { |
4706 | + if ( $index eq 'PRIMARY' || $indexes->{$index}->{is_unique} ) { |
4707 | + $best_index = $index; |
4708 | + last; |
4709 | + } |
4710 | + else { |
4711 | + push @possible_indexes, $index; |
4712 | + } |
4713 | + } |
4714 | + } |
4715 | + |
4716 | + if ( !$best_index && @possible_indexes ) { |
4717 | + MKDEBUG && _d('No PRIMARY or unique indexes;', |
4718 | + 'will use index with highest cardinality'); |
4719 | + foreach my $index ( @possible_indexes ) { |
4720 | + $indexes->{$index}->{cardinality} = _get_index_cardinality( |
4721 | + %args, |
4722 | + index => $index, |
4723 | + ); |
4724 | + } |
4725 | + @possible_indexes = sort { |
4726 | + my $cmp |
4727 | + = $indexes->{$b}->{cardinality} <=> $indexes->{$b}->{cardinality}; |
4728 | + if ( $cmp == 0 ) { |
4729 | + $cmp = scalar @{$indexes->{$b}->{cols}} |
4730 | + <=> scalar @{$indexes->{$a}->{cols}}; |
4731 | + } |
4732 | + $cmp; |
4733 | + } @possible_indexes; |
4734 | + $best_index = $possible_indexes[0]; |
4735 | + } |
4736 | + |
4737 | + MKDEBUG && _d('Best index:', $best_index); |
4738 | + return $best_index; |
4739 | +} |
4740 | + |
4741 | +sub _get_index_cardinality { |
4742 | + my (%args) = @_; |
4743 | + my @required_args = qw(Cxn tbl index Quoter); |
4744 | + my ($cxn, $tbl, $index, $q) = @args{@required_args}; |
4745 | + |
4746 | + my $sql = "SHOW INDEXES FROM " . $q->quote(@{$tbl}{qw(db tbl)}) |
4747 | + . " WHERE Key_name = '$index'"; |
4748 | + MKDEBUG && _d($sql); |
4749 | + my $cardinality = 1; |
4750 | + my $rows = $cxn->dbh()->selectall_hashref($sql, 'key_name'); |
4751 | + foreach my $row ( values %$rows ) { |
4752 | + $cardinality *= $row->{cardinality} if $row->{cardinality}; |
4753 | + } |
4754 | + MKDEBUG && _d('Index', $index, 'cardinality:', $cardinality); |
4755 | + return $cardinality; |
4756 | +} |
4757 | + |
4758 | +sub get_row_estimate { |
4759 | + my (%args) = @_; |
4760 | + my @required_args = qw(Cxn tbl OptionParser TableParser Quoter); |
4761 | + my ($cxn, $tbl, $o, $tp, $q) = @args{@required_args}; |
4762 | + |
4763 | + if ( $args{where} ) { |
4764 | + MKDEBUG && _d('WHERE clause, using explain plan for row estimate'); |
4765 | + my $table = $q->quote(@{$tbl}{qw(db tbl)}); |
4766 | + my $sql = "EXPLAIN SELECT * FROM $table WHERE $args{where}"; |
4767 | + MKDEBUG && _d($sql); |
4768 | + my $expl = $cxn->dbh()->selectrow_hashref($sql); |
4769 | + MKDEBUG && _d(Dumper($expl)); |
4770 | + return ($expl->{rows} || 0), $expl->{key}; |
4771 | + } |
4772 | + else { |
4773 | + MKDEBUG && _d('No WHERE clause, using table status for row estimate'); |
4774 | + return $tbl->{tbl_status}->{rows} || 0; |
4775 | + } |
4776 | +} |
4777 | + |
4778 | +sub _prepare_sths { |
4779 | + my ($self) = @_; |
4780 | + MKDEBUG && _d('Preparing statement handles'); |
4781 | + |
4782 | + my $dbh = $self->{Cxn}->dbh(); |
4783 | + |
4784 | + $self->{nibble_sth} = $dbh->prepare($self->{nibble_sql}); |
4785 | + $self->{explain_nibble_sth} = $dbh->prepare($self->{explain_nibble_sql}); |
4786 | + |
4787 | + if ( !$self->{one_nibble} ) { |
4788 | + $self->{ub_sth} = $dbh->prepare($self->{ub_sql}); |
4789 | + $self->{explain_ub_sth} = $dbh->prepare($self->{explain_ub_sql}); |
4790 | + } |
4791 | + |
4792 | + return; |
4793 | +} |
4794 | + |
4795 | +sub _get_bounds { |
4796 | + my ($self) = @_; |
4797 | + |
4798 | + if ( $self->{one_nibble} ) { |
4799 | + if ( $self->{resume} ) { |
4800 | + $self->{no_more_boundaries} = 1; |
4801 | + } |
4802 | + return; |
4803 | + } |
4804 | + |
4805 | + my $dbh = $self->{Cxn}->dbh(); |
4806 | + |
4807 | + $self->{first_lower} = $dbh->selectrow_arrayref($self->{first_lb_sql}); |
4808 | + MKDEBUG && _d('First lower boundary:', Dumper($self->{first_lower})); |
4809 | + |
4810 | + if ( my $nibble = $self->{resume} ) { |
4811 | + if ( defined $nibble->{lower_boundary} |
4812 | + && defined $nibble->{upper_boundary} ) { |
4813 | + my $sth = $dbh->prepare($self->{resume_lb_sql}); |
4814 | + my @ub = split ',', $nibble->{upper_boundary}; |
4815 | + MKDEBUG && _d($sth->{Statement}, 'params:', @ub); |
4816 | + $sth->execute(@ub); |
4817 | + $self->{next_lower} = $sth->fetchrow_arrayref(); |
4818 | + $sth->finish(); |
4819 | + } |
4820 | + } |
4821 | + else { |
4822 | + $self->{next_lower} = $self->{first_lower}; |
4823 | + } |
4824 | + MKDEBUG && _d('Next lower boundary:', Dumper($self->{next_lower})); |
4825 | + |
4826 | + if ( !$self->{next_lower} ) { |
4827 | + MKDEBUG && _d('At end of table, or no more boundaries to resume'); |
4828 | + $self->{no_more_boundaries} = 1; |
4829 | + } |
4830 | + |
4831 | + $self->{last_upper} = $dbh->selectrow_arrayref($self->{last_ub_sql}); |
4832 | + MKDEBUG && _d('Last upper boundary:', Dumper($self->{last_upper})); |
4833 | + |
4834 | + return; |
4835 | +} |
4836 | + |
4837 | +sub _next_boundaries { |
4838 | + my ($self) = @_; |
4839 | + |
4840 | + if ( $self->{no_more_boundaries} ) { |
4841 | + MKDEBUG && _d('No more boundaries'); |
4842 | + return; # stop nibbling |
4843 | + } |
4844 | + |
4845 | + if ( $self->{one_nibble} ) { |
4846 | + $self->{lower} = $self->{upper} = []; |
4847 | + $self->{no_more_boundaries} = 1; # for next call |
4848 | + return 1; # continue nibbling |
4849 | + } |
4850 | + |
4851 | + if ( $self->identical_boundaries($self->{lower}, $self->{next_lower}) ) { |
4852 | + MKDEBUG && _d('Infinite loop detected'); |
4853 | + my $tbl = $self->{tbl}; |
4854 | + my $index = $tbl->{tbl_struct}->{keys}->{$self->{index}}; |
4855 | + my $n_cols = scalar @{$index->{cols}}; |
4856 | + my $chunkno = $self->{nibbleno}; |
4857 | + die "Possible infinite loop detected! " |
4858 | + . "The lower boundary for chunk $chunkno is " |
4859 | + . "<" . join(', ', @{$self->{lower}}) . "> and the lower " |
4860 | + . "boundary for chunk " . ($chunkno + 1) . " is also " |
4861 | + . "<" . join(', ', @{$self->{next_lower}}) . ">. " |
4862 | + . "This usually happens when using a non-unique single " |
4863 | + . "column index. The current chunk index for table " |
4864 | + . "$tbl->{db}.$tbl->{tbl} is $self->{index} which is" |
4865 | + . ($index->{is_unique} ? '' : ' not') . " unique and covers " |
4866 | + . ($n_cols > 1 ? "$n_cols columns" : "1 column") . ".\n"; |
4867 | + } |
4868 | + $self->{lower} = $self->{next_lower}; |
4869 | + |
4870 | + if ( my $callback = $self->{callbacks}->{next_boundaries} ) { |
4871 | + my $oktonibble = $callback->( |
4872 | + Cxn => $self->{Cxn}, |
4873 | + tbl => $self->{tbl}, |
4874 | + NibbleIterator => $self, |
4875 | + ); |
4876 | + MKDEBUG && _d('next_boundaries callback returned', $oktonibble); |
4877 | + if ( !$oktonibble ) { |
4878 | + $self->{no_more_boundaries} = 1; |
4879 | + return; # stop nibbling |
4880 | + } |
4881 | + } |
4882 | + |
4883 | + MKDEBUG && _d($self->{ub_sth}->{Statement}, 'params:', |
4884 | + join(', ', @{$self->{lower}}), $self->{limit}); |
4885 | + $self->{ub_sth}->execute(@{$self->{lower}}, $self->{limit}); |
4886 | + my $boundary = $self->{ub_sth}->fetchall_arrayref(); |
4887 | + MKDEBUG && _d('Next boundary:', Dumper($boundary)); |
4888 | + if ( $boundary && @$boundary ) { |
4889 | + $self->{upper} = $boundary->[0]; # this nibble |
4890 | + if ( $boundary->[1] ) { |
4891 | + $self->{next_lower} = $boundary->[1]; # next nibble |
4892 | + } |
4893 | + else { |
4894 | + $self->{no_more_boundaries} = 1; # for next call |
4895 | + MKDEBUG && _d('Last upper boundary:', Dumper($boundary->[0])); |
4896 | + } |
4897 | + } |
4898 | + else { |
4899 | + $self->{no_more_boundaries} = 1; # for next call |
4900 | + $self->{upper} = $self->{last_upper}; |
4901 | + MKDEBUG && _d('Last upper boundary:', Dumper($self->{upper})); |
4902 | + } |
4903 | + $self->{ub_sth}->finish(); |
4904 | + |
4905 | + return 1; # continue nibbling |
4906 | +} |
4907 | + |
4908 | +sub identical_boundaries { |
4909 | + my ($self, $b1, $b2) = @_; |
4910 | + |
4911 | + return 0 if ($b1 && !$b2) || (!$b1 && $b2); |
4912 | + |
4913 | + return 1 if !$b1 && !$b2; |
4914 | + |
4915 | + die "Boundaries have different numbers of values" |
4916 | + if scalar @$b1 != scalar @$b2; # shouldn't happen |
4917 | + my $n_vals = scalar @$b1; |
4918 | + for my $i ( 0..($n_vals-1) ) { |
4919 | + return 0 if $b1->[$i] ne $b2->[$i]; # diff |
4920 | + } |
4921 | + return 1; |
4922 | +} |
4923 | + |
4924 | +sub DESTROY { |
4925 | + my ( $self ) = @_; |
4926 | + foreach my $key ( keys %$self ) { |
4927 | + if ( $key =~ m/_sth$/ ) { |
4928 | + MKDEBUG && _d('Finish', $key); |
4929 | + $self->{$key}->finish(); |
4930 | + } |
4931 | + } |
4932 | + return; |
4933 | +} |
4934 | + |
4935 | +sub _d { |
4936 | + my ($package, undef, $line) = caller 0; |
4937 | + @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; } |
4938 | + map { defined $_ ? $_ : 'undef' } |
4939 | + @_; |
4940 | + print STDERR "# $package:$line $PID ", join(' ', @_), "\n"; |
4941 | +} |
4942 | + |
4943 | +1; |
4944 | +} |
4945 | +# ########################################################################### |
4946 | +# End NibbleIterator package |
4947 | +# ########################################################################### |
4948 | + |
4949 | +# ########################################################################### |
4950 | +# OobNibbleIterator package |
4951 | +# This package is a copy without comments from the original. The original |
4952 | +# with comments and its test file can be found in the Bazaar repository at, |
4953 | +# lib/OobNibbleIterator.pm |
4954 | +# t/lib/OobNibbleIterator.t |
4955 | +# See https://launchpad.net/percona-toolkit for more information. |
4956 | +# ########################################################################### |
4957 | +{ |
4958 | +package OobNibbleIterator; |
4959 | +use base 'NibbleIterator'; |
4960 | + |
4961 | +use strict; |
4962 | +use warnings FATAL => 'all'; |
4963 | +use English qw(-no_match_vars); |
4964 | +use constant MKDEBUG => $ENV{MKDEBUG} || 0; |
4965 | + |
4966 | +use Data::Dumper; |
4967 | +$Data::Dumper::Indent = 1; |
4968 | +$Data::Dumper::Sortkeys = 1; |
4969 | +$Data::Dumper::Quotekeys = 0; |
4970 | + |
4971 | +sub new { |
4972 | + my ( $class, %args ) = @_; |
4973 | + my @required_args = qw(); |
4974 | + foreach my $arg ( @required_args ) { |
4975 | + die "I need a $arg argument" unless $args{$arg}; |
4976 | + } |
4977 | + |
4978 | + my $self = $class->SUPER::new(%args); |
4979 | + |
4980 | + my $q = $self->{Quoter}; |
4981 | + my $o = $self->{OptionParser}; |
4982 | + my $where = $o->get('where'); |
4983 | + |
4984 | + if ( !$self->one_nibble() ) { |
4985 | + my $head_sql |
4986 | + = ($args{past_dml} || "SELECT ") |
4987 | + . ($args{past_select} |
4988 | + || join(', ', map { $q->quote($_) } @{$self->{sql}->{columns}})) |
4989 | + . " FROM " . $self->{sql}->{from}; |
4990 | + |
4991 | + my $tail_sql |
4992 | + = ($where ? " AND ($where)" : '') |
4993 | + . " ORDER BY " . $self->{sql}->{order_by}; |
4994 | + |
4995 | + my $past_lower_sql |
4996 | + = $head_sql |
4997 | + . " WHERE " . $self->{sql}->{boundaries}->{'<'} |
4998 | + . $tail_sql |
4999 | + . " /*past lower chunk*/"; |
5000 | + MKDEBUG && _d('Past lower statement:', $past_lower_sql); |
The diff has been truncated for viewing.