MySQL comments causing messed up bind parameter count with Perl DBI

Question

I've got the following Perl code which makes a DBI call:

my $artsql = q{ *** SNIP A BUNCH OF SQL ***
                where a.article_id != ?
                      and at.type_name != 'List Element'   -- don't get list children
                      and aw.flowstate = 'Published'
                      and a.visible_as_article = 1 }
      . ( $filter ? q{and ch.channel_id = ?
                      and cat.category_id = ? }
                  : '' ) 
         . q{order by a.publish_date desc
                limit 5};

my @bind = ( $article );
push @bind, ( $channel_id, $category_id ) if $filter;

my $articles = $dbh->selectall_arrayref( $artsql, { Slice => { } }, @bind );

When $filter is on, this code was dying with the error:

DBD::mysql::db selectall_arrayref failed: called with 3 bind variables when 1 are needed

At first I thought this was a problem with the ternary conditional in the middle of the string, (I've been bitten by that bug multiple times) but it was correct. Dumping some debug values shows that the query and @bind array were being constructed correctly.

I then noticed that the query had a SQL comment right after the first bind variable, so on a whim I removed it. Poof, it worked!

According to the MySQL docs on comments,

MySQL Server supports three comment styles: From a “#” character to the end of the line. From a “-- ” sequence to the end of the line. In MySQL, the “-- ” (double-dash) comment > style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on).

Since the comment had -- followed by a space and (presumably) ended with the end of the line, why did MySQL choke? Is DBI doing something weird with newlines or whitespace behind the scenes?

Answer 1

There doesn't look to be anything wrong with your SQL or Perl code.

This could be a bug in DBI, DBD::mysql or MySQL itself. The first step in debugging this problem would be to find out which bit is at fault. So, start by eliminating the variables.

Start by eliminating the bind variables, hard code some values and see if the process correctly. If not then its probably a bug in DBD::mysql or DBI. First try updating both of them and see if the problem is fixed. If that doesn't work, report the bug. Note there's a similar comment parsing bug so it very may well be DBD::mysql. (You're sure it's do not get list children and not don't get list children?)

Next eliminate Perl from the equation. Run the query in the mysql shell (use \e to bring up an editor). Does it have the same problem? If so, then MySQL is at fault. Again, try upgrading.

Answer 2

I've seen similar things happen elsewhere. Most likely what happened is that somewhere in the various layers (Schwern is right that you'll have to dig to see which one) some piece of code is converting newlines to spaces for what must have seemed like a legitimate reason at the time, and so your comment is taking up the entire rest of the query.

The advice I give people is don't use single-line comments in SQL, unless using a command-line or other dedicated client. There's just too many layers involved and opportunity for hidden bugs.

Answer 3

Single quotes inside the comment screws it up. Don't know what is responsible for this bug. Change "don't get last child" to "Do not get last child" and your problem will go away.