Tutorial :disable foreign key check deleting InnoDB tables Perl Script



Question:

Am fairly new to using MySQL and a total novice at Perl but am trying to hack someone elses script to help me out. I got the script from here. It looks great so far but it fails as the tables have some foreign key check going on. I could go through phpmyadmin and try and delete them all one by one but this takes for ever and is my third time of having to do this :( My query is, can this script be ammended to include:

`SET FOREIGN_KEY_CHECKS = 0;  

before it runs the drop table command? I tried to follow the script through but could not find a definitive command part of the script (probably due to ignorance/lack of understanding). Any help greatly appreciated.

#!/usr/bin/perl    use strict;  use DBI;    my $hostname = '';  my $database = '';  my $username = '';  my $password = '';    my $dbh = DBI->connect("dbi:mysql:${database}:$hostname",    $username, $password) or die "Error: $DBI::errstr\n";    my $sth = $dbh->prepare("SHOW TABLES");  $sth->execute or die "SQL Error: $DBI::errstr\n";  my $i = 0;  my @all_tables = ();  while(my $table = $sth->fetchrow_array)  {    $i++;    print "table $i: $table\n";    push @all_tables, $table;  }  my $total_table_count = $i;    print "Enter string or regex to match tables to "    . "delete (won't delete yet): ";  my $regex = <STDIN>;  chomp $regex;    $i = 0;  my @matching_tables = ();  foreach my $table (@all_tables)  {    if($table =~ /$regex/i)    {      $i++;      print "matching table $i: $table\n";      push @matching_tables, $table;    }  }  my $matching_table_count = $i;    if($matching_table_count)  {    print "$matching_table_count out of $total_table_count "      . "tables match, and will be deleted.\n";    print "Delete tables now? [y/n] ";    my $decision = <STDIN>;    chomp $decision;      $i = 0;    if($decision =~ /y/i)    {      foreach my $table (@matching_tables)      {        $i++;        print "deleting table $i: $table\n";        my $sth = $dbh->prepare("DROP TABLE $table");        $sth->execute or die "SQL Error: $DBI::errstr\n";      }    }    else    {      print "Not deleting any tables.\n";    }  }  else  {    print "No matching tables.\n";  }  


Solution:1

Setting the FOREIGN_KEY_CHECKS value to zero:

SET FOREIGN_KEY_CHECKS = 0;  

...prior to the drop scripts will disable foreign key constraints instance wide. Because you can have more than one catalog/database on a MySQL instance, this risks impacting anything else database-wise.

The general habit is to script these in order of key dependency, deleting/truncating data from parent tables before children.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »