PERL - DBD::mysql::st execute failed: MySQL client ran out of memory

Question

I've researched a bit and found that using mysql_use_result=1 is supposed to alleviate the memory issue. However, since I am new to this DBI module I don't understand what is happening here:

#!/usr/bin/perl -w

use strict;
use DBI;


my $dbh = DBI>connect('DBI:mysql:blah;host=blah.blah.blah.blah;mysql_use_result=1','blah','blah',{RaiseError => 1});

my $sth = $dbh->prepare('select * from TaqMinute where tradeDate<=\'2014-04-22\' and symbol<=\'AAPL\' ;') ;

if (defined($sth)) {
        $sth->execute();
    my @row;
    while (@row = $sth-> fetchrow_array()) {
       print "@row\n" ;
    }
}
$sth->finish();


$dbh->disconnect

Before I added mysql_use_result=1, the script would fail after about 1.5 minutes complaining that it ran out of memory. After adding this my query arguments are ignored and I simply get all the data in the database.

Any ideas how to help me or how to use this switch properly? Btw, the database that I am querying is very large.

Thanks in advance!

Craig

You should move the $sth->finish(); inside the if block because otherwise if $sth is undef, you'll still try to call ->finish against it. — Andy Lester
– Andy Lester, Commented Apr 23, 2014 at 19:57
If you're getting all the data in the table, then your query is matching every row, and mysql_use_result is delivering the result set without storing it in memory. Which is to say, why do you think you shouldn't be getting all the rows? Can you demonstrate that conclusively? — pilcrow
– pilcrow, Commented Apr 23, 2014 at 23:48
I'm not saying that I shouldn't be getting all the rows, I'm simply stating that when storing the data I run out of memory and the only solution I could find for this error message was to use mysql_use_result which IMO is the same as simply using select * without any arguments. I could be wrong, but this is my observation. Again, I am unfamiliar with the uses of the module and still learning about it. — user3566108
– user3566108, Commented Apr 24, 2014 at 10:49

user1126070 · Accepted Answer · 2014-04-25 08:49:16Z

1

Try to use parametized queries, maybe something wrong with your qoutes.

my $dbh = DBI>connect('DBI:mysql:blah;host=blah.blah.blah.blah','blah','blah',{RaiseError => 1});
my $sth = $dbh->prepare_cached('select * from TaqMinute where tradeDate<=? and symbol<=?') ;
die "sth undef!" if ! defined $sth;
$sth->execute('2014-04-22','AAPL');
while (my $rowref = $sth->fetchrow_arrayref()) {
 print Dumper($rowref) ;
}
$sth->finish;

edited Apr 25, 2014 at 8:49

answered Apr 24, 2014 at 9:43

user1126070

5,0691 gold badge18 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3566108 Over a year ago

Thanks for the advice, I will incorporate that into my scripts going forward, however I still get the memory error even with your recommendation.

user1126070 Over a year ago

You could try to use fetchrow_arrayref, it is a bit more efficient. You could try to 64 bit perl if nothing else works. I am just curious: how many rows and columns is in your table? Could use only one column? What type of fields do you have?

user3566108 Over a year ago

AFAIK, there are approximately 2000 stocks, 390 rows/day/stock (one minute increments over 6.5 hours), 252 days/year, 10 years, 28 columns... ~55,036,800,000 unique elements. ~1,965,600,000 rows. The output of your new code does not resolve the specific parameters that I desire; it outputs every row available. $VAR1 = [ 'AAII', '2004-02-26','330','4','15.5400','15.5300','15.5400', '15.5375','31075','2000','8','15.5300','15.5400','600','700', '2','15.5400','15.5400','15.5400','15.5400','23310','1500', '2','15.5300','15.5400','600','700',''];

user3566108 Over a year ago

I was able to get things going. It was my error in the MySQL syntax. Thank you all for your help.

Collectives™ on Stack Overflow

PERL - DBD::mysql::st execute failed: MySQL client ran out of memory

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related