Re: Vague DBI Error

[Ian Harisay]

How large did the file get when you were dumping 4.7 million rows?  Did 
you exceed the max file size for your OS?

Igor Korolev wrote:

Your "Terminated" did not come from DBI.  Your process got SIGTERM signal
from some monitoring process or an administrator.
-----Original Message-----
From: Jamin Roth [mailto:[EMAIL PROTECTED]
Sent: Friday, June 06, 2003 7:28 PM
To: [EMAIL PROTECTED]
Subject: Vague DBI Error
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);
}