SQL fails on prepared LIMIT 2005-05-29 - By Jason Dixon
Back
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/perl?unsub=mysql@(protected)
|
|
