Percona Toolkit moved to https://jira.percona.com/projects/PT

Merge lp:~percona-toolkit-dev/percona-toolkit/fix-table-status-bug-960513 into lp:percona-toolkit/2.1

  1. fix-table-status-bug-960513
  2. Merge into 2.1
Proposed by Daniel Nichter
Status: Merged
Approved by: Daniel Nichter
Approved revision: 228
Merged at revision: 227
Proposed branch: lp:~percona-toolkit-dev/percona-toolkit/fix-table-status-bug-960513
Merge into: lp:percona-toolkit/2.1
Diff against target: 3105 lines (+1575/-763)
11 files modified
bin/pt-duplicate-key-checker (+171/-92)
bin/pt-index-usage (+170/-92)
bin/pt-table-checksum (+42/-67)
bin/pt-table-sync (+553/-404)
lib/NibbleIterator.pm (+8/-15)
lib/SchemaIterator.pm (+27/-45)
t/lib/SchemaIterator.t (+2/-47)
t/lib/samples/SchemaIterator/all-dbs-tbls.txt (+486/-0)
t/lib/samples/SchemaIterator/resume-from-ignored-sakila-payment.txt (+49/-0)
t/lib/samples/SchemaIterator/resume-from-sakila-payment.txt (+66/-0)
t/pt-table-sync/issue_408.t (+1/-1)
To merge this branch: bzr merge lp:~percona-toolkit-dev/percona-toolkit/fix-table-status-bug-960513
Reviewer Review Type Date Requested Status
Baron Schwartz (community) Approve
Daniel Nichter Approve
Review via email: mp+100295@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Daniel Nichter (daniel-nichter) :
review: Approve
Revision history for this message
Baron Schwartz (baron-xaprb) :
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'bin/pt-duplicate-key-checker'
2--- bin/pt-duplicate-key-checker 2012-03-07 23:41:54 +0000
3+++ bin/pt-duplicate-key-checker 2012-03-31 16:06:20 +0000
4@@ -199,19 +199,58 @@
5 return bless $self, $class;
6 }
7
8+sub get_create_table {
9+ my ( $self, $dbh, $db, $tbl ) = @_;
10+ die "I need a dbh parameter" unless $dbh;
11+ die "I need a db parameter" unless $db;
12+ die "I need a tbl parameter" unless $tbl;
13+ my $q = $self->{Quoter};
14+
15+ my $new_sql_mode
16+ = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
17+ . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
18+ . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
19+ . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
20+
21+ my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
22+ . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
23+
24+ PTDEBUG && _d($new_sql_mode);
25+ eval { $dbh->do($new_sql_mode); };
26+ PTDEBUG && $EVAL_ERROR && _d($EVAL_ERROR);
27+
28+ my $use_sql = 'USE ' . $q->quote($db);
29+ PTDEBUG && _d($dbh, $use_sql);
30+ $dbh->do($use_sql);
31+
32+ my $show_sql = "SHOW CREATE TABLE " . $q->quote($db, $tbl);
33+ PTDEBUG && _d($show_sql);
34+ my $href;
35+ eval { $href = $dbh->selectrow_hashref($show_sql); };
36+ if ( $EVAL_ERROR ) {
37+ PTDEBUG && _d($EVAL_ERROR);
38+
39+ PTDEBUG && _d($old_sql_mode);
40+ $dbh->do($old_sql_mode);
41+
42+ return;
43+ }
44+
45+ PTDEBUG && _d($old_sql_mode);
46+ $dbh->do($old_sql_mode);
47+
48+ my ($key) = grep { m/create (?:table|view)/i } keys %$href;
49+ if ( !$key ) {
50+ die "Error: no 'Create Table' or 'Create View' in result set from "
51+ . "$show_sql: " . Dumper($href);
52+ }
53+
54+ return $href->{$key};
55+}
56+
57 sub parse {
58 my ( $self, $ddl, $opts ) = @_;
59 return unless $ddl;
60- if ( ref $ddl eq 'ARRAY' ) {
61- if ( lc $ddl->[0] eq 'table' ) {
62- $ddl = $ddl->[1];
63- }
64- else {
65- return {
66- engine => 'VIEW',
67- };
68- }
69- }
70
71 if ( $ddl !~ m/CREATE (?:TEMPORARY )?TABLE `/ ) {
72 die "Cannot parse table definition; is ANSI quoting "
73@@ -518,41 +557,31 @@
74 return $ddl;
75 }
76
77-sub remove_secondary_indexes {
78- my ( $self, $ddl ) = @_;
79- my $sec_indexes_ddl;
80- my $tbl_struct = $self->parse($ddl);
81-
82- if ( ($tbl_struct->{engine} || '') =~ m/InnoDB/i ) {
83- my $clustered_key = $tbl_struct->{clustered_key};
84- $clustered_key ||= '';
85-
86- my @sec_indexes = map {
87- my $key_def = $_->{ddl};
88- $key_def =~ s/([\(\)])/\\$1/g;
89- $ddl =~ s/\s+$key_def//i;
90-
91- my $key_ddl = "ADD $_->{ddl}";
92- $key_ddl .= ',' unless $key_ddl =~ m/,$/;
93- $key_ddl;
94- }
95- grep { $_->{name} ne $clustered_key }
96- values %{$tbl_struct->{keys}};
97- PTDEBUG && _d('Secondary indexes:', Dumper(\@sec_indexes));
98-
99- if ( @sec_indexes ) {
100- $sec_indexes_ddl = join(' ', @sec_indexes);
101- $sec_indexes_ddl =~ s/,$//;
102- }
103-
104- $ddl =~ s/,(\n\) )/$1/s;
105- }
106- else {
107- PTDEBUG && _d('Not removing secondary indexes from',
108- $tbl_struct->{engine}, 'table');
109- }
110-
111- return $ddl, $sec_indexes_ddl, $tbl_struct;
112+sub get_table_status {
113+ my ( $self, $dbh, $db, $like ) = @_;
114+ my $q = $self->{Quoter};
115+ my $sql = "SHOW TABLE STATUS FROM " . $q->quote($db);
116+ my @params;
117+ if ( $like ) {
118+ $sql .= ' LIKE ?';
119+ push @params, $like;
120+ }
121+ PTDEBUG && _d($sql, @params);
122+ my $sth = $dbh->prepare($sql);
123+ eval { $sth->execute(@params); };
124+ if ($EVAL_ERROR) {
125+ PTDEBUG && _d($EVAL_ERROR);
126+ return;
127+ }
128+ my @tables = @{$sth->fetchall_arrayref({})};
129+ @tables = map {
130+ my %tbl; # Make a copy with lowercased keys
131+ @tbl{ map { lc $_ } keys %$_ } = values %$_;
132+ $tbl{engine} ||= $tbl{type} || $tbl{comment};
133+ delete $tbl{type};
134+ \%tbl;
135+ } @tables;
136+ return @tables;
137 }
138
139 sub _d {
140@@ -3195,7 +3224,7 @@
141
142 sub new {
143 my ( $class, %args ) = @_;
144- my @required_args = qw(OptionParser Quoter);
145+ my @required_args = qw(OptionParser TableParser Quoter);
146 foreach my $arg ( @required_args ) {
147 die "I need a $arg argument" unless $args{$arg};
148 }
149@@ -3204,8 +3233,19 @@
150 die "I need either a dbh or file_itr argument"
151 if (!$dbh && !$file_itr) || ($dbh && $file_itr);
152
153+ my %resume;
154+ if ( my $table = $args{resume} ) {
155+ PTDEBUG && _d('Will resume from or after', $table);
156+ my ($db, $tbl) = $args{Quoter}->split_unquote($table);
157+ die "Resume table must be database-qualified: $table"
158+ unless $db && $tbl;
159+ $resume{db} = $db;
160+ $resume{tbl} = $tbl;
161+ }
162+
163 my $self = {
164 %args,
165+ resume => \%resume,
166 filters => _make_filters(%args),
167 };
168
169@@ -3266,9 +3306,19 @@
170 return \%filters;
171 }
172
173-sub next_schema_object {
174+sub next {
175 my ( $self ) = @_;
176
177+ if ( !$self->{initialized} ) {
178+ $self->{initialized} = 1;
179+ if ( $self->{resume}->{tbl}
180+ && !$self->table_is_allowed(@{$self->{resume}}{qw(db tbl)}) ) {
181+ PTDEBUG && _d('Will resume after',
182+ join('.', @{$self->{resume}}{qw(db tbl)}));
183+ $self->{resume}->{after} = 1;
184+ }
185+ }
186+
187 my $schema_obj;
188 if ( $self->{file_itr} ) {
189 $schema_obj= $self->_iterate_files();
190@@ -3278,24 +3328,18 @@
191 }
192
193 if ( $schema_obj ) {
194- if ( $schema_obj->{ddl} && $self->{TableParser} ) {
195- $schema_obj->{tbl_struct}
196- = $self->{TableParser}->parse($schema_obj->{ddl});
197- }
198-
199- delete $schema_obj->{ddl} unless $self->{keep_ddl};
200-
201 if ( my $schema = $self->{Schema} ) {
202 $schema->add_schema_object($schema_obj);
203 }
204+ PTDEBUG && _d('Next schema object:',
205+ $schema_obj->{db}, $schema_obj->{tbl});
206 }
207
208- PTDEBUG && _d('Next schema object:', $schema_obj->{db}, $schema_obj->{tbl});
209 return $schema_obj;
210 }
211
212 sub _iterate_files {
213- my ( $self ) = @_;
214+ my ( $self ) = @_;
215
216 if ( !$self->{fh} ) {
217 my ($fh, $file) = $self->{file_itr}->();
218@@ -3316,7 +3360,8 @@
219 my $db = $1; # XXX
220 $db =~ s/^`//; # strip leading `
221 $db =~ s/`$//; # and trailing `
222- if ( $self->database_is_allowed($db) ) {
223+ if ( $self->database_is_allowed($db)
224+ && $self->_resume_from_database($db) ) {
225 $self->{db} = $db;
226 }
227 }
228@@ -3329,21 +3374,22 @@
229 my ($tbl) = $chunk =~ m/$tbl_name/;
230 $tbl =~ s/^\s*`//;
231 $tbl =~ s/`\s*$//;
232- if ( $self->table_is_allowed($self->{db}, $tbl) ) {
233+ if ( $self->_resume_from_table($tbl)
234+ && $self->table_is_allowed($self->{db}, $tbl) ) {
235 my ($ddl) = $chunk =~ m/^(?:$open_comment)?(CREATE TABLE.+?;)$/ms;
236 if ( !$ddl ) {
237 warn "Failed to parse CREATE TABLE from\n" . $chunk;
238 next CHUNK;
239 }
240 $ddl =~ s/ \*\/;\Z/;/; # remove end of version comment
241-
242- my ($engine) = $ddl =~ m/\).*?(?:ENGINE|TYPE)=(\w+)/;
243-
244- if ( !$engine || $self->engine_is_allowed($engine) ) {
245+ my $tbl_struct = $self->{TableParser}->parse($ddl);
246+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
247 return {
248- db => $self->{db},
249- tbl => $tbl,
250- ddl => $ddl,
251+ db => $self->{db},
252+ tbl => $tbl,
253+ name => $self->{Quoter}->quote($self->{db}, $tbl),
254+ ddl => $ddl,
255+ tbl_struct => $tbl_struct,
256 };
257 }
258 }
259@@ -3360,6 +3406,7 @@
260 sub _iterate_dbh {
261 my ( $self ) = @_;
262 my $q = $self->{Quoter};
263+ my $tp = $self->{TableParser};
264 my $dbh = $self->{dbh};
265 PTDEBUG && _d('Getting next schema object from dbh', $dbh);
266
267@@ -3373,7 +3420,9 @@
268 }
269
270 if ( !$self->{db} ) {
271- $self->{db} = shift @{$self->{dbs}};
272+ do {
273+ $self->{db} = shift @{$self->{dbs}};
274+ } until $self->_resume_from_database($self->{db});
275 PTDEBUG && _d('Next database:', $self->{db});
276 return unless $self->{db};
277 }
278@@ -3386,8 +3435,9 @@
279 }
280 grep {
281 my ($tbl, $type) = @$_;
282- $self->table_is_allowed($self->{db}, $tbl)
283- && (!$type || ($type ne 'VIEW'));
284+ (!$type || ($type ne 'VIEW'))
285+ && $self->_resume_from_table($tbl)
286+ && $self->table_is_allowed($self->{db}, $tbl);
287 }
288 @{$dbh->selectall_arrayref($sql)};
289 PTDEBUG && _d('Found', scalar @tbls, 'tables in database', $self->{db});
290@@ -3395,27 +3445,15 @@
291 }
292
293 while ( my $tbl = shift @{$self->{tbls}} ) {
294- my $engine;
295- if ( $self->{filters}->{'engines'}
296- || $self->{filters}->{'ignore-engines'} ) {
297- my $sql = "SHOW TABLE STATUS FROM " . $q->quote($self->{db})
298- . " LIKE \'$tbl\'";
299- PTDEBUG && _d($sql);
300- $engine = $dbh->selectrow_hashref($sql)->{engine};
301- PTDEBUG && _d($tbl, 'uses', $engine, 'engine');
302- }
303-
304-
305- if ( !$engine || $self->engine_is_allowed($engine) ) {
306- my $ddl;
307- if ( my $du = $self->{MySQLDump} ) {
308- $ddl = $du->get_create_table($dbh, $q, $self->{db}, $tbl)->[1];
309- }
310-
311+ my $ddl = $tp->get_create_table($dbh, $self->{db}, $tbl);
312+ my $tbl_struct = $tp->parse($ddl);
313+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
314 return {
315- db => $self->{db},
316- tbl => $tbl,
317- ddl => $ddl,
318+ db => $self->{db},
319+ tbl => $tbl,
320+ name => $q->quote($self->{db}, $tbl),
321+ ddl => $ddl,
322+ tbl_struct => $tbl_struct,
323 };
324 }
325 }
326@@ -3476,6 +3514,10 @@
327
328 my $filter = $self->{filters};
329
330+ if ( $db eq 'mysql' && ($tbl eq 'general_log' || $tbl eq 'slow_log') ) {
331+ return 0;
332+ }
333+
334 if ( $filter->{'ignore-tables'}->{$tbl}
335 && ($filter->{'ignore-tables'}->{$tbl} eq '*'
336 || $filter->{'ignore-tables'}->{$tbl} eq $db) ) {
337@@ -3515,7 +3557,11 @@
338
339 sub engine_is_allowed {
340 my ( $self, $engine ) = @_;
341- die "I need an engine argument" unless $engine;
342+
343+ if ( !$engine ) {
344+ PTDEBUG && _d('No engine specified; allowing the table');
345+ return 1;
346+ }
347
348 $engine = lc $engine;
349
350@@ -3535,6 +3581,40 @@
351 return 1;
352 }
353
354+sub _resume_from_database {
355+ my ($self, $db) = @_;
356+
357+ return 1 unless $self->{resume}->{db};
358+
359+ if ( $db eq $self->{resume}->{db} ) {
360+ PTDEBUG && _d('At resume db', $db);
361+ delete $self->{resume}->{db};
362+ return 1;
363+ }
364+
365+ return 0;
366+}
367+
368+sub _resume_from_table {
369+ my ($self, $tbl) = @_;
370+
371+ return 1 unless $self->{resume}->{tbl};
372+
373+ if ( $tbl eq $self->{resume}->{tbl} ) {
374+ if ( !$self->{resume}->{after} ) {
375+ PTDEBUG && _d('Resuming from table', $tbl);
376+ delete $self->{resume}->{tbl};
377+ return 1;
378+ }
379+ else {
380+ PTDEBUG && _d('Resuming after table', $tbl);
381+ delete $self->{resume}->{tbl};
382+ }
383+ }
384+
385+ return 0;
386+}
387+
388 sub _d {
389 my ($package, undef, $line) = caller 0;
390 @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
391@@ -3644,11 +3724,10 @@
392 MySQLDump => $du,
393 TableParser => $tp,
394 Schema => $schema,
395- keep_ddl => 1,
396 );
397 TABLE:
398- while ( my $tbl = $schema_itr->next_schema_object() ) {
399- $tbl->{engine} = $tp->get_engine($tbl->{ddl});
400+ while ( my $tbl = $schema_itr->next() ) {
401+ $tbl->{engine} = $tbl->{tbl_struct}->{engine};
402
403 my ($keys, $clustered_key, $fks);
404 if ( $get_keys ) {
405
406=== modified file 'bin/pt-index-usage'
407--- bin/pt-index-usage 2012-03-07 23:41:54 +0000
408+++ bin/pt-index-usage 2012-03-31 16:06:20 +0000
409@@ -2669,19 +2669,58 @@
410 return bless $self, $class;
411 }
412
413+sub get_create_table {
414+ my ( $self, $dbh, $db, $tbl ) = @_;
415+ die "I need a dbh parameter" unless $dbh;
416+ die "I need a db parameter" unless $db;
417+ die "I need a tbl parameter" unless $tbl;
418+ my $q = $self->{Quoter};
419+
420+ my $new_sql_mode
421+ = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
422+ . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
423+ . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
424+ . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
425+
426+ my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
427+ . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
428+
429+ PTDEBUG && _d($new_sql_mode);
430+ eval { $dbh->do($new_sql_mode); };
431+ PTDEBUG && $EVAL_ERROR && _d($EVAL_ERROR);
432+
433+ my $use_sql = 'USE ' . $q->quote($db);
434+ PTDEBUG && _d($dbh, $use_sql);
435+ $dbh->do($use_sql);
436+
437+ my $show_sql = "SHOW CREATE TABLE " . $q->quote($db, $tbl);
438+ PTDEBUG && _d($show_sql);
439+ my $href;
440+ eval { $href = $dbh->selectrow_hashref($show_sql); };
441+ if ( $EVAL_ERROR ) {
442+ PTDEBUG && _d($EVAL_ERROR);
443+
444+ PTDEBUG && _d($old_sql_mode);
445+ $dbh->do($old_sql_mode);
446+
447+ return;
448+ }
449+
450+ PTDEBUG && _d($old_sql_mode);
451+ $dbh->do($old_sql_mode);
452+
453+ my ($key) = grep { m/create (?:table|view)/i } keys %$href;
454+ if ( !$key ) {
455+ die "Error: no 'Create Table' or 'Create View' in result set from "
456+ . "$show_sql: " . Dumper($href);
457+ }
458+
459+ return $href->{$key};
460+}
461+
462 sub parse {
463 my ( $self, $ddl, $opts ) = @_;
464 return unless $ddl;
465- if ( ref $ddl eq 'ARRAY' ) {
466- if ( lc $ddl->[0] eq 'table' ) {
467- $ddl = $ddl->[1];
468- }
469- else {
470- return {
471- engine => 'VIEW',
472- };
473- }
474- }
475
476 if ( $ddl !~ m/CREATE (?:TEMPORARY )?TABLE `/ ) {
477 die "Cannot parse table definition; is ANSI quoting "
478@@ -2988,41 +3027,31 @@
479 return $ddl;
480 }
481
482-sub remove_secondary_indexes {
483- my ( $self, $ddl ) = @_;
484- my $sec_indexes_ddl;
485- my $tbl_struct = $self->parse($ddl);
486-
487- if ( ($tbl_struct->{engine} || '') =~ m/InnoDB/i ) {
488- my $clustered_key = $tbl_struct->{clustered_key};
489- $clustered_key ||= '';
490-
491- my @sec_indexes = map {
492- my $key_def = $_->{ddl};
493- $key_def =~ s/([\(\)])/\\$1/g;
494- $ddl =~ s/\s+$key_def//i;
495-
496- my $key_ddl = "ADD $_->{ddl}";
497- $key_ddl .= ',' unless $key_ddl =~ m/,$/;
498- $key_ddl;
499- }
500- grep { $_->{name} ne $clustered_key }
501- values %{$tbl_struct->{keys}};
502- PTDEBUG && _d('Secondary indexes:', Dumper(\@sec_indexes));
503-
504- if ( @sec_indexes ) {
505- $sec_indexes_ddl = join(' ', @sec_indexes);
506- $sec_indexes_ddl =~ s/,$//;
507- }
508-
509- $ddl =~ s/,(\n\) )/$1/s;
510- }
511- else {
512- PTDEBUG && _d('Not removing secondary indexes from',
513- $tbl_struct->{engine}, 'table');
514- }
515-
516- return $ddl, $sec_indexes_ddl, $tbl_struct;
517+sub get_table_status {
518+ my ( $self, $dbh, $db, $like ) = @_;
519+ my $q = $self->{Quoter};
520+ my $sql = "SHOW TABLE STATUS FROM " . $q->quote($db);
521+ my @params;
522+ if ( $like ) {
523+ $sql .= ' LIKE ?';
524+ push @params, $like;
525+ }
526+ PTDEBUG && _d($sql, @params);
527+ my $sth = $dbh->prepare($sql);
528+ eval { $sth->execute(@params); };
529+ if ($EVAL_ERROR) {
530+ PTDEBUG && _d($EVAL_ERROR);
531+ return;
532+ }
533+ my @tables = @{$sth->fetchall_arrayref({})};
534+ @tables = map {
535+ my %tbl; # Make a copy with lowercased keys
536+ @tbl{ map { lc $_ } keys %$_ } = values %$_;
537+ $tbl{engine} ||= $tbl{type} || $tbl{comment};
538+ delete $tbl{type};
539+ \%tbl;
540+ } @tables;
541+ return @tables;
542 }
543
544 sub _d {
545@@ -3912,7 +3941,7 @@
546
547 sub new {
548 my ( $class, %args ) = @_;
549- my @required_args = qw(OptionParser Quoter);
550+ my @required_args = qw(OptionParser TableParser Quoter);
551 foreach my $arg ( @required_args ) {
552 die "I need a $arg argument" unless $args{$arg};
553 }
554@@ -3921,8 +3950,19 @@
555 die "I need either a dbh or file_itr argument"
556 if (!$dbh && !$file_itr) || ($dbh && $file_itr);
557
558+ my %resume;
559+ if ( my $table = $args{resume} ) {
560+ PTDEBUG && _d('Will resume from or after', $table);
561+ my ($db, $tbl) = $args{Quoter}->split_unquote($table);
562+ die "Resume table must be database-qualified: $table"
563+ unless $db && $tbl;
564+ $resume{db} = $db;
565+ $resume{tbl} = $tbl;
566+ }
567+
568 my $self = {
569 %args,
570+ resume => \%resume,
571 filters => _make_filters(%args),
572 };
573
574@@ -3983,9 +4023,19 @@
575 return \%filters;
576 }
577
578-sub next_schema_object {
579+sub next {
580 my ( $self ) = @_;
581
582+ if ( !$self->{initialized} ) {
583+ $self->{initialized} = 1;
584+ if ( $self->{resume}->{tbl}
585+ && !$self->table_is_allowed(@{$self->{resume}}{qw(db tbl)}) ) {
586+ PTDEBUG && _d('Will resume after',
587+ join('.', @{$self->{resume}}{qw(db tbl)}));
588+ $self->{resume}->{after} = 1;
589+ }
590+ }
591+
592 my $schema_obj;
593 if ( $self->{file_itr} ) {
594 $schema_obj= $self->_iterate_files();
595@@ -3995,24 +4045,18 @@
596 }
597
598 if ( $schema_obj ) {
599- if ( $schema_obj->{ddl} && $self->{TableParser} ) {
600- $schema_obj->{tbl_struct}
601- = $self->{TableParser}->parse($schema_obj->{ddl});
602- }
603-
604- delete $schema_obj->{ddl} unless $self->{keep_ddl};
605-
606 if ( my $schema = $self->{Schema} ) {
607 $schema->add_schema_object($schema_obj);
608 }
609+ PTDEBUG && _d('Next schema object:',
610+ $schema_obj->{db}, $schema_obj->{tbl});
611 }
612
613- PTDEBUG && _d('Next schema object:', $schema_obj->{db}, $schema_obj->{tbl});
614 return $schema_obj;
615 }
616
617 sub _iterate_files {
618- my ( $self ) = @_;
619+ my ( $self ) = @_;
620
621 if ( !$self->{fh} ) {
622 my ($fh, $file) = $self->{file_itr}->();
623@@ -4033,7 +4077,8 @@
624 my $db = $1; # XXX
625 $db =~ s/^`//; # strip leading `
626 $db =~ s/`$//; # and trailing `
627- if ( $self->database_is_allowed($db) ) {
628+ if ( $self->database_is_allowed($db)
629+ && $self->_resume_from_database($db) ) {
630 $self->{db} = $db;
631 }
632 }
633@@ -4046,21 +4091,22 @@
634 my ($tbl) = $chunk =~ m/$tbl_name/;
635 $tbl =~ s/^\s*`//;
636 $tbl =~ s/`\s*$//;
637- if ( $self->table_is_allowed($self->{db}, $tbl) ) {
638+ if ( $self->_resume_from_table($tbl)
639+ && $self->table_is_allowed($self->{db}, $tbl) ) {
640 my ($ddl) = $chunk =~ m/^(?:$open_comment)?(CREATE TABLE.+?;)$/ms;
641 if ( !$ddl ) {
642 warn "Failed to parse CREATE TABLE from\n" . $chunk;
643 next CHUNK;
644 }
645 $ddl =~ s/ \*\/;\Z/;/; # remove end of version comment
646-
647- my ($engine) = $ddl =~ m/\).*?(?:ENGINE|TYPE)=(\w+)/;
648-
649- if ( !$engine || $self->engine_is_allowed($engine) ) {
650+ my $tbl_struct = $self->{TableParser}->parse($ddl);
651+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
652 return {
653- db => $self->{db},
654- tbl => $tbl,
655- ddl => $ddl,
656+ db => $self->{db},
657+ tbl => $tbl,
658+ name => $self->{Quoter}->quote($self->{db}, $tbl),
659+ ddl => $ddl,
660+ tbl_struct => $tbl_struct,
661 };
662 }
663 }
664@@ -4077,6 +4123,7 @@
665 sub _iterate_dbh {
666 my ( $self ) = @_;
667 my $q = $self->{Quoter};
668+ my $tp = $self->{TableParser};
669 my $dbh = $self->{dbh};
670 PTDEBUG && _d('Getting next schema object from dbh', $dbh);
671
672@@ -4090,7 +4137,9 @@
673 }
674
675 if ( !$self->{db} ) {
676- $self->{db} = shift @{$self->{dbs}};
677+ do {
678+ $self->{db} = shift @{$self->{dbs}};
679+ } until $self->_resume_from_database($self->{db});
680 PTDEBUG && _d('Next database:', $self->{db});
681 return unless $self->{db};
682 }
683@@ -4103,8 +4152,9 @@
684 }
685 grep {
686 my ($tbl, $type) = @$_;
687- $self->table_is_allowed($self->{db}, $tbl)
688- && (!$type || ($type ne 'VIEW'));
689+ (!$type || ($type ne 'VIEW'))
690+ && $self->_resume_from_table($tbl)
691+ && $self->table_is_allowed($self->{db}, $tbl);
692 }
693 @{$dbh->selectall_arrayref($sql)};
694 PTDEBUG && _d('Found', scalar @tbls, 'tables in database', $self->{db});
695@@ -4112,27 +4162,15 @@
696 }
697
698 while ( my $tbl = shift @{$self->{tbls}} ) {
699- my $engine;
700- if ( $self->{filters}->{'engines'}
701- || $self->{filters}->{'ignore-engines'} ) {
702- my $sql = "SHOW TABLE STATUS FROM " . $q->quote($self->{db})
703- . " LIKE \'$tbl\'";
704- PTDEBUG && _d($sql);
705- $engine = $dbh->selectrow_hashref($sql)->{engine};
706- PTDEBUG && _d($tbl, 'uses', $engine, 'engine');
707- }
708-
709-
710- if ( !$engine || $self->engine_is_allowed($engine) ) {
711- my $ddl;
712- if ( my $du = $self->{MySQLDump} ) {
713- $ddl = $du->get_create_table($dbh, $q, $self->{db}, $tbl)->[1];
714- }
715-
716+ my $ddl = $tp->get_create_table($dbh, $self->{db}, $tbl);
717+ my $tbl_struct = $tp->parse($ddl);
718+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
719 return {
720- db => $self->{db},
721- tbl => $tbl,
722- ddl => $ddl,
723+ db => $self->{db},
724+ tbl => $tbl,
725+ name => $q->quote($self->{db}, $tbl),
726+ ddl => $ddl,
727+ tbl_struct => $tbl_struct,
728 };
729 }
730 }
731@@ -4193,6 +4231,10 @@
732
733 my $filter = $self->{filters};
734
735+ if ( $db eq 'mysql' && ($tbl eq 'general_log' || $tbl eq 'slow_log') ) {
736+ return 0;
737+ }
738+
739 if ( $filter->{'ignore-tables'}->{$tbl}
740 && ($filter->{'ignore-tables'}->{$tbl} eq '*'
741 || $filter->{'ignore-tables'}->{$tbl} eq $db) ) {
742@@ -4232,7 +4274,11 @@
743
744 sub engine_is_allowed {
745 my ( $self, $engine ) = @_;
746- die "I need an engine argument" unless $engine;
747+
748+ if ( !$engine ) {
749+ PTDEBUG && _d('No engine specified; allowing the table');
750+ return 1;
751+ }
752
753 $engine = lc $engine;
754
755@@ -4252,6 +4298,40 @@
756 return 1;
757 }
758
759+sub _resume_from_database {
760+ my ($self, $db) = @_;
761+
762+ return 1 unless $self->{resume}->{db};
763+
764+ if ( $db eq $self->{resume}->{db} ) {
765+ PTDEBUG && _d('At resume db', $db);
766+ delete $self->{resume}->{db};
767+ return 1;
768+ }
769+
770+ return 0;
771+}
772+
773+sub _resume_from_table {
774+ my ($self, $tbl) = @_;
775+
776+ return 1 unless $self->{resume}->{tbl};
777+
778+ if ( $tbl eq $self->{resume}->{tbl} ) {
779+ if ( !$self->{resume}->{after} ) {
780+ PTDEBUG && _d('Resuming from table', $tbl);
781+ delete $self->{resume}->{tbl};
782+ return 1;
783+ }
784+ else {
785+ PTDEBUG && _d('Resuming after table', $tbl);
786+ delete $self->{resume}->{tbl};
787+ }
788+ }
789+
790+ return 0;
791+}
792+
793 sub _d {
794 my ($package, undef, $line) = caller 0;
795 @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
796@@ -5144,13 +5224,11 @@
797 dbh => $dbh,
798 OptionParser => $o,
799 Quoter => $q,
800- MySQLDump => $du,
801 TableParser => $tp,
802 Schema => $schema,
803- keep_ddl => 1,
804 );
805 TALBE:
806- while ( my $tbl = $schema_itr->next_schema_object() ) {
807+ while ( my $tbl = $schema_itr->next() ) {
808 eval {
809 my ($indexes) = $tp->get_keys($tbl->{ddl}, {version => $version});
810 $iu->add_indexes(%$tbl, indexes=>$indexes);
811
812=== modified file 'bin/pt-table-checksum'
813--- bin/pt-table-checksum 2012-03-30 16:02:37 +0000
814+++ bin/pt-table-checksum 2012-03-31 16:06:20 +0000
815@@ -3941,22 +3941,15 @@
816
817 sub get_row_estimate {
818 my (%args) = @_;
819- my @required_args = qw(Cxn tbl OptionParser TableParser Quoter);
820- my ($cxn, $tbl, $o, $tp, $q) = @args{@required_args};
821+ my @required_args = qw(Cxn tbl);
822+ my ($cxn, $tbl) = @args{@required_args};
823
824- if ( $args{where} ) {
825- PTDEBUG && _d('WHERE clause, using explain plan for row estimate');
826- my $table = $q->quote(@{$tbl}{qw(db tbl)});
827- my $sql = "EXPLAIN SELECT * FROM $table WHERE $args{where}";
828- PTDEBUG && _d($sql);
829- my $expl = $cxn->dbh()->selectrow_hashref($sql);
830- PTDEBUG && _d(Dumper($expl));
831- return ($expl->{rows} || 0), $expl->{key};
832- }
833- else {
834- PTDEBUG && _d('No WHERE clause, using table status for row estimate');
835- return $tbl->{tbl_status}->{rows} || 0;
836- }
837+ my $sql = "EXPLAIN SELECT * FROM $tbl->{name} "
838+ . "WHERE " . ($args{where} || '1=1');
839+ PTDEBUG && _d($sql);
840+ my $expl = $cxn->dbh()->selectrow_hashref($sql);
841+ PTDEBUG && _d(Dumper($expl));
842+ return ($expl->{rows} || 0), $expl->{key};
843 }
844
845 sub _prepare_sths {
846@@ -4543,7 +4536,7 @@
847
848 sub new {
849 my ( $class, %args ) = @_;
850- my @required_args = qw(OptionParser Quoter);
851+ my @required_args = qw(OptionParser TableParser Quoter);
852 foreach my $arg ( @required_args ) {
853 die "I need a $arg argument" unless $args{$arg};
854 }
855@@ -4647,25 +4640,18 @@
856 }
857
858 if ( $schema_obj ) {
859- if ( $schema_obj->{ddl} && $self->{TableParser} ) {
860- $schema_obj->{tbl_struct}
861- = $self->{TableParser}->parse($schema_obj->{ddl});
862- }
863-
864- delete $schema_obj->{ddl} unless $self->{keep_ddl};
865- delete $schema_obj->{tbl_status} unless $self->{keep_tbl_status};
866-
867 if ( my $schema = $self->{Schema} ) {
868 $schema->add_schema_object($schema_obj);
869 }
870- PTDEBUG && _d('Next schema object:', $schema_obj->{db}, $schema_obj->{tbl});
871+ PTDEBUG && _d('Next schema object:',
872+ $schema_obj->{db}, $schema_obj->{tbl});
873 }
874
875 return $schema_obj;
876 }
877
878 sub _iterate_files {
879- my ( $self ) = @_;
880+ my ( $self ) = @_;
881
882 if ( !$self->{fh} ) {
883 my ($fh, $file) = $self->{file_itr}->();
884@@ -4708,14 +4694,14 @@
885 next CHUNK;
886 }
887 $ddl =~ s/ \*\/;\Z/;/; # remove end of version comment
888-
889- my ($engine) = $ddl =~ m/\).*?(?:ENGINE|TYPE)=(\w+)/;
890-
891- if ( !$engine || $self->engine_is_allowed($engine) ) {
892+ my $tbl_struct = $self->{TableParser}->parse($ddl);
893+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
894 return {
895- db => $self->{db},
896- tbl => $tbl,
897- ddl => $ddl,
898+ db => $self->{db},
899+ tbl => $tbl,
900+ name => $self->{Quoter}->quote($self->{db}, $tbl),
901+ ddl => $ddl,
902+ tbl_struct => $tbl_struct,
903 };
904 }
905 }
906@@ -4732,6 +4718,7 @@
907 sub _iterate_dbh {
908 my ( $self ) = @_;
909 my $q = $self->{Quoter};
910+ my $tp = $self->{TableParser};
911 my $dbh = $self->{dbh};
912 PTDEBUG && _d('Getting next schema object from dbh', $dbh);
913
914@@ -4770,30 +4757,15 @@
915 }
916
917 while ( my $tbl = shift @{$self->{tbls}} ) {
918- my $tbl_status;
919- if ( $self->{filters}->{'engines'}
920- || $self->{filters}->{'ignore-engines'}
921- || $self->{keep_tbl_status} )
922- {
923- my $sql = "SHOW TABLE STATUS FROM " . $q->quote($self->{db})
924- . " LIKE \'$tbl\'";
925- PTDEBUG && _d($sql);
926- $tbl_status = $dbh->selectrow_hashref($sql);
927- PTDEBUG && _d(Dumper($tbl_status));
928- }
929-
930- if ( !$tbl_status
931- || $self->engine_is_allowed($tbl_status->{engine}) ) {
932- my $ddl;
933- if ( my $tp = $self->{TableParser} ) {
934- $ddl = $tp->get_create_table($dbh, $self->{db}, $tbl);
935- }
936-
937+ my $ddl = $tp->get_create_table($dbh, $self->{db}, $tbl);
938+ my $tbl_struct = $tp->parse($ddl);
939+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
940 return {
941 db => $self->{db},
942 tbl => $tbl,
943+ name => $q->quote($self->{db}, $tbl),
944 ddl => $ddl,
945- tbl_status => $tbl_status,
946+ tbl_struct => $tbl_struct,
947 };
948 }
949 }
950@@ -4897,7 +4869,11 @@
951
952 sub engine_is_allowed {
953 my ( $self, $engine ) = @_;
954- die "I need an engine argument" unless $engine;
955+
956+ if ( !$engine ) {
957+ PTDEBUG && _d('No engine specified; allowing the table');
958+ return 1;
959+ }
960
961 $engine = lc $engine;
962
963@@ -6253,13 +6229,12 @@
964 }
965
966 my $schema_iter = new SchemaIterator(
967- dbh => $master_dbh,
968- resume => $last_chunk ? $q->quote(@{$last_chunk}{qw(db tbl)})
969- : "",
970- keep_tbl_status => 1,
971- OptionParser => $o,
972- TableParser => $tp,
973- Quoter => $q,
974+ dbh => $master_dbh,
975+ resume => $last_chunk ? $q->quote(@{$last_chunk}{qw(db tbl)})
976+ : "",
977+ OptionParser => $o,
978+ TableParser => $tp,
979+ Quoter => $q,
980 );
981
982 if ( $last_chunk &&
983@@ -6334,13 +6309,13 @@
984 my $chunk_size_limit = $o->get('chunk-size-limit');
985 my @too_large;
986 foreach my $slave ( @$slaves ) {
987+ # get_row_estimate() returns (row_est, index), but
988+ # we only need the row_est. Maybe in the future we'll
989+ # care what index MySQL will use on a slave.
990 my ($n_rows) = NibbleIterator::get_row_estimate(
991- Cxn => $slave,
992- tbl => $tbl,
993- where => $o->get('where') || "1=1",
994- OptionParser => $o,
995- TableParser => $tp,
996- Quoter => $q,
997+ Cxn => $slave,
998+ tbl => $tbl,
999+ where => $o->get('where'),
1000 );
1001 PTDEBUG && _d('Table on', $slave->name(),
1002 'has', $n_rows, 'rows');
1003
1004=== modified file 'bin/pt-table-sync'
1005--- bin/pt-table-sync 2012-03-30 16:37:16 +0000
1006+++ bin/pt-table-sync 2012-03-31 16:06:20 +0000
1007@@ -959,7 +959,7 @@
1008 $opt->{value} = ($pre || '') . $num;
1009 }
1010 else {
1011- $self->save_error("Invalid size for --$opt->{long}");
1012+ $self->save_error("Invalid size for --$opt->{long}: $val");
1013 }
1014 return;
1015 }
1016@@ -1285,12 +1285,14 @@
1017 sub as_string {
1018 my ( $self, $dsn, $props ) = @_;
1019 return $dsn unless ref $dsn;
1020- my %allowed = $props ? map { $_=>1 } @$props : ();
1021+ my @keys = $props ? @$props : sort keys %$dsn;
1022 return join(',',
1023- map { "$_=" . ($_ eq 'p' ? '...' : $dsn->{$_}) }
1024- grep { defined $dsn->{$_} && $self->{opts}->{$_} }
1025- grep { !$props || $allowed{$_} }
1026- sort keys %$dsn );
1027+ map { "$_=" . ($_ eq 'p' ? '...' : $dsn->{$_}) }
1028+ grep {
1029+ exists $self->{opts}->{$_}
1030+ && exists $dsn->{$_}
1031+ && defined $dsn->{$_}
1032+ } @keys);
1033 }
1034
1035 sub usage {
1036@@ -1741,19 +1743,58 @@
1037 return bless $self, $class;
1038 }
1039
1040+sub get_create_table {
1041+ my ( $self, $dbh, $db, $tbl ) = @_;
1042+ die "I need a dbh parameter" unless $dbh;
1043+ die "I need a db parameter" unless $db;
1044+ die "I need a tbl parameter" unless $tbl;
1045+ my $q = $self->{Quoter};
1046+
1047+ my $new_sql_mode
1048+ = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
1049+ . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
1050+ . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
1051+ . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
1052+
1053+ my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
1054+ . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
1055+
1056+ PTDEBUG && _d($new_sql_mode);
1057+ eval { $dbh->do($new_sql_mode); };
1058+ PTDEBUG && $EVAL_ERROR && _d($EVAL_ERROR);
1059+
1060+ my $use_sql = 'USE ' . $q->quote($db);
1061+ PTDEBUG && _d($dbh, $use_sql);
1062+ $dbh->do($use_sql);
1063+
1064+ my $show_sql = "SHOW CREATE TABLE " . $q->quote($db, $tbl);
1065+ PTDEBUG && _d($show_sql);
1066+ my $href;
1067+ eval { $href = $dbh->selectrow_hashref($show_sql); };
1068+ if ( $EVAL_ERROR ) {
1069+ PTDEBUG && _d($EVAL_ERROR);
1070+
1071+ PTDEBUG && _d($old_sql_mode);
1072+ $dbh->do($old_sql_mode);
1073+
1074+ return;
1075+ }
1076+
1077+ PTDEBUG && _d($old_sql_mode);
1078+ $dbh->do($old_sql_mode);
1079+
1080+ my ($key) = grep { m/create (?:table|view)/i } keys %$href;
1081+ if ( !$key ) {
1082+ die "Error: no 'Create Table' or 'Create View' in result set from "
1083+ . "$show_sql: " . Dumper($href);
1084+ }
1085+
1086+ return $href->{$key};
1087+}
1088+
1089 sub parse {
1090 my ( $self, $ddl, $opts ) = @_;
1091 return unless $ddl;
1092- if ( ref $ddl eq 'ARRAY' ) {
1093- if ( lc $ddl->[0] eq 'table' ) {
1094- $ddl = $ddl->[1];
1095- }
1096- else {
1097- return {
1098- engine => 'VIEW',
1099- };
1100- }
1101- }
1102
1103 if ( $ddl !~ m/CREATE (?:TEMPORARY )?TABLE `/ ) {
1104 die "Cannot parse table definition; is ANSI quoting "
1105@@ -2060,41 +2101,31 @@
1106 return $ddl;
1107 }
1108
1109-sub remove_secondary_indexes {
1110- my ( $self, $ddl ) = @_;
1111- my $sec_indexes_ddl;
1112- my $tbl_struct = $self->parse($ddl);
1113-
1114- if ( ($tbl_struct->{engine} || '') =~ m/InnoDB/i ) {
1115- my $clustered_key = $tbl_struct->{clustered_key};
1116- $clustered_key ||= '';
1117-
1118- my @sec_indexes = map {
1119- my $key_def = $_->{ddl};
1120- $key_def =~ s/([\(\)])/\\$1/g;
1121- $ddl =~ s/\s+$key_def//i;
1122-
1123- my $key_ddl = "ADD $_->{ddl}";
1124- $key_ddl .= ',' unless $key_ddl =~ m/,$/;
1125- $key_ddl;
1126- }
1127- grep { $_->{name} ne $clustered_key }
1128- values %{$tbl_struct->{keys}};
1129- PTDEBUG && _d('Secondary indexes:', Dumper(\@sec_indexes));
1130-
1131- if ( @sec_indexes ) {
1132- $sec_indexes_ddl = join(' ', @sec_indexes);
1133- $sec_indexes_ddl =~ s/,$//;
1134- }
1135-
1136- $ddl =~ s/,(\n\) )/$1/s;
1137- }
1138- else {
1139- PTDEBUG && _d('Not removing secondary indexes from',
1140- $tbl_struct->{engine}, 'table');
1141- }
1142-
1143- return $ddl, $sec_indexes_ddl, $tbl_struct;
1144+sub get_table_status {
1145+ my ( $self, $dbh, $db, $like ) = @_;
1146+ my $q = $self->{Quoter};
1147+ my $sql = "SHOW TABLE STATUS FROM " . $q->quote($db);
1148+ my @params;
1149+ if ( $like ) {
1150+ $sql .= ' LIKE ?';
1151+ push @params, $like;
1152+ }
1153+ PTDEBUG && _d($sql, @params);
1154+ my $sth = $dbh->prepare($sql);
1155+ eval { $sth->execute(@params); };
1156+ if ($EVAL_ERROR) {
1157+ PTDEBUG && _d($EVAL_ERROR);
1158+ return;
1159+ }
1160+ my @tables = @{$sth->fetchall_arrayref({})};
1161+ @tables = map {
1162+ my %tbl; # Make a copy with lowercased keys
1163+ @tbl{ map { lc $_ } keys %$_ } = values %$_;
1164+ $tbl{engine} ||= $tbl{type} || $tbl{comment};
1165+ delete $tbl{type};
1166+ \%tbl;
1167+ } @tables;
1168+ return @tables;
1169 }
1170
1171 sub _d {
1172@@ -5445,11 +5476,12 @@
1173
1174 eval {
1175 if ( my $timeout = $args{wait} ) {
1176- my $wait = $args{wait_retry_args}->{wait} || 10;
1177+ my $ms = $self->{MasterSlave};
1178 my $tries = $args{wait_retry_args}->{tries} || 3;
1179+ my $wait;
1180 $self->{Retry}->retry(
1181- wait => sub { sleep $wait; },
1182 tries => $tries,
1183+ wait => sub { sleep $args{wait_retry_args}->{wait} || 10 },
1184 try => sub {
1185 my ( %args ) = @_;
1186
1187@@ -5457,12 +5489,18 @@
1188 warn "Retrying MASTER_POS_WAIT() for --wait $timeout...";
1189 }
1190
1191- my $ms = $self->{MasterSlave};
1192- my $wait = $ms->wait_for_master(
1193+ $wait = $ms->wait_for_master(
1194 master_status => $ms->get_master_status($src->{misc_dbh}),
1195 slave_dbh => $dst->{dbh},
1196 timeout => $timeout,
1197 );
1198+ if ( defined $wait->{result} && $wait->{result} != -1 ) {
1199+ return; # slave caught up
1200+ }
1201+ die; # call fail
1202+ },
1203+ fail => sub {
1204+ my (%args) = @_;
1205 if ( !defined $wait->{result} ) {
1206 my $msg;
1207 if ( $wait->{waited} ) {
1208@@ -5477,20 +5515,14 @@
1209 $msg .= " Sleeping $wait seconds then retrying "
1210 . ($tries - $args{tryno}) . " more times.";
1211 }
1212- warn $msg;
1213- return;
1214+ warn "$msg\n";
1215+ return 1; # call wait, call try
1216 }
1217 elsif ( $wait->{result} == -1 ) {
1218- die "Slave did not catch up to its master after waiting "
1219- . "$timeout seconds with MASTER_POS_WAIT. Try inceasing "
1220- . "the --wait time, or disable this feature by specifying "
1221- . "--wait 0.";
1222- }
1223- else {
1224- return $result; # slave caught up
1225+ return 0; # call final_fail
1226 }
1227 },
1228- on_failure => sub {
1229+ final_fail => sub {
1230 die "Slave did not catch up to its master after $tries attempts "
1231 . "of waiting $timeout seconds with MASTER_POS_WAIT. "
1232 . "Check that the slave is running, increase the --wait "
1233@@ -5603,23 +5635,21 @@
1234 die "I need a $arg argument" unless defined $args{$arg};
1235 }
1236 my ($tbl_struct, $index) = @args{@required_args};
1237- my @cols = $args{cols} ? @{$args{cols}} : @{$tbl_struct->{cols}};
1238+ my @cols = $args{cols} ? @{$args{cols}} : @{$tbl_struct->{cols}};
1239 my $q = $self->{Quoter};
1240
1241 die "Index '$index' does not exist in table"
1242 unless exists $tbl_struct->{keys}->{$index};
1243+ PTDEBUG && _d('Will ascend index', $index);
1244
1245 my @asc_cols = @{$tbl_struct->{keys}->{$index}->{cols}};
1246- my @asc_slice;
1247-
1248- @asc_cols = @{$tbl_struct->{keys}->{$index}->{cols}};
1249- PTDEBUG && _d('Will ascend index', $index);
1250- PTDEBUG && _d('Will ascend columns', join(', ', @asc_cols));
1251 if ( $args{asc_first} ) {
1252 @asc_cols = $asc_cols[0];
1253 PTDEBUG && _d('Ascending only first column');
1254 }
1255+ PTDEBUG && _d('Will ascend columns', join(', ', @asc_cols));
1256
1257+ my @asc_slice;
1258 my %col_posn = do { my $i = 0; map { $_ => $i++ } @cols };
1259 foreach my $col ( @asc_cols ) {
1260 if ( !exists $col_posn{$col} ) {
1261@@ -6720,292 +6750,427 @@
1262 # ###########################################################################
1263
1264 # ###########################################################################
1265-# SchemaIterator r7141
1266-# Don't update this package!
1267+# SchemaIterator package
1268+# This package is a copy without comments from the original. The original
1269+# with comments and its test file can be found in the Bazaar repository at,
1270+# lib/SchemaIterator.pm
1271+# t/lib/SchemaIterator.t
1272+# See https://launchpad.net/percona-toolkit for more information.
1273 # ###########################################################################
1274+{
1275 package SchemaIterator;
1276
1277 use strict;
1278 use warnings FATAL => 'all';
1279-
1280 use English qw(-no_match_vars);
1281+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
1282+
1283 use Data::Dumper;
1284 $Data::Dumper::Indent = 1;
1285 $Data::Dumper::Sortkeys = 1;
1286 $Data::Dumper::Quotekeys = 0;
1287
1288-use constant PTDEBUG => $ENV{PTDEBUG} || 0;
1289+my $open_comment = qr{/\*!\d{5} };
1290+my $tbl_name = qr{
1291+ CREATE\s+
1292+ (?:TEMPORARY\s+)?
1293+ TABLE\s+
1294+ (?:IF NOT EXISTS\s+)?
1295+ ([^\(]+)
1296+}x;
1297+
1298
1299 sub new {
1300 my ( $class, %args ) = @_;
1301- foreach my $arg ( qw(Quoter) ) {
1302+ my @required_args = qw(OptionParser TableParser Quoter);
1303+ foreach my $arg ( @required_args ) {
1304 die "I need a $arg argument" unless $args{$arg};
1305 }
1306+
1307+ my ($file_itr, $dbh) = @args{qw(file_itr dbh)};
1308+ die "I need either a dbh or file_itr argument"
1309+ if (!$dbh && !$file_itr) || ($dbh && $file_itr);
1310+
1311+ my %resume;
1312+ if ( my $table = $args{resume} ) {
1313+ PTDEBUG && _d('Will resume from or after', $table);
1314+ my ($db, $tbl) = $args{Quoter}->split_unquote($table);
1315+ die "Resume table must be database-qualified: $table"
1316+ unless $db && $tbl;
1317+ $resume{db} = $db;
1318+ $resume{tbl} = $tbl;
1319+ }
1320+
1321 my $self = {
1322 %args,
1323- filter => undef,
1324- dbs => [],
1325+ resume => \%resume,
1326+ filters => _make_filters(%args),
1327 };
1328+
1329 return bless $self, $class;
1330 }
1331
1332-sub make_filter {
1333- my ( $self, $o ) = @_;
1334- my @lines = (
1335- 'sub {',
1336- ' my ( $dbh, $db, $tbl ) = @_;',
1337- ' my $engine = undef;',
1338- );
1339-
1340-
1341- my @permit_dbs = _make_filter('unless', '$db', $o->get('databases'))
1342- if $o->has('databases');
1343- my @reject_dbs = _make_filter('if', '$db', $o->get('ignore-databases'))
1344- if $o->has('ignore-databases');
1345- my @dbs_regex;
1346- if ( $o->has('databases-regex') && (my $p = $o->get('databases-regex')) ) {
1347- push @dbs_regex, " return 0 unless \$db && (\$db =~ m/$p/o);";
1348- }
1349- my @reject_dbs_regex;
1350- if ( $o->has('ignore-databases-regex')
1351- && (my $p = $o->get('ignore-databases-regex')) ) {
1352- push @reject_dbs_regex, " return 0 if \$db && (\$db =~ m/$p/o);";
1353- }
1354- if ( @permit_dbs || @reject_dbs || @dbs_regex || @reject_dbs_regex ) {
1355- push @lines,
1356- ' if ( $db ) {',
1357- (@permit_dbs ? @permit_dbs : ()),
1358- (@reject_dbs ? @reject_dbs : ()),
1359- (@dbs_regex ? @dbs_regex : ()),
1360- (@reject_dbs_regex ? @reject_dbs_regex : ()),
1361- ' }';
1362- }
1363-
1364- if ( $o->has('tables') || $o->has('ignore-tables')
1365- || $o->has('ignore-tables-regex') ) {
1366-
1367- my $have_qtbl = 0;
1368- my $have_only_qtbls = 0;
1369- my %qtbls;
1370-
1371- my @permit_tbls;
1372- my @permit_qtbls;
1373- my %permit_qtbls;
1374- if ( $o->get('tables') ) {
1375- my %tbls;
1376- map {
1377- if ( $_ =~ m/\./ ) {
1378- $permit_qtbls{$_} = 1;
1379- }
1380- else {
1381- $tbls{$_} = 1;
1382- }
1383- } keys %{ $o->get('tables') };
1384- @permit_tbls = _make_filter('unless', '$tbl', \%tbls);
1385- @permit_qtbls = _make_filter('unless', '$qtbl', \%permit_qtbls);
1386-
1387- if ( @permit_qtbls ) {
1388- push @lines,
1389- ' my $qtbl = ($db ? "$db." : "") . ($tbl ? $tbl : "");';
1390- $have_qtbl = 1;
1391- }
1392- }
1393-
1394- my @reject_tbls;
1395- my @reject_qtbls;
1396- my %reject_qtbls;
1397- if ( $o->get('ignore-tables') ) {
1398- my %tbls;
1399- map {
1400- if ( $_ =~ m/\./ ) {
1401- $reject_qtbls{$_} = 1;
1402- }
1403- else {
1404- $tbls{$_} = 1;
1405- }
1406- } keys %{ $o->get('ignore-tables') };
1407- @reject_tbls= _make_filter('if', '$tbl', \%tbls);
1408- @reject_qtbls = _make_filter('if', '$qtbl', \%reject_qtbls);
1409-
1410- if ( @reject_qtbls && !$have_qtbl ) {
1411- push @lines,
1412- ' my $qtbl = ($db ? "$db." : "") . ($tbl ? $tbl : "");';
1413- }
1414- }
1415-
1416- if ( keys %permit_qtbls && !@permit_dbs ) {
1417- my $dbs = {};
1418- map {
1419- my ($db, undef) = split(/\./, $_);
1420- $dbs->{$db} = 1;
1421- } keys %permit_qtbls;
1422- PTDEBUG && _d('Adding restriction "--databases',
1423- (join(',', keys %$dbs) . '"'));
1424- if ( keys %$dbs ) {
1425- $o->set('databases', $dbs);
1426- return $self->make_filter($o);
1427- }
1428- }
1429-
1430- my @tbls_regex;
1431- if ( $o->has('tables-regex') && (my $p = $o->get('tables-regex')) ) {
1432- push @tbls_regex, " return 0 unless \$tbl && (\$tbl =~ m/$p/o);";
1433- }
1434- my @reject_tbls_regex;
1435- if ( $o->has('ignore-tables-regex')
1436- && (my $p = $o->get('ignore-tables-regex')) ) {
1437- push @reject_tbls_regex,
1438- " return 0 if \$tbl && (\$tbl =~ m/$p/o);";
1439- }
1440-
1441- my @get_eng;
1442- my @permit_engs;
1443- my @reject_engs;
1444- if ( ($o->has('engines') && $o->get('engines'))
1445- || ($o->has('ignore-engines') && $o->get('ignore-engines')) ) {
1446- push @get_eng,
1447- ' my $sql = "SHOW TABLE STATUS "',
1448- ' . ($db ? "FROM `$db`" : "")',
1449- ' . " LIKE \'$tbl\'";',
1450- ' PTDEBUG && _d($sql);',
1451- ' eval {',
1452- ' $engine = $dbh->selectrow_hashref($sql)->{engine};',
1453- ' };',
1454- ' PTDEBUG && $EVAL_ERROR && _d($EVAL_ERROR);',
1455- ' PTDEBUG && _d($tbl, "uses engine", $engine);',
1456- ' $engine = lc $engine if $engine;',
1457- @permit_engs
1458- = _make_filter('unless', '$engine', $o->get('engines'), 1);
1459- @reject_engs
1460- = _make_filter('if', '$engine', $o->get('ignore-engines'), 1)
1461- }
1462-
1463- if ( @permit_tbls || @permit_qtbls || @reject_tbls || @tbls_regex
1464- || @reject_tbls_regex || @permit_engs || @reject_engs ) {
1465- push @lines,
1466- ' if ( $tbl ) {',
1467- (@permit_tbls ? @permit_tbls : ()),
1468- (@reject_tbls ? @reject_tbls : ()),
1469- (@tbls_regex ? @tbls_regex : ()),
1470- (@reject_tbls_regex ? @reject_tbls_regex : ()),
1471- (@permit_qtbls ? @permit_qtbls : ()),
1472- (@reject_qtbls ? @reject_qtbls : ()),
1473- (@get_eng ? @get_eng : ()),
1474- (@permit_engs ? @permit_engs : ()),
1475- (@reject_engs ? @reject_engs : ()),
1476- ' }';
1477- }
1478- }
1479-
1480- push @lines,
1481- ' PTDEBUG && _d(\'Passes filters:\', $db, $tbl, $engine, $dbh);',
1482- ' return 1;', '}';
1483-
1484- my $code = join("\n", @lines);
1485- PTDEBUG && _d('filter sub:', $code);
1486- my $filter_sub= eval $code
1487- or die "Error compiling subroutine code:\n$code\n$EVAL_ERROR";
1488-
1489- return $filter_sub;
1490-}
1491-
1492-sub set_filter {
1493- my ( $self, $filter_sub ) = @_;
1494- $self->{filter} = $filter_sub;
1495- PTDEBUG && _d('Set filter sub');
1496- return;
1497-}
1498-
1499-sub get_db_itr {
1500- my ( $self, %args ) = @_;
1501- my @required_args = qw(dbh);
1502+sub _make_filters {
1503+ my ( %args ) = @_;
1504+ my @required_args = qw(OptionParser Quoter);
1505 foreach my $arg ( @required_args ) {
1506 die "I need a $arg argument" unless $args{$arg};
1507 }
1508- my ($dbh) = @args{@required_args};
1509-
1510- my $filter = $self->{filter};
1511- my @dbs;
1512- eval {
1513+ my ($o, $q) = @args{@required_args};
1514+
1515+ my %filters;
1516+
1517+
1518+ my @simple_filters = qw(
1519+ databases tables engines
1520+ ignore-databases ignore-tables ignore-engines);
1521+ FILTER:
1522+ foreach my $filter ( @simple_filters ) {
1523+ if ( $o->has($filter) ) {
1524+ my $objs = $o->get($filter);
1525+ next FILTER unless $objs && scalar keys %$objs;
1526+ my $is_table = $filter =~ m/table/ ? 1 : 0;
1527+ foreach my $obj ( keys %$objs ) {
1528+ die "Undefined value for --$filter" unless $obj;
1529+ $obj = lc $obj;
1530+ if ( $is_table ) {
1531+ my ($db, $tbl) = $q->split_unquote($obj);
1532+ $db ||= '*';
1533+ PTDEBUG && _d('Filter', $filter, 'value:', $db, $tbl);
1534+ $filters{$filter}->{$tbl} = $db;
1535+ }
1536+ else { # database
1537+ PTDEBUG && _d('Filter', $filter, 'value:', $obj);
1538+ $filters{$filter}->{$obj} = 1;
1539+ }
1540+ }
1541+ }
1542+ }
1543+
1544+ my @regex_filters = qw(
1545+ databases-regex tables-regex
1546+ ignore-databases-regex ignore-tables-regex);
1547+ REGEX_FILTER:
1548+ foreach my $filter ( @regex_filters ) {
1549+ if ( $o->has($filter) ) {
1550+ my $pat = $o->get($filter);
1551+ next REGEX_FILTER unless $pat;
1552+ $filters{$filter} = qr/$pat/;
1553+ PTDEBUG && _d('Filter', $filter, 'value:', $filters{$filter});
1554+ }
1555+ }
1556+
1557+ PTDEBUG && _d('Schema object filters:', Dumper(\%filters));
1558+ return \%filters;
1559+}
1560+
1561+sub next {
1562+ my ( $self ) = @_;
1563+
1564+ if ( !$self->{initialized} ) {
1565+ $self->{initialized} = 1;
1566+ if ( $self->{resume}->{tbl}
1567+ && !$self->table_is_allowed(@{$self->{resume}}{qw(db tbl)}) ) {
1568+ PTDEBUG && _d('Will resume after',
1569+ join('.', @{$self->{resume}}{qw(db tbl)}));
1570+ $self->{resume}->{after} = 1;
1571+ }
1572+ }
1573+
1574+ my $schema_obj;
1575+ if ( $self->{file_itr} ) {
1576+ $schema_obj= $self->_iterate_files();
1577+ }
1578+ else { # dbh
1579+ $schema_obj= $self->_iterate_dbh();
1580+ }
1581+
1582+ if ( $schema_obj ) {
1583+ if ( my $schema = $self->{Schema} ) {
1584+ $schema->add_schema_object($schema_obj);
1585+ }
1586+ PTDEBUG && _d('Next schema object:',
1587+ $schema_obj->{db}, $schema_obj->{tbl});
1588+ }
1589+
1590+ return $schema_obj;
1591+}
1592+
1593+sub _iterate_files {
1594+ my ( $self ) = @_;
1595+
1596+ if ( !$self->{fh} ) {
1597+ my ($fh, $file) = $self->{file_itr}->();
1598+ if ( !$fh ) {
1599+ PTDEBUG && _d('No more files to iterate');
1600+ return;
1601+ }
1602+ $self->{fh} = $fh;
1603+ $self->{file} = $file;
1604+ }
1605+ my $fh = $self->{fh};
1606+ PTDEBUG && _d('Getting next schema object from', $self->{file});
1607+
1608+ local $INPUT_RECORD_SEPARATOR = '';
1609+ CHUNK:
1610+ while (defined(my $chunk = <$fh>)) {
1611+ if ($chunk =~ m/Database: (\S+)/) {
1612+ my $db = $1; # XXX
1613+ $db =~ s/^`//; # strip leading `
1614+ $db =~ s/`$//; # and trailing `
1615+ if ( $self->database_is_allowed($db)
1616+ && $self->_resume_from_database($db) ) {
1617+ $self->{db} = $db;
1618+ }
1619+ }
1620+ elsif ($self->{db} && $chunk =~ m/CREATE TABLE/) {
1621+ if ($chunk =~ m/DROP VIEW IF EXISTS/) {
1622+ PTDEBUG && _d('Table is a VIEW, skipping');
1623+ next CHUNK;
1624+ }
1625+
1626+ my ($tbl) = $chunk =~ m/$tbl_name/;
1627+ $tbl =~ s/^\s*`//;
1628+ $tbl =~ s/`\s*$//;
1629+ if ( $self->_resume_from_table($tbl)
1630+ && $self->table_is_allowed($self->{db}, $tbl) ) {
1631+ my ($ddl) = $chunk =~ m/^(?:$open_comment)?(CREATE TABLE.+?;)$/ms;
1632+ if ( !$ddl ) {
1633+ warn "Failed to parse CREATE TABLE from\n" . $chunk;
1634+ next CHUNK;
1635+ }
1636+ $ddl =~ s/ \*\/;\Z/;/; # remove end of version comment
1637+ my $tbl_struct = $self->{TableParser}->parse($ddl);
1638+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
1639+ return {
1640+ db => $self->{db},
1641+ tbl => $tbl,
1642+ name => $self->{Quoter}->quote($self->{db}, $tbl),
1643+ ddl => $ddl,
1644+ tbl_struct => $tbl_struct,
1645+ };
1646+ }
1647+ }
1648+ }
1649+ } # CHUNK
1650+
1651+ PTDEBUG && _d('No more schema objects in', $self->{file});
1652+ close $self->{fh};
1653+ $self->{fh} = undef;
1654+
1655+ return $self->_iterate_files();
1656+}
1657+
1658+sub _iterate_dbh {
1659+ my ( $self ) = @_;
1660+ my $q = $self->{Quoter};
1661+ my $tp = $self->{TableParser};
1662+ my $dbh = $self->{dbh};
1663+ PTDEBUG && _d('Getting next schema object from dbh', $dbh);
1664+
1665+ if ( !defined $self->{dbs} ) {
1666 my $sql = 'SHOW DATABASES';
1667 PTDEBUG && _d($sql);
1668- @dbs = grep {
1669- my $ok = $filter ? $filter->($dbh, $_, undef) : 1;
1670- $ok = 0 if $_ =~ m/information_schema|performance_schema|lost\+found/;
1671- $ok;
1672- } @{ $dbh->selectcol_arrayref($sql) };
1673+ my @dbs = grep { $self->database_is_allowed($_) }
1674+ @{$dbh->selectcol_arrayref($sql)};
1675 PTDEBUG && _d('Found', scalar @dbs, 'databases');
1676- };
1677-
1678- PTDEBUG && $EVAL_ERROR && _d($EVAL_ERROR);
1679- my $iterator = sub {
1680- return shift @dbs;
1681- };
1682-
1683- if (wantarray) {
1684- return ($iterator, scalar @dbs);
1685- }
1686- else {
1687- return $iterator;
1688- }
1689-}
1690-
1691-sub get_tbl_itr {
1692- my ( $self, %args ) = @_;
1693- my @required_args = qw(dbh db);
1694- foreach my $arg ( @required_args ) {
1695- die "I need a $arg argument" unless $args{$arg};
1696- }
1697- my ($dbh, $db, $views) = @args{@required_args, 'views'};
1698-
1699- my $filter = $self->{filter};
1700- my @tbls;
1701- if ( $db ) {
1702- eval {
1703- my $sql = 'SHOW /*!50002 FULL*/ TABLES FROM '
1704- . $self->{Quoter}->quote($db);
1705- PTDEBUG && _d($sql);
1706- @tbls = map {
1707- $_->[0]
1708- }
1709- grep {
1710- my ($tbl, $type) = @$_;
1711- my $ok = $filter ? $filter->($dbh, $db, $tbl) : 1;
1712- if ( !$views ) {
1713- $ok = 0 if ($type || '') eq 'VIEW';
1714- }
1715- $ok;
1716- }
1717- @{ $dbh->selectall_arrayref($sql) };
1718- PTDEBUG && _d('Found', scalar @tbls, 'tables in', $db);
1719- };
1720- PTDEBUG && $EVAL_ERROR && _d($EVAL_ERROR);
1721- }
1722- else {
1723- PTDEBUG && _d('No db given so no tables');
1724- }
1725-
1726- my $iterator = sub {
1727- return shift @tbls;
1728- };
1729-
1730- if ( wantarray ) {
1731- return ($iterator, scalar @tbls);
1732- }
1733- else {
1734- return $iterator;
1735- }
1736-}
1737-
1738-sub _make_filter {
1739- my ( $cond, $var_name, $objs, $lc ) = @_;
1740- my @lines;
1741- if ( scalar keys %$objs ) {
1742- my $test = join(' || ',
1743- map { "$var_name eq '" . ($lc ? lc $_ : $_) ."'" } keys %$objs);
1744- push @lines, " return 0 $cond $var_name && ($test);",
1745- }
1746- return @lines;
1747+ $self->{dbs} = \@dbs;
1748+ }
1749+
1750+ if ( !$self->{db} ) {
1751+ do {
1752+ $self->{db} = shift @{$self->{dbs}};
1753+ } until $self->_resume_from_database($self->{db});
1754+ PTDEBUG && _d('Next database:', $self->{db});
1755+ return unless $self->{db};
1756+ }
1757+
1758+ if ( !defined $self->{tbls} ) {
1759+ my $sql = 'SHOW /*!50002 FULL*/ TABLES FROM ' . $q->quote($self->{db});
1760+ PTDEBUG && _d($sql);
1761+ my @tbls = map {
1762+ $_->[0]; # (tbl, type)
1763+ }
1764+ grep {
1765+ my ($tbl, $type) = @$_;
1766+ (!$type || ($type ne 'VIEW'))
1767+ && $self->_resume_from_table($tbl)
1768+ && $self->table_is_allowed($self->{db}, $tbl);
1769+ }
1770+ @{$dbh->selectall_arrayref($sql)};
1771+ PTDEBUG && _d('Found', scalar @tbls, 'tables in database', $self->{db});
1772+ $self->{tbls} = \@tbls;
1773+ }
1774+
1775+ while ( my $tbl = shift @{$self->{tbls}} ) {
1776+ my $ddl = $tp->get_create_table($dbh, $self->{db}, $tbl);
1777+ my $tbl_struct = $tp->parse($ddl);
1778+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
1779+ return {
1780+ db => $self->{db},
1781+ tbl => $tbl,
1782+ name => $q->quote($self->{db}, $tbl),
1783+ ddl => $ddl,
1784+ tbl_struct => $tbl_struct,
1785+ };
1786+ }
1787+ }
1788+
1789+ PTDEBUG && _d('No more tables in database', $self->{db});
1790+ $self->{db} = undef;
1791+ $self->{tbls} = undef;
1792+
1793+ return $self->_iterate_dbh();
1794+}
1795+
1796+sub database_is_allowed {
1797+ my ( $self, $db ) = @_;
1798+ die "I need a db argument" unless $db;
1799+
1800+ $db = lc $db;
1801+
1802+ my $filter = $self->{filters};
1803+
1804+ if ( $db =~ m/information_schema|performance_schema|lost\+found/ ) {
1805+ PTDEBUG && _d('Database', $db, 'is a system database, ignoring');
1806+ return 0;
1807+ }
1808+
1809+ if ( $self->{filters}->{'ignore-databases'}->{$db} ) {
1810+ PTDEBUG && _d('Database', $db, 'is in --ignore-databases list');
1811+ return 0;
1812+ }
1813+
1814+ if ( $filter->{'ignore-databases-regex'}
1815+ && $db =~ $filter->{'ignore-databases-regex'} ) {
1816+ PTDEBUG && _d('Database', $db, 'matches --ignore-databases-regex');
1817+ return 0;
1818+ }
1819+
1820+ if ( $filter->{'databases'}
1821+ && !$filter->{'databases'}->{$db} ) {
1822+ PTDEBUG && _d('Database', $db, 'is not in --databases list, ignoring');
1823+ return 0;
1824+ }
1825+
1826+ if ( $filter->{'databases-regex'}
1827+ && $db !~ $filter->{'databases-regex'} ) {
1828+ PTDEBUG && _d('Database', $db, 'does not match --databases-regex, ignoring');
1829+ return 0;
1830+ }
1831+
1832+ return 1;
1833+}
1834+
1835+sub table_is_allowed {
1836+ my ( $self, $db, $tbl ) = @_;
1837+ die "I need a db argument" unless $db;
1838+ die "I need a tbl argument" unless $tbl;
1839+
1840+ $db = lc $db;
1841+ $tbl = lc $tbl;
1842+
1843+ my $filter = $self->{filters};
1844+
1845+ if ( $db eq 'mysql' && ($tbl eq 'general_log' || $tbl eq 'slow_log') ) {
1846+ return 0;
1847+ }
1848+
1849+ if ( $filter->{'ignore-tables'}->{$tbl}
1850+ && ($filter->{'ignore-tables'}->{$tbl} eq '*'
1851+ || $filter->{'ignore-tables'}->{$tbl} eq $db) ) {
1852+ PTDEBUG && _d('Table', $tbl, 'is in --ignore-tables list');
1853+ return 0;
1854+ }
1855+
1856+ if ( $filter->{'ignore-tables-regex'}
1857+ && $tbl =~ $filter->{'ignore-tables-regex'} ) {
1858+ PTDEBUG && _d('Table', $tbl, 'matches --ignore-tables-regex');
1859+ return 0;
1860+ }
1861+
1862+ if ( $filter->{'tables'}
1863+ && !$filter->{'tables'}->{$tbl} ) {
1864+ PTDEBUG && _d('Table', $tbl, 'is not in --tables list, ignoring');
1865+ return 0;
1866+ }
1867+
1868+ if ( $filter->{'tables-regex'}
1869+ && $tbl !~ $filter->{'tables-regex'} ) {
1870+ PTDEBUG && _d('Table', $tbl, 'does not match --tables-regex, ignoring');
1871+ return 0;
1872+ }
1873+
1874+ if ( $filter->{'tables'}
1875+ && $filter->{'tables'}->{$tbl}
1876+ && $filter->{'tables'}->{$tbl} ne '*'
1877+ && $filter->{'tables'}->{$tbl} ne $db ) {
1878+ PTDEBUG && _d('Table', $tbl, 'is only allowed in database',
1879+ $filter->{'tables'}->{$tbl});
1880+ return 0;
1881+ }
1882+
1883+ return 1;
1884+}
1885+
1886+sub engine_is_allowed {
1887+ my ( $self, $engine ) = @_;
1888+
1889+ if ( !$engine ) {
1890+ PTDEBUG && _d('No engine specified; allowing the table');
1891+ return 1;
1892+ }
1893+
1894+ $engine = lc $engine;
1895+
1896+ my $filter = $self->{filters};
1897+
1898+ if ( $filter->{'ignore-engines'}->{$engine} ) {
1899+ PTDEBUG && _d('Engine', $engine, 'is in --ignore-databases list');
1900+ return 0;
1901+ }
1902+
1903+ if ( $filter->{'engines'}
1904+ && !$filter->{'engines'}->{$engine} ) {
1905+ PTDEBUG && _d('Engine', $engine, 'is not in --engines list, ignoring');
1906+ return 0;
1907+ }
1908+
1909+ return 1;
1910+}
1911+
1912+sub _resume_from_database {
1913+ my ($self, $db) = @_;
1914+
1915+ return 1 unless $self->{resume}->{db};
1916+
1917+ if ( $db eq $self->{resume}->{db} ) {
1918+ PTDEBUG && _d('At resume db', $db);
1919+ delete $self->{resume}->{db};
1920+ return 1;
1921+ }
1922+
1923+ return 0;
1924+}
1925+
1926+sub _resume_from_table {
1927+ my ($self, $tbl) = @_;
1928+
1929+ return 1 unless $self->{resume}->{tbl};
1930+
1931+ if ( $tbl eq $self->{resume}->{tbl} ) {
1932+ if ( !$self->{resume}->{after} ) {
1933+ PTDEBUG && _d('Resuming from table', $tbl);
1934+ delete $self->{resume}->{tbl};
1935+ return 1;
1936+ }
1937+ else {
1938+ PTDEBUG && _d('Resuming after table', $tbl);
1939+ delete $self->{resume}->{tbl};
1940+ }
1941+ }
1942+
1943+ return 0;
1944 }
1945
1946 sub _d {
1947@@ -7017,7 +7182,7 @@
1948 }
1949
1950 1;
1951-
1952+}
1953 # ###########################################################################
1954 # End SchemaIterator package
1955 # ###########################################################################
1956@@ -7304,48 +7469,42 @@
1957
1958 sub retry {
1959 my ( $self, %args ) = @_;
1960- my @required_args = qw(try wait);
1961+ my @required_args = qw(try fail final_fail);
1962 foreach my $arg ( @required_args ) {
1963 die "I need a $arg argument" unless $args{$arg};
1964 };
1965- my ($try, $wait) = @args{@required_args};
1966+ my ($try, $fail, $final_fail) = @args{@required_args};
1967+ my $wait = $args{wait} || sub { sleep 1; };
1968 my $tries = $args{tries} || 3;
1969
1970+ my $last_error;
1971 my $tryno = 0;
1972+ TRY:
1973 while ( ++$tryno <= $tries ) {
1974- PTDEBUG && _d("Retry", $tryno, "of", $tries);
1975+ PTDEBUG && _d("Try", $tryno, "of", $tries);
1976 my $result;
1977 eval {
1978 $result = $try->(tryno=>$tryno);
1979 };
1980+ if ( $EVAL_ERROR ) {
1981+ PTDEBUG && _d("Try code failed:", $EVAL_ERROR);
1982+ $last_error = $EVAL_ERROR;
1983
1984- if ( defined $result ) {
1985+ if ( $tryno < $tries ) { # more retries
1986+ my $retry = $fail->(tryno=>$tryno, error=>$last_error);
1987+ last TRY unless $retry;
1988+ PTDEBUG && _d("Calling wait code");
1989+ $wait->(tryno=>$tryno);
1990+ }
1991+ }
1992+ else {
1993 PTDEBUG && _d("Try code succeeded");
1994- if ( my $on_success = $args{on_success} ) {
1995- PTDEBUG && _d("Calling on_success code");
1996- $on_success->(tryno=>$tryno, result=>$result);
1997- }
1998 return $result;
1999 }
2000-
2001- if ( $EVAL_ERROR ) {
2002- PTDEBUG && _d("Try code died:", $EVAL_ERROR);
2003- die $EVAL_ERROR unless $args{retry_on_die};
2004- }
2005-
2006- if ( $tryno < $tries ) {
2007- PTDEBUG && _d("Try code failed, calling wait code");
2008- $wait->(tryno=>$tryno);
2009- }
2010- }
2011-
2012- PTDEBUG && _d("Try code did not succeed");
2013- if ( my $on_failure = $args{on_failure} ) {
2014- PTDEBUG && _d("Calling on_failure code");
2015- $on_failure->();
2016- }
2017-
2018- return;
2019+ }
2020+
2021+ PTDEBUG && _d('Try code did not succeed');
2022+ return $final_fail->(error=>$last_error);
2023 }
2024
2025 sub _d {
2026@@ -8006,27 +8165,20 @@
2027 tbl => undef, # set later
2028 };
2029
2030- my $si = new SchemaIterator(
2031- Quoter => $args{Quoter},
2032+ my $schema_iter = new SchemaIterator(
2033+ dbh => $src->{dbh},
2034+ OptionParser => $o,
2035+ TableParser => $args{TableParser},
2036+ Quoter => $args{Quoter},
2037 );
2038- $si->set_filter($si->make_filter($o));
2039
2040 # Make a list of all dbs.tbls on the source. It's more efficient this
2041 # way because it avoids open/closing a dbh for each tbl and dsn, unless
2042 # we pre-opened the dsn. It would also cause confusing verbose output.
2043 my @dbs_tbls;
2044- my $next_db = $si->get_db_itr(dbh => $src->{dbh});
2045- while ( my $db = $next_db->() ) {
2046- PTDEBUG && _d('Getting tables from', $db);
2047- my $next_tbl = $si->get_tbl_itr(
2048- dbh => $src->{dbh},
2049- db => $db,
2050- views => 0,
2051- );
2052- while ( my $tbl = $next_tbl->() ) {
2053- PTDEBUG && _d('Got table', $tbl);
2054- push @dbs_tbls, { db => $db, tbl => $tbl };
2055- }
2056+ while ( my $tbl = $schema_iter->next() ) {
2057+ PTDEBUG && _d('Got table', $tbl->{db}, $tbl->{tbl});
2058+ push @dbs_tbls, $tbl;
2059 }
2060
2061 my $exit_status = 0;
2062@@ -8048,6 +8200,7 @@
2063 lock_server(src => $src, dst => $dst, %args);
2064
2065 foreach my $db_tbl ( @dbs_tbls ) {
2066+ $src->{tbl_struct} = $db_tbl->{tbl_struct};
2067 $src->{db} = $dst->{db} = $db_tbl->{db};
2068 $src->{tbl} = $dst->{tbl} = $db_tbl->{tbl};
2069
2070@@ -8194,8 +8347,9 @@
2071 $start_ts = get_server_time($src->{dbh}) if $o->get('verbose');
2072
2073 # This will either die if there's a problem or return the tbl struct.
2074- my $tbl_struct = ok_to_sync($src, $dst, %args);
2075-
2076+ ok_to_sync($src, $dst, %args);
2077+ my $tbl_struct = $src->{tbl_struct};
2078+
2079 if ( my $diff = $args{diff} ) {
2080 PTDEBUG && _d('Converting checksum diff to WHERE:', Dumper($diff));
2081 $args{where} = diff_where(
2082@@ -8568,35 +8722,30 @@
2083 }
2084 my ($src, $dst, $dp, $q, $vp, $tp, $du, $syncer, $o) = @args{@required_args};
2085
2086- # First things first: check that the src and dst dbs and tbls exist.
2087- # This can fail in cases like h=host,D=bad,t=also_bad (i.e. simple
2088- # user error). It can also fail when syncing all dbs/tbls with sync_all()
2089- # because the dst db/tbl is assumed to be the same as the src but
2090- # this isn't always the case.
2091- my $src_tbl_ddl;
2092- eval {
2093- # FYI: get_create_table() does USE db but doesn't eval it.
2094- $src->{dbh}->do("USE `$src->{db}`");
2095- $src_tbl_ddl = $du->get_create_table($src->{dbh}, $q,
2096- $src->{db}, $src->{tbl});
2097- };
2098- die $EVAL_ERROR if $EVAL_ERROR;
2099-
2100- my $dst_tbl_ddl;
2101- eval {
2102- # FYI: get_create_table() does USE db but doesn't eval it.
2103- $dst->{dbh}->do("USE `$dst->{db}`");
2104- $dst_tbl_ddl = $du->get_create_table($dst->{dbh}, $q,
2105- $dst->{db}, $dst->{tbl});
2106- };
2107- die $EVAL_ERROR if $EVAL_ERROR;
2108-
2109- # This doesn't work at the moment when syncing different table names.
2110- # Check that src.db.tbl has the exact same schema as dst.db.tbl.
2111- # if ( $o->get('check-schema') && ($src_tbl_ddl ne $dst_tbl_ddl) ) {
2112- # die "Source and destination tables have different schemas";
2113- # }
2114- my $tbl_struct = $tp->parse($src_tbl_ddl);
2115+ if ( !$src->{tbl_struct} ) {
2116+ eval {
2117+ $src->{ddl} = $tp->get_create_table(
2118+ $src->{dbh}, $src->{db}, $src->{tbl});
2119+ $src->{tbl_struct} = $tp->parse($src->{ddl});
2120+
2121+ };
2122+ if ( $EVAL_ERROR ) {
2123+ die "Error getting table structure for $src->{db}.$src->{tbl} on "
2124+ . $dp->as_string($src->{dsn}) . "$EVAL_ERROR\nEnsure that "
2125+ . "the table exists and is accessible.\n";
2126+ }
2127+ }
2128+
2129+ # Check that the dst has the table.
2130+ my $dst_has_table = $tp->check_table(
2131+ dbh => $dst->{dbh},
2132+ db => $dst->{db},
2133+ tbl => $dst->{tbl},
2134+ );
2135+ if ( !$dst_has_table ) {
2136+ die "Table $dst->{db}.$dst->{tbl} does not exist on "
2137+ . $dp->as_string($dst->{dsn}) . "\n";
2138+ }
2139
2140 # Check that the user has all the necessary privs on the tbls.
2141 if ( $o->get('check-privileges') ) {
2142@@ -8629,7 +8778,7 @@
2143 }
2144 }
2145
2146- return $tbl_struct;
2147+ return;
2148 }
2149
2150 # Sub: filter_diffs
2151
2152=== modified file 'lib/NibbleIterator.pm'
2153--- lib/NibbleIterator.pm 2012-02-06 20:29:08 +0000
2154+++ lib/NibbleIterator.pm 2012-03-31 16:06:20 +0000
2155@@ -511,22 +511,15 @@
2156
2157 sub get_row_estimate {
2158 my (%args) = @_;
2159- my @required_args = qw(Cxn tbl OptionParser TableParser Quoter);
2160- my ($cxn, $tbl, $o, $tp, $q) = @args{@required_args};
2161+ my @required_args = qw(Cxn tbl);
2162+ my ($cxn, $tbl) = @args{@required_args};
2163
2164- if ( $args{where} ) {
2165- PTDEBUG && _d('WHERE clause, using explain plan for row estimate');
2166- my $table = $q->quote(@{$tbl}{qw(db tbl)});
2167- my $sql = "EXPLAIN SELECT * FROM $table WHERE $args{where}";
2168- PTDEBUG && _d($sql);
2169- my $expl = $cxn->dbh()->selectrow_hashref($sql);
2170- PTDEBUG && _d(Dumper($expl));
2171- return ($expl->{rows} || 0), $expl->{key};
2172- }
2173- else {
2174- PTDEBUG && _d('No WHERE clause, using table status for row estimate');
2175- return $tbl->{tbl_status}->{rows} || 0;
2176- }
2177+ my $sql = "EXPLAIN SELECT * FROM $tbl->{name} "
2178+ . "WHERE " . ($args{where} || '1=1');
2179+ PTDEBUG && _d($sql);
2180+ my $expl = $cxn->dbh()->selectrow_hashref($sql);
2181+ PTDEBUG && _d(Dumper($expl));
2182+ return ($expl->{rows} || 0), $expl->{key};
2183 }
2184
2185 sub _prepare_sths {
2186
2187=== modified file 'lib/SchemaIterator.pm'
2188--- lib/SchemaIterator.pm 2012-01-19 19:46:56 +0000
2189+++ lib/SchemaIterator.pm 2012-03-31 16:06:20 +0000
2190@@ -55,12 +55,10 @@
2191 # OptionParser - <OptionParser> object. All filters are gotten from this
2192 # obj: --databases, --tables, etc.
2193 # Quoter - <Quoter> object.
2194+# TableParser - <TableParser> object get tbl_struct.
2195 #
2196 # Optional Arguments:
2197 # Schema - <Schema> object to initialize while iterating.
2198-# TableParser - <TableParser> object get tbl_struct.
2199-# keep_ddl - Keep SHOW CREATE TABLE (default false).
2200-# keep_tbl_status - Keep SHOW TABLE STATUS (default false).
2201 # resume - Skip tables so first call to <next()> returns
2202 # this "db.table".
2203 #
2204@@ -68,7 +66,7 @@
2205 # SchemaIterator object
2206 sub new {
2207 my ( $class, %args ) = @_;
2208- my @required_args = qw(OptionParser Quoter);
2209+ my @required_args = qw(OptionParser TableParser Quoter);
2210 foreach my $arg ( @required_args ) {
2211 die "I need a $arg argument" unless $args{$arg};
2212 }
2213@@ -222,25 +220,18 @@
2214 }
2215
2216 if ( $schema_obj ) {
2217- if ( $schema_obj->{ddl} && $self->{TableParser} ) {
2218- $schema_obj->{tbl_struct}
2219- = $self->{TableParser}->parse($schema_obj->{ddl});
2220- }
2221-
2222- delete $schema_obj->{ddl} unless $self->{keep_ddl};
2223- delete $schema_obj->{tbl_status} unless $self->{keep_tbl_status};
2224-
2225 if ( my $schema = $self->{Schema} ) {
2226 $schema->add_schema_object($schema_obj);
2227 }
2228- PTDEBUG && _d('Next schema object:', $schema_obj->{db}, $schema_obj->{tbl});
2229+ PTDEBUG && _d('Next schema object:',
2230+ $schema_obj->{db}, $schema_obj->{tbl});
2231 }
2232
2233 return $schema_obj;
2234 }
2235
2236 sub _iterate_files {
2237- my ( $self ) = @_;
2238+ my ( $self ) = @_;
2239
2240 if ( !$self->{fh} ) {
2241 my ($fh, $file) = $self->{file_itr}->();
2242@@ -295,14 +286,14 @@
2243 next CHUNK;
2244 }
2245 $ddl =~ s/ \*\/;\Z/;/; # remove end of version comment
2246-
2247- my ($engine) = $ddl =~ m/\).*?(?:ENGINE|TYPE)=(\w+)/;
2248-
2249- if ( !$engine || $self->engine_is_allowed($engine) ) {
2250+ my $tbl_struct = $self->{TableParser}->parse($ddl);
2251+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
2252 return {
2253- db => $self->{db},
2254- tbl => $tbl,
2255- ddl => $ddl,
2256+ db => $self->{db},
2257+ tbl => $tbl,
2258+ name => $self->{Quoter}->quote($self->{db}, $tbl),
2259+ ddl => $ddl,
2260+ tbl_struct => $tbl_struct,
2261 };
2262 }
2263 }
2264@@ -321,6 +312,7 @@
2265 sub _iterate_dbh {
2266 my ( $self ) = @_;
2267 my $q = $self->{Quoter};
2268+ my $tp = $self->{TableParser};
2269 my $dbh = $self->{dbh};
2270 PTDEBUG && _d('Getting next schema object from dbh', $dbh);
2271
2272@@ -360,33 +352,15 @@
2273 }
2274
2275 while ( my $tbl = shift @{$self->{tbls}} ) {
2276- # If there are engine filters, we have to get the table status.
2277- # Else, get it if the user wants to keep it since they'll expect
2278- # it to be available.
2279- my $tbl_status;
2280- if ( $self->{filters}->{'engines'}
2281- || $self->{filters}->{'ignore-engines'}
2282- || $self->{keep_tbl_status} )
2283- {
2284- my $sql = "SHOW TABLE STATUS FROM " . $q->quote($self->{db})
2285- . " LIKE \'$tbl\'";
2286- PTDEBUG && _d($sql);
2287- $tbl_status = $dbh->selectrow_hashref($sql);
2288- PTDEBUG && _d(Dumper($tbl_status));
2289- }
2290-
2291- if ( !$tbl_status
2292- || $self->engine_is_allowed($tbl_status->{engine}) ) {
2293- my $ddl;
2294- if ( my $tp = $self->{TableParser} ) {
2295- $ddl = $tp->get_create_table($dbh, $self->{db}, $tbl);
2296- }
2297-
2298+ my $ddl = $tp->get_create_table($dbh, $self->{db}, $tbl);
2299+ my $tbl_struct = $tp->parse($ddl);
2300+ if ( $self->engine_is_allowed($tbl_struct->{engine}) ) {
2301 return {
2302 db => $self->{db},
2303 tbl => $tbl,
2304+ name => $q->quote($self->{db}, $tbl),
2305 ddl => $ddl,
2306- tbl_status => $tbl_status,
2307+ tbl_struct => $tbl_struct,
2308 };
2309 }
2310 }
2311@@ -502,7 +476,15 @@
2312
2313 sub engine_is_allowed {
2314 my ( $self, $engine ) = @_;
2315- die "I need an engine argument" unless $engine;
2316+
2317+ if ( !$engine ) {
2318+ # This normally doesn't happen, but it can if the user
2319+ # is iterating a file of their own table dumps, i.e. that
2320+ # weren't created by mysqldump, so there's no ENGINE=
2321+ # on the CREATE TABLE.
2322+ PTDEBUG && _d('No engine specified; allowing the table');
2323+ return 1;
2324+ }
2325
2326 $engine = lc $engine;
2327
2328
2329=== modified file 't/lib/SchemaIterator.t'
2330--- t/lib/SchemaIterator.t 2012-03-07 16:35:50 +0000
2331+++ t/lib/SchemaIterator.t 2012-03-31 16:06:20 +0000
2332@@ -9,7 +9,7 @@
2333 use strict;
2334 use warnings FATAL => 'all';
2335 use English qw(-no_match_vars);
2336-use Test::More tests => 31;
2337+use Test::More tests => 29;
2338
2339 use SchemaIterator;
2340 use FileIterator;
2341@@ -32,7 +32,7 @@
2342 my $sb = new Sandbox(basedir => '/tmp', DSNParser => $dp);
2343 my $dbh = $sb->get_dbh_for('master');
2344
2345-my $tp;
2346+my $tp = new TableParser(Quoter => $q);
2347 my $fi = new FileIterator();
2348 my $o = new OptionParser(description => 'SchemaIterator');
2349 $o->get_specs("$trunk/bin/pt-table-checksum");
2350@@ -55,7 +55,6 @@
2351 my $file_itr = $fi->get_file_itr(@{$args{files}});
2352 $si = new SchemaIterator(
2353 file_itr => $file_itr,
2354- keep_ddl => defined $args{keep_ddl} ? $args{keep_ddl} : 1,
2355 resume => $args{resume},
2356 OptionParser => $o,
2357 Quoter => $q,
2358@@ -65,7 +64,6 @@
2359 else {
2360 $si = new SchemaIterator(
2361 dbh => $dbh,
2362- keep_ddl => defined $args{keep_ddl} ? $args{keep_ddl} : 1,
2363 resume => $args{resume},
2364 OptionParser => $o,
2365 Quoter => $q,
2366@@ -311,7 +309,6 @@
2367 # ########################################################################
2368 # Getting CREATE TALBE (ddl).
2369 # ########################################################################
2370- $tp = new TableParser(Quoter => $q);
2371 test_so(
2372 filters => [qw(-t mysql.user)],
2373 result => $sandbox_version ge '5.1' ? "$out/mysql-user-ddl.txt"
2374@@ -319,9 +316,6 @@
2375 test_name => "Get CREATE TABLE with dbh",
2376 );
2377
2378- # Kill the TableParser obj in case the next tests don't want to use it.
2379- $tp = undef;
2380-
2381 $sb->wipe_clean($dbh);
2382 };
2383
2384@@ -370,49 +364,10 @@
2385
2386 is(
2387 $n_tbl_structs,
2388- 0,
2389- 'No tbl_struct without TableParser'
2390-);
2391-
2392-$tp = new TableParser(Quoter => $q);
2393-
2394-$objs = test_so(
2395- files => ["$in/dump001.txt"],
2396- result => "", # hack to let return_objs work
2397- test_name => "", # hack to let return_objs work
2398- return_objs => 1,
2399-);
2400-
2401-$n_tbl_structs = grep { exists $_->{tbl_struct} } @$objs;
2402-
2403-is(
2404- $n_tbl_structs,
2405 scalar @$objs,
2406 'Got tbl_struct for each schema object'
2407 );
2408
2409-# Kill the TableParser obj in case the next tests don't want to use it.
2410-$tp = undef;
2411-
2412-# ############################################################################
2413-# keep_ddl
2414-# ############################################################################
2415-$objs = test_so(
2416- files => ["$in/dump001.txt"],
2417- result => "", # hack to let return_objs work
2418- test_name => "", # hack to let return_objs work
2419- return_objs => 1,
2420- keep_ddl => 0,
2421-);
2422-
2423-my $n_ddls = grep { exists $_->{ddl} } @$objs;
2424-
2425-is(
2426- $n_ddls,
2427- 0,
2428- 'DDL deleted unless keep_ddl'
2429-);
2430-
2431 # ############################################################################
2432 # Resume
2433 # ############################################################################
2434
2435=== modified file 't/lib/samples/SchemaIterator/all-dbs-tbls.txt'
2436--- t/lib/samples/SchemaIterator/all-dbs-tbls.txt 2011-09-23 14:07:03 +0000
2437+++ t/lib/samples/SchemaIterator/all-dbs-tbls.txt 2012-03-31 16:06:20 +0000
2438@@ -1,37 +1,523 @@
2439 mysql.columns_priv
2440+CREATE TABLE `columns_priv` (
2441+ `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
2442+ `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2443+ `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
2444+ `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2445+ `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2446+ `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2447+ `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
2448+ PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
2449+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges'
2450+
2451 mysql.db
2452+CREATE TABLE `db` (
2453+ `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
2454+ `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2455+ `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
2456+ `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2457+ `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2458+ `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2459+ `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2460+ `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2461+ `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2462+ `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2463+ `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2464+ `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2465+ `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2466+ `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2467+ `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2468+ `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2469+ `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2470+ `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2471+ `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2472+ `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2473+ `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2474+ `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2475+ PRIMARY KEY (`Host`,`Db`,`User`),
2476+ KEY `User` (`User`)
2477+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
2478+
2479 mysql.event
2480+CREATE TABLE `event` (
2481+ `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
2482+ `name` char(64) NOT NULL DEFAULT '',
2483+ `body` longblob NOT NULL,
2484+ `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
2485+ `execute_at` datetime DEFAULT NULL,
2486+ `interval_value` int(11) DEFAULT NULL,
2487+ `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
2488+ `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2489+ `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
2490+ `last_executed` datetime DEFAULT NULL,
2491+ `starts` datetime DEFAULT NULL,
2492+ `ends` datetime DEFAULT NULL,
2493+ `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
2494+ `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
2495+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
2496+ `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
2497+ `originator` int(10) unsigned NOT NULL,
2498+ `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
2499+ `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
2500+ `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
2501+ `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
2502+ `body_utf8` longblob,
2503+ PRIMARY KEY (`db`,`name`)
2504+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events'
2505+
2506 mysql.func
2507+CREATE TABLE `func` (
2508+ `name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2509+ `ret` tinyint(1) NOT NULL DEFAULT '0',
2510+ `dl` char(128) COLLATE utf8_bin NOT NULL DEFAULT '',
2511+ `type` enum('function','aggregate') CHARACTER SET utf8 NOT NULL,
2512+ PRIMARY KEY (`name`)
2513+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions'
2514+
2515 mysql.help_category
2516+CREATE TABLE `help_category` (
2517+ `help_category_id` smallint(5) unsigned NOT NULL,
2518+ `name` char(64) NOT NULL,
2519+ `parent_category_id` smallint(5) unsigned DEFAULT NULL,
2520+ `url` char(128) NOT NULL,
2521+ PRIMARY KEY (`help_category_id`),
2522+ UNIQUE KEY `name` (`name`)
2523+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help categories'
2524+
2525 mysql.help_keyword
2526+CREATE TABLE `help_keyword` (
2527+ `help_keyword_id` int(10) unsigned NOT NULL,
2528+ `name` char(64) NOT NULL,
2529+ PRIMARY KEY (`help_keyword_id`),
2530+ UNIQUE KEY `name` (`name`)
2531+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help keywords'
2532+
2533 mysql.help_relation
2534+CREATE TABLE `help_relation` (
2535+ `help_topic_id` int(10) unsigned NOT NULL,
2536+ `help_keyword_id` int(10) unsigned NOT NULL,
2537+ PRIMARY KEY (`help_keyword_id`,`help_topic_id`)
2538+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='keyword-topic relation'
2539+
2540 mysql.help_topic
2541+CREATE TABLE `help_topic` (
2542+ `help_topic_id` int(10) unsigned NOT NULL,
2543+ `name` char(64) NOT NULL,
2544+ `help_category_id` smallint(5) unsigned NOT NULL,
2545+ `description` text NOT NULL,
2546+ `example` text NOT NULL,
2547+ `url` char(128) NOT NULL,
2548+ PRIMARY KEY (`help_topic_id`),
2549+ UNIQUE KEY `name` (`name`)
2550+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help topics'
2551+
2552 mysql.host
2553+CREATE TABLE `host` (
2554+ `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
2555+ `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2556+ `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2557+ `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2558+ `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2559+ `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2560+ `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2561+ `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2562+ `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2563+ `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2564+ `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2565+ `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2566+ `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2567+ `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2568+ `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2569+ `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2570+ `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2571+ `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2572+ `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2573+ `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2574+ PRIMARY KEY (`Host`,`Db`)
2575+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges; Merged with database privileges'
2576+
2577 mysql.ndb_binlog_index
2578+CREATE TABLE `ndb_binlog_index` (
2579+ `Position` bigint(20) unsigned NOT NULL,
2580+ `File` varchar(255) NOT NULL,
2581+ `epoch` bigint(20) unsigned NOT NULL,
2582+ `inserts` bigint(20) unsigned NOT NULL,
2583+ `updates` bigint(20) unsigned NOT NULL,
2584+ `deletes` bigint(20) unsigned NOT NULL,
2585+ `schemaops` bigint(20) unsigned NOT NULL,
2586+ PRIMARY KEY (`epoch`)
2587+) ENGINE=MyISAM DEFAULT CHARSET=latin1
2588+
2589 mysql.plugin
2590+CREATE TABLE `plugin` (
2591+ `name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2592+ `dl` char(128) COLLATE utf8_bin NOT NULL DEFAULT '',
2593+ PRIMARY KEY (`name`)
2594+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='MySQL plugins'
2595+
2596 mysql.proc
2597+CREATE TABLE `proc` (
2598+ `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
2599+ `name` char(64) NOT NULL DEFAULT '',
2600+ `type` enum('FUNCTION','PROCEDURE') NOT NULL,
2601+ `specific_name` char(64) NOT NULL DEFAULT '',
2602+ `language` enum('SQL') NOT NULL DEFAULT 'SQL',
2603+ `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
2604+ `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
2605+ `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
2606+ `param_list` blob NOT NULL,
2607+ `returns` longblob NOT NULL,
2608+ `body` longblob NOT NULL,
2609+ `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
2610+ `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2611+ `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
2612+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
2613+ `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
2614+ `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
2615+ `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
2616+ `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
2617+ `body_utf8` longblob,
2618+ PRIMARY KEY (`db`,`name`,`type`)
2619+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'
2620+
2621 mysql.procs_priv
2622+CREATE TABLE `procs_priv` (
2623+ `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
2624+ `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2625+ `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
2626+ `Routine_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2627+ `Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,
2628+ `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
2629+ `Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',
2630+ `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2631+ PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
2632+ KEY `Grantor` (`Grantor`)
2633+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges'
2634+
2635 mysql.servers
2636+CREATE TABLE `servers` (
2637+ `Server_name` char(64) NOT NULL DEFAULT '',
2638+ `Host` char(64) NOT NULL DEFAULT '',
2639+ `Db` char(64) NOT NULL DEFAULT '',
2640+ `Username` char(64) NOT NULL DEFAULT '',
2641+ `Password` char(64) NOT NULL DEFAULT '',
2642+ `Port` int(4) NOT NULL DEFAULT '0',
2643+ `Socket` char(64) NOT NULL DEFAULT '',
2644+ `Wrapper` char(64) NOT NULL DEFAULT '',
2645+ `Owner` char(64) NOT NULL DEFAULT '',
2646+ PRIMARY KEY (`Server_name`)
2647+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table'
2648+
2649 mysql.tables_priv
2650+CREATE TABLE `tables_priv` (
2651+ `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
2652+ `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2653+ `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
2654+ `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
2655+ `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
2656+ `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2657+ `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
2658+ `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
2659+ PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
2660+ KEY `Grantor` (`Grantor`)
2661+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
2662+
2663 mysql.time_zone
2664+CREATE TABLE `time_zone` (
2665+ `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
2666+ `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
2667+ PRIMARY KEY (`Time_zone_id`)
2668+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zones'
2669+
2670 mysql.time_zone_leap_second
2671+CREATE TABLE `time_zone_leap_second` (
2672+ `Transition_time` bigint(20) NOT NULL,
2673+ `Correction` int(11) NOT NULL,
2674+ PRIMARY KEY (`Transition_time`)
2675+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Leap seconds information for time zones'
2676+
2677 mysql.time_zone_name
2678+CREATE TABLE `time_zone_name` (
2679+ `Name` char(64) NOT NULL,
2680+ `Time_zone_id` int(10) unsigned NOT NULL,
2681+ PRIMARY KEY (`Name`)
2682+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone names'
2683+
2684 mysql.time_zone_transition
2685+CREATE TABLE `time_zone_transition` (
2686+ `Time_zone_id` int(10) unsigned NOT NULL,
2687+ `Transition_time` bigint(20) NOT NULL,
2688+ `Transition_type_id` int(10) unsigned NOT NULL,
2689+ PRIMARY KEY (`Time_zone_id`,`Transition_time`)
2690+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transitions'
2691+
2692 mysql.time_zone_transition_type
2693+CREATE TABLE `time_zone_transition_type` (
2694+ `Time_zone_id` int(10) unsigned NOT NULL,
2695+ `Transition_type_id` int(10) unsigned NOT NULL,
2696+ `Offset` int(11) NOT NULL DEFAULT '0',
2697+ `Is_DST` tinyint(3) unsigned NOT NULL DEFAULT '0',
2698+ `Abbreviation` char(8) NOT NULL DEFAULT '',
2699+ PRIMARY KEY (`Time_zone_id`,`Transition_type_id`)
2700+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transition types'
2701+
2702 mysql.user
2703+CREATE TABLE `user` (
2704+ `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
2705+ `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
2706+ `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
2707+ `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2708+ `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2709+ `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2710+ `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2711+ `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2712+ `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2713+ `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2714+ `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2715+ `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2716+ `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2717+ `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2718+ `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2719+ `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2720+ `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2721+ `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2722+ `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2723+ `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2724+ `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2725+ `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2726+ `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2727+ `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2728+ `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2729+ `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2730+ `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2731+ `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2732+ `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2733+ `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2734+ `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
2735+ `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
2736+ `ssl_cipher` blob NOT NULL,
2737+ `x509_issuer` blob NOT NULL,
2738+ `x509_subject` blob NOT NULL,
2739+ `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
2740+ `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
2741+ `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
2742+ `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
2743+ PRIMARY KEY (`Host`,`User`)
2744+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
2745+
2746 sakila.actor
2747+CREATE TABLE `actor` (
2748+ `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
2749+ `first_name` varchar(45) NOT NULL,
2750+ `last_name` varchar(45) NOT NULL,
2751+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2752+ PRIMARY KEY (`actor_id`),
2753+ KEY `idx_actor_last_name` (`last_name`)
2754+) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
2755+
2756 sakila.address
2757+CREATE TABLE `address` (
2758+ `address_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
2759+ `address` varchar(50) NOT NULL,
2760+ `address2` varchar(50) DEFAULT NULL,
2761+ `district` varchar(20) NOT NULL,
2762+ `city_id` smallint(5) unsigned NOT NULL,
2763+ `postal_code` varchar(10) DEFAULT NULL,
2764+ `phone` varchar(20) NOT NULL,
2765+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2766+ PRIMARY KEY (`address_id`),
2767+ KEY `idx_fk_city_id` (`city_id`),
2768+ CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
2769+) ENGINE=InnoDB AUTO_INCREMENT=606 DEFAULT CHARSET=utf8
2770+
2771 sakila.category
2772+CREATE TABLE `category` (
2773+ `category_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
2774+ `name` varchar(25) NOT NULL,
2775+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2776+ PRIMARY KEY (`category_id`)
2777+) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
2778+
2779 sakila.city
2780+CREATE TABLE `city` (
2781+ `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
2782+ `city` varchar(50) NOT NULL,
2783+ `country_id` smallint(5) unsigned NOT NULL,
2784+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2785+ PRIMARY KEY (`city_id`),
2786+ KEY `idx_fk_country_id` (`country_id`),
2787+ CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
2788+) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8
2789+
2790 sakila.country
2791+CREATE TABLE `country` (
2792+ `country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
2793+ `country` varchar(50) NOT NULL,
2794+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2795+ PRIMARY KEY (`country_id`)
2796+) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
2797+
2798 sakila.customer
2799+CREATE TABLE `customer` (
2800+ `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
2801+ `store_id` tinyint(3) unsigned NOT NULL,
2802+ `first_name` varchar(45) NOT NULL,
2803+ `last_name` varchar(45) NOT NULL,
2804+ `email` varchar(50) DEFAULT NULL,
2805+ `address_id` smallint(5) unsigned NOT NULL,
2806+ `active` tinyint(1) NOT NULL DEFAULT '1',
2807+ `create_date` datetime NOT NULL,
2808+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2809+ PRIMARY KEY (`customer_id`),
2810+ KEY `idx_fk_store_id` (`store_id`),
2811+ KEY `idx_fk_address_id` (`address_id`),
2812+ KEY `idx_last_name` (`last_name`),
2813+ CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE,
2814+ CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE
2815+) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8
2816+
2817 sakila.film
2818+CREATE TABLE `film` (
2819+ `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
2820+ `title` varchar(255) NOT NULL,
2821+ `description` text,
2822+ `release_year` year(4) DEFAULT NULL,
2823+ `language_id` tinyint(3) unsigned NOT NULL,
2824+ `original_language_id` tinyint(3) unsigned DEFAULT NULL,
2825+ `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
2826+ `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
2827+ `length` smallint(5) unsigned DEFAULT NULL,
2828+ `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
2829+ `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
2830+ `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
2831+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2832+ PRIMARY KEY (`film_id`),
2833+ KEY `idx_title` (`title`),
2834+ KEY `idx_fk_language_id` (`language_id`),
2835+ KEY `idx_fk_original_language_id` (`original_language_id`),
2836+ CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
2837+ CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
2838+) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
2839+
2840 sakila.film_actor
2841+CREATE TABLE `film_actor` (
2842+ `actor_id` smallint(5) unsigned NOT NULL,
2843+ `film_id` smallint(5) unsigned NOT NULL,
2844+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2845+ PRIMARY KEY (`actor_id`,`film_id`),
2846+ KEY `idx_fk_film_id` (`film_id`),
2847+ CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
2848+ CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
2849+) ENGINE=InnoDB DEFAULT CHARSET=utf8
2850+
2851 sakila.film_category
2852+CREATE TABLE `film_category` (
2853+ `film_id` smallint(5) unsigned NOT NULL,
2854+ `category_id` tinyint(3) unsigned NOT NULL,
2855+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2856+ PRIMARY KEY (`film_id`,`category_id`),
2857+ KEY `fk_film_category_category` (`category_id`),
2858+ CONSTRAINT `fk_film_category_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE,
2859+ CONSTRAINT `fk_film_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON UPDATE CASCADE
2860+) ENGINE=InnoDB DEFAULT CHARSET=utf8
2861+
2862 sakila.film_text
2863+CREATE TABLE `film_text` (
2864+ `film_id` smallint(6) NOT NULL,
2865+ `title` varchar(255) NOT NULL,
2866+ `description` text,
2867+ PRIMARY KEY (`film_id`),
2868+ FULLTEXT KEY `idx_title_description` (`title`,`description`)
2869+) ENGINE=MyISAM DEFAULT CHARSET=utf8
2870+
2871 sakila.inventory
2872+CREATE TABLE `inventory` (
2873+ `inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
2874+ `film_id` smallint(5) unsigned NOT NULL,
2875+ `store_id` tinyint(3) unsigned NOT NULL,
2876+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2877+ PRIMARY KEY (`inventory_id`),
2878+ KEY `idx_fk_film_id` (`film_id`),
2879+ KEY `idx_store_id_film_id` (`store_id`,`film_id`),
2880+ CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE,
2881+ CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
2882+) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8
2883+
2884 sakila.language
2885+CREATE TABLE `language` (
2886+ `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
2887+ `name` char(20) NOT NULL,
2888+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2889+ PRIMARY KEY (`language_id`)
2890+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
2891+
2892 sakila.payment
2893+CREATE TABLE `payment` (
2894+ `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
2895+ `customer_id` smallint(5) unsigned NOT NULL,
2896+ `staff_id` tinyint(3) unsigned NOT NULL,
2897+ `rental_id` int(11) DEFAULT NULL,
2898+ `amount` decimal(5,2) NOT NULL,
2899+ `payment_date` datetime NOT NULL,
2900+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2901+ PRIMARY KEY (`payment_id`),
2902+ KEY `idx_fk_staff_id` (`staff_id`),
2903+ KEY `idx_fk_customer_id` (`customer_id`),
2904+ KEY `fk_payment_rental` (`rental_id`),
2905+ CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
2906+ CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
2907+ CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
2908+) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
2909+
2910 sakila.rental
2911+CREATE TABLE `rental` (
2912+ `rental_id` int(11) NOT NULL AUTO_INCREMENT,
2913+ `rental_date` datetime NOT NULL,
2914+ `inventory_id` mediumint(8) unsigned NOT NULL,
2915+ `customer_id` smallint(5) unsigned NOT NULL,
2916+ `return_date` datetime DEFAULT NULL,
2917+ `staff_id` tinyint(3) unsigned NOT NULL,
2918+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2919+ PRIMARY KEY (`rental_id`),
2920+ UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
2921+ KEY `idx_fk_inventory_id` (`inventory_id`),
2922+ KEY `idx_fk_customer_id` (`customer_id`),
2923+ KEY `idx_fk_staff_id` (`staff_id`),
2924+ CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE,
2925+ CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
2926+ CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE
2927+) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
2928+
2929 sakila.staff
2930+CREATE TABLE `staff` (
2931+ `staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
2932+ `first_name` varchar(45) NOT NULL,
2933+ `last_name` varchar(45) NOT NULL,
2934+ `address_id` smallint(5) unsigned NOT NULL,
2935+ `picture` blob,
2936+ `email` varchar(50) DEFAULT NULL,
2937+ `store_id` tinyint(3) unsigned NOT NULL,
2938+ `active` tinyint(1) NOT NULL DEFAULT '1',
2939+ `username` varchar(16) NOT NULL,
2940+ `password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
2941+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2942+ PRIMARY KEY (`staff_id`),
2943+ KEY `idx_fk_store_id` (`store_id`),
2944+ KEY `idx_fk_address_id` (`address_id`),
2945+ CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE,
2946+ CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE
2947+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
2948+
2949 sakila.store
2950+CREATE TABLE `store` (
2951+ `store_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
2952+ `manager_staff_id` tinyint(3) unsigned NOT NULL,
2953+ `address_id` smallint(5) unsigned NOT NULL,
2954+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2955+ PRIMARY KEY (`store_id`),
2956+ UNIQUE KEY `idx_unique_manager` (`manager_staff_id`),
2957+ KEY `idx_fk_address_id` (`address_id`),
2958+ CONSTRAINT `fk_store_staff` FOREIGN KEY (`manager_staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE,
2959+ CONSTRAINT `fk_store_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE
2960+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
2961+
2962
2963=== modified file 't/lib/samples/SchemaIterator/resume-from-ignored-sakila-payment.txt'
2964--- t/lib/samples/SchemaIterator/resume-from-ignored-sakila-payment.txt 2011-11-08 17:20:59 +0000
2965+++ t/lib/samples/SchemaIterator/resume-from-ignored-sakila-payment.txt 2012-03-31 16:06:20 +0000
2966@@ -1,3 +1,52 @@
2967 sakila.rental
2968+CREATE TABLE `rental` (
2969+ `rental_id` int(11) NOT NULL AUTO_INCREMENT,
2970+ `rental_date` datetime NOT NULL,
2971+ `inventory_id` mediumint(8) unsigned NOT NULL,
2972+ `customer_id` smallint(5) unsigned NOT NULL,
2973+ `return_date` datetime DEFAULT NULL,
2974+ `staff_id` tinyint(3) unsigned NOT NULL,
2975+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2976+ PRIMARY KEY (`rental_id`),
2977+ UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
2978+ KEY `idx_fk_inventory_id` (`inventory_id`),
2979+ KEY `idx_fk_customer_id` (`customer_id`),
2980+ KEY `idx_fk_staff_id` (`staff_id`),
2981+ CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE,
2982+ CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
2983+ CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE
2984+) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
2985+
2986 sakila.staff
2987+CREATE TABLE `staff` (
2988+ `staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
2989+ `first_name` varchar(45) NOT NULL,
2990+ `last_name` varchar(45) NOT NULL,
2991+ `address_id` smallint(5) unsigned NOT NULL,
2992+ `picture` blob,
2993+ `email` varchar(50) DEFAULT NULL,
2994+ `store_id` tinyint(3) unsigned NOT NULL,
2995+ `active` tinyint(1) NOT NULL DEFAULT '1',
2996+ `username` varchar(16) NOT NULL,
2997+ `password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
2998+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2999+ PRIMARY KEY (`staff_id`),
3000+ KEY `idx_fk_store_id` (`store_id`),
3001+ KEY `idx_fk_address_id` (`address_id`),
3002+ CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE,
3003+ CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE
3004+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
3005+
3006 sakila.store
3007+CREATE TABLE `store` (
3008+ `store_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
3009+ `manager_staff_id` tinyint(3) unsigned NOT NULL,
3010+ `address_id` smallint(5) unsigned NOT NULL,
3011+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3012+ PRIMARY KEY (`store_id`),
3013+ UNIQUE KEY `idx_unique_manager` (`manager_staff_id`),
3014+ KEY `idx_fk_address_id` (`address_id`),
3015+ CONSTRAINT `fk_store_staff` FOREIGN KEY (`manager_staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE,
3016+ CONSTRAINT `fk_store_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE
3017+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
3018+
3019
3020=== modified file 't/lib/samples/SchemaIterator/resume-from-sakila-payment.txt'
3021--- t/lib/samples/SchemaIterator/resume-from-sakila-payment.txt 2011-09-27 19:06:24 +0000
3022+++ t/lib/samples/SchemaIterator/resume-from-sakila-payment.txt 2012-03-31 16:06:20 +0000
3023@@ -1,4 +1,70 @@
3024 sakila.payment
3025+CREATE TABLE `payment` (
3026+ `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
3027+ `customer_id` smallint(5) unsigned NOT NULL,
3028+ `staff_id` tinyint(3) unsigned NOT NULL,
3029+ `rental_id` int(11) DEFAULT NULL,
3030+ `amount` decimal(5,2) NOT NULL,
3031+ `payment_date` datetime NOT NULL,
3032+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3033+ PRIMARY KEY (`payment_id`),
3034+ KEY `idx_fk_staff_id` (`staff_id`),
3035+ KEY `idx_fk_customer_id` (`customer_id`),
3036+ KEY `fk_payment_rental` (`rental_id`),
3037+ CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
3038+ CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
3039+ CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
3040+) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
3041+
3042 sakila.rental
3043+CREATE TABLE `rental` (
3044+ `rental_id` int(11) NOT NULL AUTO_INCREMENT,
3045+ `rental_date` datetime NOT NULL,
3046+ `inventory_id` mediumint(8) unsigned NOT NULL,
3047+ `customer_id` smallint(5) unsigned NOT NULL,
3048+ `return_date` datetime DEFAULT NULL,
3049+ `staff_id` tinyint(3) unsigned NOT NULL,
3050+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3051+ PRIMARY KEY (`rental_id`),
3052+ UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
3053+ KEY `idx_fk_inventory_id` (`inventory_id`),
3054+ KEY `idx_fk_customer_id` (`customer_id`),
3055+ KEY `idx_fk_staff_id` (`staff_id`),
3056+ CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE,
3057+ CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
3058+ CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE
3059+) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
3060+
3061 sakila.staff
3062+CREATE TABLE `staff` (
3063+ `staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
3064+ `first_name` varchar(45) NOT NULL,
3065+ `last_name` varchar(45) NOT NULL,
3066+ `address_id` smallint(5) unsigned NOT NULL,
3067+ `picture` blob,
3068+ `email` varchar(50) DEFAULT NULL,
3069+ `store_id` tinyint(3) unsigned NOT NULL,
3070+ `active` tinyint(1) NOT NULL DEFAULT '1',
3071+ `username` varchar(16) NOT NULL,
3072+ `password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
3073+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3074+ PRIMARY KEY (`staff_id`),
3075+ KEY `idx_fk_store_id` (`store_id`),
3076+ KEY `idx_fk_address_id` (`address_id`),
3077+ CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE,
3078+ CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE
3079+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
3080+
3081 sakila.store
3082+CREATE TABLE `store` (
3083+ `store_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
3084+ `manager_staff_id` tinyint(3) unsigned NOT NULL,
3085+ `address_id` smallint(5) unsigned NOT NULL,
3086+ `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3087+ PRIMARY KEY (`store_id`),
3088+ UNIQUE KEY `idx_unique_manager` (`manager_staff_id`),
3089+ KEY `idx_fk_address_id` (`address_id`),
3090+ CONSTRAINT `fk_store_staff` FOREIGN KEY (`manager_staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE,
3091+ CONSTRAINT `fk_store_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE
3092+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
3093+
3094
3095=== modified file 't/pt-table-sync/issue_408.t'
3096--- t/pt-table-sync/issue_408.t 2011-12-22 22:50:52 +0000
3097+++ t/pt-table-sync/issue_408.t 2012-03-31 16:06:20 +0000
3098@@ -47,7 +47,7 @@
3099 $output = `$trunk/bin/pt-table-sync --databases test --execute h=127.1,P=12345,u=msandbox,p=msandbox h=127.1,P=12348 2>&1`;
3100 like(
3101 $output,
3102- qr/Unknown database 'test'/,
3103+ qr/Table test.foo does not exist on P=12348/,
3104 'Warn about --databases missing on dest host'
3105 );
3106

Subscribers

People subscribed via source and target branches