Full Text

I was in the middle of upgrading the Wasington Parkour forum software to the new phpBB 3.0 when I got distracted by a MySQL limitation I wasn’t previously aware of: it seems you can’t drop more than one table at a time in the MySQL command line interface. This curious limitation begs for a command-line script solution, and indeed I was not surprised to find that there are plenty of them out there. As usual, though, I’m reinventing the wheel for my own entertainment.

For today’s exercise, I experimented with PHP on the command line (PHP CLI). Some of the features I wanted to include weren’t in the scripts I found elsewhere, so I built them in order to make this useful for my current phpBB upgrade and on into the future for various other situations:

  • Allow the user to store db login information in the script if he really wants to, but if he’s a security-conscious and relatively sane person, leave it empty in the script and prompt him for the login information when the script is called.
  • Allow the user to specify table prefixes so only certain tables are deleted. When I experienced epic failure on my first attempted phpBB 2 -> phpbb3 conversion, I had to go back and remove all the bad tables that had been created. They were prefixed with phpbb3_ while the old tables were prefixed with phpbb_. Obviously I wanted to leave the old ones alone.
  • Provide some useful reporting on any errors encountered and results of the batch drop.

So, here’s what I have for you today: a PHP CLI script to batch drop all your unwanted MySQL tables (after the fold).

Example script session:

================================================================
MySQL Batch Table Drop started at Sat, 01 Mar 08 05:54:07 +0000
================================================================

This script REMOVES ALL TABLES from a MySQL db.
Is that REALLY something you want to do? (y/n) y
You have saved login information.
Use saved data? (y/n) y
Checking for missing data ...
Enter the DATABASE NAME: dbname
Enter the DATABASE USERNAME: dbuser
Enter the DATABASE PASSWORD: password
Ready to go!
Delete tables with prefix 'phpbb3_'
in db 'dbname' using login 'dbuser'? (y/n) y
RESULTS ...
-=> Found 110 table(s) matching criteria.
-=> Dropped 110 table(s) (100.00% of attempted drops).
-=> Left 96 table(s) alone.
Exiting.
 

PHP source code (or download here):

<?php

/* MySQL Batch Remove Tables        */
/* Jeremy Modjeska (www.daqron.com) */
/* 02.29.08 */ 

// =============================================================== //
// Global Vars

global $db_location, $username, $password, $db_name, $table_prefix;
global $usesaved, $sure, $reallysure, $errors;

// =============================================================== //
// CONFIG

// You can store database login information here
// but it's probably not a good idea. 

// If you just leave this blank, the script will
// prompt you to enter it each time it runs; that
// is a safer plan! But, in case you hate yourself ...

          $username     = "";
          $password     = "";
          $db_name      = "";
          $table_prefix = "";

// DB location (probably localhost).
// OK to leave this set in config.

          $db_location  = "localhost";

// =============================================================== //
// GATHER LOGIN DATA

echo "\n\n\n\n\n\n\n\n";
echo "\n================================================================\n";
echo "MySQL Batch Table Drop started at " . date(DATE_RFC822);
echo "\nPress CTRL+C to exit at any time";
echo "\n================================================================";
echo "\n\n";

fwrite(STDOUT, "This script REMOVES ALL TABLES from a MySQL db.\n");
fwrite(STDOUT, "Is that REALLY something you want to do? (y/n) ");
$sure = trim(fgets(STDIN));

// Check for saved data; see if we're going to use it

if ( $username != "" || $password != ""
     || $db_name != "" || $table_prefix != "" ) {

  echo "\n\n\n";

  fwrite(STDOUT, "You have saved login information.\n");
  fwrite(STDOUT, "Use saved data? (y/n) ");
  $usesaved = trim(fgets(STDIN));

}

// Case if ignoring saved data or no saved data
// Gather login information

if ( $usesaved != "y" && $sure == "y" ) {

  echo "\n\n\n";

  fwrite(STDOUT, "Enter the DATABASE NAME: ");
  $db_name = trim(fgets(STDIN));

  fwrite(STDOUT, "Enter the DATABASE USERNAME: ");
  $username = trim(fgets(STDIN));

  fwrite(STDOUT, "Enter the DATABASE PASSWORD: ");
  $password = trim(fgets(STDIN));

  echo "\n\n\n";

  fwrite(STDOUT, "If you only want to delete tables with a \n");
  fwrite(STDOUT, "with a certain prefix, enter it here (else return): ");
  $table_prefix = trim(fgets(STDIN));

}

// Case if saved data and we're using it
// Validate that we have what we need

elseif ( $usesaved == "y" && $sure == "y" ) {

  echo "\n\n\n";

  echo "Checking for missing data ... \n";

  if ( $db_name == "" ) {
    fwrite(STDOUT, "Enter the DATABASE NAME: ");
    $db_name = trim(fgets(STDIN));
  }

  if ( $username == "" ) {
    fwrite(STDOUT, "Enter the DATABASE USERNAME: ");
    $username = trim(fgets(STDIN));
  }

  if ( $password == "" ) {
    fwrite(STDOUT, "Enter the DATABASE PASSWORD: ");
    $password = trim(fgets(STDIN));
  }

  if ( $table_prefix == "" ) {
    echo "\n\n\n";
    fwrite(STDOUT, "If you only want to delete tables with a \n");
    fwrite(STDOUT, "certain prefix, enter it here (or hit return): ");
    $table_prefix = trim(fgets(STDIN));
  }

  echo "Ready to go!";

}

// Otherwise we got a "no" and we should exit

else {
  exit ("Exiting.\n\n\n");
}

// =============================================================== //
// CONFIRM ACTION

// Now we have all the data. Double-check that we want to
// proceed and validate the action.

echo "\n\n\n";

if ( $table_prefix ) {
    fwrite(STDOUT, "Delete tables with prefix '$table_prefix' \n");
    fwrite(STDOUT, "in db '$db_name' using login '$username'? (y/n) ");
    $reallysure = trim(fgets(STDIN));
}

else {
  fwrite(STDOUT, "Delete all tables in db '$db_name' \n");
  fwrite(STDOUT, "using login '$username'? (y/n) ");
  $reallysure = trim(fgets(STDIN));
}

// We're sure. Delete stuff.

if ( $reallysure == "y") {
  DeleteTables();
}

else {
  exit("Exiting.\n\n\n");
}

// =============================================================== //
// ERROR Subroutine

function errOut($errstr) {

  echo "Uh Oh! MySQL reported an error. The error was: \n\n";
  echo $errstr;
  echo "\n\n";
  exit("Exiting.\n\n\n");

}

// =============================================================== //
// DELETE Subroutine

function DeleteTables() {

  global $db_location, $username, $password, $db_name, $table_prefix;

  echo "\n\n\n";

  // Connect to MySQL

  if (!$link = @mysql_connect($db_location, $username, $password)) {
    errOut("Could not connect to MySQL: " . mysql_error());
  }    

  // Initialize table counter

  $first_count = 0; 

  // Get all the tables in the given database

  $sql = "SHOW TABLES FROM $db_name"; 

  if ( $result = @mysql_query($sql) ) { 

    while ( $row = @mysql_fetch_row($result) ) {
      $ftbls[] = $row[0];
      $first_count++;
    } 

  } 

  else {
    errOut("Error getting tables: " . mysql_error());
  }    

  // Make sure we got some tables. If not, report and exit.

  if ( $first_count == 0 ) {
    $req = "";
    if ( $table_prefix != "" ) {
      $reqsum = " with prefix $table_prefix";
    }
    echo "No tables were found$req. \n";
    exit("Exiting. \n\n\n");
  }

  // Initialize counts for reporting

  $found_count = 0;
  $removed_count = 0;

  // Drop each table
  // If a prefix is given, drop only those tables

  foreach ( $ftbls as $tbl ) { 

    if ( strncmp($tbl, $table_prefix, strlen($table_prefix)) == 0 ) { 

      echo "DELETE $tbl ... \n";
      $found_count++; 

      $sql = "DROP TABLE $db_name.$tbl"; 

      if ( $result = @mysql_query($sql) ) {
        echo "Dropped table $tbl. \n";
        $removed_count++;
      } 

      else{
        errOut("Error dropping $tbl: " . mysql_error());
      } 

    }

  }

  // Do some math and report results

  echo "\n";
  echo "RESULTS ... \n";

  $left_alone = $first_count - $found_count;
  $missed_tbls = $found_count - $removed_count;

  if ( $found_count > 0 ) {
    $success = ( $removed_count / $found_count ) * 100;
    $success = number_format($success, 2, '.', '');
  }
  else {
    $success = 0;
  }

  echo "-=> Found $found_count table(s) matching criteria. \n";
  echo "-=> Dropped $removed_count table(s) " .
       "($success% of attempted drops).\n";

  if ( $left_alone > 0 ) {
    echo "-=> Left $left_alone table(s) alone.\n";
  }

  if ( $missed_tbls > 0 ) {
    echo "-=> Failed to drop $missed_tbls table(s).\n";
  }

  echo "\nExiting.\n\n\n";

}

?>
  • Digg
  • del.icio.us
  • Google Bookmarks
  • Reddit
  • StumbleUpon
  • Technorati
  • Facebook
  • LinkedIn
  • Twitter
Posted in Code  |  No Comments

Leave a Reply