SQL fails on prepared LIMIT

I have a small-ish Perl application running beautifully on CentOS 3.4 
(RHEL 3 Update 4) using MySQL 3.23.58, Perl 5.8.0 and DBD::mysql 
2.1021.  However, another developer ran into problems with the SQL 
execution on his Fedora Core 2 system running MySQL 3.23.58 with Perl 
5.8.3 and DBD::mysql 2.9003-4.  The execute fails when using a 
placeholder for LIMIT.  Here is the SQL query with placeholders:

$query = "SELECT SUM(flow_octets) as bytes, ${direction}_port, 
protocol, agent_addr ";
$query   .= "FROM flows ";
$query   .= "WHERE ${direction}_port < 1024 ";
$query   .= "AND agent_addr=? ";
$query   .= "AND (if_index_in=? or if_index_out=?) ";
$query   .= "AND (from_unixtime(time_sec) >= date_sub(NOW(), interval ? 
hour)) ";
$query   .= "GROUP BY ${direction}_port, protocol ";
$query   .= "ORDER BY bytes desc ";
$query   .= "LIMIT ?";

The prepared query is then prepared and then executed with multiple 
values from CGI and an internal-only value:

$sth->execute($cgi->param('d'), int $cgi->param('i'), int 
$cgi->param('i'), ($cgi->param('t') || 1), int ($limit * 2)) ||
  die $dbh->errstr;

On his system, it fails with "You have an error in your SQL syntax.  
Check the manual that corresponds to your MySQL server version for the 
right syntax to use near ''20'' at line 1 at Monitor/Services.pm line 
33."  Right off, I figured it might have something to do with 
placeholders so we moved the "($limit * 2)" into the query and removed 
it from the execute.

...
$query   .= "LIMIT ($limit * 2)";
...
$sth->execute($cgi->param('d'), int $cgi->param('i'), int 
$cgi->param('i'), ($cgi->param('t') || 1)) || die $dbh->errstr;
...

After this, it complains "DBD::mysql::st execute failed: You have an 
error in your SQL syntax.  Check the manual that corresponds to your 
MySQL server version for the right syntax to use near '(10 * 2)' at 
line 1 at Monitor/Services.pm line 34."  We create a new variable just 
to hold this alternate value and adjust the query accordingly.

...
$query   .= "LIMIT $limit";
...
$sth->execute($cgi->param('d'), int $cgi->param('i'), int 
$cgi->param('i'), ($cgi->param('t') || 1)) || die $dbh->errstr;
...

The program finally runs without error, but I'm not sure why these 
changes are necessary.  As I mentioned before, the application runs 
fine on my own production RHEL server.  However, it fails identically 
on his FC2 box and another system I setup just to recreate this problem 
(OpenBSD 3.6-stable, MySQL 4.0.20, Perl 5.8.5 and DBD::mysql 2.9004).  
The common denominator in this appears to be DBD::mysql version 2.9, 
but I'm grasping at straws.  Anyone run into this before or know of any 
changes that I should be aware of to suggest this isn't a bug?

Thanks,

--
Jason Dixon
DixonGroup Consulting
http://www.dixongroup.net


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]