Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Proposed code to implement foreign-key-search-schemas option
diff --git a/bin/pt-online-schema-change b/bin/pt-online-schema-change
index 84063c6..cf2659c 100755
--- a/bin/pt-online-schema-change
+++ b/bin/pt-online-schema-change
@@ -7796,6 +7796,8 @@ sub main {
$o->set('drop-old-table', 0);
}
+ my $search_schema = $o->get('foreign-key-search-schemas');
+
# Explicit --chunk-size disable auto chunk sizing.
$o->set('chunk-time', 0) if $o->got('chunk-size');
@@ -7863,6 +7865,13 @@ sub main {
if ( $alter_fk_method eq 'drop_swap' && !$o->get('drop-new-table') ) {
$o->save_error("--alter-foreign-keys-method=drop_swap does not work with --no-drop-new-table.");
}
+
+ if ( $search_schema ne 'all'
+ && $search_schema ne 'same')
+ {
+ $o->save_error("Invalid --foreign-key-search-schemas value: $search_schema");
+ }
+
}
eval {
@@ -8234,9 +8243,10 @@ sub main {
}
else {
$child_tables = find_child_tables(
- tbl => $orig_tbl,
- Cxn => $cxn,
- Quoter => $q,
+ tbl => $orig_tbl,
+ Cxn => $cxn,
+ Quoter => $q,
+ search_schema => $search_schema
);
if ( !$child_tables ) {
if ( $alter_fk_method ) {
@@ -9792,11 +9802,11 @@ sub check_orig_table {
sub find_child_tables {
my ( %args ) = @_;
- my @required_args = qw(tbl Cxn Quoter);
+ my @required_args = qw(tbl Cxn Quoter search_schema);
foreach my $arg ( @required_args ) {
die "I need a $arg argument" unless $args{$arg};
}
- my ($tbl, $cxn, $q) = @args{@required_args};
+ my ($tbl, $cxn, $q, $search_schema) = @args{@required_args};
if ( lc($tbl->{tbl_struct}->{engine} || '') eq 'myisam' ) {
PTDEBUG && _d(q{MyISAM table, not looking for child tables});
@@ -9807,8 +9817,9 @@ sub find_child_tables {
my $sql = "SELECT table_schema, table_name "
. "FROM information_schema.key_column_usage "
- . "WHERE constraint_schema='$tbl->{db}' "
+ . "WHERE referenced_table_schema='$tbl->{db}' "
. "AND referenced_table_name='$tbl->{tbl}'";
+ $sql .= " AND table_schema='$tbl->{db}'" if $search_schema eq 'same';
PTDEBUG && _d($sql);
my $rows = $cxn->dbh()->selectall_arrayref($sql);
if ( !$rows || !@$rows ) {
@@ -10733,6 +10744,28 @@ tool's built-in functionality if desired.
=back
+=item --foreign-key-search-schemas
+
+type: string; default: all
+
+This option specifies which schemas are searched for foreign key references to
+the table being changed.
+
+=over
+
+=item all
+
+Search across all schemas for references to the table. This option may lead to
+poor performance if queries against information_schema are slow.
+
+=item same
+
+Restrict the search for foreign keys to the table's schema. The may speed up
+the search for foreign keys but will miss any foreign keys with cross-schema
+references.
+
+=back
+
=item --ask-pass
Prompt for a password when connecting to MySQL.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment