Here is the script from my previous question:
#!/usr/bin/perl -w
# #################################################################
# # This Script performs a backup of the specified host
# #################################################################
use DBI;
use DBD::mysql;
$|=1;
#$db = 'pathtracker1';
#$defaulthost = '192.168.50.215';
#turkey ip = 192.168.50.197
#pteranodon ip = 192.168.50.215
$port = "3306";
$user = "backup";
$password = "********";
$dsn = '';
$dbh = '';
# Use standard comma for delimination
$d = ",";
my $host = '192.168.50.215';
my $database = 'pathtracker1';
my $table = 'mnhtn_stats_detail';
$dsn = "DBI:mysql:database=$database;host=$host;port=$port";
$dbh = DBI->connect($dsn,$user,$password,{RaiseError => 1,AutoCommit
=> 0});
$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";
}
$dbh->disconnect or warn "Error disconnecting: $DBI::errstr\n";
Thanks,
Jamin Roth
Systems/Network Administrator
Sorensen Associates Inc
Phone: (503) 665-0123 ext 234
Fax: (503) 666-5113
http://www.sorensen-associates.com/
-----Original Message-----
From: Stephen Clouse [mailto:[EMAIL PROTECTED]
Sent: Monday, June 09, 2003 2:12 PM
To: Jamin Roth
Cc: [EMAIL PROTECTED]
Subject: Re: Vague DBI Error
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, Jun 06, 2003 at 05:27:30PM -0700, Jamin Roth wrote:
> 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)?
If this is Linux, it sounds like the kernel OOM killer is sniping the
process.
Hopefully you're not trying to hold all those records in memory at once....
- --
Stephen Clouse <[EMAIL PROTECTED]>
Senior Programmer/DBE, Core Technology Developer
The IQ Group, Inc. <http://www.theiqgroup.com/>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)
iD8DBQE+5Pg4A4aoazQ9p2cRAsk1AJ48gukiiX9gJX8gkNupUmTK58YdKgCg4J1b
LKzYhEcTDG4YgAj8Dp6HeQ0=
=j8yu
-----END PGP SIGNATURE-----
