in reply to Unexplained MySQL Error

I'd actually thought I'd done a good job of laying out the question, but judging from the replies (and the wonderful 0-reputation), it's quite clear I was in error.

So in a general answer: the sql query I'm having a problem with was exactly as in the original post:

SELECT parent FROM categories WHERE id=1

I'm using the DBI error handling code, and that's what the apache logs give as the query, so it's not a matter of my not knowing what the query was. I should have made that clear.

As I said, the most puzzling part of it is that when you replace "parent" with "name" or "id", the query executes fine, and returns the respective name or id. When I copy the query directly from the error log and run it in the mysql shell, it runs fine.

As per numerous requests, here's the offending code: 

sub getParentCategory {
  my ($child) = @_;
  my $res = getDb()->selectrow_arrayref("SELECT parent FROM categories
+ WHERE id=$child");
  return $res ? $res->[0] : undef;
}

[download]

Hope this helps you help me :) And for all of you looking for me eating my own words for thinking it could possibly be a problem in the db, and not in my code - keep an eye on this node - I'll post my solution if it's ever found.

Cheers
James Beninger

Replies are listed 'Best First'.
Re: Re: Unexplained MySQL Error
by dbwiz (Curate) on Jul 20, 2003 at 18:46 UTC
    And for all of you looking for me eating my own words for thinking it could possibly be a problem in the db, and not in my code - keep an eye on this node

    I hear you, and I can't wait to see that error in the db.

    But in the meantime, allow me to say something abut your code.

    • $child could be empty. You don't test if the sub has been passed a value at all.
    • I assume that getDb() is returning a database handler, which must either be global, since you don't pass any parameter, or it returns the result of a connection. But if the latter is true, you are not checking for an error, so this sub can fail.
    • If getDb does not fail, as I think it doesn't, since you actually got an error from the database, then you are executing a query, again without error checking.
    • Your query does not use placeholders. Your "id=$child" can only be correct if (a) id is numeric and (b) $child is not empty and contains a valid number, or (c) id is a text column and $child contains a quoted string . Since you aren't checking if $child has any meningful value, I believe that the reason for your query failure is exactly there.
    • If selectrow_arrayref does not fail, it can return either an array reference or undef for an empty set. Therefore, your checking for $res contents is redundant. You can just return $res straight away.

    OK. That was only three lines of code, and there were several things that are at least questionable.

    Whenever you find the "error in the database," I would be very curious to see it.

    Cheers

[reply]
      Thanks for the points - as you can see in my "solved" message earlier, I suspect a problem in the DBI package, not the database itself, and even that could be a misinterpretation of the DBI logging system by myself.

      To answer your concerns though, $child is checked before getParentCategory is called. It should never be empty, and if it is, it will result in an easily interpreted error. I log the query to the error logs as well as the SQL error so that I can easily locate the source of a problem. Perhaps there should be a die() involved, but that's neither here nor there.

      getDb() gets a global database handle that has been tested for success at the beginning of the script. And I can't simply return $res straight away, since I'm returning only the first element - not the reference itself - and if it's undef, then it will fail (undef->[0] causes an error)

      Cheers

[reply]
[d/l]
        Alright, I'm willing to take the XP loss here by posting this node, but I'd like to know why my rep is so low throughout this node. If my posts are so bad, I'd certainly like to know what's wrong with them so I don't make the same mistakes in the future - it looks bad for me and it wastes your time.

        The top node, I can understand. It looks like your standard "I'm getting a weird error and here's what I *think* is going to the database" post by someone who's never used a db before. but even after my clarification and solution, the scores have been abysmal compared to the surrounding ones scorning me for putting up such a spurious node.

        Yes, I've read Why did I get downvoted and realize that rep isn't all that important, but it is an indicator of how good your posts are, and it seems that my recent ones have been pretty useless. I'd like that improved, so feel free to lay into me and tell me exactly what you think. I'd prefer not to have this happen again.

        Cheers
        James Beninger

[reply]
Solved!
by jbeninger (Monk) on Jul 20, 2003 at 15:43 UTC
    Special thanks to blokhead for suggesting DBI->trace, which I was unaware of. It turns out that the problem wasn't in the query at all, but in a subsequent query. The reason it would fail on "parent" and work on "id" or "name" is that the latter would result in a different program branch - one that didn't lead to the real error location.

    I'd set $dbh->{ShowErrorStatement} = 1, but the error logs were showing the wrong query. I hesitate to suggest an error in the DBI package is more likely than my simply not understanding how it works - maybe someone could shed a bit more light on the issue?

    Once again, thanks for the help.
    James Beninger

[reply]

In Section Seekers of Perl Wisdom