Vague DBI Error

[Jamin Roth]

I designed a simple script to backup my MySQL databases.  Basically it loops
through all the databases then through each table.  This section of code
retreives the table and writes it to a csv file.  When I execute the code at
the end of this message on a table with 2.7 million rows it works fine.
When it's executed on 4.7 million rows it exits out with only "Terminated"
as the error message.  Is there any way I can get more information about
what is going on?  Could MySQL be timing out?  Should I split up the table
if it is over 2.5 million records (just do a limit in the SQL statement)?

Thank you for any help you can provide,

Jamin Roth
Systems/Network Administrator
Sorensen Associates Inc
Phone: (503) 665-0123 ext 234
Fax: (503) 666-5113
http://www.sorensen-associates.com/


#!/usr/bin/perl -w

# #################################################################
# # This Script performs a backup of the specified host
# #################################################################

use DBI;
use DBD::mysql;

$|=1;

#$db    = 'pathtracker';
$defaulthost    = '192.168.50.215';
#turkey ip = 192.168.50.197
#pteranodon ip = 192.168.50.215
$port = "3306";
$user = "backup";
$password = "12345";

$dsn = '';
$dbh = '';

# Use standard comma for delimination
$d = ",";

sub BackupHost { # Send host
        my $host = $_[0];

        $dsn = "DBI:mysql:host=$host;port=$port";
        $dbh = DBI->connect($dsn,$user,$password)
                or die "Can't connect to the database: $DBI::errstr\n";;

#       print "Finding Databases for $host\n";
        my $SQLQuery = 'SHOW DATABASES';
        my $sth = $dbh->prepare($SQLQuery)
                or die "Can't prepare SQL statement: $DBI::errstr\n";;
        $sth->execute()
                or die "Can't execute SQL statement: $DBI::errstr\n";;
        while(my $databases = $sth->fetchrow_arrayref) {
                $database = $$databases[0];
                print "Performing backup of $database database on $host\n";
                $dsn = "DBI:mysql:database=$database;host=$host;port=$port";
                $dbh = DBI->connect($dsn,$user,$password);
                # Backup each database in host
                BackupDatabase($host,$database);
        }
        warn "Data fetching terminated early by error: $DBI::errstr\n" if
$DBI::err;
        $dbh->disconnect or warn "Error disconnecting: $DBI::errstr\n";
}



sub BackupDatabase { # Send host and database
        my $host = $_[0];
        my $database = $_[1];

        my $SQLQuery = 'SHOW TABLES';
        my $sth = $dbh->prepare($SQLQuery)
                or die "Can't prepare SQL statement: $DBI::errstr\n";
        $sth->execute()
                or die "Can't execute SQL statement: $DBI::errstr\n";;
        while(my $tables = $sth->fetchrow_arrayref) {
                $table = $$tables[0];
                # Backup the table to CSV
                BackupTableToCSV($host,$database,$table);
        }
        warn "Data fetching terminated early by error: $DBI::errstr\n" if
$DBI::err;
}


sub BackupTableToCSV { # Send host, database and table
        my $host = $_[0];
        my $datbase = $_[1];
        my $table = $_[2];

        $outfile = "$host\_$database\_$table.csv";
        open(OUTFILE, ">${outfile}") || die "Cannot open $outfile for
output!\n";

        print "Creating $outfile";

        # Print column names
        my $SQLQuery = "SHOW COLUMNS FROM $table";
        my $sth = $dbh->prepare($SQLQuery)
                or die "Can't prepare SQL statement: $DBI::errstr\n";
        $sth->execute()
                or die "Can't execute SQL statement: $DBI::errstr\n";;
        my $row = $sth->fetchrow_arrayref();
        my $line = '';
        my $found = 0;
        foreach $field(@row) {
                if($found > 0) {$line = $line.$d; }
                $line = $line.$field;
                $found = 1;
        }
        warn "Data fetching terminated early by error: $DBI::errstr\n" if
$DBI::err;
        print OUTFILE $line."\n";


        # Print table contents
        $SQLQuery = "SELECT * FROM $table";
        print "Preparing $SQLQuery\n";
        $sth = $dbh->prepare($SQLQuery)
                or die "Can't prepare SQL statement: $DBI::errstr\n";
        print "Executing $SQLQuery\n";
        $sth->execute()
                or die "Can't execute SQL statement: $DBI::errstr\n";;
        print " which has ".$sth->rows." lines\n";
        while($row = $sth->fetchrow_arrayref) {
                my $line = '';
                my $found = 0;
                foreach my $field(@$row) {
                        if($found > 0) { $line = $line.$d; }
                        if($field) {
                                $line = $line.$field;
                        }else{
                                $line = $line."NULL";
                        }
                        $found = 1;
                }
                print OUTFILE $line."\n";
        }
        warn "Data fetching terminated early by error: $DBI::errstr\n" if
$DBI::err;
        close(OUTFILE);
        $sth->finish;
}



# Loop through command line arguments
if(scalar(@ARGV) > 0) {
        for ($i = 0;$i < scalar(@ARGV);$i++) {
                $host = $ARGV[$i];
                # backup each host
                BackupHost($host);
        }
}else{
        # backup default host
        BackupHost($defaulthost);
}