Merge lp:~percona-toolkit-dev/percona-toolkit/pt-osc-2.1.1 into lp:percona-toolkit/2.1

Proposed by Daniel Nichter on 2012-04-03
Status: Merged
Merged at revision: 228
Proposed branch: lp:~percona-toolkit-dev/percona-toolkit/pt-osc-2.1.1
Merge into: lp:percona-toolkit/2.1
Diff against target: 9987 lines (+6370/-2594) (has conflicts)
24 files modified
bin/pt-online-schema-change (+4615/-1243)
bin/pt-table-checksum (+114/-37)
lib/CleanupTask.pm (+7/-2)
lib/MySQLStatusWaiter.pm (+34/-12)
lib/NibbleIterator.pm (+165/-49)
lib/OSCCaptureSync.pm (+0/-142)
lib/OobNibbleIterator.pm (+1/-1)
lib/SchemaIterator.pm (+12/-0)
t/lib/CleanupTask.t (+18/-1)
t/lib/MySQLStatusWaiter.t (+38/-4)
t/lib/NibbleIterator.t (+42/-4)
t/lib/OSCCaptureSync.t (+0/-131)
t/lib/OobNibbleIterator.t (+8/-3)
t/pt-online-schema-change/alter_active_table.t (+56/-46)
t/pt-online-schema-change/basics.t (+541/-218)
t/pt-online-schema-change/check_tables.t (+0/-126)
t/pt-online-schema-change/option_sanity.t (+12/-12)
t/pt-online-schema-change/samples/basic_no_fks.data (+500/-500)
t/pt-online-schema-change/samples/basic_no_fks.sql (+30/-0)
t/pt-online-schema-change/samples/basic_with_fks.sql (+56/-0)
t/pt-online-schema-change/samples/fk_tables_schema.sql (+0/-31)
t/pt-online-schema-change/samples/query_table.pl (+8/-5)
t/pt-online-schema-change/samples/small_table.sql (+0/-27)
t/pt-online-schema-change/sanity_checks.t (+113/-0)
Text conflict in bin/pt-online-schema-change
Text conflict in bin/pt-table-checksum
Text conflict in lib/NibbleIterator.pm
Text conflict in lib/SchemaIterator.pm
To merge this branch: bzr merge lp:~percona-toolkit-dev/percona-toolkit/pt-osc-2.1.1
Reviewer Review Type Date Requested Status
Baron Schwartz (community) 2012-04-03 Approve on 2012-04-03
Daniel Nichter Approve on 2012-04-03
Brian Fraser 2012-04-03 Pending
Review via email: mp+100540@code.launchpad.net
To post a comment you must log in.
review: Approve
review: Approve
251. By Daniel Nichter on 2012-04-03

Add comment about code that will fail with new Cxn due to NAME_lc.

252. By Daniel Nichter on 2012-04-03

Catch lost cleanup task (should rarely happen).

253. By Daniel Nichter on 2012-04-03

Handle Key_name or key_name in NibbleIterator::_get_index_cardinality().

254. By Daniel Nichter on 2012-04-03

Add XXX comment about improperly copying .

255. By Daniel Nichter on 2012-04-03

Uncomment cleanup lines in alter_active_table.t.

256. By Daniel Nichter on 2012-04-03

Update modules in pt-online-schema-change.

257. By Daniel Nichter on 2012-04-03

Fix typo.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'bin/pt-online-schema-change'
2--- bin/pt-online-schema-change 2012-03-30 16:37:16 +0000
3+++ bin/pt-online-schema-change 2012-04-03 02:13:24 +0000
4@@ -1784,245 +1784,246 @@
5 # ###########################################################################
6
7 # ###########################################################################
8-# Transformers package
9+# TableNibbler package
10 # This package is a copy without comments from the original. The original
11 # with comments and its test file can be found in the Bazaar repository at,
12-# lib/Transformers.pm
13-# t/lib/Transformers.t
14+# lib/TableNibbler.pm
15+# t/lib/TableNibbler.t
16 # See https://launchpad.net/percona-toolkit for more information.
17 # ###########################################################################
18 {
19-package Transformers;
20+package TableNibbler;
21
22 use strict;
23 use warnings FATAL => 'all';
24 use English qw(-no_match_vars);
25 use constant PTDEBUG => $ENV{PTDEBUG} || 0;
26
27-use Time::Local qw(timegm timelocal);
28-use Digest::MD5 qw(md5_hex);
29-
30-require Exporter;
31-our @ISA = qw(Exporter);
32-our %EXPORT_TAGS = ();
33-our @EXPORT = ();
34-our @EXPORT_OK = qw(
35- micro_t
36- percentage_of
37- secs_to_time
38- time_to_secs
39- shorten
40- ts
41- parse_timestamp
42- unix_timestamp
43- any_unix_timestamp
44- make_checksum
45- crc32
46-);
47-
48-our $mysql_ts = qr/(\d\d)(\d\d)(\d\d) +(\d+):(\d+):(\d+)(\.\d+)?/;
49-our $proper_ts = qr/(\d\d\d\d)-(\d\d)-(\d\d)[T ](\d\d):(\d\d):(\d\d)(\.\d+)?/;
50-our $n_ts = qr/(\d{1,5})([shmd]?)/; # Limit \d{1,5} because \d{6} looks
51-
52-sub micro_t {
53- my ( $t, %args ) = @_;
54- my $p_ms = defined $args{p_ms} ? $args{p_ms} : 0; # precision for ms vals
55- my $p_s = defined $args{p_s} ? $args{p_s} : 0; # precision for s vals
56- my $f;
57-
58- $t = 0 if $t < 0;
59-
60- $t = sprintf('%.17f', $t) if $t =~ /e/;
61-
62- $t =~ s/\.(\d{1,6})\d*/\.$1/;
63-
64- if ($t > 0 && $t <= 0.000999) {
65- $f = ($t * 1000000) . 'us';
66- }
67- elsif ($t >= 0.001000 && $t <= 0.999999) {
68- $f = sprintf("%.${p_ms}f", $t * 1000);
69- $f = ($f * 1) . 'ms'; # * 1 to remove insignificant zeros
70- }
71- elsif ($t >= 1) {
72- $f = sprintf("%.${p_s}f", $t);
73- $f = ($f * 1) . 's'; # * 1 to remove insignificant zeros
74- }
75- else {
76- $f = 0; # $t should = 0 at this point
77- }
78-
79- return $f;
80-}
81-
82-sub percentage_of {
83- my ( $is, $of, %args ) = @_;
84- my $p = $args{p} || 0; # float precision
85- my $fmt = $p ? "%.${p}f" : "%d";
86- return sprintf $fmt, ($is * 100) / ($of ||= 1);
87-}
88-
89-sub secs_to_time {
90- my ( $secs, $fmt ) = @_;
91- $secs ||= 0;
92- return '00:00' unless $secs;
93-
94- $fmt ||= $secs >= 86_400 ? 'd'
95- : $secs >= 3_600 ? 'h'
96- : 'm';
97-
98- return
99- $fmt eq 'd' ? sprintf(
100- "%d+%02d:%02d:%02d",
101- int($secs / 86_400),
102- int(($secs % 86_400) / 3_600),
103- int(($secs % 3_600) / 60),
104- $secs % 60)
105- : $fmt eq 'h' ? sprintf(
106- "%02d:%02d:%02d",
107- int(($secs % 86_400) / 3_600),
108- int(($secs % 3_600) / 60),
109- $secs % 60)
110- : sprintf(
111- "%02d:%02d",
112- int(($secs % 3_600) / 60),
113- $secs % 60);
114-}
115-
116-sub time_to_secs {
117- my ( $val, $default_suffix ) = @_;
118- die "I need a val argument" unless defined $val;
119- my $t = 0;
120- my ( $prefix, $num, $suffix ) = $val =~ m/([+-]?)(\d+)([a-z])?$/;
121- $suffix = $suffix || $default_suffix || 's';
122- if ( $suffix =~ m/[smhd]/ ) {
123- $t = $suffix eq 's' ? $num * 1 # Seconds
124- : $suffix eq 'm' ? $num * 60 # Minutes
125- : $suffix eq 'h' ? $num * 3600 # Hours
126- : $num * 86400; # Days
127-
128- $t *= -1 if $prefix && $prefix eq '-';
129- }
130- else {
131- die "Invalid suffix for $val: $suffix";
132- }
133- return $t;
134-}
135-
136-sub shorten {
137- my ( $num, %args ) = @_;
138- my $p = defined $args{p} ? $args{p} : 2; # float precision
139- my $d = defined $args{d} ? $args{d} : 1_024; # divisor
140- my $n = 0;
141- my @units = ('', qw(k M G T P E Z Y));
142- while ( $num >= $d && $n < @units - 1 ) {
143- $num /= $d;
144- ++$n;
145- }
146- return sprintf(
147- $num =~ m/\./ || $n
148- ? "%.${p}f%s"
149- : '%d',
150- $num, $units[$n]);
151-}
152-
153-sub ts {
154- my ( $time, $gmt ) = @_;
155- my ( $sec, $min, $hour, $mday, $mon, $year )
156- = $gmt ? gmtime($time) : localtime($time);
157- $mon += 1;
158- $year += 1900;
159- my $val = sprintf("%d-%02d-%02dT%02d:%02d:%02d",
160- $year, $mon, $mday, $hour, $min, $sec);
161- if ( my ($us) = $time =~ m/(\.\d+)$/ ) {
162- $us = sprintf("%.6f", $us);
163- $us =~ s/^0\././;
164- $val .= $us;
165- }
166- return $val;
167-}
168-
169-sub parse_timestamp {
170- my ( $val ) = @_;
171- if ( my($y, $m, $d, $h, $i, $s, $f)
172- = $val =~ m/^$mysql_ts$/ )
173- {
174- return sprintf "%d-%02d-%02d %02d:%02d:"
175- . (defined $f ? '%09.6f' : '%02d'),
176- $y + 2000, $m, $d, $h, $i, (defined $f ? $s + $f : $s);
177- }
178- return $val;
179-}
180-
181-sub unix_timestamp {
182- my ( $val, $gmt ) = @_;
183- if ( my($y, $m, $d, $h, $i, $s, $us) = $val =~ m/^$proper_ts$/ ) {
184- $val = $gmt
185- ? timegm($s, $i, $h, $d, $m - 1, $y)
186- : timelocal($s, $i, $h, $d, $m - 1, $y);
187- if ( defined $us ) {
188- $us = sprintf('%.6f', $us);
189- $us =~ s/^0\././;
190- $val .= $us;
191- }
192- }
193- return $val;
194-}
195-
196-sub any_unix_timestamp {
197- my ( $val, $callback ) = @_;
198-
199- if ( my ($n, $suffix) = $val =~ m/^$n_ts$/ ) {
200- $n = $suffix eq 's' ? $n # Seconds
201- : $suffix eq 'm' ? $n * 60 # Minutes
202- : $suffix eq 'h' ? $n * 3600 # Hours
203- : $suffix eq 'd' ? $n * 86400 # Days
204- : $n; # default: Seconds
205- PTDEBUG && _d('ts is now - N[shmd]:', $n);
206- return time - $n;
207- }
208- elsif ( $val =~ m/^\d{9,}/ ) {
209- PTDEBUG && _d('ts is already a unix timestamp');
210- return $val;
211- }
212- elsif ( my ($ymd, $hms) = $val =~ m/^(\d{6})(?:\s+(\d+:\d+:\d+))?/ ) {
213- PTDEBUG && _d('ts is MySQL slow log timestamp');
214- $val .= ' 00:00:00' unless $hms;
215- return unix_timestamp(parse_timestamp($val));
216- }
217- elsif ( ($ymd, $hms) = $val =~ m/^(\d{4}-\d\d-\d\d)(?:[T ](\d+:\d+:\d+))?/) {
218- PTDEBUG && _d('ts is properly formatted timestamp');
219- $val .= ' 00:00:00' unless $hms;
220- return unix_timestamp($val);
221- }
222- else {
223- PTDEBUG && _d('ts is MySQL expression');
224- return $callback->($val) if $callback && ref $callback eq 'CODE';
225- }
226-
227- PTDEBUG && _d('Unknown ts type:', $val);
228- return;
229-}
230-
231-sub make_checksum {
232- my ( $val ) = @_;
233- my $checksum = uc substr(md5_hex($val), -16);
234- PTDEBUG && _d($checksum, 'checksum for', $val);
235- return $checksum;
236-}
237-
238-sub crc32 {
239- my ( $string ) = @_;
240- return unless $string;
241- my $poly = 0xEDB88320;
242- my $crc = 0xFFFFFFFF;
243- foreach my $char ( split(//, $string) ) {
244- my $comp = ($crc ^ ord($char)) & 0xFF;
245- for ( 1 .. 8 ) {
246- $comp = $comp & 1 ? $poly ^ ($comp >> 1) : $comp >> 1;
247- }
248- $crc = (($crc >> 8) & 0x00FFFFFF) ^ $comp;
249- }
250- return $crc ^ 0xFFFFFFFF;
251+sub new {
252+ my ( $class, %args ) = @_;
253+ my @required_args = qw(TableParser Quoter);
254+ foreach my $arg ( @required_args ) {
255+ die "I need a $arg argument" unless $args{$arg};
256+ }
257+ my $self = { %args };
258+ return bless $self, $class;
259+}
260+
261+sub generate_asc_stmt {
262+ my ( $self, %args ) = @_;
263+ my @required_args = qw(tbl_struct index);
264+ foreach my $arg ( @required_args ) {
265+ die "I need a $arg argument" unless defined $args{$arg};
266+ }
267+ my ($tbl_struct, $index) = @args{@required_args};
268+ my @cols = $args{cols} ? @{$args{cols}} : @{$tbl_struct->{cols}};
269+ my $q = $self->{Quoter};
270+
271+ die "Index '$index' does not exist in table"
272+ unless exists $tbl_struct->{keys}->{$index};
273+ PTDEBUG && _d('Will ascend index', $index);
274+
275+ my @asc_cols = @{$tbl_struct->{keys}->{$index}->{cols}};
276+ if ( $args{asc_first} ) {
277+ @asc_cols = $asc_cols[0];
278+ PTDEBUG && _d('Ascending only first column');
279+ }
280+ PTDEBUG && _d('Will ascend columns', join(', ', @asc_cols));
281+
282+ my @asc_slice;
283+ my %col_posn = do { my $i = 0; map { $_ => $i++ } @cols };
284+ foreach my $col ( @asc_cols ) {
285+ if ( !exists $col_posn{$col} ) {
286+ push @cols, $col;
287+ $col_posn{$col} = $#cols;
288+ }
289+ push @asc_slice, $col_posn{$col};
290+ }
291+ PTDEBUG && _d('Will ascend, in ordinal position:', join(', ', @asc_slice));
292+
293+ my $asc_stmt = {
294+ cols => \@cols,
295+ index => $index,
296+ where => '',
297+ slice => [],
298+ scols => [],
299+ };
300+
301+ if ( @asc_slice ) {
302+ my $cmp_where;
303+ foreach my $cmp ( qw(< <= >= >) ) {
304+ $cmp_where = $self->generate_cmp_where(
305+ type => $cmp,
306+ slice => \@asc_slice,
307+ cols => \@cols,
308+ quoter => $q,
309+ is_nullable => $tbl_struct->{is_nullable},
310+ );
311+ $asc_stmt->{boundaries}->{$cmp} = $cmp_where->{where};
312+ }
313+ my $cmp = $args{asc_only} ? '>' : '>=';
314+ $asc_stmt->{where} = $asc_stmt->{boundaries}->{$cmp};
315+ $asc_stmt->{slice} = $cmp_where->{slice};
316+ $asc_stmt->{scols} = $cmp_where->{scols};
317+ }
318+
319+ return $asc_stmt;
320+}
321+
322+sub generate_cmp_where {
323+ my ( $self, %args ) = @_;
324+ foreach my $arg ( qw(type slice cols is_nullable) ) {
325+ die "I need a $arg arg" unless defined $args{$arg};
326+ }
327+ my @slice = @{$args{slice}};
328+ my @cols = @{$args{cols}};
329+ my $is_nullable = $args{is_nullable};
330+ my $type = $args{type};
331+ my $q = $self->{Quoter};
332+
333+ (my $cmp = $type) =~ s/=//;
334+
335+ my @r_slice; # Resulting slice columns, by ordinal
336+ my @r_scols; # Ditto, by name
337+
338+ my @clauses;
339+ foreach my $i ( 0 .. $#slice ) {
340+ my @clause;
341+
342+ foreach my $j ( 0 .. $i - 1 ) {
343+ my $ord = $slice[$j];
344+ my $col = $cols[$ord];
345+ my $quo = $q->quote($col);
346+ if ( $is_nullable->{$col} ) {
347+ push @clause, "((? IS NULL AND $quo IS NULL) OR ($quo = ?))";
348+ push @r_slice, $ord, $ord;
349+ push @r_scols, $col, $col;
350+ }
351+ else {
352+ push @clause, "$quo = ?";
353+ push @r_slice, $ord;
354+ push @r_scols, $col;
355+ }
356+ }
357+
358+ my $ord = $slice[$i];
359+ my $col = $cols[$ord];
360+ my $quo = $q->quote($col);
361+ my $end = $i == $#slice; # Last clause of the whole group.
362+ if ( $is_nullable->{$col} ) {
363+ if ( $type =~ m/=/ && $end ) {
364+ push @clause, "(? IS NULL OR $quo $type ?)";
365+ }
366+ elsif ( $type =~ m/>/ ) {
367+ push @clause, "((? IS NULL AND $quo IS NOT NULL) OR ($quo $cmp ?))";
368+ }
369+ else { # If $type =~ m/</ ) {
370+ push @clause, "((? IS NOT NULL AND $quo IS NULL) OR ($quo $cmp ?))";
371+ }
372+ push @r_slice, $ord, $ord;
373+ push @r_scols, $col, $col;
374+ }
375+ else {
376+ push @r_slice, $ord;
377+ push @r_scols, $col;
378+ push @clause, ($type =~ m/=/ && $end ? "$quo $type ?" : "$quo $cmp ?");
379+ }
380+
381+ push @clauses, '(' . join(' AND ', @clause) . ')';
382+ }
383+ my $result = '(' . join(' OR ', @clauses) . ')';
384+ my $where = {
385+ slice => \@r_slice,
386+ scols => \@r_scols,
387+ where => $result,
388+ };
389+ return $where;
390+}
391+
392+sub generate_del_stmt {
393+ my ( $self, %args ) = @_;
394+
395+ my $tbl = $args{tbl_struct};
396+ my @cols = $args{cols} ? @{$args{cols}} : ();
397+ my $tp = $self->{TableParser};
398+ my $q = $self->{Quoter};
399+
400+ my @del_cols;
401+ my @del_slice;
402+
403+ my $index = $tp->find_best_index($tbl, $args{index});
404+ die "Cannot find an ascendable index in table" unless $index;
405+
406+ if ( $index ) {
407+ @del_cols = @{$tbl->{keys}->{$index}->{cols}};
408+ }
409+ else {
410+ @del_cols = @{$tbl->{cols}};
411+ }
412+ PTDEBUG && _d('Columns needed for DELETE:', join(', ', @del_cols));
413+
414+ my %col_posn = do { my $i = 0; map { $_ => $i++ } @cols };
415+ foreach my $col ( @del_cols ) {
416+ if ( !exists $col_posn{$col} ) {
417+ push @cols, $col;
418+ $col_posn{$col} = $#cols;
419+ }
420+ push @del_slice, $col_posn{$col};
421+ }
422+ PTDEBUG && _d('Ordinals needed for DELETE:', join(', ', @del_slice));
423+
424+ my $del_stmt = {
425+ cols => \@cols,
426+ index => $index,
427+ where => '',
428+ slice => [],
429+ scols => [],
430+ };
431+
432+ my @clauses;
433+ foreach my $i ( 0 .. $#del_slice ) {
434+ my $ord = $del_slice[$i];
435+ my $col = $cols[$ord];
436+ my $quo = $q->quote($col);
437+ if ( $tbl->{is_nullable}->{$col} ) {
438+ push @clauses, "((? IS NULL AND $quo IS NULL) OR ($quo = ?))";
439+ push @{$del_stmt->{slice}}, $ord, $ord;
440+ push @{$del_stmt->{scols}}, $col, $col;
441+ }
442+ else {
443+ push @clauses, "$quo = ?";
444+ push @{$del_stmt->{slice}}, $ord;
445+ push @{$del_stmt->{scols}}, $col;
446+ }
447+ }
448+
449+ $del_stmt->{where} = '(' . join(' AND ', @clauses) . ')';
450+
451+ return $del_stmt;
452+}
453+
454+sub generate_ins_stmt {
455+ my ( $self, %args ) = @_;
456+ foreach my $arg ( qw(ins_tbl sel_cols) ) {
457+ die "I need a $arg argument" unless $args{$arg};
458+ }
459+ my $ins_tbl = $args{ins_tbl};
460+ my @sel_cols = @{$args{sel_cols}};
461+
462+ die "You didn't specify any SELECT columns" unless @sel_cols;
463+
464+ my @ins_cols;
465+ my @ins_slice;
466+ for my $i ( 0..$#sel_cols ) {
467+ next unless $ins_tbl->{is_col}->{$sel_cols[$i]};
468+ push @ins_cols, $sel_cols[$i];
469+ push @ins_slice, $i;
470+ }
471+
472+ return {
473+ cols => \@ins_cols,
474+ slice => \@ins_slice,
475+ };
476 }
477
478 sub _d {
479@@ -2036,7 +2037,7 @@
480 1;
481 }
482 # ###########################################################################
483-# End Transformers package
484+# End TableNibbler package
485 # ###########################################################################
486
487 # ###########################################################################
488@@ -2077,41 +2078,43 @@
489 die "I need a tbl parameter" unless $tbl;
490 my $q = $self->{Quoter};
491
492- my $sql = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
493- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
494- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
495- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
496- PTDEBUG && _d($sql);
497- eval { $dbh->do($sql); };
498+ my $new_sql_mode
499+ = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
500+ . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
501+ . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
502+ . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
503+
504+ my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
505+ . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
506+
507+ PTDEBUG && _d($new_sql_mode);
508+ eval { $dbh->do($new_sql_mode); };
509 PTDEBUG && $EVAL_ERROR && _d($EVAL_ERROR);
510
511- $sql = 'USE ' . $q->quote($db);
512- PTDEBUG && _d($dbh, $sql);
513- $dbh->do($sql);
514+ my $use_sql = 'USE ' . $q->quote($db);
515+ PTDEBUG && _d($dbh, $use_sql);
516+ $dbh->do($use_sql);
517
518- $sql = "SHOW CREATE TABLE " . $q->quote($db, $tbl);
519- PTDEBUG && _d($sql);
520+ my $show_sql = "SHOW CREATE TABLE " . $q->quote($db, $tbl);
521+ PTDEBUG && _d($show_sql);
522 my $href;
523- eval { $href = $dbh->selectrow_hashref($sql); };
524+ eval { $href = $dbh->selectrow_hashref($show_sql); };
525 if ( $EVAL_ERROR ) {
526 PTDEBUG && _d($EVAL_ERROR);
527+
528+ PTDEBUG && _d($old_sql_mode);
529+ $dbh->do($old_sql_mode);
530+
531 return;
532 }
533
534- $sql = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
535- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
536- PTDEBUG && _d($sql);
537- $dbh->do($sql);
538+ PTDEBUG && _d($old_sql_mode);
539+ $dbh->do($old_sql_mode);
540
541- my ($key) = grep { m/create table/i } keys %$href;
542- if ( $key ) {
543- PTDEBUG && _d('This table is a base table');
544- $href->{$key} =~ s/\b[ ]{2,}/ /g;
545- $href->{$key} .= "\n";
546- }
547- else {
548- PTDEBUG && _d('This table is a view');
549- ($key) = grep { m/create view/i } keys %$href;
550+ my ($key) = grep { m/create (?:table|view)/i } keys %$href;
551+ if ( !$key ) {
552+ die "Error: no 'Create Table' or 'Create View' in result set from "
553+ . "$show_sql: " . Dumper($href);
554 }
555
556 return $href->{$key};
557@@ -2468,6 +2471,7 @@
558 # ###########################################################################
559
560 # ###########################################################################
561+<<<<<<< TREE
562 # TableChunker package
563 # This package is a copy without comments from the original. The original
564 # with comments and its test file can be found in the Bazaar repository at,
565@@ -3399,6 +3403,8 @@
566 # ###########################################################################
567
568 # ###########################################################################
569+=======
570+>>>>>>> MERGE-SOURCE
571 # Progress package
572 # This package is a copy without comments from the original. The original
573 # with comments and its test file can be found in the Bazaar repository at,
574@@ -3547,241 +3553,6 @@
575 # ###########################################################################
576
577 # ###########################################################################
578-# OSCCaptureSync package
579-# This package is a copy without comments from the original. The original
580-# with comments and its test file can be found in the Bazaar repository at,
581-# lib/OSCCaptureSync.pm
582-# t/lib/OSCCaptureSync.t
583-# See https://launchpad.net/percona-toolkit for more information.
584-# ###########################################################################
585-{
586-package OSCCaptureSync;
587-
588-use strict;
589-use warnings FATAL => 'all';
590-use English qw(-no_match_vars);
591-use constant PTDEBUG => $ENV{PTDEBUG} || 0;
592-
593-sub new {
594- my ( $class, %args ) = @_;
595- my @required_args = qw(Quoter);
596- foreach my $arg ( @required_args ) {
597- die "I need a $arg argument" unless $args{$arg};
598- }
599-
600- my $self = {
601- Quoter => $args{Quoter},
602- };
603-
604- return bless $self, $class;
605-}
606-
607-sub capture {
608- my ( $self, %args ) = @_;
609- my @required_args = qw(msg dbh db tbl tmp_tbl columns chunk_column);
610- foreach my $arg ( @required_args ) {
611- die "I need a $arg argument" unless $args{$arg};
612- }
613- my ($msg, $dbh) = @args{@required_args};
614-
615- my @triggers = $self->_make_triggers(%args);
616- foreach my $sql ( @triggers ) {
617- $msg->($sql);
618- $dbh->do($sql) unless $args{print};
619- }
620-
621- return;
622-}
623-
624-sub _make_triggers {
625- my ( $self, %args ) = @_;
626- my @required_args = qw(db tbl tmp_tbl chunk_column columns);
627- foreach my $arg ( @required_args ) {
628- die "I need a $arg argument" unless $args{$arg};
629- }
630- my ($db, $tbl, $tmp_tbl, $chunk_column) = @args{@required_args};
631- my $q = $self->{Quoter};
632-
633- $chunk_column = $q->quote($chunk_column);
634-
635- my $old_table = $q->quote($db, $tbl);
636- my $new_table = $q->quote($db, $tmp_tbl);
637- my $new_values = join(', ', map { "NEW.".$q->quote($_) } @{$args{columns}});
638- my $columns = join(', ', map { $q->quote($_) } @{$args{columns}});
639-
640- my $delete_trigger = "CREATE TRIGGER mk_osc_del AFTER DELETE ON $old_table "
641- . "FOR EACH ROW "
642- . "DELETE IGNORE FROM $new_table "
643- . "WHERE $new_table.$chunk_column = OLD.$chunk_column";
644-
645- my $insert_trigger = "CREATE TRIGGER mk_osc_ins AFTER INSERT ON $old_table "
646- . "FOR EACH ROW "
647- . "REPLACE INTO $new_table ($columns) "
648- . "VALUES($new_values)";
649-
650- my $update_trigger = "CREATE TRIGGER mk_osc_upd AFTER UPDATE ON $old_table "
651- . "FOR EACH ROW "
652- . "REPLACE INTO $new_table ($columns) "
653- . "VALUES ($new_values)";
654-
655- return $delete_trigger, $update_trigger, $insert_trigger;
656-}
657-
658-sub sync {
659- my ( $self, %args ) = @_;
660- my @required_args = qw();
661- foreach my $arg ( @required_args ) {
662- die "I need a $arg argument" unless $args{$arg};
663- }
664- return;
665-}
666-
667-sub cleanup {
668- my ( $self, %args ) = @_;
669- my @required_args = qw(dbh db msg);
670- foreach my $arg ( @required_args ) {
671- die "I need a $arg argument" unless $args{$arg};
672- }
673- my ($dbh, $db, $msg) = @args{@required_args};
674- my $q = $self->{Quoter};
675-
676- foreach my $trigger ( qw(del ins upd) ) {
677- my $sql = "DROP TRIGGER IF EXISTS " . $q->quote($db, "mk_osc_$trigger");
678- $msg->($sql);
679- $dbh->do($sql) unless $args{print};
680- }
681-
682- return;
683-}
684-
685-sub _d {
686- my ($package, undef, $line) = caller 0;
687- @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
688- map { defined $_ ? $_ : 'undef' }
689- @_;
690- print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
691-}
692-
693-1;
694-}
695-# ###########################################################################
696-# End OSCCaptureSync package
697-# ###########################################################################
698-
699-# ###########################################################################
700-# CopyRowsInsertSelect package
701-# This package is a copy without comments from the original. The original
702-# with comments and its test file can be found in the Bazaar repository at,
703-# lib/CopyRowsInsertSelect.pm
704-# t/lib/CopyRowsInsertSelect.t
705-# See https://launchpad.net/percona-toolkit for more information.
706-# ###########################################################################
707-{
708-package CopyRowsInsertSelect;
709-
710-use strict;
711-use warnings FATAL => 'all';
712-use English qw(-no_match_vars);
713-use constant PTDEBUG => $ENV{PTDEBUG} || 0;
714-
715-sub new {
716- my ( $class, %args ) = @_;
717- my @required_args = qw(Retry Quoter);
718- foreach my $arg ( @required_args ) {
719- die "I need a $arg argument" unless $args{$arg};
720- }
721-
722- my $self = {
723- Retry => $args{Retry},
724- Quoter => $args{Quoter},
725- };
726-
727- return bless $self, $class;
728-}
729-
730-sub copy {
731- my ( $self, %args ) = @_;
732- my @required_args = qw(dbh msg from_table to_table chunks columns);
733- foreach my $arg ( @required_args ) {
734- die "I need a $arg argument" unless $args{$arg};
735- }
736- my ($dbh, $msg, $from_table, $to_table, $chunks) = @args{@required_args};
737- my $q = $self->{Quoter};
738- my $pr = $args{Progress};
739- my $sleep = $args{sleep};
740- my $columns = join(', ', map { $q->quote($_) } @{$args{columns}});
741- my $n_chunks = @$chunks - 1;
742-
743- for my $chunkno ( 0..$n_chunks ) {
744- if ( !$chunks->[$chunkno] ) {
745- warn "Chunk number ", ($chunkno + 1), "is undefined";
746- next;
747- }
748-
749- my $sql = "INSERT IGNORE INTO $to_table ($columns) "
750- . "SELECT $columns FROM $from_table "
751- . "WHERE ($chunks->[$chunkno])"
752- . ($args{where} ? " AND ($args{where})" : "")
753- . ($args{engine_flags} ? " $args{engine_flags}" : "");
754-
755- if ( $args{print} ) {
756- $msg->($sql);
757- }
758- else {
759- PTDEBUG && _d($dbh, $sql);
760- my $error;
761- $self->{Retry}->retry(
762- wait => sub { sleep 1; },
763- tries => 3,
764- try => sub {
765- $dbh->do($sql);
766- return;
767- },
768- fail => sub {
769- my (%args) = @_;
770- my $error = $args{error};
771- PTDEBUG && _d($error);
772- if ( $error =~ m/Lock wait timeout exceeded/ ) {
773- $msg->("Lock wait timeout exceeded; retrying $sql");
774- return 1; # call wait, call try
775- }
776- return 0; # call final_fail
777- },
778- final_fail => sub {
779- my (%args) = @_;
780- die $args{error};
781- },
782- );
783- }
784-
785- $pr->update(sub { return $chunkno + 1; }) if $pr;
786-
787- $sleep->($chunkno + 1) if $sleep && $chunkno < $n_chunks;
788- }
789-
790- return;
791-}
792-
793-sub cleanup {
794- my ( $self, %args ) = @_;
795- return;
796-}
797-
798-sub _d {
799- my ($package, undef, $line) = caller 0;
800- @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
801- map { defined $_ ? $_ : 'undef' }
802- @_;
803- print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
804-}
805-
806-1;
807-}
808-# ###########################################################################
809-# End CopyRowsInsertSelect package
810-# ###########################################################################
811-
812-# ###########################################################################
813 # Retry package
814 # This package is a copy without comments from the original. The original
815 # with comments and its test file can be found in the Bazaar repository at,
816@@ -3860,6 +3631,2187 @@
817 # ###########################################################################
818
819 # ###########################################################################
820+# Cxn package
821+# This package is a copy without comments from the original. The original
822+# with comments and its test file can be found in the Bazaar repository at,
823+# lib/Cxn.pm
824+# t/lib/Cxn.t
825+# See https://launchpad.net/percona-toolkit for more information.
826+# ###########################################################################
827+{
828+package Cxn;
829+
830+use strict;
831+use warnings FATAL => 'all';
832+use English qw(-no_match_vars);
833+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
834+
835+use constant PERCONA_TOOLKIT_TEST_USE_DSN_NAMES => $ENV{PERCONA_TOOLKIT_TEST_USE_DSN_NAMES} || 0;
836+
837+sub new {
838+ my ( $class, %args ) = @_;
839+ my @required_args = qw(DSNParser OptionParser);
840+ foreach my $arg ( @required_args ) {
841+ die "I need a $arg argument" unless $args{$arg};
842+ };
843+ my ($dp, $o) = @args{@required_args};
844+
845+ my $dsn_defaults = $dp->parse_options($o);
846+ my $prev_dsn = $args{prev_dsn};
847+ my $dsn = $args{dsn};
848+ if ( !$dsn ) {
849+ $args{dsn_string} ||= 'h=' . ($dsn_defaults->{h} || 'localhost');
850+
851+ $dsn = $dp->parse(
852+ $args{dsn_string}, $prev_dsn, $dsn_defaults);
853+ }
854+ elsif ( $prev_dsn ) {
855+ $dsn = $dp->copy($prev_dsn, $dsn);
856+ }
857+
858+ my $self = {
859+ dsn => $dsn,
860+ dbh => $args{dbh},
861+ dsn_name => $dp->as_string($dsn, [qw(h P S)]),
862+ hostname => '',
863+ set => $args{set},
864+ dbh_set => 0,
865+ OptionParser => $o,
866+ DSNParser => $dp,
867+ };
868+
869+ return bless $self, $class;
870+}
871+
872+sub connect {
873+ my ( $self ) = @_;
874+ my $dsn = $self->{dsn};
875+ my $dp = $self->{DSNParser};
876+ my $o = $self->{OptionParser};
877+
878+ my $dbh = $self->{dbh};
879+ if ( !$dbh || !$dbh->ping() ) {
880+ if ( $o->get('ask-pass') && !$self->{asked_for_pass} ) {
881+ $dsn->{p} = OptionParser::prompt_noecho("Enter MySQL password: ");
882+ $self->{asked_for_pass} = 1;
883+ }
884+ $dbh = $dp->get_dbh($dp->get_cxn_params($dsn), { AutoCommit => 1 });
885+ }
886+ PTDEBUG && _d($dbh, 'Connected dbh to', $self->{name});
887+
888+ return $self->set_dbh($dbh);
889+}
890+
891+sub set_dbh {
892+ my ($self, $dbh) = @_;
893+
894+ if ( $self->{dbh} && $self->{dbh} == $dbh && $self->{dbh_set} ) {
895+ PTDEBUG && _d($dbh, 'Already set dbh');
896+ return $dbh;
897+ }
898+
899+ PTDEBUG && _d($dbh, 'Setting dbh');
900+
901+ $dbh->{FetchHashKeyName} = 'NAME_lc';
902+
903+ my $sql = 'SELECT @@hostname, @@server_id';
904+ PTDEBUG && _d($dbh, $sql);
905+ my ($hostname, $server_id) = $dbh->selectrow_array($sql);
906+ PTDEBUG && _d($dbh, 'hostname:', $hostname, $server_id);
907+ if ( $hostname ) {
908+ $self->{hostname} = $hostname;
909+ }
910+
911+ if ( my $set = $self->{set}) {
912+ $set->($dbh);
913+ }
914+
915+ $self->{dbh} = $dbh;
916+ $self->{dbh_set} = 1;
917+ return $dbh;
918+}
919+
920+sub dbh {
921+ my ($self) = @_;
922+ return $self->{dbh};
923+}
924+
925+sub dsn {
926+ my ($self) = @_;
927+ return $self->{dsn};
928+}
929+
930+sub name {
931+ my ($self) = @_;
932+ return $self->{dsn_name} if PERCONA_TOOLKIT_TEST_USE_DSN_NAMES;
933+ return $self->{hostname} || $self->{dsn_name} || 'unknown host';
934+}
935+
936+sub DESTROY {
937+ my ($self) = @_;
938+ if ( $self->{dbh} ) {
939+ PTDEBUG && _d('Disconnecting dbh', $self->{dbh}, $self->{name});
940+ $self->{dbh}->disconnect();
941+ }
942+ return;
943+}
944+
945+sub _d {
946+ my ($package, undef, $line) = caller 0;
947+ @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
948+ map { defined $_ ? $_ : 'undef' }
949+ @_;
950+ print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
951+}
952+
953+1;
954+}
955+# ###########################################################################
956+# End Cxn package
957+# ###########################################################################
958+
959+# ###########################################################################
960+# MasterSlave package
961+# This package is a copy without comments from the original. The original
962+# with comments and its test file can be found in the Bazaar repository at,
963+# lib/MasterSlave.pm
964+# t/lib/MasterSlave.t
965+# See https://launchpad.net/percona-toolkit for more information.
966+# ###########################################################################
967+{
968+package MasterSlave;
969+
970+use strict;
971+use warnings FATAL => 'all';
972+use English qw(-no_match_vars);
973+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
974+
975+sub new {
976+ my ( $class, %args ) = @_;
977+ my $self = {
978+ %args,
979+ replication_thread => {},
980+ };
981+ return bless $self, $class;
982+}
983+
984+sub get_slaves {
985+ my ($self, %args) = @_;
986+ my @required_args = qw(make_cxn OptionParser DSNParser Quoter);
987+ foreach my $arg ( @required_args ) {
988+ die "I need a $arg argument" unless $args{$arg};
989+ }
990+ my ($make_cxn, $o, $dp) = @args{@required_args};
991+
992+ my $slaves = [];
993+ my $method = $o->get('recursion-method');
994+ PTDEBUG && _d('Slave recursion method:', $method);
995+ if ( !$method || $method =~ m/processlist|hosts/i ) {
996+ my @required_args = qw(dbh dsn);
997+ foreach my $arg ( @required_args ) {
998+ die "I need a $arg argument" unless $args{$arg};
999+ }
1000+ my ($dbh, $dsn) = @args{@required_args};
1001+ $self->recurse_to_slaves(
1002+ { dbh => $dbh,
1003+ dsn => $dsn,
1004+ dsn_parser => $dp,
1005+ recurse => $o->get('recurse'),
1006+ method => $o->get('recursion-method'),
1007+ callback => sub {
1008+ my ( $dsn, $dbh, $level, $parent ) = @_;
1009+ return unless $level;
1010+ PTDEBUG && _d('Found slave:', $dp->as_string($dsn));
1011+ push @$slaves, $make_cxn->(dsn => $dsn, dbh => $dbh);
1012+ return;
1013+ },
1014+ }
1015+ );
1016+ }
1017+ elsif ( $method =~ m/^dsn=/i ) {
1018+ my ($dsn_table_dsn) = $method =~ m/^dsn=(.+)/i;
1019+ $slaves = $self->get_cxn_from_dsn_table(
1020+ %args,
1021+ dsn_table_dsn => $dsn_table_dsn,
1022+ );
1023+ }
1024+ else {
1025+ die "Invalid --recursion-method: $method. Valid values are: "
1026+ . "dsn=DSN, hosts, or processlist.\n";
1027+ }
1028+
1029+ return $slaves;
1030+}
1031+
1032+sub recurse_to_slaves {
1033+ my ( $self, $args, $level ) = @_;
1034+ $level ||= 0;
1035+ my $dp = $args->{dsn_parser};
1036+ my $dsn = $args->{dsn};
1037+
1038+ my $dbh;
1039+ eval {
1040+ $dbh = $args->{dbh} || $dp->get_dbh(
1041+ $dp->get_cxn_params($dsn), { AutoCommit => 1 });
1042+ PTDEBUG && _d('Connected to', $dp->as_string($dsn));
1043+ };
1044+ if ( $EVAL_ERROR ) {
1045+ print STDERR "Cannot connect to ", $dp->as_string($dsn), "\n"
1046+ or die "Cannot print: $OS_ERROR";
1047+ return;
1048+ }
1049+
1050+ my $sql = 'SELECT @@SERVER_ID';
1051+ PTDEBUG && _d($sql);
1052+ my ($id) = $dbh->selectrow_array($sql);
1053+ PTDEBUG && _d('Working on server ID', $id);
1054+ my $master_thinks_i_am = $dsn->{server_id};
1055+ if ( !defined $id
1056+ || ( defined $master_thinks_i_am && $master_thinks_i_am != $id )
1057+ || $args->{server_ids_seen}->{$id}++
1058+ ) {
1059+ PTDEBUG && _d('Server ID seen, or not what master said');
1060+ if ( $args->{skip_callback} ) {
1061+ $args->{skip_callback}->($dsn, $dbh, $level, $args->{parent});
1062+ }
1063+ return;
1064+ }
1065+
1066+ $args->{callback}->($dsn, $dbh, $level, $args->{parent});
1067+
1068+ if ( !defined $args->{recurse} || $level < $args->{recurse} ) {
1069+
1070+ my @slaves =
1071+ grep { !$_->{master_id} || $_->{master_id} == $id } # Only my slaves.
1072+ $self->find_slave_hosts($dp, $dbh, $dsn, $args->{method});
1073+
1074+ foreach my $slave ( @slaves ) {
1075+ PTDEBUG && _d('Recursing from',
1076+ $dp->as_string($dsn), 'to', $dp->as_string($slave));
1077+ $self->recurse_to_slaves(
1078+ { %$args, dsn => $slave, dbh => undef, parent => $dsn }, $level + 1 );
1079+ }
1080+ }
1081+}
1082+
1083+sub find_slave_hosts {
1084+ my ( $self, $dsn_parser, $dbh, $dsn, $method ) = @_;
1085+
1086+ my @methods = qw(processlist hosts);
1087+ if ( $method ) {
1088+ @methods = grep { $_ ne $method } @methods;
1089+ unshift @methods, $method;
1090+ }
1091+ else {
1092+ if ( ($dsn->{P} || 3306) != 3306 ) {
1093+ PTDEBUG && _d('Port number is non-standard; using only hosts method');
1094+ @methods = qw(hosts);
1095+ }
1096+ }
1097+ PTDEBUG && _d('Looking for slaves on', $dsn_parser->as_string($dsn),
1098+ 'using methods', @methods);
1099+
1100+ my @slaves;
1101+ METHOD:
1102+ foreach my $method ( @methods ) {
1103+ my $find_slaves = "_find_slaves_by_$method";
1104+ PTDEBUG && _d('Finding slaves with', $find_slaves);
1105+ @slaves = $self->$find_slaves($dsn_parser, $dbh, $dsn);
1106+ last METHOD if @slaves;
1107+ }
1108+
1109+ PTDEBUG && _d('Found', scalar(@slaves), 'slaves');
1110+ return @slaves;
1111+}
1112+
1113+sub _find_slaves_by_processlist {
1114+ my ( $self, $dsn_parser, $dbh, $dsn ) = @_;
1115+
1116+ my @slaves = map {
1117+ my $slave = $dsn_parser->parse("h=$_", $dsn);
1118+ $slave->{source} = 'processlist';
1119+ $slave;
1120+ }
1121+ grep { $_ }
1122+ map {
1123+ my ( $host ) = $_->{host} =~ m/^([^:]+):/;
1124+ if ( $host eq 'localhost' ) {
1125+ $host = '127.0.0.1'; # Replication never uses sockets.
1126+ }
1127+ $host;
1128+ } $self->get_connected_slaves($dbh);
1129+
1130+ return @slaves;
1131+}
1132+
1133+sub _find_slaves_by_hosts {
1134+ my ( $self, $dsn_parser, $dbh, $dsn ) = @_;
1135+
1136+ my @slaves;
1137+ my $sql = 'SHOW SLAVE HOSTS';
1138+ PTDEBUG && _d($dbh, $sql);
1139+ @slaves = @{$dbh->selectall_arrayref($sql, { Slice => {} })};
1140+
1141+ if ( @slaves ) {
1142+ PTDEBUG && _d('Found some SHOW SLAVE HOSTS info');
1143+ @slaves = map {
1144+ my %hash;
1145+ @hash{ map { lc $_ } keys %$_ } = values %$_;
1146+ my $spec = "h=$hash{host},P=$hash{port}"
1147+ . ( $hash{user} ? ",u=$hash{user}" : '')
1148+ . ( $hash{password} ? ",p=$hash{password}" : '');
1149+ my $dsn = $dsn_parser->parse($spec, $dsn);
1150+ $dsn->{server_id} = $hash{server_id};
1151+ $dsn->{master_id} = $hash{master_id};
1152+ $dsn->{source} = 'hosts';
1153+ $dsn;
1154+ } @slaves;
1155+ }
1156+
1157+ return @slaves;
1158+}
1159+
1160+sub get_connected_slaves {
1161+ my ( $self, $dbh ) = @_;
1162+
1163+ my $show = "SHOW GRANTS FOR ";
1164+ my $user = 'CURRENT_USER()';
1165+ my $vp = $self->{VersionParser};
1166+ if ( $vp && !$vp->version_ge($dbh, '4.1.2') ) {
1167+ $user = $dbh->selectrow_arrayref('SELECT USER()')->[0];
1168+ $user =~ s/([^@]+)@(.+)/'$1'\@'$2'/;
1169+ }
1170+ my $sql = $show . $user;
1171+ PTDEBUG && _d($dbh, $sql);
1172+
1173+ my $proc;
1174+ eval {
1175+ $proc = grep {
1176+ m/ALL PRIVILEGES.*?\*\.\*|PROCESS/
1177+ } @{$dbh->selectcol_arrayref($sql)};
1178+ };
1179+ if ( $EVAL_ERROR ) {
1180+
1181+ if ( $EVAL_ERROR =~ m/no such grant defined for user/ ) {
1182+ PTDEBUG && _d('Retrying SHOW GRANTS without host; error:',
1183+ $EVAL_ERROR);
1184+ ($user) = split('@', $user);
1185+ $sql = $show . $user;
1186+ PTDEBUG && _d($sql);
1187+ eval {
1188+ $proc = grep {
1189+ m/ALL PRIVILEGES.*?\*\.\*|PROCESS/
1190+ } @{$dbh->selectcol_arrayref($sql)};
1191+ };
1192+ }
1193+
1194+ die "Failed to $sql: $EVAL_ERROR" if $EVAL_ERROR;
1195+ }
1196+ if ( !$proc ) {
1197+ die "You do not have the PROCESS privilege";
1198+ }
1199+
1200+ $sql = 'SHOW PROCESSLIST';
1201+ PTDEBUG && _d($dbh, $sql);
1202+ grep { $_->{command} =~ m/Binlog Dump/i }
1203+ map { # Lowercase the column names
1204+ my %hash;
1205+ @hash{ map { lc $_ } keys %$_ } = values %$_;
1206+ \%hash;
1207+ }
1208+ @{$dbh->selectall_arrayref($sql, { Slice => {} })};
1209+}
1210+
1211+sub is_master_of {
1212+ my ( $self, $master, $slave ) = @_;
1213+ my $master_status = $self->get_master_status($master)
1214+ or die "The server specified as a master is not a master";
1215+ my $slave_status = $self->get_slave_status($slave)
1216+ or die "The server specified as a slave is not a slave";
1217+ my @connected = $self->get_connected_slaves($master)
1218+ or die "The server specified as a master has no connected slaves";
1219+ my (undef, $port) = $master->selectrow_array('SHOW VARIABLES LIKE "port"');
1220+
1221+ if ( $port != $slave_status->{master_port} ) {
1222+ die "The slave is connected to $slave_status->{master_port} "
1223+ . "but the master's port is $port";
1224+ }
1225+
1226+ if ( !grep { $slave_status->{master_user} eq $_->{user} } @connected ) {
1227+ die "I don't see any slave I/O thread connected with user "
1228+ . $slave_status->{master_user};
1229+ }
1230+
1231+ if ( ($slave_status->{slave_io_state} || '')
1232+ eq 'Waiting for master to send event' )
1233+ {
1234+ my ( $master_log_name, $master_log_num )
1235+ = $master_status->{file} =~ m/^(.*?)\.0*([1-9][0-9]*)$/;
1236+ my ( $slave_log_name, $slave_log_num )
1237+ = $slave_status->{master_log_file} =~ m/^(.*?)\.0*([1-9][0-9]*)$/;
1238+ if ( $master_log_name ne $slave_log_name
1239+ || abs($master_log_num - $slave_log_num) > 1 )
1240+ {
1241+ die "The slave thinks it is reading from "
1242+ . "$slave_status->{master_log_file}, but the "
1243+ . "master is writing to $master_status->{file}";
1244+ }
1245+ }
1246+ return 1;
1247+}
1248+
1249+sub get_master_dsn {
1250+ my ( $self, $dbh, $dsn, $dsn_parser ) = @_;
1251+ my $master = $self->get_slave_status($dbh) or return undef;
1252+ my $spec = "h=$master->{master_host},P=$master->{master_port}";
1253+ return $dsn_parser->parse($spec, $dsn);
1254+}
1255+
1256+sub get_slave_status {
1257+ my ( $self, $dbh ) = @_;
1258+ if ( !$self->{not_a_slave}->{$dbh} ) {
1259+ my $sth = $self->{sths}->{$dbh}->{SLAVE_STATUS}
1260+ ||= $dbh->prepare('SHOW SLAVE STATUS');
1261+ PTDEBUG && _d($dbh, 'SHOW SLAVE STATUS');
1262+ $sth->execute();
1263+ my ($ss) = @{$sth->fetchall_arrayref({})};
1264+
1265+ if ( $ss && %$ss ) {
1266+ $ss = { map { lc($_) => $ss->{$_} } keys %$ss }; # lowercase the keys
1267+ return $ss;
1268+ }
1269+
1270+ PTDEBUG && _d('This server returns nothing for SHOW SLAVE STATUS');
1271+ $self->{not_a_slave}->{$dbh}++;
1272+ }
1273+}
1274+
1275+sub get_master_status {
1276+ my ( $self, $dbh ) = @_;
1277+
1278+ if ( $self->{not_a_master}->{$dbh} ) {
1279+ PTDEBUG && _d('Server on dbh', $dbh, 'is not a master');
1280+ return;
1281+ }
1282+
1283+ my $sth = $self->{sths}->{$dbh}->{MASTER_STATUS}
1284+ ||= $dbh->prepare('SHOW MASTER STATUS');
1285+ PTDEBUG && _d($dbh, 'SHOW MASTER STATUS');
1286+ $sth->execute();
1287+ my ($ms) = @{$sth->fetchall_arrayref({})};
1288+ PTDEBUG && _d(
1289+ $ms ? map { "$_=" . (defined $ms->{$_} ? $ms->{$_} : '') } keys %$ms
1290+ : '');
1291+
1292+ if ( !$ms || scalar keys %$ms < 2 ) {
1293+ PTDEBUG && _d('Server on dbh', $dbh, 'does not seem to be a master');
1294+ $self->{not_a_master}->{$dbh}++;
1295+ }
1296+
1297+ return { map { lc($_) => $ms->{$_} } keys %$ms }; # lowercase the keys
1298+}
1299+
1300+sub wait_for_master {
1301+ my ( $self, %args ) = @_;
1302+ my @required_args = qw(master_status slave_dbh);
1303+ foreach my $arg ( @required_args ) {
1304+ die "I need a $arg argument" unless $args{$arg};
1305+ }
1306+ my ($master_status, $slave_dbh) = @args{@required_args};
1307+ my $timeout = $args{timeout} || 60;
1308+
1309+ my $result;
1310+ my $waited;
1311+ if ( $master_status ) {
1312+ my $sql = "SELECT MASTER_POS_WAIT('$master_status->{file}', "
1313+ . "$master_status->{position}, $timeout)";
1314+ PTDEBUG && _d($slave_dbh, $sql);
1315+ my $start = time;
1316+ ($result) = $slave_dbh->selectrow_array($sql);
1317+
1318+ $waited = time - $start;
1319+
1320+ PTDEBUG && _d('Result of waiting:', $result);
1321+ PTDEBUG && _d("Waited", $waited, "seconds");
1322+ }
1323+ else {
1324+ PTDEBUG && _d('Not waiting: this server is not a master');
1325+ }
1326+
1327+ return {
1328+ result => $result,
1329+ waited => $waited,
1330+ };
1331+}
1332+
1333+sub stop_slave {
1334+ my ( $self, $dbh ) = @_;
1335+ my $sth = $self->{sths}->{$dbh}->{STOP_SLAVE}
1336+ ||= $dbh->prepare('STOP SLAVE');
1337+ PTDEBUG && _d($dbh, $sth->{Statement});
1338+ $sth->execute();
1339+}
1340+
1341+sub start_slave {
1342+ my ( $self, $dbh, $pos ) = @_;
1343+ if ( $pos ) {
1344+ my $sql = "START SLAVE UNTIL MASTER_LOG_FILE='$pos->{file}', "
1345+ . "MASTER_LOG_POS=$pos->{position}";
1346+ PTDEBUG && _d($dbh, $sql);
1347+ $dbh->do($sql);
1348+ }
1349+ else {
1350+ my $sth = $self->{sths}->{$dbh}->{START_SLAVE}
1351+ ||= $dbh->prepare('START SLAVE');
1352+ PTDEBUG && _d($dbh, $sth->{Statement});
1353+ $sth->execute();
1354+ }
1355+}
1356+
1357+sub catchup_to_master {
1358+ my ( $self, $slave, $master, $timeout ) = @_;
1359+ $self->stop_slave($master);
1360+ $self->stop_slave($slave);
1361+ my $slave_status = $self->get_slave_status($slave);
1362+ my $slave_pos = $self->repl_posn($slave_status);
1363+ my $master_status = $self->get_master_status($master);
1364+ my $master_pos = $self->repl_posn($master_status);
1365+ PTDEBUG && _d('Master position:', $self->pos_to_string($master_pos),
1366+ 'Slave position:', $self->pos_to_string($slave_pos));
1367+
1368+ my $result;
1369+ if ( $self->pos_cmp($slave_pos, $master_pos) < 0 ) {
1370+ PTDEBUG && _d('Waiting for slave to catch up to master');
1371+ $self->start_slave($slave, $master_pos);
1372+
1373+ $result = $self->wait_for_master(
1374+ master_status => $master_status,
1375+ slave_dbh => $slave,
1376+ timeout => $timeout,
1377+ master_status => $master_status
1378+ );
1379+ if ( !defined $result->{result} ) {
1380+ $slave_status = $self->get_slave_status($slave);
1381+ if ( !$self->slave_is_running($slave_status) ) {
1382+ PTDEBUG && _d('Master position:',
1383+ $self->pos_to_string($master_pos),
1384+ 'Slave position:', $self->pos_to_string($slave_pos));
1385+ $slave_pos = $self->repl_posn($slave_status);
1386+ if ( $self->pos_cmp($slave_pos, $master_pos) != 0 ) {
1387+ die "MASTER_POS_WAIT() returned NULL but slave has not "
1388+ . "caught up to master";
1389+ }
1390+ PTDEBUG && _d('Slave is caught up to master and stopped');
1391+ }
1392+ else {
1393+ die "Slave has not caught up to master and it is still running";
1394+ }
1395+ }
1396+ }
1397+ else {
1398+ PTDEBUG && _d("Slave is already caught up to master");
1399+ }
1400+
1401+ return $result;
1402+}
1403+
1404+sub catchup_to_same_pos {
1405+ my ( $self, $s1_dbh, $s2_dbh ) = @_;
1406+ $self->stop_slave($s1_dbh);
1407+ $self->stop_slave($s2_dbh);
1408+ my $s1_status = $self->get_slave_status($s1_dbh);
1409+ my $s2_status = $self->get_slave_status($s2_dbh);
1410+ my $s1_pos = $self->repl_posn($s1_status);
1411+ my $s2_pos = $self->repl_posn($s2_status);
1412+ if ( $self->pos_cmp($s1_pos, $s2_pos) < 0 ) {
1413+ $self->start_slave($s1_dbh, $s2_pos);
1414+ }
1415+ elsif ( $self->pos_cmp($s2_pos, $s1_pos) < 0 ) {
1416+ $self->start_slave($s2_dbh, $s1_pos);
1417+ }
1418+
1419+ $s1_status = $self->get_slave_status($s1_dbh);
1420+ $s2_status = $self->get_slave_status($s2_dbh);
1421+ $s1_pos = $self->repl_posn($s1_status);
1422+ $s2_pos = $self->repl_posn($s2_status);
1423+
1424+ if ( $self->slave_is_running($s1_status)
1425+ || $self->slave_is_running($s2_status)
1426+ || $self->pos_cmp($s1_pos, $s2_pos) != 0)
1427+ {
1428+ die "The servers aren't both stopped at the same position";
1429+ }
1430+
1431+}
1432+
1433+sub slave_is_running {
1434+ my ( $self, $slave_status ) = @_;
1435+ return ($slave_status->{slave_sql_running} || 'No') eq 'Yes';
1436+}
1437+
1438+sub has_slave_updates {
1439+ my ( $self, $dbh ) = @_;
1440+ my $sql = q{SHOW VARIABLES LIKE 'log_slave_updates'};
1441+ PTDEBUG && _d($dbh, $sql);
1442+ my ($name, $value) = $dbh->selectrow_array($sql);
1443+ return $value && $value =~ m/^(1|ON)$/;
1444+}
1445+
1446+sub repl_posn {
1447+ my ( $self, $status ) = @_;
1448+ if ( exists $status->{file} && exists $status->{position} ) {
1449+ return {
1450+ file => $status->{file},
1451+ position => $status->{position},
1452+ };
1453+ }
1454+ else {
1455+ return {
1456+ file => $status->{relay_master_log_file},
1457+ position => $status->{exec_master_log_pos},
1458+ };
1459+ }
1460+}
1461+
1462+sub get_slave_lag {
1463+ my ( $self, $dbh ) = @_;
1464+ my $stat = $self->get_slave_status($dbh);
1465+ return unless $stat; # server is not a slave
1466+ return $stat->{seconds_behind_master};
1467+}
1468+
1469+sub pos_cmp {
1470+ my ( $self, $a, $b ) = @_;
1471+ return $self->pos_to_string($a) cmp $self->pos_to_string($b);
1472+}
1473+
1474+sub short_host {
1475+ my ( $self, $dsn ) = @_;
1476+ my ($host, $port);
1477+ if ( $dsn->{master_host} ) {
1478+ $host = $dsn->{master_host};
1479+ $port = $dsn->{master_port};
1480+ }
1481+ else {
1482+ $host = $dsn->{h};
1483+ $port = $dsn->{P};
1484+ }
1485+ return ($host || '[default]') . ( ($port || 3306) == 3306 ? '' : ":$port" );
1486+}
1487+
1488+sub is_replication_thread {
1489+ my ( $self, $query, %args ) = @_;
1490+ return unless $query;
1491+
1492+ my $type = lc($args{type} || 'all');
1493+ die "Invalid type: $type"
1494+ unless $type =~ m/^binlog_dump|slave_io|slave_sql|all$/i;
1495+
1496+ my $match = 0;
1497+ if ( $type =~ m/binlog_dump|all/i ) {
1498+ $match = 1
1499+ if ($query->{Command} || $query->{command} || '') eq "Binlog Dump";
1500+ }
1501+ if ( !$match ) {
1502+ if ( ($query->{User} || $query->{user} || '') eq "system user" ) {
1503+ PTDEBUG && _d("Slave replication thread");
1504+ if ( $type ne 'all' ) {
1505+ my $state = $query->{State} || $query->{state} || '';
1506+
1507+ if ( $state =~ m/^init|end$/ ) {
1508+ PTDEBUG && _d("Special state:", $state);
1509+ $match = 1;
1510+ }
1511+ else {
1512+ my ($slave_sql) = $state =~ m/
1513+ ^(Waiting\sfor\sthe\snext\sevent
1514+ |Reading\sevent\sfrom\sthe\srelay\slog
1515+ |Has\sread\sall\srelay\slog;\swaiting
1516+ |Making\stemp\sfile
1517+ |Waiting\sfor\sslave\smutex\son\sexit)/xi;
1518+
1519+ $match = $type eq 'slave_sql' && $slave_sql ? 1
1520+ : $type eq 'slave_io' && !$slave_sql ? 1
1521+ : 0;
1522+ }
1523+ }
1524+ else {
1525+ $match = 1;
1526+ }
1527+ }
1528+ else {
1529+ PTDEBUG && _d('Not system user');
1530+ }
1531+
1532+ if ( !defined $args{check_known_ids} || $args{check_known_ids} ) {
1533+ my $id = $query->{Id} || $query->{id};
1534+ if ( $match ) {
1535+ $self->{replication_thread}->{$id} = 1;
1536+ }
1537+ else {
1538+ if ( $self->{replication_thread}->{$id} ) {
1539+ PTDEBUG && _d("Thread ID is a known replication thread ID");
1540+ $match = 1;
1541+ }
1542+ }
1543+ }
1544+ }
1545+
1546+ PTDEBUG && _d('Matches', $type, 'replication thread:',
1547+ ($match ? 'yes' : 'no'), '; match:', $match);
1548+
1549+ return $match;
1550+}
1551+
1552+
1553+sub get_replication_filters {
1554+ my ( $self, %args ) = @_;
1555+ my @required_args = qw(dbh);
1556+ foreach my $arg ( @required_args ) {
1557+ die "I need a $arg argument" unless $args{$arg};
1558+ }
1559+ my ($dbh) = @args{@required_args};
1560+
1561+ my %filters = ();
1562+
1563+ my $status = $self->get_master_status($dbh);
1564+ if ( $status ) {
1565+ map { $filters{$_} = $status->{$_} }
1566+ grep { defined $status->{$_} && $status->{$_} ne '' }
1567+ qw(
1568+ binlog_do_db
1569+ binlog_ignore_db
1570+ );
1571+ }
1572+
1573+ $status = $self->get_slave_status($dbh);
1574+ if ( $status ) {
1575+ map { $filters{$_} = $status->{$_} }
1576+ grep { defined $status->{$_} && $status->{$_} ne '' }
1577+ qw(
1578+ replicate_do_db
1579+ replicate_ignore_db
1580+ replicate_do_table
1581+ replicate_ignore_table
1582+ replicate_wild_do_table
1583+ replicate_wild_ignore_table
1584+ );
1585+
1586+ my $sql = "SHOW VARIABLES LIKE 'slave_skip_errors'";
1587+ PTDEBUG && _d($dbh, $sql);
1588+ my $row = $dbh->selectrow_arrayref($sql);
1589+ $filters{slave_skip_errors} = $row->[1] if $row->[1] && $row->[1] ne 'OFF';
1590+ }
1591+
1592+ return \%filters;
1593+}
1594+
1595+
1596+sub pos_to_string {
1597+ my ( $self, $pos ) = @_;
1598+ my $fmt = '%s/%020d';
1599+ return sprintf($fmt, @{$pos}{qw(file position)});
1600+}
1601+
1602+sub reset_known_replication_threads {
1603+ my ( $self ) = @_;
1604+ $self->{replication_thread} = {};
1605+ return;
1606+}
1607+
1608+sub get_cxn_from_dsn_table {
1609+ my ($self, %args) = @_;
1610+ my @required_args = qw(dsn_table_dsn make_cxn DSNParser Quoter);
1611+ foreach my $arg ( @required_args ) {
1612+ die "I need a $arg argument" unless $args{$arg};
1613+ }
1614+ my ($dsn_table_dsn, $make_cxn, $dp, $q) = @args{@required_args};
1615+ PTDEBUG && _d('DSN table DSN:', $dsn_table_dsn);
1616+
1617+ my $dsn = $dp->parse($dsn_table_dsn);
1618+ my $dsn_table;
1619+ if ( $dsn->{D} && $dsn->{t} ) {
1620+ $dsn_table = $q->quote($dsn->{D}, $dsn->{t});
1621+ }
1622+ elsif ( $dsn->{t} && $dsn->{t} =~ m/\./ ) {
1623+ $dsn_table = $q->quote($q->split_unquote($dsn->{t}));
1624+ }
1625+ else {
1626+ die "DSN table DSN does not specify a database (D) "
1627+ . "or a database-qualified table (t)";
1628+ }
1629+
1630+ my $dsn_tbl_cxn = $make_cxn->(dsn => $dsn);
1631+ my $dbh = $dsn_tbl_cxn->connect();
1632+ my $sql = "SELECT dsn FROM $dsn_table ORDER BY id";
1633+ PTDEBUG && _d($sql);
1634+ my $dsn_strings = $dbh->selectcol_arrayref($sql);
1635+ my @cxn;
1636+ if ( $dsn_strings ) {
1637+ foreach my $dsn_string ( @$dsn_strings ) {
1638+ PTDEBUG && _d('DSN from DSN table:', $dsn_string);
1639+ push @cxn, $make_cxn->(dsn_string => $dsn_string);
1640+ }
1641+ }
1642+ return \@cxn;
1643+}
1644+
1645+sub _d {
1646+ my ($package, undef, $line) = caller 0;
1647+ @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
1648+ map { defined $_ ? $_ : 'undef' }
1649+ @_;
1650+ print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
1651+}
1652+
1653+1;
1654+}
1655+# ###########################################################################
1656+# End MasterSlave package
1657+# ###########################################################################
1658+
1659+# ###########################################################################
1660+# ReplicaLagWaiter package
1661+# This package is a copy without comments from the original. The original
1662+# with comments and its test file can be found in the Bazaar repository at,
1663+# lib/ReplicaLagWaiter.pm
1664+# t/lib/ReplicaLagWaiter.t
1665+# See https://launchpad.net/percona-toolkit for more information.
1666+# ###########################################################################
1667+{
1668+package ReplicaLagWaiter;
1669+
1670+use strict;
1671+use warnings FATAL => 'all';
1672+use English qw(-no_match_vars);
1673+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
1674+
1675+use Time::HiRes qw(sleep time);
1676+use Data::Dumper;
1677+
1678+sub new {
1679+ my ( $class, %args ) = @_;
1680+ my @required_args = qw(oktorun get_lag sleep max_lag slaves);
1681+ foreach my $arg ( @required_args ) {
1682+ die "I need a $arg argument" unless defined $args{$arg};
1683+ }
1684+
1685+ my $self = {
1686+ %args,
1687+ };
1688+
1689+ return bless $self, $class;
1690+}
1691+
1692+sub wait {
1693+ my ( $self, %args ) = @_;
1694+ my @required_args = qw();
1695+ foreach my $arg ( @required_args ) {
1696+ die "I need a $arg argument" unless $args{$arg};
1697+ }
1698+ my $pr = $args{Progress};
1699+
1700+ my $oktorun = $self->{oktorun};
1701+ my $get_lag = $self->{get_lag};
1702+ my $sleep = $self->{sleep};
1703+ my $slaves = $self->{slaves};
1704+ my $max_lag = $self->{max_lag};
1705+
1706+ my $worst; # most lagging slave
1707+ my $pr_callback;
1708+ my $pr_first_report;
1709+ if ( $pr ) {
1710+ $pr_callback = sub {
1711+ my ($fraction, $elapsed, $remaining, $eta, $completed) = @_;
1712+ my $dsn_name = $worst->{cxn}->name();
1713+ if ( defined $worst->{lag} ) {
1714+ print STDERR "Replica lag is " . ($worst->{lag} || '?')
1715+ . " seconds on $dsn_name. Waiting.\n";
1716+ }
1717+ else {
1718+ print STDERR "Replica $dsn_name is stopped. Waiting.\n";
1719+ }
1720+ return;
1721+ };
1722+ $pr->set_callback($pr_callback);
1723+
1724+ $pr_first_report = sub {
1725+ my $dsn_name = $worst->{cxn}->name();
1726+ if ( !defined $worst->{lag} ) {
1727+ print STDERR "Replica $dsn_name is stopped. Waiting.\n";
1728+ }
1729+ return;
1730+ };
1731+ }
1732+
1733+ my @lagged_slaves = map { {cxn=>$_, lag=>undef} } @$slaves;
1734+ while ( $oktorun->() && @lagged_slaves ) {
1735+ PTDEBUG && _d('Checking slave lag');
1736+ for my $i ( 0..$#lagged_slaves ) {
1737+ my $lag = $get_lag->($lagged_slaves[$i]->{cxn});
1738+ PTDEBUG && _d($lagged_slaves[$i]->{cxn}->name(),
1739+ 'slave lag:', $lag);
1740+ if ( !defined $lag || $lag > $max_lag ) {
1741+ $lagged_slaves[$i]->{lag} = $lag;
1742+ }
1743+ else {
1744+ delete $lagged_slaves[$i];
1745+ }
1746+ }
1747+
1748+ @lagged_slaves = grep { defined $_ } @lagged_slaves;
1749+ if ( @lagged_slaves ) {
1750+ @lagged_slaves = reverse sort {
1751+ defined $a->{lag} && defined $b->{lag} ? $a->{lag} <=> $b->{lag}
1752+ : defined $a->{lag} ? -1
1753+ : 1;
1754+ } @lagged_slaves;
1755+ $worst = $lagged_slaves[0];
1756+ PTDEBUG && _d(scalar @lagged_slaves, 'slaves are lagging, worst:',
1757+ $worst->{lag}, 'on', Dumper($worst->{cxn}->dsn()));
1758+
1759+ if ( $pr ) {
1760+ $pr->update(
1761+ sub { return 0; },
1762+ first_report => $pr_first_report,
1763+ );
1764+ }
1765+
1766+ PTDEBUG && _d('Calling sleep callback');
1767+ $sleep->($worst->{cxn}, $worst->{lag});
1768+ }
1769+ }
1770+
1771+ PTDEBUG && _d('All slaves caught up');
1772+ return;
1773+}
1774+
1775+sub _d {
1776+ my ($package, undef, $line) = caller 0;
1777+ @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
1778+ map { defined $_ ? $_ : 'undef' }
1779+ @_;
1780+ print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
1781+}
1782+
1783+1;
1784+}
1785+# ###########################################################################
1786+# End ReplicaLagWaiter package
1787+# ###########################################################################
1788+
1789+# ###########################################################################
1790+# MySQLStatusWaiter package
1791+# This package is a copy without comments from the original. The original
1792+# with comments and its test file can be found in the Bazaar repository at,
1793+# lib/MySQLStatusWaiter.pm
1794+# t/lib/MySQLStatusWaiter.t
1795+# See https://launchpad.net/percona-toolkit for more information.
1796+# ###########################################################################
1797+{
1798+package MySQLStatusWaiter;
1799+
1800+use strict;
1801+use warnings FATAL => 'all';
1802+use English qw(-no_match_vars);
1803+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
1804+
1805+sub new {
1806+ my ( $class, %args ) = @_;
1807+ my @required_args = qw(max_spec get_status sleep oktorun);
1808+ foreach my $arg ( @required_args ) {
1809+ die "I need a $arg argument" unless defined $args{$arg};
1810+ }
1811+
1812+ PTDEBUG && _d('Parsing spec for max thresholds');
1813+ my $max_val_for = _parse_spec(
1814+ spec => $args{max_spec},
1815+ get_status => $args{get_status},
1816+ threshold_factor => 0.2, # +20%
1817+ );
1818+
1819+ PTDEBUG && _d('Parsing spec for critical thresholds');
1820+ my $critical_val_for = _parse_spec(
1821+ spec => $args{critical_spec} || [],
1822+ get_status => $args{get_status},
1823+ threshold_factor => 1.0, # double (x2; +100%)
1824+ );
1825+
1826+ my $self = {
1827+ get_status => $args{get_status},
1828+ sleep => $args{sleep},
1829+ oktorun => $args{oktorun},
1830+ max_val_for => $max_val_for,
1831+ critical_val_for => $critical_val_for,
1832+ };
1833+
1834+ return bless $self, $class;
1835+}
1836+
1837+sub _parse_spec {
1838+ my ( %args ) = @_;
1839+ my @required_args = qw(spec get_status);
1840+ foreach my $arg ( @required_args ) {
1841+ die "I need a $arg argument" unless defined $args{$arg};
1842+ }
1843+ my ($spec, $get_status) = @args{@required_args};
1844+
1845+ return unless $spec && scalar @$spec;
1846+ my $threshold_factor = $args{threshold_factor} || 0.20;
1847+
1848+ my %max_val_for;
1849+ foreach my $var_val ( @$spec ) {
1850+ my ($var, $val) = split /[:=]/, $var_val;
1851+ die "Invalid spec: $var_val" unless $var;
1852+ if ( !$val ) {
1853+ my $init_val = $get_status->($var);
1854+ PTDEBUG && _d('Initial', $var, 'value:', $init_val);
1855+ $val = int(($init_val * $threshold_factor) + $init_val);
1856+ }
1857+ PTDEBUG && _d('Wait if', $var, '>=', $val);
1858+ $max_val_for{$var} = $val;
1859+ }
1860+
1861+ return \%max_val_for;
1862+}
1863+
1864+sub max_values {
1865+ my ($self) = @_;
1866+ return $self->{max_val_for};
1867+}
1868+
1869+sub critical_values {
1870+ my ($self) = @_;
1871+ return $self->{critical_val_for};
1872+}
1873+
1874+sub wait {
1875+ my ( $self, %args ) = @_;
1876+
1877+ return unless $self->{max_val_for};
1878+
1879+ my $pr = $args{Progress}; # optional
1880+
1881+ my $oktorun = $self->{oktorun};
1882+ my $get_status = $self->{get_status};
1883+ my $sleep = $self->{sleep};
1884+
1885+ my %vals_too_high = %{$self->{max_val_for}};
1886+ my $pr_callback;
1887+ if ( $pr ) {
1888+ $pr_callback = sub {
1889+ print STDERR "Pausing because "
1890+ . join(', ',
1891+ map {
1892+ "$_="
1893+ . (defined $vals_too_high{$_} ? $vals_too_high{$_}
1894+ : 'unknown')
1895+ } sort keys %vals_too_high
1896+ )
1897+ . ".\n";
1898+ return;
1899+ };
1900+ $pr->set_callback($pr_callback);
1901+ }
1902+
1903+ while ( $oktorun->() ) {
1904+ PTDEBUG && _d('Checking status variables');
1905+ foreach my $var ( sort keys %vals_too_high ) {
1906+ my $val = $get_status->($var);
1907+ PTDEBUG && _d($var, '=', $val);
1908+ if ( $val
1909+ && exists $self->{critical_val_for}->{$var}
1910+ && $val >= $self->{critical_val_for}->{$var} ) {
1911+ die "$var=$val exceeds its critical threshold "
1912+ . "$self->{critical_val_for}->{$var}\n";
1913+ }
1914+ if ( !$val || $val >= $self->{max_val_for}->{$var} ) {
1915+ $vals_too_high{$var} = $val;
1916+ }
1917+ else {
1918+ delete $vals_too_high{$var};
1919+ }
1920+ }
1921+
1922+ last unless scalar keys %vals_too_high;
1923+
1924+ PTDEBUG && _d(scalar keys %vals_too_high, 'values are too high:',
1925+ %vals_too_high);
1926+ if ( $pr ) {
1927+ $pr->update(sub { return 0; });
1928+ }
1929+ PTDEBUG && _d('Calling sleep callback');
1930+ $sleep->();
1931+ %vals_too_high = %{$self->{max_val_for}}; # recheck all vars
1932+ }
1933+
1934+ PTDEBUG && _d('All var vals are low enough');
1935+ return;
1936+}
1937+
1938+sub _d {
1939+ my ($package, undef, $line) = caller 0;
1940+ @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
1941+ map { defined $_ ? $_ : 'undef' }
1942+ @_;
1943+ print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
1944+}
1945+
1946+1;
1947+}
1948+# ###########################################################################
1949+# End MySQLStatusWaiter package
1950+# ###########################################################################
1951+
1952+# ###########################################################################
1953+# WeightedAvgRate package
1954+# This package is a copy without comments from the original. The original
1955+# with comments and its test file can be found in the Bazaar repository at,
1956+# lib/WeightedAvgRate.pm
1957+# t/lib/WeightedAvgRate.t
1958+# See https://launchpad.net/percona-toolkit for more information.
1959+# ###########################################################################
1960+{
1961+package WeightedAvgRate;
1962+
1963+use strict;
1964+use warnings FATAL => 'all';
1965+use English qw(-no_match_vars);
1966+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
1967+
1968+sub new {
1969+ my ( $class, %args ) = @_;
1970+ my @required_args = qw(target_t);
1971+ foreach my $arg ( @required_args ) {
1972+ die "I need a $arg argument" unless defined $args{$arg};
1973+ }
1974+
1975+ my $self = {
1976+ %args,
1977+ avg_n => 0,
1978+ avg_t => 0,
1979+ weight => $args{weight} || 0.75,
1980+ };
1981+
1982+ return bless $self, $class;
1983+}
1984+
1985+sub update {
1986+ my ($self, $n, $t) = @_;
1987+ PTDEBUG && _d('Master op time:', $n, 'n /', $t, 's');
1988+
1989+ if ( $self->{avg_n} && $self->{avg_t} ) {
1990+ $self->{avg_n} = ($self->{avg_n} * $self->{weight}) + $n;
1991+ $self->{avg_t} = ($self->{avg_t} * $self->{weight}) + $t;
1992+ $self->{avg_rate} = $self->{avg_n} / $self->{avg_t};
1993+ PTDEBUG && _d('Weighted avg rate:', $self->{avg_rate}, 'n/s');
1994+ }
1995+ else {
1996+ $self->{avg_n} = $n;
1997+ $self->{avg_t} = $t;
1998+ $self->{avg_rate} = $self->{avg_n} / $self->{avg_t};
1999+ PTDEBUG && _d('Initial avg rate:', $self->{avg_rate}, 'n/s');
2000+ }
2001+
2002+ my $new_n = int($self->{avg_rate} * $self->{target_t});
2003+ PTDEBUG && _d('Adjust n to', $new_n);
2004+ return $new_n;
2005+}
2006+
2007+sub _d {
2008+ my ($package, undef, $line) = caller 0;
2009+ @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
2010+ map { defined $_ ? $_ : 'undef' }
2011+ @_;
2012+ print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
2013+}
2014+
2015+1;
2016+}
2017+# ###########################################################################
2018+# End WeightedAvgRate package
2019+# ###########################################################################
2020+
2021+# ###########################################################################
2022+# NibbleIterator package
2023+# This package is a copy without comments from the original. The original
2024+# with comments and its test file can be found in the Bazaar repository at,
2025+# lib/NibbleIterator.pm
2026+# t/lib/NibbleIterator.t
2027+# See https://launchpad.net/percona-toolkit for more information.
2028+# ###########################################################################
2029+{
2030+package NibbleIterator;
2031+
2032+use strict;
2033+use warnings FATAL => 'all';
2034+use English qw(-no_match_vars);
2035+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
2036+
2037+use Data::Dumper;
2038+$Data::Dumper::Indent = 1;
2039+$Data::Dumper::Sortkeys = 1;
2040+$Data::Dumper::Quotekeys = 0;
2041+
2042+sub new {
2043+ my ( $class, %args ) = @_;
2044+ my @required_args = qw(Cxn tbl chunk_size OptionParser Quoter TableNibbler TableParser);
2045+ foreach my $arg ( @required_args ) {
2046+ die "I need a $arg argument" unless $args{$arg};
2047+ }
2048+ my ($cxn, $tbl, $chunk_size, $o, $q) = @args{@required_args};
2049+
2050+ my $nibble_params = can_nibble(%args);
2051+
2052+ my %comments = (
2053+ bite => "bite table",
2054+ nibble => "nibble table",
2055+ );
2056+ if ( $args{comments} ) {
2057+ map { $comments{$_} = $args{comments}->{$_} }
2058+ grep { defined $args{comments}->{$_} }
2059+ keys %{$args{comments}};
2060+ }
2061+
2062+ my $where = $o->has('where') ? $o->get('where') : '';
2063+ my $tbl_struct = $tbl->{tbl_struct};
2064+ my $ignore_col = $o->has('ignore-columns')
2065+ ? ($o->get('ignore-columns') || {})
2066+ : {};
2067+ my $all_cols = $o->has('columns')
2068+ ? ($o->get('columns') || $tbl_struct->{cols})
2069+ : $tbl_struct->{cols};
2070+ my @cols = grep { !$ignore_col->{$_} } @$all_cols;
2071+ my $self;
2072+ if ( $nibble_params->{one_nibble} ) {
2073+ my $nibble_sql
2074+ = ($args{dml} ? "$args{dml} " : "SELECT ")
2075+ . ($args{select} ? $args{select}
2076+ : join(', ', map { $q->quote($_) } @cols))
2077+ . " FROM $tbl->{name}"
2078+ . ($where ? " WHERE $where" : '')
2079+ . " /*$comments{bite}*/";
2080+ PTDEBUG && _d('One nibble statement:', $nibble_sql);
2081+
2082+ my $explain_nibble_sql
2083+ = "EXPLAIN SELECT "
2084+ . ($args{select} ? $args{select}
2085+ : join(', ', map { $q->quote($_) } @cols))
2086+ . " FROM $tbl->{name}"
2087+ . ($where ? " WHERE $where" : '')
2088+ . " /*explain $comments{bite}*/";
2089+ PTDEBUG && _d('Explain one nibble statement:', $explain_nibble_sql);
2090+
2091+ $self = {
2092+ %args,
2093+ one_nibble => 1,
2094+ limit => 0,
2095+ nibble_sql => $nibble_sql,
2096+ explain_nibble_sql => $explain_nibble_sql,
2097+ };
2098+ }
2099+ else {
2100+ my $index = $nibble_params->{index}; # brevity
2101+ my $index_cols = $tbl->{tbl_struct}->{keys}->{$index}->{cols};
2102+
2103+ my $asc = $args{TableNibbler}->generate_asc_stmt(
2104+ %args,
2105+ tbl_struct => $tbl->{tbl_struct},
2106+ index => $index,
2107+ cols => \@cols,
2108+ asc_only => 1,
2109+ );
2110+ PTDEBUG && _d('Ascend params:', Dumper($asc));
2111+
2112+ my $from = "$tbl->{name} FORCE INDEX(`$index`)";
2113+ my $order_by = join(', ', map {$q->quote($_)} @{$index_cols});
2114+
2115+ my $first_lb_sql
2116+ = "SELECT /*!40001 SQL_NO_CACHE */ "
2117+ . join(', ', map { $q->quote($_) } @{$asc->{scols}})
2118+ . " FROM $from"
2119+ . ($where ? " WHERE $where" : '')
2120+ . " ORDER BY $order_by"
2121+ . " LIMIT 1"
2122+ . " /*first lower boundary*/";
2123+ PTDEBUG && _d('First lower boundary statement:', $first_lb_sql);
2124+
2125+ my $resume_lb_sql;
2126+ if ( $args{resume} ) {
2127+ $resume_lb_sql
2128+ = "SELECT /*!40001 SQL_NO_CACHE */ "
2129+ . join(', ', map { $q->quote($_) } @{$asc->{scols}})
2130+ . " FROM $from"
2131+ . " WHERE " . $asc->{boundaries}->{'>'}
2132+ . ($where ? " AND ($where)" : '')
2133+ . " ORDER BY $order_by"
2134+ . " LIMIT 1"
2135+ . " /*resume lower boundary*/";
2136+ PTDEBUG && _d('Resume lower boundary statement:', $resume_lb_sql);
2137+ }
2138+
2139+ my $last_ub_sql
2140+ = "SELECT /*!40001 SQL_NO_CACHE */ "
2141+ . join(', ', map { $q->quote($_) } @{$asc->{scols}})
2142+ . " FROM $from"
2143+ . ($where ? " WHERE $where" : '')
2144+ . " ORDER BY "
2145+ . join(' DESC, ', map {$q->quote($_)} @{$index_cols}) . ' DESC'
2146+ . " LIMIT 1"
2147+ . " /*last upper boundary*/";
2148+ PTDEBUG && _d('Last upper boundary statement:', $last_ub_sql);
2149+
2150+ my $ub_sql
2151+ = "SELECT /*!40001 SQL_NO_CACHE */ "
2152+ . join(', ', map { $q->quote($_) } @{$asc->{scols}})
2153+ . " FROM $from"
2154+ . " WHERE " . $asc->{boundaries}->{'>='}
2155+ . ($where ? " AND ($where)" : '')
2156+ . " ORDER BY $order_by"
2157+ . " LIMIT ?, 2"
2158+ . " /*next chunk boundary*/";
2159+ PTDEBUG && _d('Upper boundary statement:', $ub_sql);
2160+
2161+ my $nibble_sql
2162+ = ($args{dml} ? "$args{dml} " : "SELECT ")
2163+ . ($args{select} ? $args{select}
2164+ : join(', ', map { $q->quote($_) } @{$asc->{cols}}))
2165+ . " FROM $from"
2166+ . " WHERE " . $asc->{boundaries}->{'>='} # lower boundary
2167+ . " AND " . $asc->{boundaries}->{'<='} # upper boundary
2168+ . ($where ? " AND ($where)" : '')
2169+ . ($args{order_by} ? " ORDER BY $order_by" : "")
2170+ . " /*$comments{nibble}*/";
2171+ PTDEBUG && _d('Nibble statement:', $nibble_sql);
2172+
2173+ my $explain_nibble_sql
2174+ = "EXPLAIN SELECT "
2175+ . ($args{select} ? $args{select}
2176+ : join(', ', map { $q->quote($_) } @{$asc->{cols}}))
2177+ . " FROM $from"
2178+ . " WHERE " . $asc->{boundaries}->{'>='} # lower boundary
2179+ . " AND " . $asc->{boundaries}->{'<='} # upper boundary
2180+ . ($where ? " AND ($where)" : '')
2181+ . ($args{order_by} ? " ORDER BY $order_by" : "")
2182+ . " /*explain $comments{nibble}*/";
2183+ PTDEBUG && _d('Explain nibble statement:', $explain_nibble_sql);
2184+
2185+ my $limit = $chunk_size - 1;
2186+ PTDEBUG && _d('Initial chunk size (LIMIT):', $limit);
2187+
2188+ $self = {
2189+ %args,
2190+ index => $index,
2191+ limit => $limit,
2192+ first_lb_sql => $first_lb_sql,
2193+ last_ub_sql => $last_ub_sql,
2194+ ub_sql => $ub_sql,
2195+ nibble_sql => $nibble_sql,
2196+ explain_ub_sql => "EXPLAIN $ub_sql",
2197+ explain_nibble_sql => $explain_nibble_sql,
2198+ resume_lb_sql => $resume_lb_sql,
2199+ sql => {
2200+ columns => $asc->{scols},
2201+ from => $from,
2202+ where => $where,
2203+ boundaries => $asc->{boundaries},
2204+ order_by => $order_by,
2205+ },
2206+ };
2207+ }
2208+
2209+ $self->{row_est} = $nibble_params->{row_est},
2210+ $self->{nibbleno} = 0;
2211+ $self->{have_rows} = 0;
2212+ $self->{rowno} = 0;
2213+ $self->{oktonibble} = 1;
2214+
2215+ return bless $self, $class;
2216+}
2217+
2218+sub next {
2219+ my ($self) = @_;
2220+
2221+ if ( !$self->{oktonibble} ) {
2222+ PTDEBUG && _d('Not ok to nibble');
2223+ return;
2224+ }
2225+
2226+ my %callback_args = (
2227+ Cxn => $self->{Cxn},
2228+ tbl => $self->{tbl},
2229+ NibbleIterator => $self,
2230+ );
2231+
2232+ if ($self->{nibbleno} == 0) {
2233+ $self->_prepare_sths();
2234+ $self->_get_bounds();
2235+ if ( my $callback = $self->{callbacks}->{init} ) {
2236+ $self->{oktonibble} = $callback->(%callback_args);
2237+ PTDEBUG && _d('init callback returned', $self->{oktonibble});
2238+ if ( !$self->{oktonibble} ) {
2239+ $self->{no_more_boundaries} = 1;
2240+ return;
2241+ }
2242+ }
2243+ }
2244+
2245+ NIBBLE:
2246+ while ( $self->{have_rows} || $self->_next_boundaries() ) {
2247+ if ( !$self->{have_rows} ) {
2248+ $self->{nibbleno}++;
2249+ PTDEBUG && _d($self->{nibble_sth}->{Statement}, 'params:',
2250+ join(', ', (@{$self->{lower}}, @{$self->{upper}})));
2251+ if ( my $callback = $self->{callbacks}->{exec_nibble} ) {
2252+ $self->{have_rows} = $callback->(%callback_args);
2253+ }
2254+ else {
2255+ $self->{nibble_sth}->execute(@{$self->{lower}}, @{$self->{upper}});
2256+ $self->{have_rows} = $self->{nibble_sth}->rows();
2257+ }
2258+ PTDEBUG && _d($self->{have_rows}, 'rows in nibble', $self->{nibbleno});
2259+ }
2260+
2261+ if ( $self->{have_rows} ) {
2262+ my $row = $self->{nibble_sth}->fetchrow_arrayref();
2263+ if ( $row ) {
2264+ $self->{rowno}++;
2265+ PTDEBUG && _d('Row', $self->{rowno}, 'in nibble',$self->{nibbleno});
2266+ return [ @$row ];
2267+ }
2268+ }
2269+
2270+ PTDEBUG && _d('No rows in nibble or nibble skipped');
2271+ if ( my $callback = $self->{callbacks}->{after_nibble} ) {
2272+ $callback->(%callback_args);
2273+ }
2274+ $self->{rowno} = 0;
2275+ $self->{have_rows} = 0;
2276+ }
2277+
2278+ PTDEBUG && _d('Done nibbling');
2279+ if ( my $callback = $self->{callbacks}->{done} ) {
2280+ $callback->(%callback_args);
2281+ }
2282+
2283+ return;
2284+}
2285+
2286+sub nibble_number {
2287+ my ($self) = @_;
2288+ return $self->{nibbleno};
2289+}
2290+
2291+sub set_nibble_number {
2292+ my ($self, $n) = @_;
2293+ die "I need a number" unless $n;
2294+ $self->{nibbleno} = $n;
2295+ PTDEBUG && _d('Set new nibble number:', $n);
2296+ return;
2297+}
2298+
2299+sub nibble_index {
2300+ my ($self) = @_;
2301+ return $self->{index};
2302+}
2303+
2304+sub statements {
2305+ my ($self) = @_;
2306+ return {
2307+ nibble => $self->{nibble_sth},
2308+ explain_nibble => $self->{explain_nibble_sth},
2309+ upper_boundary => $self->{ub_sth},
2310+ explain_upper_boundary => $self->{explain_ub_sth},
2311+ }
2312+}
2313+
2314+sub boundaries {
2315+ my ($self) = @_;
2316+ return {
2317+ first_lower => $self->{first_lower},
2318+ lower => $self->{lower},
2319+ upper => $self->{upper},
2320+ next_lower => $self->{next_lower},
2321+ last_upper => $self->{last_upper},
2322+ };
2323+}
2324+
2325+sub set_boundary {
2326+ my ($self, $boundary, $values) = @_;
2327+ die "I need a boundary parameter"
2328+ unless $boundary;
2329+ die "Invalid boundary: $boundary"
2330+ unless $boundary =~ m/^(?:lower|upper|next_lower|last_upper)$/;
2331+ die "I need a values arrayref parameter"
2332+ unless $values && ref $values eq 'ARRAY';
2333+ $self->{$boundary} = $values;
2334+ PTDEBUG && _d('Set new', $boundary, 'boundary:', Dumper($values));
2335+ return;
2336+}
2337+
2338+sub one_nibble {
2339+ my ($self) = @_;
2340+ return $self->{one_nibble};
2341+}
2342+
2343+sub chunk_size {
2344+ my ($self) = @_;
2345+ return $self->{limit} + 1;
2346+}
2347+
2348+sub set_chunk_size {
2349+ my ($self, $limit) = @_;
2350+ return if $self->{one_nibble};
2351+ die "Chunk size must be > 0" unless $limit;
2352+ $self->{limit} = $limit - 1;
2353+ PTDEBUG && _d('Set new chunk size (LIMIT):', $limit);
2354+ return;
2355+}
2356+
2357+sub sql {
2358+ my ($self) = @_;
2359+ return $self->{sql};
2360+}
2361+
2362+sub more_boundaries {
2363+ my ($self) = @_;
2364+ return !$self->{no_more_boundaries};
2365+}
2366+
2367+sub row_estimate {
2368+ my ($self) = @_;
2369+ return $self->{row_est};
2370+}
2371+
2372+sub can_nibble {
2373+ my (%args) = @_;
2374+ my @required_args = qw(Cxn tbl chunk_size OptionParser TableParser);
2375+ foreach my $arg ( @required_args ) {
2376+ die "I need a $arg argument" unless $args{$arg};
2377+ }
2378+ my ($cxn, $tbl, $chunk_size, $o) = @args{@required_args};
2379+
2380+ my ($row_est, $mysql_index) = get_row_estimate(
2381+ Cxn => $cxn,
2382+ tbl => $tbl,
2383+ where => $o->has('where') ? $o->get('where') : '',
2384+ );
2385+
2386+ my $one_nibble = !defined $args{one_nibble} || $args{one_nibble}
2387+ ? $row_est <= $chunk_size * $o->get('chunk-size-limit')
2388+ : 0;
2389+ PTDEBUG && _d('One nibble:', $one_nibble ? 'yes' : 'no');
2390+
2391+ if ( $args{resume}
2392+ && !defined $args{resume}->{lower_boundary}
2393+ && !defined $args{resume}->{upper_boundary} ) {
2394+ PTDEBUG && _d('Resuming from one nibble table');
2395+ $one_nibble = 1;
2396+ }
2397+
2398+ my $index = _find_best_index(%args, mysql_index => $mysql_index);
2399+ if ( !$index && !$one_nibble ) {
2400+ die "There is no good index and the table is oversized.";
2401+ }
2402+
2403+ return {
2404+ row_est => $row_est, # nibble about this many rows
2405+ index => $index, # using this index
2406+ one_nibble => $one_nibble, # if the table fits in one nibble/chunk
2407+ };
2408+}
2409+
2410+sub _find_best_index {
2411+ my (%args) = @_;
2412+ my @required_args = qw(Cxn tbl TableParser);
2413+ my ($cxn, $tbl, $tp) = @args{@required_args};
2414+ my $tbl_struct = $tbl->{tbl_struct};
2415+ my $indexes = $tbl_struct->{keys};
2416+
2417+ my $want_index = $args{chunk_index};
2418+ if ( $want_index ) {
2419+ PTDEBUG && _d('User wants to use index', $want_index);
2420+ if ( !exists $indexes->{$want_index} ) {
2421+ PTDEBUG && _d('Cannot use user index because it does not exist');
2422+ $want_index = undef;
2423+ }
2424+ }
2425+
2426+ if ( !$want_index && $args{mysql_index} ) {
2427+ PTDEBUG && _d('MySQL wants to use index', $args{mysql_index});
2428+ $want_index = $args{mysql_index};
2429+ }
2430+
2431+ my $best_index;
2432+ my @possible_indexes;
2433+ if ( $want_index ) {
2434+ if ( $indexes->{$want_index}->{is_unique} ) {
2435+ PTDEBUG && _d('Will use wanted index');
2436+ $best_index = $want_index;
2437+ }
2438+ else {
2439+ PTDEBUG && _d('Wanted index is a possible index');
2440+ push @possible_indexes, $want_index;
2441+ }
2442+ }
2443+ else {
2444+ PTDEBUG && _d('Auto-selecting best index');
2445+ foreach my $index ( $tp->sort_indexes($tbl_struct) ) {
2446+ if ( $index eq 'PRIMARY' || $indexes->{$index}->{is_unique} ) {
2447+ $best_index = $index;
2448+ last;
2449+ }
2450+ else {
2451+ push @possible_indexes, $index;
2452+ }
2453+ }
2454+ }
2455+
2456+ if ( !$best_index && @possible_indexes ) {
2457+ PTDEBUG && _d('No PRIMARY or unique indexes;',
2458+ 'will use index with highest cardinality');
2459+ foreach my $index ( @possible_indexes ) {
2460+ $indexes->{$index}->{cardinality} = _get_index_cardinality(
2461+ %args,
2462+ index => $index,
2463+ );
2464+ }
2465+ @possible_indexes = sort {
2466+ my $cmp
2467+ = $indexes->{$b}->{cardinality} <=> $indexes->{$b}->{cardinality};
2468+ if ( $cmp == 0 ) {
2469+ $cmp = scalar @{$indexes->{$b}->{cols}}
2470+ <=> scalar @{$indexes->{$a}->{cols}};
2471+ }
2472+ $cmp;
2473+ } @possible_indexes;
2474+ $best_index = $possible_indexes[0];
2475+ }
2476+
2477+ PTDEBUG && _d('Best index:', $best_index);
2478+ return $best_index;
2479+}
2480+
2481+sub _get_index_cardinality {
2482+ my (%args) = @_;
2483+ my @required_args = qw(Cxn tbl index);
2484+ my ($cxn, $tbl, $index) = @args{@required_args};
2485+
2486+ my $sql = "SHOW INDEXES FROM $tbl->{name} "
2487+ . "WHERE Key_name = '$index'";
2488+ PTDEBUG && _d($sql);
2489+ my $cardinality = 1;
2490+ my $rows = $cxn->dbh()->selectall_hashref($sql, 'key_name');
2491+ foreach my $row ( values %$rows ) {
2492+ $cardinality *= $row->{cardinality} if $row->{cardinality};
2493+ }
2494+ PTDEBUG && _d('Index', $index, 'cardinality:', $cardinality);
2495+ return $cardinality;
2496+}
2497+
2498+sub get_row_estimate {
2499+ my (%args) = @_;
2500+ my @required_args = qw(Cxn tbl);
2501+ foreach my $arg ( @required_args ) {
2502+ die "I need a $arg argument" unless $args{$arg};
2503+ }
2504+ my ($cxn, $tbl) = @args{@required_args};
2505+
2506+ if ( !$args{where} && exists $tbl->{tbl_status} ) {
2507+ PTDEBUG && _d('Using table status for row estimate');
2508+ return $tbl->{tbl_status}->{rows} || 0;
2509+ }
2510+ else {
2511+ PTDEBUG && _d('Use EXPLAIN for row estimate');
2512+ my $sql = "EXPLAIN SELECT * FROM $tbl->{name} "
2513+ . "WHERE " . ($args{where} || '1=1');
2514+ PTDEBUG && _d($sql);
2515+ my $expl = $cxn->dbh()->selectrow_hashref($sql);
2516+ PTDEBUG && _d(Dumper($expl));
2517+ return ($expl->{rows} || 0), $expl->{key};
2518+ }
2519+}
2520+
2521+sub _prepare_sths {
2522+ my ($self) = @_;
2523+ PTDEBUG && _d('Preparing statement handles');
2524+
2525+ my $dbh = $self->{Cxn}->dbh();
2526+
2527+ $self->{nibble_sth} = $dbh->prepare($self->{nibble_sql});
2528+ $self->{explain_nibble_sth} = $dbh->prepare($self->{explain_nibble_sql});
2529+
2530+ if ( !$self->{one_nibble} ) {
2531+ $self->{ub_sth} = $dbh->prepare($self->{ub_sql});
2532+ $self->{explain_ub_sth} = $dbh->prepare($self->{explain_ub_sql});
2533+ }
2534+
2535+ return;
2536+}
2537+
2538+sub _get_bounds {
2539+ my ($self) = @_;
2540+
2541+ if ( $self->{one_nibble} ) {
2542+ if ( $self->{resume} ) {
2543+ $self->{no_more_boundaries} = 1;
2544+ }
2545+ return;
2546+ }
2547+
2548+ my $dbh = $self->{Cxn}->dbh();
2549+
2550+ $self->{first_lower} = $dbh->selectrow_arrayref($self->{first_lb_sql});
2551+ PTDEBUG && _d('First lower boundary:', Dumper($self->{first_lower}));
2552+
2553+ if ( my $nibble = $self->{resume} ) {
2554+ if ( defined $nibble->{lower_boundary}
2555+ && defined $nibble->{upper_boundary} ) {
2556+ my $sth = $dbh->prepare($self->{resume_lb_sql});
2557+ my @ub = split ',', $nibble->{upper_boundary};
2558+ PTDEBUG && _d($sth->{Statement}, 'params:', @ub);
2559+ $sth->execute(@ub);
2560+ $self->{next_lower} = $sth->fetchrow_arrayref();
2561+ $sth->finish();
2562+ }
2563+ }
2564+ else {
2565+ $self->{next_lower} = $self->{first_lower};
2566+ }
2567+ PTDEBUG && _d('Next lower boundary:', Dumper($self->{next_lower}));
2568+
2569+ if ( !$self->{next_lower} ) {
2570+ PTDEBUG && _d('At end of table, or no more boundaries to resume');
2571+ $self->{no_more_boundaries} = 1;
2572+ }
2573+
2574+ return;
2575+}
2576+
2577+sub _next_boundaries {
2578+ my ($self) = @_;
2579+
2580+ if ( $self->{no_more_boundaries} ) {
2581+ PTDEBUG && _d('No more boundaries');
2582+ return; # stop nibbling
2583+ }
2584+
2585+ if ( $self->{one_nibble} ) {
2586+ $self->{lower} = $self->{upper} = [];
2587+ $self->{no_more_boundaries} = 1; # for next call
2588+ return 1; # continue nibbling
2589+ }
2590+
2591+ if ( $self->identical_boundaries($self->{lower}, $self->{next_lower}) ) {
2592+ PTDEBUG && _d('Infinite loop detected');
2593+ my $tbl = $self->{tbl};
2594+ my $index = $tbl->{tbl_struct}->{keys}->{$self->{index}};
2595+ my $n_cols = scalar @{$index->{cols}};
2596+ my $chunkno = $self->{nibbleno};
2597+ die "Possible infinite loop detected! "
2598+ . "The lower boundary for chunk $chunkno is "
2599+ . "<" . join(', ', @{$self->{lower}}) . "> and the lower "
2600+ . "boundary for chunk " . ($chunkno + 1) . " is also "
2601+ . "<" . join(', ', @{$self->{next_lower}}) . ">. "
2602+ . "This usually happens when using a non-unique single "
2603+ . "column index. The current chunk index for table "
2604+ . "$tbl->{db}.$tbl->{tbl} is $self->{index} which is"
2605+ . ($index->{is_unique} ? '' : ' not') . " unique and covers "
2606+ . ($n_cols > 1 ? "$n_cols columns" : "1 column") . ".\n";
2607+ }
2608+ $self->{lower} = $self->{next_lower};
2609+
2610+ if ( my $callback = $self->{callbacks}->{next_boundaries} ) {
2611+ my $oktonibble = $callback->(
2612+ Cxn => $self->{Cxn},
2613+ tbl => $self->{tbl},
2614+ NibbleIterator => $self,
2615+ );
2616+ PTDEBUG && _d('next_boundaries callback returned', $oktonibble);
2617+ if ( !$oktonibble ) {
2618+ $self->{no_more_boundaries} = 1;
2619+ return; # stop nibbling
2620+ }
2621+ }
2622+
2623+ PTDEBUG && _d($self->{ub_sth}->{Statement}, 'params:',
2624+ join(', ', @{$self->{lower}}), $self->{limit});
2625+ $self->{ub_sth}->execute(@{$self->{lower}}, $self->{limit});
2626+ my $boundary = $self->{ub_sth}->fetchall_arrayref();
2627+ PTDEBUG && _d('Next boundary:', Dumper($boundary));
2628+ if ( $boundary && @$boundary ) {
2629+ $self->{upper} = $boundary->[0];
2630+
2631+ if ( $boundary->[1] ) {
2632+ $self->{next_lower} = $boundary->[1];
2633+ }
2634+ else {
2635+ PTDEBUG && _d('End of table boundary:', Dumper($boundary->[0]));
2636+ $self->{no_more_boundaries} = 1; # for next call
2637+
2638+ $self->{last_upper} = $boundary->[0];
2639+ }
2640+ }
2641+ else {
2642+ my $dbh = $self->{Cxn}->dbh();
2643+ $self->{upper} = $dbh->selectrow_arrayref($self->{last_ub_sql});
2644+ PTDEBUG && _d('Last upper boundary:', Dumper($self->{upper}));
2645+ $self->{no_more_boundaries} = 1; # for next call
2646+
2647+ $self->{last_upper} = $self->{upper};
2648+ }
2649+ $self->{ub_sth}->finish();
2650+
2651+ return 1; # continue nibbling
2652+}
2653+
2654+sub identical_boundaries {
2655+ my ($self, $b1, $b2) = @_;
2656+
2657+ return 0 if ($b1 && !$b2) || (!$b1 && $b2);
2658+
2659+ return 1 if !$b1 && !$b2;
2660+
2661+ die "Boundaries have different numbers of values"
2662+ if scalar @$b1 != scalar @$b2; # shouldn't happen
2663+ my $n_vals = scalar @$b1;
2664+ for my $i ( 0..($n_vals-1) ) {
2665+ return 0 if $b1->[$i] ne $b2->[$i]; # diff
2666+ }
2667+ return 1;
2668+}
2669+
2670+sub DESTROY {
2671+ my ( $self ) = @_;
2672+ foreach my $key ( keys %$self ) {
2673+ if ( $key =~ m/_sth$/ ) {
2674+ PTDEBUG && _d('Finish', $key);
2675+ $self->{$key}->finish();
2676+ }
2677+ }
2678+ return;
2679+}
2680+
2681+sub _d {
2682+ my ($package, undef, $line) = caller 0;
2683+ @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
2684+ map { defined $_ ? $_ : 'undef' }
2685+ @_;
2686+ print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
2687+}
2688+
2689+1;
2690+}
2691+# ###########################################################################
2692+# End NibbleIterator package
2693+# ###########################################################################
2694+
2695+# ###########################################################################
2696+# Transformers package
2697+# This package is a copy without comments from the original. The original
2698+# with comments and its test file can be found in the Bazaar repository at,
2699+# lib/Transformers.pm
2700+# t/lib/Transformers.t
2701+# See https://launchpad.net/percona-toolkit for more information.
2702+# ###########################################################################
2703+{
2704+package Transformers;
2705+
2706+use strict;
2707+use warnings FATAL => 'all';
2708+use English qw(-no_match_vars);
2709+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
2710+
2711+use Time::Local qw(timegm timelocal);
2712+use Digest::MD5 qw(md5_hex);
2713+
2714+require Exporter;
2715+our @ISA = qw(Exporter);
2716+our %EXPORT_TAGS = ();
2717+our @EXPORT = ();
2718+our @EXPORT_OK = qw(
2719+ micro_t
2720+ percentage_of
2721+ secs_to_time
2722+ time_to_secs
2723+ shorten
2724+ ts
2725+ parse_timestamp
2726+ unix_timestamp
2727+ any_unix_timestamp
2728+ make_checksum
2729+ crc32
2730+);
2731+
2732+our $mysql_ts = qr/(\d\d)(\d\d)(\d\d) +(\d+):(\d+):(\d+)(\.\d+)?/;
2733+our $proper_ts = qr/(\d\d\d\d)-(\d\d)-(\d\d)[T ](\d\d):(\d\d):(\d\d)(\.\d+)?/;
2734+our $n_ts = qr/(\d{1,5})([shmd]?)/; # Limit \d{1,5} because \d{6} looks
2735+
2736+sub micro_t {
2737+ my ( $t, %args ) = @_;
2738+ my $p_ms = defined $args{p_ms} ? $args{p_ms} : 0; # precision for ms vals
2739+ my $p_s = defined $args{p_s} ? $args{p_s} : 0; # precision for s vals
2740+ my $f;
2741+
2742+ $t = 0 if $t < 0;
2743+
2744+ $t = sprintf('%.17f', $t) if $t =~ /e/;
2745+
2746+ $t =~ s/\.(\d{1,6})\d*/\.$1/;
2747+
2748+ if ($t > 0 && $t <= 0.000999) {
2749+ $f = ($t * 1000000) . 'us';
2750+ }
2751+ elsif ($t >= 0.001000 && $t <= 0.999999) {
2752+ $f = sprintf("%.${p_ms}f", $t * 1000);
2753+ $f = ($f * 1) . 'ms'; # * 1 to remove insignificant zeros
2754+ }
2755+ elsif ($t >= 1) {
2756+ $f = sprintf("%.${p_s}f", $t);
2757+ $f = ($f * 1) . 's'; # * 1 to remove insignificant zeros
2758+ }
2759+ else {
2760+ $f = 0; # $t should = 0 at this point
2761+ }
2762+
2763+ return $f;
2764+}
2765+
2766+sub percentage_of {
2767+ my ( $is, $of, %args ) = @_;
2768+ my $p = $args{p} || 0; # float precision
2769+ my $fmt = $p ? "%.${p}f" : "%d";
2770+ return sprintf $fmt, ($is * 100) / ($of ||= 1);
2771+}
2772+
2773+sub secs_to_time {
2774+ my ( $secs, $fmt ) = @_;
2775+ $secs ||= 0;
2776+ return '00:00' unless $secs;
2777+
2778+ $fmt ||= $secs >= 86_400 ? 'd'
2779+ : $secs >= 3_600 ? 'h'
2780+ : 'm';
2781+
2782+ return
2783+ $fmt eq 'd' ? sprintf(
2784+ "%d+%02d:%02d:%02d",
2785+ int($secs / 86_400),
2786+ int(($secs % 86_400) / 3_600),
2787+ int(($secs % 3_600) / 60),
2788+ $secs % 60)
2789+ : $fmt eq 'h' ? sprintf(
2790+ "%02d:%02d:%02d",
2791+ int(($secs % 86_400) / 3_600),
2792+ int(($secs % 3_600) / 60),
2793+ $secs % 60)
2794+ : sprintf(
2795+ "%02d:%02d",
2796+ int(($secs % 3_600) / 60),
2797+ $secs % 60);
2798+}
2799+
2800+sub time_to_secs {
2801+ my ( $val, $default_suffix ) = @_;
2802+ die "I need a val argument" unless defined $val;
2803+ my $t = 0;
2804+ my ( $prefix, $num, $suffix ) = $val =~ m/([+-]?)(\d+)([a-z])?$/;
2805+ $suffix = $suffix || $default_suffix || 's';
2806+ if ( $suffix =~ m/[smhd]/ ) {
2807+ $t = $suffix eq 's' ? $num * 1 # Seconds
2808+ : $suffix eq 'm' ? $num * 60 # Minutes
2809+ : $suffix eq 'h' ? $num * 3600 # Hours
2810+ : $num * 86400; # Days
2811+
2812+ $t *= -1 if $prefix && $prefix eq '-';
2813+ }
2814+ else {
2815+ die "Invalid suffix for $val: $suffix";
2816+ }
2817+ return $t;
2818+}
2819+
2820+sub shorten {
2821+ my ( $num, %args ) = @_;
2822+ my $p = defined $args{p} ? $args{p} : 2; # float precision
2823+ my $d = defined $args{d} ? $args{d} : 1_024; # divisor
2824+ my $n = 0;
2825+ my @units = ('', qw(k M G T P E Z Y));
2826+ while ( $num >= $d && $n < @units - 1 ) {
2827+ $num /= $d;
2828+ ++$n;
2829+ }
2830+ return sprintf(
2831+ $num =~ m/\./ || $n
2832+ ? "%.${p}f%s"
2833+ : '%d',
2834+ $num, $units[$n]);
2835+}
2836+
2837+sub ts {
2838+ my ( $time, $gmt ) = @_;
2839+ my ( $sec, $min, $hour, $mday, $mon, $year )
2840+ = $gmt ? gmtime($time) : localtime($time);
2841+ $mon += 1;
2842+ $year += 1900;
2843+ my $val = sprintf("%d-%02d-%02dT%02d:%02d:%02d",
2844+ $year, $mon, $mday, $hour, $min, $sec);
2845+ if ( my ($us) = $time =~ m/(\.\d+)$/ ) {
2846+ $us = sprintf("%.6f", $us);
2847+ $us =~ s/^0\././;
2848+ $val .= $us;
2849+ }
2850+ return $val;
2851+}
2852+
2853+sub parse_timestamp {
2854+ my ( $val ) = @_;
2855+ if ( my($y, $m, $d, $h, $i, $s, $f)
2856+ = $val =~ m/^$mysql_ts$/ )
2857+ {
2858+ return sprintf "%d-%02d-%02d %02d:%02d:"
2859+ . (defined $f ? '%09.6f' : '%02d'),
2860+ $y + 2000, $m, $d, $h, $i, (defined $f ? $s + $f : $s);
2861+ }
2862+ return $val;
2863+}
2864+
2865+sub unix_timestamp {
2866+ my ( $val, $gmt ) = @_;
2867+ if ( my($y, $m, $d, $h, $i, $s, $us) = $val =~ m/^$proper_ts$/ ) {
2868+ $val = $gmt
2869+ ? timegm($s, $i, $h, $d, $m - 1, $y)
2870+ : timelocal($s, $i, $h, $d, $m - 1, $y);
2871+ if ( defined $us ) {
2872+ $us = sprintf('%.6f', $us);
2873+ $us =~ s/^0\././;
2874+ $val .= $us;
2875+ }
2876+ }
2877+ return $val;
2878+}
2879+
2880+sub any_unix_timestamp {
2881+ my ( $val, $callback ) = @_;
2882+
2883+ if ( my ($n, $suffix) = $val =~ m/^$n_ts$/ ) {
2884+ $n = $suffix eq 's' ? $n # Seconds
2885+ : $suffix eq 'm' ? $n * 60 # Minutes
2886+ : $suffix eq 'h' ? $n * 3600 # Hours
2887+ : $suffix eq 'd' ? $n * 86400 # Days
2888+ : $n; # default: Seconds
2889+ PTDEBUG && _d('ts is now - N[shmd]:', $n);
2890+ return time - $n;
2891+ }
2892+ elsif ( $val =~ m/^\d{9,}/ ) {
2893+ PTDEBUG && _d('ts is already a unix timestamp');
2894+ return $val;
2895+ }
2896+ elsif ( my ($ymd, $hms) = $val =~ m/^(\d{6})(?:\s+(\d+:\d+:\d+))?/ ) {
2897+ PTDEBUG && _d('ts is MySQL slow log timestamp');
2898+ $val .= ' 00:00:00' unless $hms;
2899+ return unix_timestamp(parse_timestamp($val));
2900+ }
2901+ elsif ( ($ymd, $hms) = $val =~ m/^(\d{4}-\d\d-\d\d)(?:[T ](\d+:\d+:\d+))?/) {
2902+ PTDEBUG && _d('ts is properly formatted timestamp');
2903+ $val .= ' 00:00:00' unless $hms;
2904+ return unix_timestamp($val);
2905+ }
2906+ else {
2907+ PTDEBUG && _d('ts is MySQL expression');
2908+ return $callback->($val) if $callback && ref $callback eq 'CODE';
2909+ }
2910+
2911+ PTDEBUG && _d('Unknown ts type:', $val);
2912+ return;
2913+}
2914+
2915+sub make_checksum {
2916+ my ( $val ) = @_;
2917+ my $checksum = uc substr(md5_hex($val), -16);
2918+ PTDEBUG && _d($checksum, 'checksum for', $val);
2919+ return $checksum;
2920+}
2921+
2922+sub crc32 {
2923+ my ( $string ) = @_;
2924+ return unless $string;
2925+ my $poly = 0xEDB88320;
2926+ my $crc = 0xFFFFFFFF;
2927+ foreach my $char ( split(//, $string) ) {
2928+ my $comp = ($crc ^ ord($char)) & 0xFF;
2929+ for ( 1 .. 8 ) {
2930+ $comp = $comp & 1 ? $poly ^ ($comp >> 1) : $comp >> 1;
2931+ }
2932+ $crc = (($crc >> 8) & 0x00FFFFFF) ^ $comp;
2933+ }
2934+ return $crc ^ 0xFFFFFFFF;
2935+}
2936+
2937+sub _d {
2938+ my ($package, undef, $line) = caller 0;
2939+ @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
2940+ map { defined $_ ? $_ : 'undef' }
2941+ @_;
2942+ print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
2943+}
2944+
2945+1;
2946+}
2947+# ###########################################################################
2948+# End Transformers package
2949+# ###########################################################################
2950+
2951+# ###########################################################################
2952+# CleanupTask package
2953+# This package is a copy without comments from the original. The original
2954+# with comments and its test file can be found in the Bazaar repository at,
2955+# lib/CleanupTask.pm
2956+# t/lib/CleanupTask.t
2957+# See https://launchpad.net/percona-toolkit for more information.
2958+# ###########################################################################
2959+{
2960+package CleanupTask;
2961+
2962+use strict;
2963+use warnings FATAL => 'all';
2964+use English qw(-no_match_vars);
2965+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
2966+
2967+sub new {
2968+ my ( $class, $task ) = @_;
2969+ die "I need a task parameter" unless $task;
2970+ die "The task parameter must be a coderef" unless ref $task eq 'CODE';
2971+ my $self = {
2972+ task => $task,
2973+ };
2974+ PTDEBUG && _d('Created cleanup task', $task);
2975+ return bless $self, $class;
2976+}
2977+
2978+sub DESTROY {
2979+ my ($self) = @_;
2980+ my $task = $self->{task};
2981+ PTDEBUG && _d('Calling cleanup task', $task);
2982+ $task->();
2983+ return;
2984+}
2985+
2986+sub _d {
2987+ my ($package, undef, $line) = caller 0;
2988+ @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
2989+ map { defined $_ ? $_ : 'undef' }
2990+ @_;
2991+ print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
2992+}
2993+
2994+1;
2995+}
2996+# ###########################################################################
2997+# End CleanupTask package
2998+# ###########################################################################
2999+
3000+# ###########################################################################
3001 # This is a combination of modules and programs in one -- a runnable module.
3002 # http://www.perl.com/pub/a/2006/07/13/lightning-articles.html?page=last
3003 # Or, look it up in the Camel book on pages 642 and 643 in the 3rd edition.
3004@@ -3869,29 +5821,37 @@
3005 # ###########################################################################
3006 package pt_online_schema_change;
3007
3008+use strict;
3009+use warnings FATAL => 'all';
3010 use English qw(-no_match_vars);
3011-use Time::HiRes qw(sleep);
3012+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
3013+
3014+use Time::HiRes qw(time);
3015 use Data::Dumper;
3016 $Data::Dumper::Indent = 1;
3017 $Data::Dumper::Sortkeys = 1;
3018 $Data::Dumper::Quotekeys = 0;
3019
3020-Transformers->import(qw(ts));
3021-
3022-use constant PTDEBUG => $ENV{PTDEBUG} || 0;
3023-
3024-my $quiet = 0; # for msg()
3025+use sigtrap 'handler', \&sig_int, 'normal-signals';
3026+
3027+my $exit_status = 0;
3028+my $oktorun = 1;
3029+my @drop_trigger_sqls;
3030+
3031+$OUTPUT_AUTOFLUSH = 1;
3032
3033 sub main {
3034- @ARGV = @_; # set global ARGV for this package
3035- my $vp = new VersionParser();
3036- my $q = new Quoter();
3037- my $tp = new TableParser(Quoter => $q);
3038- my $chunker = new TableChunker(Quoter => $q, TableParser => $tp);
3039+ # Reset global vars else tests will fail.
3040+ @ARGV = @_;
3041+ $oktorun = 1;
3042+ @drop_trigger_sqls = ();
3043+
3044+ $exit_status = 0;
3045
3046 # ########################################################################
3047 # Get configuration information.
3048 # ########################################################################
3049+ my $q = new Quoter();
3050 my $o = new OptionParser();
3051 $o->get_specs();
3052 $o->get_opts();
3053@@ -3899,41 +5859,36 @@
3054 my $dp = $o->DSNParser();
3055 $dp->prop('set-vars', $o->get('set-vars'));
3056
3057- $quiet = $o->get('quiet'); # for msg()
3058-
3059- my ($dsn, $db, $tbl);
3060- $dsn = shift @ARGV;
3061+ # The original table, i.e. the one being altered, must be specified
3062+ # on the command line via the DSN.
3063+ my ($db, $tbl);
3064+ my $dsn = shift @ARGV;
3065 if ( !$dsn ) {
3066- $o->save_error('A DSN with a t part must be specified');
3067+ $o->save_error('A DSN must be specified');
3068 }
3069 else {
3070+ # Parse DSN string and convert it to a DSN data struct.
3071 $dsn = $dp->parse($dsn, $dp->parse_options($o));
3072- if ( !$dsn->{t} ) {
3073- $o->save_error('The DSN must specify a t (table) part');
3074- }
3075- else {
3076- ($db, $tbl) = $q->split_unquote($dsn->{t} || "", $dsn->{D} || "");
3077- }
3078- }
3079-
3080- my $rename_fk_method = lc($o->get('update-foreign-keys-method') || '');
3081- if ( ($rename_fk_method || '') eq 'drop_old_table' ) {
3082- $o->set('rename-tables', 0);
3083- $o->set('drop-old-table', 0),
3084- }
3085+ $db = $dsn->{D};
3086+ $tbl = $dsn->{t};
3087+ }
3088+
3089+ my $alter_fk_method = $o->get('alter-foreign-keys-method') || '';
3090+ if ( $alter_fk_method eq 'drop_swap' ) {
3091+ $o->set('swap-tables', 0);
3092+ $o->set('drop-old-table', 0);
3093+ }
3094+
3095+ # Explicit --chunk-size disable auto chunk sizing.
3096+ $o->set('chunk-time', 0) if $o->got('chunk-size');
3097
3098 if ( !$o->get('help') ) {
3099 if ( @ARGV ) {
3100 $o->save_error('Specify only one DSN on the command line');
3101 }
3102
3103- if ( !$db ) {
3104- $o->save_error("No database was specified in the DSN or by "
3105- . "--database (-D)");
3106- }
3107-
3108- if ( $tbl && $tbl eq ($o->get('tmp-table') || "") ) {
3109- $o->save_error("--tmp-table cannot be the same as the table");
3110+ if ( !$db || !$tbl ) {
3111+ $o->save_error("The DSN must specify a database (D) and a table (t)");
3112 }
3113
3114 if ( $o->get('progress') ) {
3115@@ -3944,523 +5899,1755 @@
3116 }
3117 }
3118
3119- if ( $o->get('child-tables') && !$o->get('update-foreign-keys-method') ) {
3120- $o->save_error("--child-tables requires --update-foreign-keys-method");
3121- }
3122-
3123- if ( $rename_fk_method
3124- && $rename_fk_method ne 'rebuild_constraints'
3125- && $rename_fk_method ne 'drop_old_table' ) {
3126- $o->save_error("Invalid --update-foreign-keys-method value");
3127- }
3128- }
3129-
3130- $o->usage_or_errors();
3131-
3132- msg("$PROGRAM_NAME started");
3133- my $exit_status = 0;
3134+ # See the "pod-based-option-value-validation" spec for how this may
3135+ # be automagically validated.
3136+ if ( $alter_fk_method
3137+ && $alter_fk_method ne 'auto'
3138+ && $alter_fk_method ne 'rebuild_constraints'
3139+ && $alter_fk_method ne 'drop_swap'
3140+ && $alter_fk_method ne 'none' )
3141+ {
3142+ $o->save_error("Invalid --alter-foreign-keys-method value: $alter_fk_method");
3143+ }
3144+ }
3145+
3146+ $o->usage_or_errors();
3147+
3148+ if ( $o->get('quiet') ) {
3149+ # BARON: this will fail on Windows, where there is no /dev/null. I feel
3150+ # it's a hack, like ignoring a problem instead of fixing it somehow. We
3151+ # should take a look at the things that get printed in a "normal"
3152+ # non-quiet run, and "if !quiet" them, and then do some kind of Logger.pm
3153+ # or Messager.pm module for a future release.
3154+ close STDOUT;
3155+ open STDOUT, '>', '/dev/null'
3156+ or warn "Cannot reopen STDOUT to /dev/null: $OS_ERROR";
3157+ }
3158
3159 # ########################################################################
3160 # Connect to MySQL.
3161 # ########################################################################
3162- my $dbh = get_cxn(
3163- dsn => $dsn,
3164- DSNParser => $dp,
3165+ my $set_on_connect = sub {
3166+ my ($dbh) = @_;
3167+
3168+ # See the same code in pt-table-checksum.
3169+ my $lock_wait_timeout = $o->get('lock-wait-timeout');
3170+ my $set_lwt = "SET SESSION innodb_lock_wait_timeout=$lock_wait_timeout";
3171+ PTDEBUG && _d($set_lwt);
3172+ eval {
3173+ $dbh->do($set_lwt);
3174+ };
3175+ if ( $EVAL_ERROR ) {
3176+ PTDEBUG && _d($EVAL_ERROR);
3177+ # Get the server's current value.
3178+ my $sql = "SHOW SESSION VARIABLES LIKE 'innodb_lock_wait_timeout'";
3179+ PTDEBUG && _d($dbh, $sql);
3180+ my (undef, $curr_lwt) = $dbh->selectrow_array($sql);
3181+ PTDEBUG && _d('innodb_lock_wait_timeout on server:', $curr_lwt);
3182+ if ( $curr_lwt > $lock_wait_timeout ) {
3183+ warn "Failed to $set_lwt: $EVAL_ERROR\n"
3184+ . "The current innodb_lock_wait_timeout value "
3185+ . "$curr_lwt is greater than the --lock-wait-timeout "
3186+ . "value $lock_wait_timeout and the variable cannot be "
3187+ . "changed. innodb_lock_wait_timeout is only dynamic when "
3188+ . "using the InnoDB plugin. To prevent this warning, either "
3189+ . "specify --lock-wait-time=$curr_lwt, or manually set "
3190+ . "innodb_lock_wait_timeout to a value less than or equal "
3191+ . "to $lock_wait_timeout and restart MySQL.\n";
3192+ }
3193+ }
3194+ };
3195+
3196+ # Do not call "new Cxn(" directly; use this sub so that set_on_connect
3197+ # is applied to every cxn.
3198+ # BARON: why not make this a subroutine instead of a subroutine variable? I
3199+ # think that can be less confusing. Also, the $set_on_connect variable can be
3200+ # inlined into this subroutine. Many of our tools have a get_dbh() subroutine
3201+ # and it might be good to just make a convention of it.
3202+ my $make_cxn = sub {
3203+ my (%args) = @_;
3204+ my $cxn = new Cxn(
3205+ %args,
3206+ DSNParser => $dp,
3207+ OptionParser => $o,
3208+ set => $set_on_connect,
3209+ );
3210+ eval { $cxn->connect() }; # connect or die trying
3211+ if ( $EVAL_ERROR ) {
3212+ die "Cannot connect to MySQL: $EVAL_ERROR\n";
3213+ }
3214+ return $cxn;
3215+ };
3216+
3217+ my $cxn = $make_cxn->(dsn => $dsn);
3218+
3219+ # ########################################################################
3220+ # Check if MySQL is new enough to have the triggers we need.
3221+ # Although triggers were introduced in 5.0.2, "Prior to MySQL 5.0.10,
3222+ # triggers cannot contain direct references to tables by name."
3223+ # ########################################################################
3224+ my $vp = new VersionParser();
3225+ if ( !$vp->version_ge($cxn->dbh(), '5.0.10') ) {
3226+ die "This tool requires MySQL 5.0.10 or newer.\n";
3227+ }
3228+
3229+ # ########################################################################
3230+ # Setup lag and load monitors.
3231+ # ########################################################################
3232+ my $slaves; # all slaves that are found or specified
3233+ my $slave_lag_cxns; # slaves whose lag we'll check
3234+ my $replica_lag; # ReplicaLagWaiter object
3235+ my $replica_lag_pr; # Progress for ReplicaLagWaiter
3236+ my $sys_load; # MySQLStatusWaiter object
3237+ my $sys_load_pr; # Progress for MySQLStatusWaiter object
3238+
3239+ if ( $o->get('execute') ) {
3240+ # #####################################################################
3241+ # Find and connect to slaves.
3242+ # #####################################################################
3243+ my $ms = new MasterSlave();
3244+ $slaves = $ms->get_slaves(
3245+ dbh => $cxn->dbh(),
3246+ dsn => $cxn->dsn(),
3247+ OptionParser => $o,
3248+ DSNParser => $dp,
3249+ Quoter => $q,
3250+ make_cxn => sub {
3251+ return $make_cxn->(@_, prev_dsn => $cxn->dsn());
3252+ },
3253+ );
3254+ PTDEBUG && _d(scalar @$slaves, 'slaves found');
3255+
3256+ if ( $o->get('check-slave-lag') ) {
3257+ PTDEBUG && _d('Will use --check-slave-lag to check for slave lag');
3258+ my $cxn = $make_cxn->(
3259+ dsn_string => $o->get('check-slave-lag'),
3260+ prev_dsn => $cxn->dsn(),
3261+ );
3262+ $slave_lag_cxns = [ $cxn ];
3263+ }
3264+ else {
3265+ PTDEBUG && _d('Will check slave lag on all slaves');
3266+ $slave_lag_cxns = $slaves;
3267+ }
3268+
3269+ # #####################################################################
3270+ # Check for replication filters.
3271+ # #####################################################################
3272+ if ( $o->get('check-replication-filters') ) {
3273+ PTDEBUG && _d("Checking slave replication filters");
3274+ my @all_repl_filters;
3275+ foreach my $slave ( @$slaves ) {
3276+ my $repl_filters = $ms->get_replication_filters(
3277+ dbh => $slave->dbh(),
3278+ );
3279+ if ( keys %$repl_filters ) {
3280+ push @all_repl_filters,
3281+ { name => $slave->name(),
3282+ filters => $repl_filters,
3283+ };
3284+ }
3285+ }
3286+ if ( @all_repl_filters ) {
3287+ my $msg = "Replication filters are set on these hosts:\n";
3288+ foreach my $host ( @all_repl_filters ) {
3289+ my $filters = $host->{filters};
3290+ $msg .= " $host->{name}\n"
3291+ . join("\n", map { " $_ = $host->{filters}->{$_}" }
3292+ keys %{$host->{filters}})
3293+ . "\n";
3294+ }
3295+ $msg .= "Please read the --check-replication-filters documentation "
3296+ . "to learn how to solve this problem.";
3297+ die $msg;
3298+ }
3299+ }
3300+
3301+ # #####################################################################
3302+ # Make a ReplicaLagWaiter to help wait for slaves after each chunk.
3303+ # #####################################################################
3304+ my $sleep = sub {
3305+ # Don't let the master dbh die while waiting for slaves because we
3306+ # may wait a very long time for slaves.
3307+ my $dbh = $cxn->dbh();
3308+ if ( !$dbh || !$dbh->ping() ) {
3309+ eval { $dbh = $cxn->connect() }; # connect or die trying
3310+ if ( $EVAL_ERROR ) {
3311+ $oktorun = 0; # flag for cleanup tasks
3312+ chomp $EVAL_ERROR;
3313+ die "Lost connection to " . $cxn->name() . " while waiting for "
3314+ . "replica lag ($EVAL_ERROR)\n";
3315+ }
3316+ }
3317+ $dbh->do("SELECT 'pt-online-schema-change keepalive'");
3318+ sleep $o->get('check-interval');
3319+ return;
3320+ };
3321+
3322+ my $get_lag = sub {
3323+ my ($cxn) = @_;
3324+ my $dbh = $cxn->dbh();
3325+ if ( !$dbh || !$dbh->ping() ) {
3326+ eval { $dbh = $cxn->connect() }; # connect or die trying
3327+ if ( $EVAL_ERROR ) {
3328+ $oktorun = 0; # flag for cleanup tasks
3329+ chomp $EVAL_ERROR;
3330+ die "Lost connection to replica " . $cxn->name()
3331+ . " while attempting to get its lag ($EVAL_ERROR)\n";
3332+ }
3333+ }
3334+ return $ms->get_slave_lag($dbh);
3335+ };
3336+
3337+ $replica_lag = new ReplicaLagWaiter(
3338+ slaves => $slave_lag_cxns,
3339+ max_lag => $o->get('max-lag'),
3340+ oktorun => sub { return $oktorun },
3341+ get_lag => $get_lag,
3342+ sleep => $sleep,
3343+ );
3344+
3345+ my $get_status;
3346+ {
3347+ my $sql = "SHOW GLOBAL STATUS LIKE ?";
3348+ my $sth = $cxn->dbh()->prepare($sql);
3349+
3350+ $get_status = sub {
3351+ my ($var) = @_;
3352+ PTDEBUG && _d($sth->{Statement}, $var);
3353+ $sth->execute($var);
3354+ my (undef, $val) = $sth->fetchrow_array();
3355+ return $val;
3356+ };
3357+ }
3358+
3359+ $sys_load = new MySQLStatusWaiter(
3360+ max_spec => $o->get('max-load'),
3361+ critical_spec => $o->get('critical-load'),
3362+ get_status => $get_status,
3363+ oktorun => sub { return $oktorun },
3364+ sleep => $sleep,
3365+ );
3366+
3367+ if ( $o->get('progress') ) {
3368+ $replica_lag_pr = new Progress(
3369+ jobsize => scalar @$slaves,
3370+ spec => $o->get('progress'),
3371+ name => "Waiting for replicas to catch up", # not used
3372+ );
3373+
3374+ $sys_load_pr = new Progress(
3375+ jobsize => scalar @{$o->get('max-load')},
3376+ spec => $o->get('progress'),
3377+ name => "Waiting for --max-load", # not used
3378+ );
3379+ }
3380+ }
3381+
3382+ # ########################################################################
3383+ # Setup and check the original table.
3384+ # ########################################################################
3385+ my $tp = new TableParser(Quoter => $q);
3386+
3387+ # Common table data struct (that modules like NibbleIterator expect).
3388+ my $orig_tbl = {
3389+ db => $db,
3390+ tbl => $tbl,
3391+ name => $q->quote($db, $tbl),
3392+ };
3393+
3394+ check_orig_table(
3395+ orig_tbl => $orig_tbl,
3396+ Cxn => $cxn,
3397 OptionParser => $o,
3398- AutoCommit => 1,
3399- );
3400- msg("USE `$db`");
3401- $dbh->do("USE `$db`");
3402-
3403- # ########################################################################
3404- # Daemonize only after (potentially) asking for passwords for --ask-pass.
3405+ TableParser => $tp,
3406+ Quoter => $q,
3407+ );
3408+
3409+ # ########################################################################
3410+ # Get child tables of the original table, if necessary.
3411+ # ########################################################################
3412+ my $child_tables = find_child_tables(
3413+ tbl => $orig_tbl,
3414+ Cxn => $cxn,
3415+ Quoter => $q,
3416+ );
3417+ if ( !$child_tables ) {
3418+ if ( $alter_fk_method ) {
3419+ warn "No foreign keys reference $orig_tbl->{name}; ignoring "
3420+ . "--alter-foreign-keys-method.\n";
3421+
3422+ if ( $alter_fk_method eq 'drop_swap' ) {
3423+ # These opts are disabled at the start if the user specifies
3424+ # the drop_swap method, but now that we know there are no
3425+ # child tables, we must re-enable these to make the alter work.
3426+ $o->set('swap-tables', 1);
3427+ $o->set('drop-old-table', 1);
3428+ }
3429+
3430+ $alter_fk_method = '';
3431+ }
3432+ # No child tables and --alter-fk-method wasn't specified,
3433+ # so nothing to do.
3434+ }
3435+ else {
3436+ print "Child tables:\n";
3437+ foreach my $child_table ( @$child_tables ) {
3438+ printf " %s (approx. %s rows)\n",
3439+ $child_table->{name},
3440+ $child_table->{row_est} || '?';
3441+ }
3442+
3443+ if ( $alter_fk_method ) {
3444+ # Let the user know how we're going to update the child table fk refs.
3445+ my $choice
3446+ = $alter_fk_method eq 'none' ? "not"
3447+ : $alter_fk_method eq 'auto' ? "automatically choose the method to"
3448+ : "use the $alter_fk_method method to";
3449+ print "Will $choice update foreign keys.\n";
3450+ }
3451+ else {
3452+ print "You did not specify --alter-foreign-keys-method, but there "
3453+ . "are foreign keys that reference the table. "
3454+ . "Please read the tool's documentation carefully.\n";
3455+ return 1;
3456+ }
3457+ }
3458+
3459+ # ########################################################################
3460+ # XXX
3461+ # Ready to begin the alter! Nothing has been changed on the server at
3462+ # this point; we've just checked and looked for things. Past this point,
3463+ # the code is live if --execute, else it's doing a --dry-run. Or, if
3464+ # the user didn't read the docs, we may bail out here.
3465+ # XXX
3466+ # ########################################################################
3467+ if ( $o->get('dry-run') ) {
3468+ print "Starting a dry run. $orig_tbl->{name} will not be altered. "
3469+ . "Specify --execute instead of --dry-run to alter the table.\n";
3470+ }
3471+ elsif ( $o->get('execute') ) {
3472+ print "Altering $orig_tbl->{name}...\n";
3473+ }
3474+ else {
3475+ print "Exiting without altering $orig_tbl->{name} because neither "
3476+ . "--dry-run nor --execute was specified. Please read the tool's "
3477+ . "documentation carefully before using this tool.\n";
3478+ return 1;
3479+ }
3480+
3481+ # ########################################################################
3482+ # Create a cleanup task object to undo changes (i.e. clean up) if the
3483+ # code dies, or we may call this explicitly at the end if all goes well.
3484+ # ########################################################################
3485+ my @cleanup_tasks;
3486+ my $cleanup = new CleanupTask(
3487+ sub {
3488+ # XXX We shouldn't copy $EVAL_ERROR here, but I found that
3489+ # errors are not re-thrown in tests. If you comment out this
3490+ # line and the die below, an error fails:
3491+ # not ok 5 - Doesn't try forever to find a new table name
3492+ # Failed test 'Doesn't try forever to find a new table name'
3493+ # at /Users/daniel/p/pt-osc-2.1.1/lib/PerconaTest.pm line 559.
3494+ # ''
3495+ # doesn't match '(?-xism:Failed to find a unique new table name)'
3496+ my $original_error = $EVAL_ERROR;
3497+ foreach my $task ( reverse @cleanup_tasks ) {
3498+ eval {
3499+ $task->();
3500+ };
3501+ if ( $EVAL_ERROR ) {
3502+ warn "Error cleaning up: $EVAL_ERROR\n";
3503+ }
3504+ }
3505+ die $original_error if $original_error; # rethrow original error
3506+ return;
3507+ }
3508+ );
3509+
3510+ # The last cleanup task is to report whether or not the orig table
3511+ # was altered.
3512+ push @cleanup_tasks, sub {
3513+ PTDEBUG && _d('Clean up done, report if orig table was altered');
3514+ if ( $o->get('dry-run') ) {
3515+ print "Dry run complete. $orig_tbl->{name} was not altered.\n";
3516+ }
3517+ else {
3518+ if ( $orig_tbl->{swapped} ) {
3519+ if ( $orig_tbl->{success} ) {
3520+ print "Successfully altered $orig_tbl->{name}.\n";
3521+ }
3522+ else {
3523+ print "Altered $orig_tbl->{name} but there were errors "
3524+ . "or warnings.\n";
3525+ }
3526+ }
3527+ else {
3528+ print "$orig_tbl->{name} was not altered.\n";
3529+ }
3530+ }
3531+ return;
3532+ };
3533+
3534+ # ########################################################################
3535+ # Check and create PID file if user specified --pid.
3536 # ########################################################################
3537 my $daemon;
3538- if ( $o->get('pid') ) {
3539+ if ( $o->get('execute') && $o->get('pid') ) {
3540 # We're not daemoninzing, it just handles PID stuff.
3541 $daemon = new Daemon(o=>$o);
3542 $daemon->make_PID_file();
3543 }
3544
3545- # ########################################################################
3546- # Setup/init some vars.
3547- # ########################################################################
3548- my $tmp_tbl = $o->get('tmp-table') || "__tmp_$tbl";
3549- my $old_tbl = "__old_$tbl"; # what tbl becomes after swapped with tmp tbl
3550- my %tables = (
3551- db => $db,
3552- tbl => $tbl,
3553- tmp_tbl => $tmp_tbl,
3554- old_tbl => $old_tbl,
3555- );
3556- msg("Alter table $tbl using temporary table $tmp_tbl");
3557-
3558- my %common_modules = (
3559- OptionParser => $o,
3560- DSNParser => $dp,
3561- Quoter => $q,
3562- TableParser => $tp,
3563- TableChunker => $chunker,
3564- VersionParser => $vp,
3565- );
3566-
3567- # ########################################################################
3568- # Create the capture-sync and copy-rows plugins. Currently, we just have
3569- # one method for each.
3570- # ########################################################################
3571- my $capture_sync = new OSCCaptureSync(Quoter => $q);
3572- my $copy_rows = new CopyRowsInsertSelect(
3573- Retry => new Retry(),
3574- Quoter => $q,
3575- );
3576-
3577- # More values are added later. These are the minimum need to do --cleanup.
3578- my %plugin_args = (
3579- dbh => $dbh,
3580- msg => \&msg, # so plugin can talk back to user
3581- print => $o->get('print'),
3582- %tables,
3583- %common_modules,
3584- );
3585-
3586- if ( my $sleep_time = $o->get('sleep') ) {
3587- PTDEBUG && _d("Sleep time:", $sleep_time);
3588- $plugin_args{sleep} = sub {
3589- my ( $chunkno ) = @_;
3590- PTDEBUG && _d("Sleeping after chunk", $chunkno);
3591- sleep($sleep_time);
3592+ # #####################################################################
3593+ # Step 1: Create the new table.
3594+ # #####################################################################
3595+ my $new_tbl;
3596+ eval {
3597+ $new_tbl = create_new_table(
3598+ orig_tbl => $orig_tbl,
3599+ suffix => '_new',
3600+ Cxn => $cxn,
3601+ Quoter => $q,
3602+ OptionParser => $o,
3603+ TableParser => $tp,
3604+ );
3605+ };
3606+ if ( $EVAL_ERROR ) {
3607+ die "Error creating new table: $EVAL_ERROR\n";
3608+ }
3609+
3610+ # If the new table still exists, drop it unless the tool was interrupted.
3611+ push @cleanup_tasks, sub {
3612+ PTDEBUG && _('Clean up new table');
3613+ my $new_tbl_exists = $tp->check_table(
3614+ dbh => $cxn->dbh(),
3615+ db => $new_tbl->{db},
3616+ tbl => $new_tbl->{tbl},
3617+ );
3618+ PTDEBUG && _d('New table exists:', $new_tbl_exists ? 'yes' : 'no');
3619+ return unless $new_tbl_exists;
3620+
3621+ my $sql = "DROP TABLE IF EXISTS $new_tbl->{name};";
3622+ if ( !$oktorun ) {
3623+ # The tool was interrupted, so do not drop the new table
3624+ # in case the user wants to resume (once resume capability
3625+ # is implemented).
3626+ print "Not dropping the new table $new_tbl->{name} because "
3627+ . "the tool was interrupted. To drop the new table, "
3628+ . "execute:\n$sql\n";
3629+ }
3630+ elsif ( $orig_tbl->{copied} && !$orig_tbl->{swapped} ) {
3631+ print "Not dropping the new table $new_tbl->{name} because "
3632+ . "--swap-tables failed. To drop the new table, "
3633+ . "execute:\n$sql\n";
3634+ }
3635+ else {
3636+ print "Dropping new table...\n";
3637+ print $sql, "\n" if $o->get('print');
3638+ PTDEBUG && _d($sql);
3639+ eval {
3640+ $cxn->dbh()->do($sql);
3641+ };
3642+ if ( $EVAL_ERROR ) {
3643+ warn "Error dropping new table $new_tbl->{name}: $EVAL_ERROR\n"
3644+ . "To try dropping the new table again, execute:\n$sql\n";
3645+ }
3646+ print "Dropped new table OK.\n";
3647+ }
3648+ };
3649+
3650+ # #####################################################################
3651+ # Step 2: Alter the new, empty table. This should be very quick,
3652+ # or die if the user specified a bad alter statement.
3653+ # #####################################################################
3654+ if ( my $alter = $o->get('alter') ) {
3655+ print "Altering new table...\n";
3656+ my $sql = "ALTER TABLE $new_tbl->{name} $alter";
3657+ print $sql, "\n" if $o->get('print');
3658+ PTDEBUG && _d($sql);
3659+ eval {
3660+ $cxn->dbh()->do($sql);
3661 };
3662- }
3663-
3664- # ########################################################################
3665- # Just cleanup and exit.
3666- # ########################################################################
3667- if ( $o->get('cleanup-and-exit') ) {
3668- msg("Calling " . (ref $copy_rows). "::cleanup()");
3669- $copy_rows->cleanup(%plugin_args);
3670-
3671- msg("Calling " . (ref $capture_sync) . "::cleanup()");
3672- $capture_sync->cleanup(%plugin_args);
3673-
3674- msg("$PROGRAM_NAME ending for --cleanup-and-exit");
3675- return 0;
3676- }
3677-
3678- # ########################################################################
3679- # Check that table can be altered.
3680- # ########################################################################
3681- my %tbl_info;
3682+ if ( $EVAL_ERROR ) {
3683+ die "Error altering new table $new_tbl->{name}: $EVAL_ERROR\n"
3684+ }
3685+ print "Altered $new_tbl->{name} OK.\n"
3686+ }
3687+
3688+ # Get the new table struct. This shouldn't die because
3689+ # we just created the table successfully so we know it's
3690+ # there. But the ghost of Ryan is everywhere.
3691+ my $ddl = $tp->get_create_table(
3692+ $cxn->dbh(),
3693+ $new_tbl->{db},
3694+ $new_tbl->{tbl},
3695+ );
3696+ $new_tbl->{tbl_struct} = $tp->parse($ddl);
3697+
3698+ # Determine what columns the original and new table share.
3699+ # If the user drops a col, that's easy: just don't copy it. If they
3700+ # add a column, it must have a default value. Other alterations
3701+ # may or may not affect the copy process--we'll know when we try!
3702+ # Note: we don't want to examine the --alter statement to see if the
3703+ # cols have changed because that's messy and prone to parsing errors.
3704+ # Col posn (position) is just for looks because user's like
3705+ # to see columns listed in their original order, not Perl's
3706+ # random hash key sorting.
3707+ my $col_posn = $orig_tbl->{tbl_struct}->{col_posn};
3708+ my $orig_cols = $orig_tbl->{tbl_struct}->{is_col};
3709+ my $new_cols = $new_tbl->{tbl_struct}->{is_col};
3710+ my @common_cols = sort { $col_posn->{$a} <=> $col_posn->{$b} }
3711+ grep { $new_cols->{$_} }
3712+ keys %$orig_cols;
3713+ PTDEBUG && _d('Common columns', @common_cols);
3714+
3715+ # ########################################################################
3716+ # Step 3: Create the triggers to capture changes on the original table and
3717+ # apply them to the new table.
3718+ # ########################################################################
3719+
3720+ # Drop the triggers. We can save this cleanup task before
3721+ # adding the triggers because if adding them fails, this will be
3722+ # called which will drop whichever triggers were created.
3723+ push @cleanup_tasks, sub {
3724+ PTDEBUG && _d('Clean up triggers');
3725+ if ( $oktorun ) {
3726+ drop_triggers(
3727+ tbl => $orig_tbl,
3728+ Cxn => $cxn,
3729+ Quoter => $q,
3730+ OptionParser => $o,
3731+ );
3732+ }
3733+ else {
3734+ print "Not dropping triggers because the tool was interrupted. "
3735+ . "To drop the triggers, execute:\n"
3736+ . join("\n", @drop_trigger_sqls) . "\n";
3737+ }
3738+ };
3739+
3740 eval {
3741- %tbl_info = check_tables(%plugin_args);
3742+ create_triggers(
3743+ orig_tbl => $orig_tbl,
3744+ new_tbl => $new_tbl,
3745+ columns => \@common_cols,
3746+ Cxn => $cxn,
3747+ Quoter => $q,
3748+ OptionParser => $o,
3749+ );
3750 };
3751 if ( $EVAL_ERROR ) {
3752- chomp $EVAL_ERROR;
3753- msg("Table $tbl cannot be altered: $EVAL_ERROR");
3754- return 1;
3755- }
3756-
3757- @plugin_args{keys %tbl_info} = values %tbl_info;
3758- msg("Table $tbl can be altered");
3759- msg("Chunk column $plugin_args{chunk_column}, index $plugin_args{chunk_index}");
3760-
3761- if ( $o->get('check-tables-and-exit') ) {
3762- msg("$PROGRAM_NAME ending for --check-tables-and-exit");
3763- return 0;
3764- }
3765-
3766- # #####################################################################
3767- # Chunk the table. If the checks pass, then this shouldn't fail.
3768- # #####################################################################
3769- my %range_stats = $chunker->get_range_statistics(
3770- dbh => $dbh,
3771- db => $db,
3772- tbl => $tbl,
3773- chunk_col => $plugin_args{chunk_column},
3774- tbl_struct => $plugin_args{tbl_struct},
3775- );
3776- my @chunks = $chunker->calculate_chunks(
3777- dbh => $dbh,
3778- db => $db,
3779- tbl => $tbl,
3780- chunk_col => $plugin_args{chunk_column},
3781- tbl_struct => $plugin_args{tbl_struct},
3782- chunk_size => $o->get('chunk-size'),
3783- %range_stats,
3784- );
3785- $plugin_args{chunks} = \@chunks;
3786- $plugin_args{Progress} = new Progress(
3787- jobsize => scalar @chunks,
3788- spec => $o->get('progress'),
3789- name => "Copying rows",
3790- );
3791- msg("Chunked table $tbl into " . scalar @chunks . " chunks");
3792-
3793- # #####################################################################
3794- # Get child tables if necessary.
3795- # #####################################################################
3796- my @child_tables;
3797- if ( my $child_tables = $o->get('child-tables') ) {
3798- if ( lc $child_tables eq 'auto_detect' ) {
3799- msg("Auto-detecting child tables of $tbl");
3800- @child_tables = get_child_tables(%plugin_args);
3801- msg("Child tables of $tables{old_tbl}: "
3802- . (@child_tables ? join(', ', @child_tables) : "(none)"));
3803- }
3804- else {
3805- @child_tables = split(',', $child_tables);
3806- msg("User-specified child tables: " . join(', ', @child_tables));
3807- }
3808- }
3809-
3810- # #####################################################################
3811- # Do the online alter.
3812- # #####################################################################
3813- if ( !$o->get('execute') ) {
3814- msg("Exiting without altering $db.$tbl because you did not "
3815- . "specify --execute. Please read the tool's documentation "
3816- . "carefully before using this tool.");
3817- return $exit_status;
3818- }
3819-
3820- msg("Starting online schema change");
3821- eval {
3822- my $sql = "";
3823-
3824- # #####################################################################
3825- # Create and alter the new table.
3826- # #####################################################################
3827- if ( $o->get('create-tmp-table') ) {
3828- $sql = "CREATE TABLE `$db`.`$tmp_tbl` LIKE `$db`.`$tbl`";
3829- msg($sql);
3830- $dbh->do($sql) unless $o->get('print');
3831- }
3832-
3833- if ( my $alter = $o->get('alter') ) {
3834- my @stmts;
3835- if ( -f $alter && -r $alter ) {
3836- msg("Reading ALTER TABLE statements from file $alter");
3837- open my $fh, '<', $alter or die "Cannot open $alter: $OS_ERROR";
3838- @stmts = <$fh>;
3839- close $fh;
3840+ die "Error creating triggers: $EVAL_ERROR\n";
3841+ };
3842+
3843+ # #####################################################################
3844+ # Step 4: Copy rows.
3845+ # #####################################################################
3846+
3847+ # The hashref of callbacks below is what NibbleIterator calls internally
3848+ # to do all the copy work. The callbacks do not need to eval their work
3849+ # because the higher call to $nibble_iter->next() is eval'ed which will
3850+ # catch any errors in the callbacks.
3851+ my $total_rows = 0;
3852+ my $total_time = 0;
3853+ my $avg_rate = 0; # rows/second
3854+ my $retry = new Retry(); # for retrying to exec the copy statement
3855+ my $limit = $o->get('chunk-size-limit'); # brevity
3856+ my $chunk_time = $o->get('chunk-time'); # brevity
3857+
3858+ my $callbacks = {
3859+ init => sub {
3860+ my (%args) = @_;
3861+ my $tbl = $args{tbl};
3862+ my $nibble_iter = $args{NibbleIterator};
3863+
3864+ if ( $o->get('dry-run') ) {
3865+ print "Not copying rows because this is a dry run.\n";
3866 }
3867 else {
3868- @stmts = split(';', $alter);
3869- }
3870-
3871- foreach my $stmt ( @stmts ) {
3872- $sql = "ALTER TABLE `$db`.`$tmp_tbl` $stmt";
3873- msg($sql);
3874- $dbh->do($sql) unless $o->get('print');
3875+ print "Copying approximately ", $nibble_iter->row_estimate(),
3876+ " rows...\n";
3877+ }
3878+
3879+ if ( $o->get('print') ) {
3880+ # Print the checksum and next boundary statements.
3881+ my $statements = $nibble_iter->statements();
3882+ foreach my $sth ( sort keys %$statements ) {
3883+ next if $sth =~ m/^explain/;
3884+ if ( $statements->{$sth} ) {
3885+ print $statements->{$sth}->{Statement}, "\n";
3886+ }
3887+ }
3888+ }
3889+
3890+ return unless $o->get('execute');
3891+
3892+ # If table is a single chunk on the master, make sure it's also
3893+ # a single chunk on all slaves. E.g. if a slave is out of sync
3894+ # and has a lot more rows than the master, single chunking on the
3895+ # master could cause the slave to choke.
3896+ if ( $nibble_iter->one_nibble() ) {
3897+ PTDEBUG && _d('Getting table row estimate on replicas');
3898+ my @too_large;
3899+ foreach my $slave ( @$slaves ) {
3900+ my ($n_rows) = NibbleIterator::get_row_estimate(
3901+ Cxn => $slave,
3902+ tbl => $tbl,
3903+ );
3904+ PTDEBUG && _d('Table on',$slave->name(),'has', $n_rows, 'rows');
3905+ if ( $n_rows && $n_rows > ($tbl->{chunk_size} * $limit) ) {
3906+ PTDEBUG && _d('Table too large on', $slave->name());
3907+ push @too_large, [$slave->name(), $n_rows || 0];
3908+ }
3909+ }
3910+ if ( @too_large ) {
3911+ my $msg
3912+ = "Cannot copy table $tbl->{name} because"
3913+ . " on the master it would be checksummed in one chunk"
3914+ . " but on these replicas it has too many rows:\n";
3915+ foreach my $info ( @too_large ) {
3916+ $msg .= " $info->[1] rows on $info->[0]\n";
3917+ }
3918+ $msg .= "The current chunk size limit is "
3919+ . ($tbl->{chunk_size} * $limit)
3920+ . " rows (chunk size=$tbl->{chunk_size}"
3921+ . " * chunk size limit=$limit).\n";
3922+ die $msg;
3923+ }
3924+ }
3925+
3926+ return 1; # continue nibbling table
3927+ },
3928+ next_boundaries => sub {
3929+ my (%args) = @_;
3930+ my $tbl = $args{tbl};
3931+ my $nibble_iter = $args{NibbleIterator};
3932+ my $sth = $nibble_iter->statements();
3933+ my $boundary = $nibble_iter->boundaries();
3934+
3935+ return 0 if $o->get('dry-run');
3936+ return 1 if $nibble_iter->one_nibble();
3937+
3938+ # Check that MySQL will use the nibble index for the next upper
3939+ # boundary sql. This check applies to the next nibble. So if
3940+ # the current nibble number is 5, then nibble 5 is already done
3941+ # and we're checking nibble number 6.
3942+ my $expl = explain_statement(
3943+ tbl => $tbl,
3944+ sth => $sth->{explain_upper_boundary},
3945+ vals => [ @{$boundary->{lower}}, $nibble_iter->chunk_size() ],
3946+ );
3947+ if (lc($expl->{key} || '') ne lc($nibble_iter->nibble_index() || '')) {
3948+ my $msg
3949+ = "Aborting copying table $tbl->{name} at chunk "
3950+ . ($nibble_iter->nibble_number() + 1)
3951+ . " because it is not safe to ascend. Chunking should "
3952+ . "use the "
3953+ . ($nibble_iter->nibble_index() || '?')
3954+ . " index, but MySQL EXPLAIN reports that "
3955+ . ($expl->{key} ? "the $expl->{key}" : "no")
3956+ . " index will be used for "
3957+ . $sth->{upper_boundary}->{Statement}
3958+ . " with values "
3959+ . join(", ", map { defined $_ ? $_ : "NULL" }
3960+ (@{$boundary->{lower}}, $nibble_iter->chunk_size()))
3961+ . "\n";
3962+ die $msg;
3963 }
3964- }
3965-
3966- # #####################################################################
3967- # Determine what columns the two tables have in common.
3968- # #####################################################################
3969- my @columns;
3970- # If --print is in effect, then chances are the new table wasn't
3971- # created above, so we can't get it's struct.
3972- # TODO: check if the new table exists because user might have created
3973- # it manually.
3974- if ( !$o->get('print') ) {
3975- my $tmp_tbl_struct = $tp->parse(
3976- $tp->get_create_table($dbh, $db, $tmp_tbl));
3977-
3978- @columns = intersection([
3979- $plugin_args{tbl_struct}->{is_col},
3980- $tmp_tbl_struct->{is_col},
3981- ]);
3982-
3983- # Order columns according to new table because people like/expect
3984- # to see things in a certain order (this has been an issue before).
3985- # This just matters to us; does't make a difference to MySQL.
3986- my $col_posn = $plugin_args{tbl_struct}->{col_posn};
3987- @columns = sort { $col_posn->{$a} <=> $col_posn->{$b} } @columns;
3988- msg("Shared columns: " . join(', ', @columns));
3989- }
3990- $plugin_args{columns} = \@columns;
3991-
3992- # #####################################################################
3993- # Start capturing changes to the new table.
3994- # #####################################################################
3995- msg("Calling " . (ref $capture_sync) . "::capture()");
3996- $capture_sync->capture(%plugin_args);
3997-
3998- # #####################################################################
3999- # Copy rows from new table to old table.
4000- # #####################################################################
4001- msg("Calling " . (ref $copy_rows) . "::copy()");
4002- $copy_rows->copy(
4003- from_table => $q->quote($db, $tbl),
4004- to_table => $q->quote($db, $tmp_tbl),
4005- %plugin_args
4006- );
4007-
4008- # #####################################################################
4009- # Sync tables.
4010- # #####################################################################
4011- msg("Calling " . (ref $capture_sync) . "::sync()");
4012- $capture_sync->sync(%plugin_args);
4013-
4014- # #####################################################################
4015- # Rename tables.
4016- # #####################################################################
4017- if ( $o->get('rename-tables') ) {
4018- msg("Renaming tables");
4019- $sql = "RENAME TABLE `$db`.`$tbl` TO `$db`.`$old_tbl`,"
4020- . " `$db`.`$tmp_tbl` TO `$db`.`$tbl`";
4021- msg($sql);
4022- $dbh->do($sql) unless $o->get('print');
4023- msg("Original table $tbl renamed to $old_tbl");
4024- }
4025-
4026- # #####################################################################
4027- # Update foreign key constraints if there are child tables.
4028- # #####################################################################
4029- if ( @child_tables ) {
4030- msg("Renaming foreign key constraints in child table");
4031- if ( $rename_fk_method eq 'rebuild_constraints' ) {
4032- update_foreign_key_constraints(
4033- child_tables => \@child_tables,
4034- %plugin_args,
4035- );
4036- }
4037- elsif ( $rename_fk_method eq 'drop_old_table' ) {
4038- $sql = "SET foreign_key_checks=0";
4039- msg($sql);
4040- $dbh->do($sql) unless $o->get('print');
4041-
4042- $sql = "DROP TABLE IF EXISTS `$db`.`$tbl`";
4043- msg($sql);
4044- $dbh->do($sql) unless $o->get('print');
4045-
4046- $sql = "RENAME TABLE `$db`.`$tmp_tbl` TO `$db`.`$tbl`";
4047- msg($sql);
4048- $dbh->do($sql) unless $o->get('print');
4049+
4050+ # Once nibbling begins for a table, control does not return to this
4051+ # tool until nibbling is done because, as noted above, all work is
4052+ # done in these callbacks. This callback is the only place where we
4053+ # can prematurely stop nibbling by returning false. This allows
4054+ # Ctrl-C to stop the tool between nibbles instead of between tables.
4055+ return $oktorun; # continue nibbling table?
4056+ },
4057+ exec_nibble => sub {
4058+ my (%args) = @_;
4059+ my $tbl = $args{tbl};
4060+ my $nibble_iter = $args{NibbleIterator};
4061+ my $sth = $nibble_iter->statements();
4062+ my $boundary = $nibble_iter->boundaries();
4063+
4064+ return if $o->get('dry-run');
4065+
4066+ # Count every chunk, even if it's ultimately skipped, etc.
4067+ $tbl->{results}->{n_chunks}++;
4068+
4069+ # If the table is being chunk (i.e., it's not small enough to be
4070+ # consumed by one nibble), then check index usage and chunk size.
4071+ if ( !$nibble_iter->one_nibble() ) {
4072+ my $expl = explain_statement(
4073+ tbl => $tbl,
4074+ sth => $sth->{explain_nibble},
4075+ vals => [ @{$boundary->{lower}}, @{$boundary->{upper}} ],
4076+ );
4077+
4078+ # Ensure that MySQL is using the chunk index.
4079+ if ( lc($expl->{key} || '')
4080+ ne lc($nibble_iter->nibble_index() || '') ) {
4081+ my $msg
4082+ = "Aborting copying table $tbl->{name} at chunk "
4083+ . $nibble_iter->nibble_number()
4084+ . " because it is not safe to chunk. Chunking should "
4085+ . "use the "
4086+ . ($nibble_iter->nibble_index() || '?')
4087+ . " index, but MySQL EXPLAIN reports that "
4088+ . ($expl->{key} ? "the $expl->{key}" : "no")
4089+ . " index will be used for "
4090+ . $sth->{explain_nibble}->{Statement}
4091+ . " with values "
4092+ . join(", ", map { defined $_ ? $_ : "NULL" }
4093+ (@{$boundary->{lower}}, @{$boundary->{upper}}))
4094+ . "\n";
4095+ die $msg;
4096+ }
4097+
4098+ # Check chunk size limit if the upper boundary and next lower
4099+ # boundary are identical.
4100+ if ( $limit ) {
4101+ my $boundary = $nibble_iter->boundaries();
4102+ my $oversize_chunk
4103+ = $limit ? ($expl->{rows} || 0) >= $tbl->{chunk_size} * $limit
4104+ : 0;
4105+ if ( $oversize_chunk
4106+ && $nibble_iter->identical_boundaries(
4107+ $boundary->{upper}, $boundary->{next_lower}) )
4108+ {
4109+ my $msg
4110+ = "Aborting copying table $tbl->{name} at chunk "
4111+ . $nibble_iter->nibble_number()
4112+ . " because the chunk is too large: MySQL estimates "
4113+ . ($expl->{rows} || 0) . "rows. The current chunk "
4114+ . "size limit is " . ($tbl->{chunk_size} * $limit)
4115+ . " rows (chunk size=$tbl->{chunk_size}"
4116+ . " * chunk size limit=$limit).\n";
4117+ die $msg;
4118+ }
4119+ }
4120+ }
4121+
4122+ # Exec and time the chunk checksum query.
4123+ $tbl->{nibble_time} = exec_nibble(
4124+ %args,
4125+ Retry => $retry,
4126+ Quoter => $q,
4127+ OptionParser => $o,
4128+ );
4129+ PTDEBUG && _d('Nibble time:', $tbl->{nibble_time});
4130+
4131+ # We're executing REPLACE queries which don't return rows.
4132+ # Returning 0 from this callback causes the nibble iter to
4133+ # get the next boundaries/nibble.
4134+ return 0;
4135+ },
4136+ after_nibble => sub {
4137+ my (%args) = @_;
4138+ my $tbl = $args{tbl};
4139+ my $nibble_iter = $args{NibbleIterator};
4140+
4141+ return unless $o->get('execute');
4142+
4143+ # Update rate, chunk size, and progress if the nibble actually
4144+ # selected some rows.
4145+ my $cnt = $tbl->{row_cnt};
4146+ if ( ($cnt || 0) > 0 ) {
4147+ # Update the rate of rows per second for the entire server.
4148+ # This is used for the initial chunk size of the next table.
4149+ $total_rows += $cnt;
4150+ $total_time += $tbl->{nibble_time};
4151+ $avg_rate = int($total_rows / $total_time);
4152+ PTDEBUG && _d('Average copy rate (rows/s):', $avg_rate);
4153+
4154+ # Adjust chunk size. This affects the next chunk.
4155+ if ( $chunk_time ) {
4156+ # Calcuate a new chunk-size based on the rate of rows/s.
4157+ $tbl->{chunk_size} = $tbl->{rate}->update(
4158+ $cnt, # processed this many rows
4159+ $tbl->{nibble_time}, # is this amount of time
4160+ );
4161+
4162+ if ( $tbl->{chunk_size} < 1 ) {
4163+ # This shouldn't happen. WeightedAvgRate::update() may
4164+ # return a value < 1, but minimum chunk size is 1.
4165+ $tbl->{chunk_size} = 1;
4166+
4167+ # This warning is printed once per table.
4168+ if ( !$tbl->{warned_slow} ) {
4169+ warn "Rows are copying very slowly. "
4170+ . "--chunk-size has been automatically reduced to 1. "
4171+ . "Check that the server is not being overloaded, "
4172+ . "or increase --chunk-time. The last chunk "
4173+ . "selected $cnt rows and took "
4174+ . sprintf('%.3f', $tbl->{nibble_time})
4175+ . " seconds to execute.\n";
4176+ $tbl->{warned_slow} = 1;
4177+ }
4178+ }
4179+
4180+ # Update chunk-size based on the rate of rows/s.
4181+ $nibble_iter->set_chunk_size($tbl->{chunk_size});
4182+ }
4183+
4184+ # Every table should have a Progress obj; update it.
4185+ if ( my $tbl_pr = $tbl->{progress} ) {
4186+ $tbl_pr->update( sub { return $total_rows } );
4187+ }
4188+ }
4189+
4190+ # Wait forever for slaves to catch up.
4191+ $replica_lag_pr->start() if $replica_lag_pr;
4192+ $replica_lag->wait(Progress => $replica_lag_pr);
4193+
4194+ # Wait forever for system load to abate. wait() will die if
4195+ # --critical load is reached.
4196+ $sys_load_pr->start() if $sys_load_pr;
4197+ $sys_load->wait(Progress => $sys_load_pr);
4198+
4199+ return;
4200+ },
4201+ done => sub {
4202+ if ( $o->get('execute') ) {
4203+ print "Copied rows OK.\n";
4204+ }
4205+ },
4206+ };
4207+
4208+ # NibbleIterator combines these two statements and adds
4209+ # "FROM $orig_table->{name} WHERE <nibble stuff>".
4210+ my $dml = "INSERT LOW_PRIORITY IGNORE INTO $new_tbl->{name} "
4211+ . "(" . join(', ', map { $q->quote($_) } @common_cols) . ") "
4212+ . "SELECT";
4213+ my $select = join(', ', map { $q->quote($_) } @common_cols);
4214+
4215+ # The chunk size is auto-adjusted, so use --chunk-size as
4216+ # the initial value, but then save and update the adjusted
4217+ # chunk size in the table data struct.
4218+ $orig_tbl->{chunk_size} = $o->get('chunk-size');
4219+
4220+ # This won't (shouldn't) fail because we already verified in
4221+ # check_orig_table() table we can NibbleIterator::can_nibble().
4222+ my $nibble_iter = new NibbleIterator(
4223+ Cxn => $cxn,
4224+ tbl => $orig_tbl,
4225+ chunk_size => $orig_tbl->{chunk_size},
4226+ chunk_index => $o->get('chunk-index'),
4227+ dml => $dml,
4228+ select => $select,
4229+ callbacks => $callbacks,
4230+ OptionParser => $o,
4231+ Quoter => $q,
4232+ TableParser => $tp,
4233+ TableNibbler => new TableNibbler(TableParser => $tp, Quoter => $q),
4234+ comments => {
4235+ bite => "pt-online-schema-change $PID copy table",
4236+ nibble => "pt-online-schema-change $PID copy nibble",
4237+ },
4238+ );
4239+
4240+ # Init a new weighted avg rate calculator for the table.
4241+ $orig_tbl->{rate} = new WeightedAvgRate(target_t => $chunk_time);
4242+
4243+ # Make a Progress obj for this table. It may not be used;
4244+ # depends on how many rows, chunk size, how fast the server
4245+ # is, etc. But just in case, all tables have a Progress obj.
4246+ if ( $o->get('progress')
4247+ && !$nibble_iter->one_nibble()
4248+ && $nibble_iter->row_estimate() )
4249+ {
4250+ $orig_tbl->{progress} = new Progress(
4251+ jobsize => $nibble_iter->row_estimate(),
4252+ spec => $o->get('progress'),
4253+ name => "Copying $orig_tbl->{name}",
4254+ );
4255+ }
4256+
4257+ # Start copying rows. This may take awhile, but --progress is on
4258+ # by default so there will be progress updates to stderr.
4259+ eval {
4260+ 1 while $nibble_iter->next();
4261+ };
4262+ if ( $EVAL_ERROR ) {
4263+ die "Error copying rows from $orig_tbl->{name} to "
4264+ . "$new_tbl->{name}: $EVAL_ERROR\n";
4265+ }
4266+ $orig_tbl->{copied} = 1; # flag for cleanup tasks
4267+
4268+
4269+ # XXX Auto-choose the alter fk method BEFORE swapping/renaming tables
4270+ # else everything will break because if drop_swap is chosen, then we
4271+ # most NOT rename tables or drop the old table.
4272+ if ( $alter_fk_method eq 'auto' ) {
4273+ # If chunk time is set, then use the average rate of rows/s
4274+ # from copying the orig table to determine the max size of
4275+ # a child table that can be altered within one chunk time.
4276+ # The limit is a fudge factor. Chunk time won't be set if
4277+ # the user specified --chunk-size=N on the cmd line, in which
4278+ # case the max child table size is their specified chunk size
4279+ # times the fudge factor.
4280+ my $max_rows
4281+ = $o->get('dry-run') ? $o->get('chunk-size') * $limit
4282+ : $chunk_time ? $avg_rate * $chunk_time * $limit
4283+ : $o->get('chunk-size') * $limit;
4284+ PTDEBUG && _d('Max allowed child table size:', $max_rows);
4285+
4286+ $alter_fk_method = determine_alter_fk_method(
4287+ child_tables => $child_tables,
4288+ max_rows => $max_rows,
4289+ Cxn => $cxn,
4290+ OptionParser => $o,
4291+ );
4292+
4293+ if ( $alter_fk_method eq 'drop_swap' ) {
4294+ $o->set('swap-tables', 0);
4295+ $o->set('drop-old-table', 0);
4296+ }
4297+ }
4298+
4299+ # #####################################################################
4300+ # XXX
4301+ # Step 5: Rename tables: orig -> old, new -> orig
4302+ # Past this step, the original table has been altered. This shouldn't
4303+ # fail, but if it does, the failure could be serious depending on what
4304+ # state the tables are left in.
4305+ # XXX
4306+ # #####################################################################
4307+ my $old_tbl;
4308+ if ( $o->get('swap-tables') ) {
4309+ eval {
4310+ $old_tbl = swap_tables(
4311+ orig_tbl => $orig_tbl,
4312+ new_tbl => $new_tbl,
4313+ suffix => '_old',
4314+ Cxn => $cxn,
4315+ Quoter => $q,
4316+ OptionParser => $o,
4317+ );
4318+ };
4319+ if ( $EVAL_ERROR ) {
4320+ die "Error swapping the tables: $EVAL_ERROR\n"
4321+ . "Verify that the original table $orig_tbl->{name} has not "
4322+ . "been modified or renamed to the old table $old_tbl->{name}. "
4323+ . "Then drop the new table $new_tbl->{name} if it exists.\n";
4324+ }
4325+ }
4326+ $orig_tbl->{swapped} = 1; # flag for cleanup tasks
4327+ PTDEBUG && _d('Old table:', Dumper($old_tbl));
4328+
4329+ # #####################################################################
4330+ # Step 6: Update foreign key constraints if there are child tables.
4331+ # #####################################################################
4332+ if ( $child_tables ) {
4333+ eval {
4334+ if ( $alter_fk_method eq 'none' ) {
4335+ print "Not updating foreign keys because "
4336+ . "--alter-foreign-keys-method=none. Foreign keys "
4337+ . "that reference the table will no longer work.\n";
4338+ }
4339+ elsif ( $alter_fk_method eq 'rebuild_constraints' ) {
4340+ rebuild_constraints(
4341+ orig_tbl => $orig_tbl,
4342+ old_tbl => $old_tbl,
4343+ child_tables => $child_tables,
4344+ OptionParser => $o,
4345+ Quoter => $q,
4346+ Cxn => $cxn,
4347+ TableParser => $tp,
4348+ );
4349+ }
4350+ elsif ( $alter_fk_method eq 'drop_swap' ) {
4351+ drop_swap(
4352+ orig_tbl => $orig_tbl,
4353+ new_tbl => $new_tbl,
4354+ Cxn => $cxn,
4355+ OptionParser => $o,
4356+ );
4357 }
4358 else {
4359- die "Invalid --update-foreign-keys-method value: $rename_fk_method";
4360- }
4361- }
4362-
4363- # #####################################################################
4364- # Cleanup.
4365- # #####################################################################
4366- msg("Calling " . (ref $copy_rows). "::cleanup()");
4367- $copy_rows->cleanup(%plugin_args);
4368-
4369- msg("Calling " . (ref $capture_sync) . "::cleanup()");
4370- $capture_sync->cleanup(%plugin_args);
4371-
4372- if ( $o->get('rename-tables') && $o->get('drop-old-table') ) {
4373- $sql = "DROP TABLE IF EXISTS `$db`.`$old_tbl`";
4374- msg($sql);
4375- $dbh->do($sql) unless $o->get('print');
4376- }
4377- };
4378- if ( $EVAL_ERROR ) {
4379- warn "An error occurred:\n\n$EVAL_ERROR\n"
4380- . "Some triggers, temp tables, etc. may not have been removed. "
4381- . "Run with --cleanup-and-exit to remove these items.\n";
4382- $exit_status = 1;
4383- }
4384-
4385- msg("$PROGRAM_NAME ended, exit status $exit_status");
4386+ # This should "never" happen because we check this var earlier.
4387+ die "Invalid --alter-foreign-keys-method: $alter_fk_method\n";
4388+ }
4389+ };
4390+ if ( $EVAL_ERROR ) {
4391+ # TODO: improve error message and handling.
4392+ die "Error updating foreign key constraints: $EVAL_ERROR\n";
4393+ }
4394+ }
4395+
4396+ # ########################################################################
4397+ # Step 7: Drop the old table.
4398+ # ########################################################################
4399+ if ( $o->get('drop-old-table') ) {
4400+ if ( $o->get('dry-run') ) {
4401+ print "Not dropping old table because this is a dry run.\n";
4402+ }
4403+ else {
4404+ print "Dropping old table...\n";
4405+
4406+ if ( $alter_fk_method eq 'none' ) {
4407+ # Child tables still reference the old table, but the user
4408+ # has chosen to break fks, so we need to disable fk checks
4409+ # in order to drop the old table.
4410+ my $sql = "SET foreign_key_checks=0";
4411+ PTDEBUG && _d($sql);
4412+ print $sql, "\n" if $o->get('print');
4413+ $cxn->dbh()->do($sql);
4414+ }
4415+
4416+ my $sql = "DROP TABLE IF EXISTS $old_tbl->{name}";
4417+ print $sql, "\n" if $o->get('print');
4418+ PTDEBUG && _d($sql);
4419+ eval {
4420+ $cxn->dbh()->do($sql);
4421+ };
4422+ if ( $EVAL_ERROR ) {
4423+ die "Error dropping the old table: $EVAL_ERROR\n";
4424+ }
4425+ print "Dropped old table $old_tbl->{name} OK.\n";
4426+ }
4427+ }
4428+
4429+ # ########################################################################
4430+ # Done.
4431+ # ########################################################################
4432+ $orig_tbl->{success} = 1; # flag for cleanup tasks
4433+ $cleanup = undef; # exec cleanup tasks
4434+
4435 return $exit_status;
4436 }
4437
4438 # ############################################################################
4439 # Subroutines.
4440 # ############################################################################
4441-sub check_tables {
4442+sub create_new_table{
4443+ my (%args) = @_;
4444+ my @required_args = qw(orig_tbl Cxn Quoter OptionParser TableParser);
4445+ foreach my $arg ( @required_args ) {
4446+ die "I need a $arg argument" unless $args{$arg};
4447+ }
4448+ my ($orig_tbl, $cxn, $q, $o, $tp) = @args{@required_args};
4449+
4450+ # Get the original table struct.
4451+ my $ddl = $tp->get_create_table(
4452+ $cxn->dbh(),
4453+ $orig_tbl->{db},
4454+ $orig_tbl->{tbl},
4455+ );
4456+
4457+ my $tries = $args{tries} || 10; # don't try forever
4458+ my $prefix = $args{prefix} || '_';
4459+ my $suffix = $args{suffix} || '_new';
4460+ my $table_name = $orig_tbl->{tbl} . $suffix;
4461+
4462+ print "Creating new table...\n";
4463+ my $tryno = 1;
4464+ my @old_tables;
4465+ while ( $tryno++ < $tries ) {
4466+ $table_name = $prefix . $table_name;
4467+ my $quoted = $q->quote($orig_tbl->{db}, $table_name);
4468+
4469+ # Generate SQL to create the new table. We do not use CREATE TABLE LIKE
4470+ # because it doesn't preserve foreign key constraints. Here we need to
4471+ # rename the FK constraints, too. This is because FK constraints are
4472+ # internally stored as <database>.<constraint> and there cannot be
4473+ # duplicates. If we don't rename the constraints, then InnoDB will throw
4474+ # error 121 (duplicate key violation) when we try to execute the CREATE
4475+ # TABLE. TODO: this code isn't perfect. If we rename a constraint from
4476+ # foo to _foo and there is already a constraint with that name in this
4477+ # or another table, we can still have a collision. But if there are
4478+ # multiple FKs on this table, it's hard to know which one is causing the
4479+ # trouble. Should we generate random/UUID FK names or something instead?
4480+ my $sql = $ddl;
4481+ $sql =~ s/\ACREATE TABLE .*?\($/CREATE TABLE $quoted (/m;
4482+ $sql =~ s/^ CONSTRAINT `/ CONSTRAINT `_/gm;
4483+ PTDEBUG && _d($sql);
4484+ eval {
4485+ $cxn->dbh()->do($sql);
4486+ };
4487+ if ( $EVAL_ERROR ) {
4488+ # Ignore this error because if multiple instances of the tool
4489+ # are running, or previous runs failed and weren't cleaned up,
4490+ # then there will be other similarly named tables with fewer
4491+ # leading prefix chars. Or, in rarer cases, the db just happens
4492+ # to have a similarly named table created by the user for other
4493+ # purposes.
4494+ if ( $EVAL_ERROR =~ m/table.+?already exists/i ) {
4495+ push @old_tables, $q->quote($orig_tbl->{db}, $table_name);
4496+ next;
4497+ }
4498+
4499+ # Some other error happened. Let the caller catch it.
4500+ die $EVAL_ERROR;
4501+ }
4502+ print $sql, "\n" if $o->get('print'); # the sql that work
4503+ print "Created new table $orig_tbl->{db}.$table_name OK.\n";
4504+ return { # success
4505+ db => $orig_tbl->{db},
4506+ tbl => $table_name,
4507+ name => $q->quote($orig_tbl->{db}, $table_name),
4508+ };
4509+ }
4510+
4511+ die "Failed to find a unique new table name after $tries attemps. "
4512+ . "The following tables exist which may be left over from previous "
4513+ . "failed runs of the tool:\n"
4514+ . join("\n", map { " $_" } @old_tables)
4515+ . "\nExamine these tables and drop some or all of them if they are "
4516+ . "no longer need, then re-run the tool.\n";
4517+}
4518+
4519+sub swap_tables {
4520+ my (%args) = @_;
4521+ my @required_args = qw(orig_tbl new_tbl Cxn Quoter OptionParser);
4522+ foreach my $arg ( @required_args ) {
4523+ die "I need a $arg argument" unless $args{$arg};
4524+ }
4525+ my ($orig_tbl, $new_tbl, $cxn, $q, $o) = @args{@required_args};
4526+
4527+ my $prefix = '_';
4528+ my $table_name = $orig_tbl->{tbl} . ($args{suffix} || '');
4529+ my $tries = 10; # don't try forever
4530+
4531+ # This sub only works for --execute. Since the options are
4532+ # mutually exclusive and we return in the if case, the elsif
4533+ # is just a paranoid check because swapping the tables is one
4534+ # of the most sensitive/dangerous operations.
4535+ if ( $o->get('dry-run') ) {
4536+ print "Not swapping tables because this is a dry run.\n";
4537+
4538+ # A return value really isn't needed, but this trick allows
4539+ # rebuild_constraints() to parse and show the sql statements
4540+ # it would used. Otherwise, this has no effect.
4541+ return $orig_tbl;
4542+ }
4543+ elsif ( $o->get('execute') ) {
4544+ print "Swapping tables...\n";
4545+
4546+ while ( $tries-- ) {
4547+ $table_name = $prefix . $table_name;
4548+ my $sql = "RENAME TABLE $orig_tbl->{name} "
4549+ . "TO " . $q->quote($orig_tbl->{db}, $table_name)
4550+ . ", $new_tbl->{name} TO $orig_tbl->{name}";
4551+ PTDEBUG && _d($sql);
4552+ eval {
4553+ $cxn->dbh()->do($sql);
4554+ };
4555+ if ( $EVAL_ERROR ) {
4556+ # Ignore this error because if multiple instances of the tool
4557+ # are running, or previous runs failed and weren't cleaned up,
4558+ # then there will be other similarly named tables with fewer
4559+ # leading prefix chars. Or, in rarer cases, the db just happens
4560+ # to have a similarly named table created by the user for other
4561+ # purposes.
4562+ next if $EVAL_ERROR =~ m/table.+?already exists/i;
4563+
4564+ # Some other error happened. Let caller catch it.
4565+ die $EVAL_ERROR;
4566+ }
4567+ print $sql, "\n" if $o->get('print');
4568+ print "Swapped original and new tables OK.\n";
4569+ return { # success
4570+ db => $orig_tbl->{db},
4571+ tbl => $table_name,
4572+ name => $q->quote($orig_tbl->{db}, $table_name),
4573+ };
4574+ }
4575+
4576+ # This shouldn't happen.
4577+ # Here and in the attempt to find a new table name we probably ought to
4578+ # use --retries (and maybe a Retry object?)
4579+ die "Failed to find a unique old table name after serveral attempts.\n";
4580+ }
4581+}
4582+
4583+sub check_orig_table {
4584 my ( %args ) = @_;
4585- my @required_args = qw(dbh db tbl tmp_tbl old_tbl VersionParser Quoter TableParser OptionParser TableChunker);
4586+ my @required_args = qw(orig_tbl Cxn TableParser OptionParser Quoter);
4587 foreach my $arg ( @required_args ) {
4588 die "I need a $arg argument" unless $args{$arg};
4589 }
4590- my ($dbh, $db, $tbl, $tmp_tbl, $old_tbl, $o, $tp)
4591- = @args{qw(dbh db tbl tmp_tbl old_tbl OptionParser TableParser)};
4592-
4593- msg("Checking if table $tbl can be altered");
4594- my %tbl_info;
4595- my $sql = "";
4596-
4597- # ########################################################################
4598- # Check MySQL.
4599- # ########################################################################
4600- # Although triggers were introduced in 5.0.2, "Prior to MySQL 5.0.10,
4601- # triggers cannot contain direct references to tables by name."
4602- if ( !$args{VersionParser}->version_ge($dbh, '5.0.10') ) {
4603- die "This tool requires MySQL 5.0.10 or newer\n";
4604- }
4605-
4606- # ########################################################################
4607- # Check the (original) table.
4608- # ########################################################################
4609- # The table must exist of course.
4610- if ( !$tp->check_table(dbh=>$dbh, db=>$db, tbl=>$tbl) ) {
4611- die "Table $db.$tbl does not exist\n";
4612- }
4613-
4614- # There cannot be any triggers on the table.
4615- $sql = "SHOW TRIGGERS FROM `$db` LIKE '$tbl'";
4616- msg($sql);
4617+ my ($orig_tbl, $cxn, $tp, $o, $q) = @args{@required_args};
4618+
4619+ my $dbh = $cxn->dbh();
4620+
4621+ # The original table must exist, of course.
4622+ if (!$tp->check_table(dbh=>$dbh,db=>$orig_tbl->{db},tbl=>$orig_tbl->{tbl})) {
4623+ die "The original table $orig_tbl->{name} does not exist.\n";
4624+ }
4625+
4626+ # There cannot be any triggers on the original table.
4627+ my $sql = 'SHOW TRIGGERS FROM ' . $q->quote($orig_tbl->{db})
4628+ . ' LIKE ' . $q->literal_like($orig_tbl->{tbl});
4629+ PTDEBUG && _d($sql);
4630 my $triggers = $dbh->selectall_arrayref($sql);
4631 if ( $triggers && @$triggers ) {
4632- die "Table $db.$tbl has triggers. This tool needs to create "
4633- . "its own triggers, so the table cannot already have triggers.\n";
4634+ die "The table $orig_tbl->{name} has triggers. This tool "
4635+ . "needs to create its own triggers, so the table cannot "
4636+ . "already have triggers.\n";
4637 }
4638
4639- # For now, we require that the old table has an exact-chunkable
4640- # column (i.e. unique single-column).
4641- $tbl_info{tbl_struct} = $tp->parse($tp->get_create_table($dbh, $db, $tbl));
4642- my ($exact, @chunkable_cols) = $args{TableChunker}->find_chunk_columns(
4643- tbl_struct => $tbl_info{tbl_struct},
4644- exact => 1,
4645+ # Get the table struct. NibbleIterator needs this, and so do we.
4646+ my $ddl = $tp->get_create_table(
4647+ $cxn->dbh(),
4648+ $orig_tbl->{db},
4649+ $orig_tbl->{tbl},
4650 );
4651- if ( !$exact || !@chunkable_cols ) {
4652- die "Table $db.$tbl cannot be chunked because it does not have "
4653- . "a unique, single-column index\n";
4654- }
4655- $tbl_info{chunk_column} = $chunkable_cols[0]->{column};
4656- $tbl_info{chunk_index} = $chunkable_cols[0]->{index};
4657-
4658- # ########################################################################
4659- # Check the tmp table.
4660- # ########################################################################
4661- # The tmp table should not exist if we're supposed to create it.
4662- # Else, if user specifies --no-create-tmp-table, they should ensure
4663- # that it exists.
4664- if ( $o->get('create-tmp-table')
4665- && $tp->check_table(dbh=>$dbh, db=>$db, tbl=>$tmp_tbl) ) {
4666- die "Temporary table $db.$tmp_tbl exists which will prevent "
4667- . "--create-tmp-table from creating the temporary table.\n";
4668- }
4669-
4670- # ########################################################################
4671- # Check the old table.
4672- # ########################################################################
4673- # If we're going to rename the tables, which we do by default, then
4674- # the old table cannot already exist.
4675- if ( $o->get('rename-tables')
4676- && $tp->check_table(dbh=>$dbh, db=>$db, tbl=>$old_tbl) ) {
4677- die "Table $db.$old_tbl exists which will prevent $db.$tbl "
4678- . "from being renamed to it. Table $db.$old_tbl could be from "
4679- . "a previous run that failed. See --drop-old-table for more "
4680- . "information.\n";
4681- }
4682-
4683- return %tbl_info;
4684+ $orig_tbl->{tbl_struct} = $tp->parse($ddl);
4685+
4686+ # Must be able to nibble the original table (to copy rows to the new table).
4687+ eval {
4688+ NibbleIterator::can_nibble(
4689+ Cxn => $cxn,
4690+ tbl => $orig_tbl,
4691+ chunk_size => $o->get('chunk-size'),
4692+ chunk_indx => $o->get('chunk-index'),
4693+ OptionParser => $o,
4694+ TableParser => $tp,
4695+ );
4696+ };
4697+ if ( $EVAL_ERROR ) {
4698+ die "Cannot chunk the original table $orig_tbl->{name}: $EVAL_ERROR\n";
4699+ }
4700+
4701+ # Find a pk or unique index to use for the delete trigger. can_nibble()
4702+ # above returns an index, but NibbleIterator will use non-unique indexes,
4703+ # so we have to do this again here.
4704+ my $indexes = $orig_tbl->{tbl_struct}->{indexes}; # brevity
4705+ foreach my $index ( $tp->sort_indexes($orig_tbl->{tbl_struct}) ) {
4706+ if ( $index eq 'PRIMARY' || $indexes->{$index}->{is_unique} ) {
4707+ PTDEBUG && _d('Delete trigger index:', Dumper($index));
4708+ $orig_tbl->{del_index} = $index;
4709+ last;
4710+ }
4711+ }
4712+ if ( !$orig_tbl->{del_index} ) {
4713+ die "The original table $orig_tbl->{name} does not have a PRIMARY KEY "
4714+ . "or a unique index which is required for the DELETE trigger.\n";
4715+ }
4716+
4717+ return; # success
4718 }
4719
4720-sub get_child_tables {
4721+sub find_child_tables {
4722 my ( %args ) = @_;
4723- my @required_args = qw(dbh db tbl Quoter);
4724+ my @required_args = qw(tbl Cxn Quoter);
4725 foreach my $arg ( @required_args ) {
4726 die "I need a $arg argument" unless $args{$arg};
4727 }
4728- my ($dbh, $db, $tbl, $q) = @args{@required_args};
4729-
4730- my $sql = "SELECT table_name "
4731+ my ($tbl, $cxn, $q) = @args{@required_args};
4732+
4733+ PTDEBUG && _d('Finding child tables');
4734+
4735+ my $sql = "SELECT table_schema, table_name "
4736 . "FROM information_schema.key_column_usage "
4737- . "WHERE constraint_schema='$db' AND referenced_table_name='$tbl'";
4738- PTDEBUG && _d($dbh, $sql);
4739- my $child_tables;
4740- eval {
4741- $child_tables = $dbh->selectall_arrayref($sql);
4742- };
4743- if ( $EVAL_ERROR ) {
4744- die "Error executing query to check $tbl for child tables.\n\n"
4745- . "Query: $sql\n\n"
4746- . "Error: $EVAL_ERROR"
4747- }
4748-
4749- PTDEBUG && _d("Child tables:", join(', ', map { $_->[0] } @$child_tables));
4750- return map { $_->[0] } @$child_tables;
4751-}
4752-
4753-sub update_foreign_key_constraints {
4754- my ( %args ) = @_;
4755- my @required_args = qw(msg dbh db tbl old_tbl child_tables Quoter);
4756- foreach my $arg ( @required_args ) {
4757- die "I need a $arg argument" unless $args{$arg};
4758- }
4759- my ($msg, $dbh, $db, $tbl, $old_tbl, $child_tables, $q)
4760+ . "WHERE constraint_schema='$tbl->{db}' "
4761+ . "AND referenced_table_name='$tbl->{tbl}'";
4762+ PTDEBUG && _d($sql);
4763+ my $rows = $cxn->dbh()->selectall_arrayref($sql);
4764+ if ( !$rows || !@$rows ) {
4765+ PTDEBUG && _d('No child tables found');
4766+ return;
4767+ }
4768+
4769+ my @child_tables;
4770+ foreach my $row ( @$rows ) {
4771+ my $tbl = {
4772+ db => $row->[0],
4773+ tbl => $row->[1],
4774+ name => $q->quote(@$row),
4775+ };
4776+
4777+ # Get row estimates for each child table so we can give the user
4778+ # some input on choosing an --alter-foreign-keys-method if they
4779+ # don't use "auto".
4780+ my ($n_rows) = NibbleIterator::get_row_estimate(
4781+ Cxn => $cxn,
4782+ tbl => $tbl,
4783+ );
4784+ $tbl->{row_est} = $n_rows;
4785+
4786+ push @child_tables, $tbl;
4787+ }
4788+
4789+ PTDEBUG && _d('Child tables:', Dumper(\@child_tables));
4790+ return \@child_tables;
4791+}
4792+
4793+sub determine_alter_fk_method {
4794+ my ( %args ) = @_;
4795+ my @required_args = qw(child_tables max_rows Cxn OptionParser);
4796+ foreach my $arg ( @required_args ) {
4797+ die "I need a $arg argument" unless $args{$arg};
4798+ }
4799+ my ($child_tables, $max_rows, $cxn, $o) = @args{@required_args};
4800+
4801+ if ( $o->get('dry-run') ) {
4802+ print "Not determining the method to update foreign keys "
4803+ . "because this is a dry run.\n";
4804+ return ''; # $alter_fk_method can't be undef
4805+ }
4806+
4807+ # The rebuild_constraints method is the default becuase it's safer
4808+ # and doesn't cause the orig table to go missing for a moment.
4809+ my $method = 'rebuild_constraints';
4810+
4811+ print "Max rows for the rebuild_constraints method: $max_rows\n",
4812+ "Determining the method to update foreign keys...\n";
4813+ foreach my $child_tbl ( @$child_tables ) {
4814+ print " $child_tbl->{name}: ";
4815+ my ($n_rows) = NibbleIterator::get_row_estimate(
4816+ Cxn => $cxn,
4817+ tbl => $child_tbl,
4818+ );
4819+ if ( $n_rows > $max_rows ) {
4820+ print "too many rows: $n_rows; must use drop_swap\n";
4821+ $method = 'drop_swap';
4822+ last;
4823+ }
4824+ else {
4825+ print "$n_rows rows; can use rebuild_constraints\n";
4826+ }
4827+ }
4828+
4829+ return $method || ''; # $alter_fk_method can't be undef
4830+}
4831+
4832+sub rebuild_constraints {
4833+ my ( %args ) = @_;
4834+ my @required_args = qw(orig_tbl old_tbl child_tables
4835+ Cxn Quoter OptionParser TableParser);
4836+ foreach my $arg ( @required_args ) {
4837+ die "I need a $arg argument" unless $args{$arg};
4838+ }
4839+ my ($orig_tbl, $old_tbl, $child_tables, $cxn, $q, $o, $tp)
4840 = @args{@required_args};
4841
4842- my $constraint = qr/^\s+(CONSTRAINT.+?REFERENCES `$old_tbl`.+)$/mo;
4843+ # MySQL has a "feature" where if the parent tbl is in the same db,
4844+ # then the child tbl ref is simply `parent_tbl`, but if the parent tbl
4845+ # is in another db, then the child tbl ref is `other_db`.`parent_tbl`.
4846+ # When we recreate the ref below, we use the db-qualified form, and
4847+ # MySQL will automatically trim the db if the tables are in the same db.
4848+ my $quoted_old_table = $q->quote($old_tbl->{tbl});
4849+ my $constraint = qr/
4850+ ^\s+
4851+ (
4852+ CONSTRAINT.+?
4853+ REFERENCES\s(?:$quoted_old_table|$old_tbl->{name})
4854+ .+
4855+ )$
4856+ /xm;
4857+ PTDEBUG && _d('Rebuilding fk constraint matching', $constraint);
4858+
4859+ if ( $o->get('dry-run') ) {
4860+ print "Not rebuilding foreign key constraints because this is a dry run.\n";
4861+ }
4862+ else {
4863+ print "Rebuilding foreign key constraints...\n";
4864+ }
4865
4866 CHILD_TABLE:
4867- foreach my $child_table ( @$child_tables ) {
4868- my $sql = "SHOW CREATE TABLE `$db`.`$child_table`";
4869- PTDEBUG && _d($dbh, $sql);
4870- $msg->($sql);
4871- my $table_def;
4872- eval {
4873- $table_def = $dbh->selectrow_arrayref($sql)->[1];
4874- };
4875- if ( $EVAL_ERROR ) {
4876- warn "Skipping child table $child_table: $EVAL_ERROR";
4877- next CHILD_TABLE;
4878- }
4879-
4880+ foreach my $child_tbl ( @$child_tables ) {
4881+ my $table_def = $tp->get_create_table(
4882+ $cxn->dbh(),
4883+ $child_tbl->{db},
4884+ $child_tbl->{tbl},
4885+ );
4886 my @constraints = $table_def =~ m/$constraint/g;
4887 if ( !@constraints ) {
4888- warn "Child table `$child_table` does not have any foreign key "
4889- . "constraints referencing $old_tbl";
4890+ warn "$child_tbl->{name} has no foreign key "
4891+ . "constraints referencing $old_tbl->{name}.\n";
4892 next CHILD_TABLE;
4893 }
4894
4895+ my @rebuilt_constraints;
4896 foreach my $constraint ( @constraints ) {
4897- my ($fk_symbol) = $constraint =~ m/CONSTRAINT\s+(\S+)/;
4898- $sql = "ALTER TABLE `$db`.`$child_table` DROP FOREIGN KEY $fk_symbol";
4899- $msg->($sql);
4900- $dbh->do($sql) unless $args{print};
4901-
4902- $constraint =~ s/REFERENCES `$old_tbl`/REFERENCES `$tbl`/o;
4903- $sql = "ALTER TABLE `$db`.`$child_table` ADD $constraint";
4904- $msg->($sql);
4905- $dbh->do($sql) unless $args{print};
4906- }
4907- }
4908-
4909- return;
4910-}
4911-
4912-sub intersection {
4913- my ( $hashes ) = @_;
4914- my %keys = map { $_ => 1 } keys %{$hashes->[0]};
4915- my $n_hashes = (scalar @$hashes) - 1;
4916- my @isect = grep { $keys{$_} } map { keys %{$hashes->[$_]} } 1..$n_hashes;
4917- return @isect;
4918-}
4919-
4920-sub get_cxn {
4921- my ( %args ) = @_;
4922- my ($dsn, $ac, $dp, $o) = @args{qw(dsn AutoCommit DSNParser OptionParser)};
4923-
4924- if ( $o->get('ask-pass') ) {
4925- $dsn->{p} = OptionParser::prompt_noecho("Enter password: ");
4926- }
4927- my $dbh = $dp->get_dbh($dp->get_cxn_params($dsn), {AutoCommit => $ac});
4928-
4929- $dbh->do('SET SQL_LOG_BIN=0') unless $o->get('bin-log');
4930- $dbh->do('SET FOREIGN_KEY_CHECKS=0') unless $o->get('foreign-key-checks');
4931-
4932- return $dbh;
4933-}
4934-
4935-sub msg {
4936- my ( $msg ) = @_;
4937- chomp $msg;
4938- print '# ', ts(time), " $msg\n" unless $quiet;
4939- PTDEBUG && _d($msg);
4940- return;
4941-}
4942-
4943-# Only for tests which may not call main().
4944-sub __set_quiet {
4945- $quiet = $_[0];
4946+ PTDEBUG && _d('Rebuilding fk constraint:', $constraint);
4947+
4948+ # Remove trailing commas in case there are multiple constraints on the
4949+ # table.
4950+ $constraint =~ s/,$//;
4951+
4952+ # Find the constraint name. It will be quoted already.
4953+ my ($fk) = $constraint =~ m/CONSTRAINT\s+`([^`]+)`/;
4954+
4955+ # Drop the reference to the old table/renamed orig table, and add a new
4956+ # reference to the new table. InnoDB will throw an error if the new
4957+ # constraint has the same name as the old one, so we must rename it.
4958+ # Example: after renaming sakila.actor to sakila.actor_old (for
4959+ # example), the foreign key on film_actor looks like this:
4960+ # CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES
4961+ # `actor_old` (`actor_id`) ON UPDATE CASCADE
4962+ # We need it to look like this instead:
4963+ # CONSTRAINT `_fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES
4964+ # `actor` (`actor_id`) ON UPDATE CASCADE
4965+ # Reference the correct table name...
4966+ $constraint =~ s/REFERENCES[^\(]+/REFERENCES $orig_tbl->{name} /;
4967+ # And rename the constraint to avoid conflict
4968+ $constraint =~ s/CONSTRAINT `$fk`/CONSTRAINT `_$fk`/;
4969+
4970+ my $sql = "DROP FOREIGN KEY `$fk`, "
4971+ . "ADD $constraint";
4972+ push @rebuilt_constraints, $sql;
4973+ }
4974+
4975+ my $sql = "ALTER TABLE $child_tbl->{name} "
4976+ . join(', ', @rebuilt_constraints);
4977+ print $sql, "\n" if $o->get('print');
4978+ if ( $o->get('execute') ) {
4979+ PTDEBUG && _d($sql);
4980+ $cxn->dbh()->do($sql);
4981+ }
4982+ }
4983+
4984+ if ( $o->get('execute') ) {
4985+ print "Rebuilt foreign key constraints OK.\n";
4986+ }
4987+
4988+ return;
4989+}
4990+
4991+sub drop_swap {
4992+ my ( %args ) = @_;
4993+ my @required_args = qw(orig_tbl new_tbl Cxn OptionParser);
4994+ foreach my $arg ( @required_args ) {
4995+ die "I need a $arg argument" unless $args{$arg};
4996+ }
4997+ my ($orig_tbl, $new_tbl, $cxn, $o) = @args{@required_args};
4998+
4999+ if ( $o->get('dry-run') ) {
5000+ print "Not drop-swapping tables because this is a dry run.\n";
The diff has been truncated for viewing.

Subscribers

People subscribed via source and target branches