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

This morning I was tinkering further with Hash lookups, Database lookups, and Scalability, trying to squeeze a little better performance out of the database query. In the CB, jZed mentioned that for my application, selectrow_array() might be an improvement over fetchrow_array(). I hadn't considered selectrow_array() because I hadn't noticed in the docs for DBI that you can pass it a statement handle to a pre-prepared SQL query. Perfect, that should be just the ticket. But when I made the change, I got a pretty little segfault.

I'm posting this question to see if anyone can shed any light on why the switch to selectrow_array() generated a segfault.

I am using DBI, DBD::SQLite version 1.07 (which is based on SQLite version 3.x), on a Win32 machine with ActiveState Perl 5.8.4. My DBI version is 1.45 (the latest). With respect to windows machines, the problem has been confirmed on both Win98 and WinXP. However, while collaborating in the CB with jZed and Corion, we discovered that the problem seems to exist cross-platform, including on Linux. jZed also discovered that the problem doesn't occur with DBD::SQLite2, which is based on the older SQLite 2.x engine.

Corion managed to determine that the problem occurs when placeholders are used, not when WHERE clause values are placed directly in the prepare() for the SQL statement.

The following is a snippet that Corion wrangled up, boiling the problem down to a simple test case. It is based loosely on my benchmarking code which originally produced the segfault. The failure appears to occur upon the second call to selectrow_array(), not the first call. Have a look at the code and see if any of you can cast additional light on the problem. I think it's a problem with the SQLite 3.x engine, but I'm not sure. 

use strict;
use warnings;
use Test::More tests => 4;
use DBI;

my $database = 'allwords.sqlite-v3';
unlink $database;

my( %forward, %backward );
my $dbh = DBI->connect( "DBI:SQLite:$database",
                        '', '',
                        { RaiseError => 1,
                          AutoCommit => 0 }
);

diag "Using DBD::SQLite $DBD::SQLite::VERSION";

diag "Creating database\n";
my @words = split /\s+/, <DATA>;
@forward{ @words } = reverse @words;

diag "Creating word-pair database...\n";
$dbh->do(
            "CREATE TABLE wordtable (
                  Left  varchar,
                  Right varchar
             ) "
);
my $sth = $dbh->prepare(
            "INSERT INTO wordtable
             ( Left, Right ) VALUES ( ?, ? )"
);
while ( my( $left, $right ) = each %forward ) {
            $sth->execute( $left, $right );
}
$sth->finish;
$dbh->commit;
diag "Database created.\n";

diag "Selecting (static value, 1st)\n";
my $value;
$sth = $dbh->prepare( "SELECT Right FROM wordtable WHERE Left = 'shine
+'" );

undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                   {},
            );
};
is( $value, 'shine', 'Got "shine"');

diag "Selecting (static value, 2nd)\n";
undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                {},
         );
};
is( $value, 'shine', "Got 'shine' and didn't crash");

diag "Selecting (placeholder, 1st round)\n";

$sth = $dbh->prepare( "SELECT Right FROM wordtable WHERE Left = ?" );

undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                   {},
                                   'shine'
            );
};
diag $@ if $@;
is( $value, 'shine', 'Got "shine"');

# This one crashes
diag "Selecting (placeholder, 2nd round)\n";
undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                {},
                                   'shine'
         );
};
diag $@ if $@;
is( $value, 'shine', "Got 'shine' and didn't crash");

$sth->finish();
$dbh->disconnect();
__DATA__
shine    shine

[download]

Curiosity abounds..........

Update: selectrow_arrayref() and selectall_arrayref() are also both similarly contaminated.


Dave

Replies are listed 'Best First'.
Re: New way to segfault: selectrow_array() with placeholders, DBD::SQLite
by pg (Canon) on Nov 01, 2004 at 02:02 UTC

    This same program seg fault with MySQL. SQL statements are slightly modified to satisfy mySQL syntax. Tested on Windows XP.

    use strict;
use warnings;
use Test::More tests => 4;
use DBI;

my $database = 'allwords.sqlite-v3';
unlink $database;

my( %forward, %backward );
my $dbh = DBI->connect( "DBI:mysql:test",
                        '', '',
                        { RaiseError => 1,
                          AutoCommit => 0 }
);

#diag "Using DBD::SQLite $DBD::SQLite::VERSION";

diag "Creating database\n";
my @words = split /\s+/, <DATA>;
@forward{ @words } = reverse @words;
#$dbh->do('drop table wordtable');

diag "Creating word-pair database...\n";
$dbh->do('create table wordtable(a varchar(10),b varchar(10))');
my $sth = $dbh->prepare(
            "INSERT INTO wordtable(a, b) VALUES ( ?, ? )"
);
while ( my( $left, $right ) = each %forward ) {
            $sth->execute( $left, $right );
}
$sth->finish;
$dbh->commit;
diag "Database created.\n";

diag "Selecting (static value, 1st)\n";
my $value;
$sth = $dbh->prepare( "SELECT b FROM wordtable WHERE a = 'shine'" );

undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                   {},
            );
};
is( $value, 'shine', 'Got "shine"');

diag "Selecting (static value, 2nd)\n";
undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                {},
         );
};
is( $value, 'shine', "Got 'shine' and didn't crash");

diag "Selecting (placeholder, 1st round)\n";
my $value;
$sth = $dbh->prepare( "SELECT b FROM wordtable WHERE a = ?" );

undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                   {},
                                   'shine'
            );
};
diag $@ if $@;
is( $value, 'shine', 'Got "shine"');

# This one crashes
diag "Selecting (placeholder, 2nd round)\n";
undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                {},
                                   'shine'
         );
};
diag $@ if $@;
is( $value, 'shine', "Got 'shine' and didn't crash");

$sth->finish();
$dbh->disconnect();
__DATA__
shine    shine

    [download]
[reply]
[d/l]

      Was okay with DBM. (for DBM, you cannot turn off AutoCommit)

      my $dbh = DBI->connect( "DBI:DBM:",
                        '', '',
                        { RaiseError => 1,
                          AutoCommit => 1 }
);

      [download]
[reply]
[d/l]

      However it does not fail with Access (ODBC).

[reply]
Re: New way to segfault: selectrow_array() with placeholders, DBD::SQLite
by erix (Prior) on Oct 31, 2004 at 21:13 UTC

    Just tried that code on win2k. As expected it fails there too ...

[reply]
Re: New way to segfault: selectrow_array() with placeholders, DBD::SQLite
by mpeppler (Vicar) on Nov 01, 2004 at 15:35 UTC
    DBD::Sybase (1.04_12, my development version) DBI 1.45, perl 5.8.3 on Fedora Core 2 runs straight through with no problems...

    Michael

[reply]
Re: New way to segfault: selectrow_array() with placeholders, DBD::SQLite
by shenme (Priest) on Nov 01, 2004 at 23:40 UTC
    Tim Bunce has asked that anyone with additional information about this segfault email him with the specifics.
    I'd appreciate it if everyone who can reproduce this problem please:
    a) send me (just me, not the list) the output of "perl -V" and
    "perl -MDBI -e 'DBI->installed_versions'"
    b) send me (just me) a stack dump, ideally from a DBI built using
    "perl Makefile.PL -g".
    c) try to reproduce it with earlier versions of the DBI
    I'll take a look at the information as soon as I can tomorrow.
    Tim.
    Re: Segfault discovered, possibly due to DBI 1.45
    Segfault discovered, possibly due to DBI 1.45
[reply]
Re: New way to segfault: selectrow_array() with placeholders, DBD::SQLite
by Jasper (Chaplain) on Nov 01, 2004 at 17:16 UTC
    The docs I have for DBI don't mention this ability to pass in a prepared statement handle. They all say selectrow_array($statement... not  selectrow_array($sth...). Perhaps I'm missing something. It is odd that it segfaults, and all, but this is undocumented behaviour.
[reply]
[d/l]
[select]

      If you are using DBI version 1.45, which is what I'm using, the POD makes the following statement:

      The $statement parameter can be a previously prepared statement handle, in which case the prepare is skipped.

      Look under the description for selectrow_array() in the POD for DBI 1.45 (the latest). It's there, and the ability to use a previously prepared statement handle is definately documented. The fact that it may cause a segfault is not. ;).


      Dave

[reply]
[d/l]
        Blummin heck, even when I'm looking for something specific I can't find it. This isn't a new feature.
[reply]

Back to Seekers of Perl Wisdom