Perl

Perl RSS

Problem with ORDER BY LIMIT in MySQL 4.1.22

Please support our Perl advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Jun 2009

Posts: 1

Reputation: trilbies is an unknown quantity at this point

Solved Threads: 0

trilbies

Offline

Newbie Poster

Problem with ORDER BY LIMIT in MySQL 4.1.22

Jun 24th, 2009

Hello. I am new and hope to find an answer to my query.

I run a classified database and have been doing so for quite a few years, I recently upgraded the Plesk CP on my server and suddenly a problem appeared with the way the DB is returning the queries.

I am currently running Perl 5 with MySQL 4.1.22

My old code was like this (trimmed code for simplification):

 Help with Code Tags 
 perl Syntax (Toggle Plain Text) 
use DBI;
 
$dbh = DBI->connect ("DBI:mysql:host=localhost;database=$SQL_DATABASE_NAME",
"$SQL_DATABASE_USERNAME",
"$SQL_DATABASE_PASSWORD", {printError => 0}) or die "Cannot connect to server: $DBI::errstr (DBI::err)";
 
$order_by = "token_key" if (!$order_by);
 
$sth = $dbh->prepare (qq~SELECT * FROM $adverts_database WHERE MATCH (advert_title,pedigree,text,area)  ORDER BY ? $show LIMIT $length~);
 
$sth->execute("$order_by");
use DBI;

$dbh = DBI->connect ("DBI:mysql:host=localhost;database=$SQL_DATABASE_NAME",
"$SQL_DATABASE_USERNAME",
"$SQL_DATABASE_PASSWORD", {printError => 0}) or die "Cannot connect to server: $DBI::errstr (DBI::err)";

$order_by = "token_key" if (!$order_by);

$sth = $dbh->prepare (qq~SELECT * FROM $adverts_database WHERE MATCH (advert_title,pedigree,text,area)  ORDER BY ? $show LIMIT $length~);

$sth->execute("$order_by");

The above used to work flawlessly, but since I upgraded the CP, the database stoped listing the adverts according to "order_by" and apparently listing them in icremental order from the db.

I had to correct the code like this to sort it out:

 Help with Code Tags 
 perl Syntax (Toggle Plain Text) 
$sth = $dbh->prepare (qq~SELECT * FROM $adverts_database WHERE MATCH (advert_title,pedigree,text,area)  ORDER BY token_key $show LIMIT $length~);
 
$sth->execute();
$sth = $dbh->prepare (qq~SELECT * FROM $adverts_database WHERE MATCH (advert_title,pedigree,text,area)  ORDER BY token_key $show LIMIT $length~);

$sth->execute();

I tried everything before arriving this this solution like puttingthe ? in quotes, removing the quotes around $order_by in the execute->() but nothing worked.

I am quite puzzled by this and having done some research on the web I initially though that it was caused by a MySQL bug affecting ODRER BY with LIMIT in certain JOIN operation, but this is nothing of the sort.

I am fairly shure that it is down to the CP upgrade as no activities took place other than the Control Panel upgrade (to Plesk 8.6 from 8.4)

Any feedback about this could be useful.

Many thanks.