Simple db select output script hanging up.

punch_card_don has asked for the wisdom of the Perl Monks concerning the following question:

Chip Monks,

What possible reasons could cause my script to "hang" after outputting just 50 of 140 rows?

Here's a simple script snippet. We've selected all 140 rows from a mysql table, read them into a list of lists, and then cycle through the data structure to output to browser. Builds the data structure just fine, but then hangs up at about the 50th line of output.

BUT - if make one tiny change, outputting the inner output loop index $b instead of the array value $data$a $b, it runs to completion just fine.

??????????????????????

    $| = 1;
    print "Content-type: text/html\n\n";

    # get records
    @results = ();
    $sql = "SELECT * FROM my_table";
print "<br>sql = $sql<p>\n";
    $sth = $dbh->prepare($sql) or die("Could not prepare!" . $dbh->err
+str);
    $sth->execute() or die("Could not execute!" . $dbh->errstr);
print "<br>executed sql<p>\n";

    $k = 0;
    while (@results = $sth->fetchrow_array()) {
        $data[$k] = [ @results ];
        $k++;
    }
    $sth->finish;

    for $a (0 .. $#data) {
        print "<br>$a => ";
        for $b (0 .. $#{$data[$a]}) {
            print ", $data[$a][$b]";
        }
    }

print "<br>Done\n";
[download]

This script hangs.

But change $data[$a][$b] in the output loop to just $b, and it runs just fine.

Update: It appears to have nothing to do with which 50 lines it outputs. If I change teh outer output loop starting index to, for example, 30, then it outputs just fine to ~ row 80, then, hangs up indefinitely.

Update 2: It appears to have nothing to do with the volume of data output. If I change the central output code to something like print ",asd5f4as6d5f4as6d5f4a6s5d4f6sa5d4f6as5d4f6as5d4f, $b";
which creates several times more data to output than the original
print ", $data[$a][$b]";
yet it runs to completion with no trouble.

Update 3 - it appears to have something to do with the way the data structure is compiled. If I build the list of lists like this;

    for $i (0 .. 137) {
        print "<br>$i => ";
        for $j (0 .. 18) {
            $data[$i][$j] = "D".$i."B".$j;
        }
    }
[download]

It outputs all 138 rows and all values just fine. Hmmmm....

Significant Update - OK, it appears to have something important to do with nul fields. Not all columns in the db table have values in them. Some are empty, nul. I added a line to fill all elements of @results with some text:

if ($results[$j] eq "") {....}
[download]

and got told by Perl: "Use of uninitialized value in string eq ". Aha.

So, when a table has x-columns, but some of them are empty, nul,

while (@results = $sth->fetchrow_array()) {.....}
[download]

does NOT initialize all the x-elements of @results???

If this is the case, then it must be getting "hung" on all the error messages as I try to output hundreds of uninitialized array elements.

Forget that fear of gravity,
Get a little savagery in your life.

Comment on Simple db select output script hanging up. Select or Download Code

Replies are listed 'Best First'.
Re: Simple db select output script hanging up. by shmem (Chancellor) on Jun 22, 2007 at 17:15 UTC
Do't use `$a` and `$b` as loop variables. Don't use these variables anywhere else than in sort subroutines. They are special. --shmem _($_=" "x(1<<5)."?\n".q·/)Oo. G°\ / /\_¯/(q / ---------------------------- \__(m.====·.(_("always off the crowd"))."· ");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}	[reply] [d/l] [select]
Re^2: Simple db select output script hanging up. by punch_card_don (Curate) on Jun 22, 2007 at 17:22 UTC
New info for me - thanks. So I changed the output loop to: `for $i (0 .. $#data) { print "<br>$i => "; for $j (0 .. $#{$data[$i]}) { print ", $data[$i][$j]"; } }` [download] but, no change, exact same behaviour.	[reply] [d/l]
Re^3: Simple db select output script hanging up. by shmem (Chancellor) on Jun 22, 2007 at 17:49 UTC
Use Data::Dump::Streamer and dump your `@data` to STDERR. Then look into your web server's error log and check whether the data is what you expect; look for funky HTML tags in there. Also, why do you interpolate? `print ', ', $data[$i][$j];` [download] is better style IMHO. --shmem _($_=" "x(1<<5)."?\n".q·/)Oo. G°\ / /\_¯/(q / ---------------------------- \__(m.====·.(_("always off the crowd"))."· ");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}	[reply] [d/l] [select]
Re: Simple db select output script hanging up. by ozone (Friar) on Jun 22, 2007 at 17:49 UTC
replace `$k = 0; while (@results = $sth->fetchrow_array()) { $data[$k] = [ @results ]; $k++; }` [download] with `while($results = $sth->fetchrow_array_ref()) { push @data, $results; }` [download] and see if that makes any difference Also, it seems to me that you don't have 'use strict;' or 'use warnings;' - add them and you might find that Perl will point out an obvious error	[reply] [d/l] [select]
Re^2: Simple db select output script hanging up. by punch_card_don (Curate) on Jun 22, 2007 at 18:09 UTC
Another interesting suggestion - so I tried it (with the necessary typo correction from fetchrow_array_ref to fetchrow_arrayref) - no change. As for strict and warnings, the presented code is just the pertinent snippet.	[reply]
Re: Simple db select output script hanging up. by jeanluca (Deacon) on Jun 22, 2007 at 16:55 UTC
try `print ", $data[$a]->[$b]" ;` [download] instead of `print ", $data[$a][$b]" ;` [download] LuCa UPDATE: Change $a en $b in something else, like $x and $y	[reply] [d/l] [select]
Re^2: Simple db select output script hanging up. by punch_card_don (Curate) on Jun 22, 2007 at 17:06 UTC
Thanks for the idea, tried it, but with the result that, admittedly, I expected - no change. I should re-emphasize - it outputs the first 50 lines just fine. If it had been a syntax or referencing issue, you'd expect it to appear right away.	[reply]
Re: Simple db select output script hanging up. by graff (Chancellor) on Jun 23, 2007 at 15:19 UTC
So, when a table has x-columns, but some of them are empty, nul, `while (@results = $sth->fetchrow_array()) {.....}` [download] does NOT initialize all the x-elements of @results??? According to the description of fetchrow_array in the DBI manual: Null fields are returned as "undef" values in the list. This seems reasonable. If you really want to use "SELECT * ..." as your SQL statement, maybe you'd rather use fetchrow_hashref: it might be a little slower in terms of runtime, but then you'll have a little more structure in what you get back, so you can easily test the hashref elements for "undef" values and know which fields are null. Spending that extra runtime will save you from having to get serious about your SQL usage -- i.e. doing things like naming the specific fields that you want from the query, using "SELECT IF(field1,field1,'Null_field1'), IF(field2,field2,'Null_field2') ... " or similar nonsense to make sure that all returned fields get defined values. What a pain. :) (update: Actually, the proper SQL nonsense to use would most likely be something like `SELECT IFNULL(field1,'Null_field1'), IFNULL(field2,'Null_field2')..."` -- which will return empty strings and zeros when "non-null" fields are set to those values.)	[reply] [d/l] [select]
Re^2: Simple db select output script hanging up. by jZed (Prior) on Jun 23, 2007 at 17:33 UTC
I agree that named, specific columns are better in most cases than SELECT *, but if you don't have your constraints and NULLABLEs set correctly, you can still end up with NULLs where you don't expect them. So I'd say either die on undef (if you want to check that your database logic ensures no NULLs) or, something along the lines of: `while(my $results = $sth->fetchrow_arrayref) { push @data, [map {defined($_) ? $_ : ''} @$results]; }` [download]	[reply] [d/l]