Databases
StackOverflow2013
Postsdbo
POdisable foreign key check deleting InnoDB tables Perl Script
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POdisable foreign key check deleting InnoDB tables Perl Script
    primarykey
    data
    text
    <p>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 <a href="http://encodable.com/tech/blog/2009/07/09/MySQL_Drop_Multiple_Tables_From_a_Database" rel="noreferrer">here</a>. 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:</p> <pre><code>`SET FOREIGN_KEY_CHECKS = 0; </code></pre> <p>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.</p> <pre><code>#!/usr/bin/perl use strict; use DBI; my $hostname = ''; my $database = ''; my $username = ''; my $password = ''; my $dbh = DBI-&gt;connect("dbi:mysql:${database}:$hostname", $username, $password) or die "Error: $DBI::errstr\n"; my $sth = $dbh-&gt;prepare("SHOW TABLES"); $sth-&gt;execute or die "SQL Error: $DBI::errstr\n"; my $i = 0; my @all_tables = (); while(my $table = $sth-&gt;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 = &lt;STDIN&gt;; 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 = &lt;STDIN&gt;; chomp $decision; $i = 0; if($decision =~ /y/i) { foreach my $table (@matching_tables) { $i++; print "deleting table $i: $table\n"; my $sth = $dbh-&gt;prepare("DROP TABLE $table"); $sth-&gt;execute or die "SQL Error: $DBI::errstr\n"; } } else { print "Not deleting any tables.\n"; } } else { print "No matching tables.\n"; } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTim Alexander
    1. USTim Alexander
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POdisable foreign key check deleting InnoDB tables Perl Script
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POdisable foreign key check deleting InnoDB tables Perl Script
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POdisable foreign key check deleting InnoDB tables Perl Script
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo you want to remove the foreign key's indefinitly, or just have them temporarily not check anything while you're working with the database?
      singulars
      1. POdisable foreign key check deleting InnoDB tables Perl Script
      1. USWrikken
    2. CO(on a side note, the perl code could be: `my $sth = $dbh->prepare("SET FOREIGN_KEY_CHECKS = 0;");$sth->execute`)
      singulars
      1. POdisable foreign key check deleting InnoDB tables Perl Script
      1. USWrikken
    3. COYou can use `->do` in place of a prepare/execute pair if you're never going to use the prepared handle again :)
      singulars
      1. POdisable foreign key check deleting InnoDB tables Perl Script
      1. UShobbs
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload