mysql prepare won't work outside of my loop

[wecc]

Hi,

I need help with the script below: I am uploading MILLIONS of data into a mysql table and I want to make it run faster by moving the prepare statement out of the loop. However, every time I do I recieve an error that says I have an error in my mysql syntax. I don't get it because it works perfectly fine inside the loop. Your help is greatly appreciated thank you!

#!/usr/local/bin/perl
#opening narr files

use warnings;
use strict;
use Math::Trig;
use DBI;

my ($id, $variance_speed, $variance_angle, $stnd_dev, @x, @y, $date, $hour, $dbh, $sth, $x, $y, $lat, $longi, $theta, $rho, $R, $pi, $rads, $degs, $phizero, $phi1, $phi2, $lambda0, $N, $F, $rhozero, $i, @files1, $file1, $filename, @uwind, $uwind, @vwind, $vwind, $resultant, $angle, $uw, $vw);

$R=6370.997;
$pi = (atan2 1,1) * 4;
$rads = $pi / 180;
$degs = 180 / $pi;
$phizero = 39 * $rads;
$phi1 = 33 * $rads;
$phi2 = 45 * $rads;
$lambda0 = -96;
$x = -5632.668;
$y = -4612.566;
$id = 0;

$N = log(cos($phi1) / cos($phi2)) / log(tan($pi / 4 + (($phi2) / 2)) / tan($pi / 4 + (($phi1)/ 2)));
$F = cos($phi1) * (tan($pi/4 + $phi1/2))**($N) / $N;
$rhozero = ($R * $F) / ((tan($pi/4 + $phizero/2))**$N);

###Open Directory to where the NARR text files are located
opendir (DIR, '1980\\1') or die "$!";

###Grabs all the files names that have "10m_u" and end in ".txt" and place them into @files1

$dbh=DBI->connect('dbi:mysql:narr_project','root','indent0') || die "Error opening database: $DBI::errstr\n";

@files1 = grep {/30m_u.txt*?/} readdir DIR;
foreach my $filename (@files1) {
$i = 0;
print "Uploading " . $filename . " into mysql 10m table\n";
print "---------------------------------------------------\n";

#Takes the filename from $filename and skips the first 11 characters
#then grabs the next 8 characters and puts them into $1. next
#space in filename is an underscore and then grabs the next
#four characters and puts them into $2 and ignores the rest

if ($filename =~ /.{11}(\d\d\d\d\d\d\d\d)_(\d\d\d\d)/) {
$date = $1;
$hour = $2;
}

#$filename now equals Directory to open one of text files (This
#will change to get the other text files one at a time as the foreach
#loop (above) cycles through. All below is inside that loop and
#repeats as the foreach loop cycles through each time

$filename = "1980\\1\\" . $filename;

#Open's the file with the data. First will be the U wind data

open(FILE, $filename) or die "$!";
@uwind = <FILE>;

$filename =~ s/u.txt/v.txt/;

open(FILE, $filename) or die "$!";
@vwind = <FILE>;

for ($y=-4612.566; $y<=4300; $y=$y+32.463) {
for ($x=-5632.668; $x<=5600; $x=$x+32.463) {

$id= $id + 1;
$i = $i + 1;

$sth=$dbh->prepare
("INSERT INTO 10m (ID, DATE, TIME, LATITUDE, LONGITUDE, X, Y, U, V, RESULTANT, VARIANCE_RESULTANT, ANGLE, VARIANCE_ANGLE)
VALUES ($id, '$date', '$hour', ?, ?, ?, ?, $uwind[$i], $vwind[$i], ?, ?, ?, ?)");

if ($uwind[$i] < 100 && $vwind[$i] < 100) {

$rho = sqrt($x*$x + ($rhozero - $y)*($rhozero - $y));
$theta = atan2($x,($rhozero - $y));
$lat = (2*atan(($R*$F/$rho)**(1/$N)) - $pi/2) * $degs;
$longi = (($theta/$N) * $degs) + $lambda0;

$resultant = sqrt($uwind[$i]**2 + $vwind[$i]**2);
$angle = (atan2($vwind[$i],$uwind[$i])) * $degs;
if ($angle<0) {
$angle=$angle + 360;
}

$variance_speed = 0;
$variance_angle = 0;

$sth->execute ($lat, $longi, $x, $y, $resultant, $variance_speed, $angle, $variance_angle) || die "Couldn't insert record : $DBI::errstr";
}
}
}
}
close(FILE);
close DIR;

 

[ishnid]

What error are you getting?

 

[RBergin]

Try passing all of the values in the execute statement.

In your other post "String variable won't work outside of loop" the error message you posted mentioned "Data truncated for column 'U' at row 1 at line 93, <FILE>". What does line 93 of your data file look like?

 

[wecc]

Line 93 is the "Execute statement"

<CODE>

$sth->execute ($lat, $longi, $x, $y, $resultant, $variance_speed, $angle, $variance_angle) || die "Couldn't insert record : $DBI::errstr";

</CODE>

The error is the "Data truncated error", but only when I move the prepare outside the loop and place the $uwind[$i] and $vwind[$i] in the execute statement.

 

[RBergin]

Please show the updated code after moving the prepare statement and post the complete error message.

It would also be helpful for you to fix your indentation and declare the vars in the smallest scope they require.

 

[wecc]

The error is:

DBD::mysql::st execute failed: Data truncated for column 'U' at row 1 at line 93, <FILE> line 193348.
Couldn't insert record : Data truncated for column 'U' at row 1 at line 93, <FILE> line 193348.

This is the updated version when I move the prepare statement and the corresponding error that comes with it.

<CODE>

$filename = "1980\\1\\" . $filename;

#Open's the file with the data. First will be the U wind data

open(FILE, $filename) or die "$!";
@uwind = <FILE>;

$filename =~ s/u.txt/v.txt/;

open(FILE, $filename) or die "$!";
@vwind = <FILE>;
$sth=$dbh->prepare
("INSERT INTO 10m (ID, DATE, TIME, LATITUDE, LONGITUDE, X, Y, U, V, RESULTANT, VARIANCE_RESULTANT, ANGLE, VARIANCE_ANGLE)
VALUES (?, '$date', '$hour', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
for ($y=-4612.566; $y<=4300; $y=$y+32.463) {
for ($x=-5632.668; $x<=5600; $x=$x+32.463) {

$id= $id + 1;
$i = $i + 1;



if ($uwind[$i] < 100 && $vwind[$i] < 100) {

$rho = sqrt($x*$x + ($rhozero - $y)*($rhozero - $y));
$theta = atan2($x,($rhozero - $y));
$lat = (2*atan(($R*$F/$rho)**(1/$N)) - $pi/2) * $degs;
$longi = (($theta/$N) * $degs) + $lambda0;

$resultant = sqrt($uwind[$i]**2 + $vwind[$i]**2);
$angle = (atan2($vwind[$i],$uwind[$i])) * $degs;
if ($angle<0) {
$angle=$angle + 360;
}

$variance_speed = 0;
$variance_angle = 0;

$sth->execute ($id, $lat, $longi, $x, $y, $uwind[$i], $vwind[$i], $resultant, $variance_speed, $angle, $variance_angle) || die "Couldn't insert record : $DBI::errstr";
}
}

</CODE>

 

[RBergin]

<FILE> line 193348

That part of the error message seems odd since <FILE> was slurped into an array and at this point <FILE> has nothing to do with the sql statement.

Try changing it to this:

open(my $FILE1, '<', $filename) or die $!;
my @uwind = <$FILE1>;
close $FILE1;

$filename =~ s/u.txt/v.txt/;

open(my $FILE2, '<', $filename) or die $!;
my @vwind = <$FILE>;
close $FILE2;

my $sth = $dbh->prepare("INSERT INTO 10m (ID, DATE, TIME, LATITUDE, LONGITUDE, X, Y, U, V, RESULTANT, VARIANCE_RESULTANT, ANGLE, VARIANCE_ANGLE)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
);

......
......
......
......
......
$sth->execute ($id, $date, $time, $lat, $longi, $x, $y, $uwind[$i], $vwind[$i], $resultant, $variance_speed, $angle, $variance_angle) || die "Couldn't insert record : $DBI::errstr";

 

[RBergin]

If you still receive the same error, you should add a print statement above the execute statement that outputs the data to be inserted, namely $uwind[$i].

Since the error states that the data was truncated, it would appear that it's attempting to insert more data in that field than what the db schema allows.

 

[TrojanWarBlade]

I'm wondering if there is some buffer limit at play here.
Maybe you should try building multiple statements (instead of 1) and execute them one by one.
I suggest no more than 1000 records per statement.



Trojan.

 

[TrojanWarBlade]

Actually, if you truly have 900 million records to load then I revert to my original suggestion that you use create a CSV file or similar and bulk load the data.



Trojan.

 

[wecc]

Ok, I have decided to bulk load. I will have multiple files that I want to load but I want the data to be average. Is it possible to bulk load one file into mysql than take that data and average it with the next file that is about to be bulk loaded in? How would I be able to do this if so? Thank you for all the help too!! It is much appreciated!

 

[TrojanWarBlade]

I so wish you'd kept this to just one thread!

:-(


Trojan.