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