Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

Unexplained MySQL Error

by jbeninger (Monk)
on Jul 20, 2003 at 01:48 UTC ( #275976=perlquestion: print w/replies, xml ) Need Help??
jbeninger has asked for the wisdom of the Perl Monks concerning the following question:

Greetings Monks,
This error has me completely stumped, so I'm hoping that one of you has run into the problem and may be able to help me. I'm getting an error (You have an error in your SQL syntax near '' at line 1) whenever I run a specific query in my script.

The Basics:

mysql -V: mysql Ver 11.18 Distrib 3.23.54, for redhat-linux-gnu (i386)

perl -v: v5.8.0

Table I'm having trouble with:
CREATE TABLE categories (id BIGINT, parent BIGINT, name VARCHAR(40), full_name VARCHAR(255))

Query I'm having trouble with:
SELECT parent FROM categories WHERE id=1

The categories table is a group of nested categories. Categories have an id, the id of a parent category, a name (eg "Birds"), and a full name (eg "Animals->Birds"). The query works in the mysql shell, but fails with the above error code when run as a cgi script. Every other database query has worked fine.

Even more puzzling, if I change the query to "SELECT id...", "SELECT full_name...", etc, it works fine. I thought maybe "parent" was an undocumented reserved word, so I changed the column name to "parent_id". Still no dice. This is frustrating me to no end - if anyone can give me an answer, I'd love to hear it.

Thanks in advance
James Beninger

Replies are listed 'Best First'.
Re: Unexplained MySQL Error
by blokhead (Monsignor) on Jul 20, 2003 at 03:22 UTC
    If you add the line
    DBI->trace(2, '/path/to/trace_output');
    ...somewhere before the errant call, you'll be able to see the actual SQL that DBI sent to MySQL, with all bind-parameters substituted. If you cut and paste that exact output into mysql command line and don't get a syntax error, then very odd things are afoot! Like the others, I have a feeling there's some quoting or variable substitution problem happening that you might not notice until seeing the final SQL result.

    Update: see also DBI's pod


Re: Unexplained MySQL Error
by bobn (Chaplain) on Jul 20, 2003 at 02:34 UTC

    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.

    --Bob Niederman,
Re: Unexplained MySQL Error
by rnahi (Curate) on Jul 20, 2003 at 08:31 UTC

    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.

Re: Unexplained MySQL Error
by antirice (Priest) on Jul 20, 2003 at 02:33 UTC

    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=

    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.

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

Re: Unexplained MySQL Error
by graff (Chancellor) on Jul 20, 2003 at 02:41 UTC
    What gets returned when your table is created/populated with a "parent" column and you use the mysql shell to run the query  "SELECT parent FROM categories WHERE id=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)?

Re: Unexplained MySQL Error
by jbeninger (Monk) on Jul 20, 2003 at 15:19 UTC
    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; }

    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.

    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.


        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)


      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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://275976]
Approved by Zaxo
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (9)
As of 2018-04-25 13:04 GMT
Find Nodes?
    Voting Booth?