Newbie: Statement handle inactive after successful execute of SELECT

From: Alan M. Carroll (amc_at_thought-mesh.net)
Date: 11/04/03

  • Next message: Michael A Chase: "Re: Newbie: Statement handle inactive after successful execute of SELECT" 
    To: dbi-users@perl.org
Date: Tue, 4 Nov 2003 14:24:35 -0600

    I'm working with a problem in Movable Type (a PERL web application) where
    certain operations fail. One place where it fails is in some code to create
    an iterator which walks a set of rows in a MySQL 3.23.58 DB using DBI 1.21.
    The code looks like this:

        my $sth = $dbh->prepare($sql) or return sub { };
        $sth->execute(@$bind) or return sub { };
        $sth->bind_columns(undef, @bind);

    What happens is the "bind_columns" fails with "Statement has no result
    columns to bind". I've put in some additional debugging code after the
    execute and before the bind_columns. Both "$sth->err" and "$sth->errstr" are
    undefined. The statement is *not* active. NUM_OF_FIELDS is zero. The execute
    returned "-1". The text of the statement (the contents of $sql) are

    select entry_id, entry_blog_id, entry_status, entry_author_id,
    entry_allow_comments, entry_title, entry_excerpt, entry_text,
    entry_text_more, entry_convert_breaks, entry_to_ping_urls,
    entry_pinged_urls, entry_allow_pings, entry_keywords, entry_tangent_cache,
    entry_category_id, entry_created_on, entry_created_by, entry_modified_on,
    entry_modified_by
    from mt_entry
    where (entry_status = ?)
    order by entry_created_on desc

    The entry_status has been bound to "2" (this was presumably done correctly
    or the execute would have failed, and I've verified that @$bind is correct
    via my debugging code). If I use PHPMyAdmin to do this directly it works
    (although PHPMyAdmin puts a LIMIT on the statement which may have an
    effect).

    This exact same code on the same host works correctly on a different
    database instance in the same MySQL process. Sometimes the code works on the
    problem database in other situations although I don't have any good criteria
    for distinguishing the working from non-working cases. As best I can tell,
    there are certain rows which, if they are in the result set, cause this
    failure. OTOH it may be size related (in this failure case, there are 16,792
    rows, while in the non-problem DB there are about 2800 rows). I've used all
    of the MySQL table repair options, none of which indicated any error in the
    tables.

    My question is, what can I look at to discover more information? I don't
    have access to the MySQL logs. I tried "DBI->trace(4)" but it had no visible
    effect. As far as I can tell from reading the documentation, this situation
    should be impossible, i.e. if the execute succeeds on a SELECT then the
    statement should be Active, even if no rows were selected (although in this
    case it should select all of the rows). Has anyone else seen this? What
    might cause that to happen? The statement handle isn't being re-used, it's
    created on the spot from the prepare. Are there any other methods I can call
    on the database or statement handle to get additional diagnostic
    information? If anyone has a handy DBI info script I can run I'd be happy to
    do that. Thanks.

  • Next message: Michael A Chase: "Re: Newbie: Statement handle inactive after successful execute of SELECT"

    Relevant Pages