Page 1 of 1

Incorrect arguments to RAND, on a new server

Posted: Mon Jul 17, 2006 10:28 am
by dotsc
Hi,

This is more of a mysql query question, I've been using the same scripts for over 2 years, never had any problems with them but now I have moved to a new server and I am getting this error when I try running the script:
select count(*) from list WHERE list_locationid='15' AND list_member!='' ORDER BY RAND(list.list_photo),RAND(NOW())

Incorrect arguments to RAND

Here is the query:

Code: Select all

$paging->Query("SELECT (TO_DAYS(CURDATE())-TO_DAYS(list_adddate)) AS daysFromAdd, (TO_DAYS(CURDATE())-TO_DAYS(list_update)) AS daysFromUpdate,list.* FROM list WHERE list_locationid='$location_id' AND $filter_section $city_filter ORDER BY RAND(list.list_photo),RAND(NOW())");
How can I get rid of this error, I can configure and install new scripts on my site but I still have a limited knowledge of php/mysql

Posted: Mon Jul 17, 2006 10:39 am
by Burrito
my guess would be different versions of MySQL.

have you tried running that query from the CLI?

Posted: Mon Jul 17, 2006 10:49 am
by dotsc
old server my sql version: mysql (4.0.27-standard)

new server mysql version: mysql (4.1.19-standard)

All my other scripts are running fine except for this 1 script. Could the mysql version possibly cause this error in just one of the scripts? Any one has any suggestions on how to correct my query?

Posted: Mon Jul 17, 2006 10:58 am
by Burrito
there are HUGE differences between those sql versions and a lot of them are changes to syntax. I'd be willing to bet that your query just doesn't work on 4.1.

try running the query from the CLI. If it doesn't work, you'll need to just rework the query to get it to work with 4.1.

Posted: Mon Jul 17, 2006 11:10 am
by RobertGonzalez
Take a look at the MySQL manual's Mathematical Functions. Do a page search for RAND.

To answer your other question, yes, there is a possibility that MySQL 4.1 could be considerably different from version 4.0 and below in many respects. You may also want to have a look at the change log to see what differences there are.

Another thing to note, also, is the server set up. Are there differences in server configurations that could have potentially been quieting errors though you didn't know it? Something to consider...

Posted: Mon Jul 17, 2006 11:51 am
by dotsc
thanks burrito and everah :-)

Did a little reseach and I came up with this code:

Code: Select all

$paging->Query("SELECT (TO_DAYS(CURDATE())-TO_DAYS(list_adddate)) AS daysFromAdd, (TO_DAYS(CURDATE())-TO_DAYS(list_update)) AS daysFromUpdate,list.* FROM list WHERE list_locationid='$location_id' AND $filter_section $city_filter ORDER BY RAND()");
And it works great!! I've removed the list.list_photo between the RAND ( ) and now the script runs nicely. E-Z fix, just needed some guideance.

I hope this helps someone to resolve a similar problem

Thanks!