DBI->trace(2, '/path/to/trace_output');
[download]

Update: see also DBI's pod

blokhead

Impossible to know without the perl code that provokes the error, thought given the '' in the error message, I'd guess it's a quoting issue or some such in the way you're constructing the query.

It would be much easier to answer your question if you followed some simple guidelines.

You think the error is in the database, but it is more likely to be in your code. However, if you don't show the code, we can't be of much help.

Please read Before asking a database related question ... and ask again.

How are you building and executing the query in your script?

After running a few possible permutations of your query, it seems to be that you're attempting the following query:

select parent from categories where id=
[download]

Again, check how you are building the query. You aren't using placeholders since doing that would evaluate as id=NULL and properly execute (albeit, not with the results for which you were looking).

Hope this helps.

antirice    
The first rule of Perl club is - use Perl
The ith rule of Perl club is - follow rule i - 1 for i > 1

Might the record with "id=1" be one that has "no parent" (i.e. a null value in this column)? If so, might it be someplace else in your perl code (where maybe you try to use the value returned by this query in some subsequent query) that is actually generating the error message (because you're using an empty string where you need a non-empty one)?

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

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

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

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